Ad Hoc Views, Reports, and Custom Report Development
This page provides best practices for your Advanced Reporting views, domains, and custom reports developed in Jaspersoft Studio.
This page provides best practices for your Advanced Reporting views, domains, and custom reports developed in Jaspersoft Studio.
Best Practices for Ad Hoc Views
To reduce performance issues and generate the desired results for your advanced reports using Jaspersoft, follow these best practices when using the Ad Hoc Viewer and the domains.
Do Not Select All Domain Fields Into Ad Hoc Views
CA PPMdomains contain hundreds of fields and measures so you can build ad hoc views that meet your business requirements. When building ad hoc views, try not to select all the fields in the domain into your view as this causes your view to perform slower. Only select the fields into you view that you plan to use. Later, if needed, you can add more fields to your view by clicking the icon to the right of the domain name and then clicking Select Fields.
The following image shows how to add more fields to your ad hoc view.
Limit the Size of Crosstab Ad Hoc Views
The Crosstab ad hoc views are more complex with aggregations and calculation functions, so they generally perform slower than table or chart views. When building and using crosstab views, limit the size of your views whenever possible to improve performance.
Create Ad Hoc Views in No Data Mode
You can create ad hoc views using three different data modes, full data, sample data, and no data. Create new ad hoc views using either No Data or Sample Data mode because the performance is much better. Later, once the view is complete, you can switch to Full Data mode.
The following image shows how to select the correct data mode when creating a new ad hoc view.
Select the Correct Metrics
CA PPMdomains, except for the Custom Master Objects domain, contain three different period types and a set of metrics for use with each period type. If the wrong metrics are selected, you do not get the expected results. For example, when looking at weekly data, use fields in the Weekly Periods set (such as Week Start Date) combined with measures from the Weekly Periods set. Do not use fields from the Calendar Periods or Fiscal Periods sets with measures from the Weekly Periods set. Also, the Summary Totals metric set is typically not for use with period types because it is summarized data and not periodic data.
The following image shows how to use weekly measures with weekly period sets.
The following field sets correspond to the measure sets described for each period type. Try not to cross the period types:
Weekly Period Type. Use Weekly Periods field set with the Weekly Periods measures set.
Calendar Period Type. Use Calendar Periods field set with the Calendar Periods measures set.
Fiscal Period Type. Use Fiscal Periods field set with the Fiscal Periods measures set.
Summary Totals.The summary totals are metrics that are not aggregated by any period type, they are summary data and not periodic data.
Each object fields set within a domain is for use with a corresponding object measures set. For example, when building ad hoc views against the Project Management domain that contain monthly team data, select metrics from the Team set under the Calendar Periods measures set. The measures under the Calendar Periods Team set are aggregated and grouped at the team level.
The following image shows how to select metrics from the Team set under Measures when working with team data in the Project Management domain.
The following object measure sets are aggregated and grouped in the Project Management domain as described. The other
CA PPMdomains follow a similar pattern.
Project metric sets. Use the project metrics aggregated and summarized at the project level with project fields.
Team metric sets. Use the team metrics aggregated and summarized at the team level with team fields.
Task metric sets. Use the task metrics aggregated and summarized at the task level and with task fields.
Assignment metric sets. Use the assignment metrics aggregated and summarized at the assignment level with assignment fields.
Use OBS Data Correctly
When selecting and displaying OBS information in ad hoc views, verify that you filter for a specific OBS Type. Otherwise, when resources and investments are attached to different OBS which they frequently are, you get duplicate results.
Use Portfolio Data Correctly
Some of the domains contain a Portfolio set that includes fields that are frequently used for filtering and grouping of investments by portfolios. The portfolio fields are included primarily for filtering and for grouping investment data by portfolio. Currently the domains do not include portfolio rollups or other complex portfolio structures like scenarios.
Use Financial Data Correctly in the Financial Management Domain
The Financial Management Domain basically contains financial planning and financial transactions data. Each of these data is stored at a different granularity level and do not have a direct relationship to each other. For example, financial plans are stored at a fiscal period level and transactions are stored at a transaction level. To avoid unexpected results, do not use the financial planning fields and metric sets along with the financial transactions fields and metric sets in the same ad hoc view. Use the transactions sets in this domain to look at actual transactions at a detail level. The actual cost, actual unit, actual revenue, and realized benefit metrics are aggregated at a fiscal period level within the Financial Plan metric sets. Use these metrics when viewing financial planning data with actuals.
To make it easier to develop reports against the Data Warehouse, financial planning property data has been combined in the same row with the financial planning detail data. This is important to understand when building ad hoc views using the Financial Plan sets in the Financial Management domain. When you select financial planning property data without selecting financial planning detail data, you get multiple occurrences of the financial planning property data because it displays for every occurrence of the financial planning detail data. Displaying the financial planning property data without any corresponding detail data is not a common scenario so you might not encounter this issue.
Use Time Data Correctly in the Time Management Domain
The Time Management domain contains timesheet data and missing time data. When using this domain to create ad hoc views, select either timesheet data or missing time data. Try not to combine the two data types in the same view as you can get unexpected results.
Reconcile Dates in CA PPM and Ad Hoc Views for Time Zone Differences
The Advanced Reporting domains display dates without the time element, except for system dates. In
CA PPM, most users set the display of dates as date, without a time element, and the display of system dates as date and time. Some examples of system dates are "Created Date" and "Last Updated Date". The following dates are not considered system dates, instead, we refer to these dates as business dates:
- Investment start and finish dates
- Task start and finish dates
- Assignment start and finish dates
As long as you display system dates in
CA PPMas date and time (with a time element), the system dates match the dates in the Advanced Reporting domains. As long as you display other dates such as business dates in
CA PPMas date (without a time element), they match the dates in the Advanced Reporting domains. Custom attributes that you create as dates in
CA PPMare not considered system dates and display as date (without a time element) in Advanced Reporting. These dates never adjust according to time zone.
When you execute a report immediately, there is no time zone adjustment for the business dates. Time zone adjustment is made only for the system dates. The adjustment is made based on the time zone defined for the resource (you) in
CA PPM, Home, Account Settings.
When you schedule a report and select a time zone to create the report schedule, there is no time zone adjustment for the business dates. Time zone adjustment is made only for the system dates. The adjustment is made based on the time zone that you select in Advanced Reporting, Output Options when creating a schedule.
: By default, the date fields that you select to display in the ad hoc views display as date (without a time element) even for system dates. When creating an ad hoc view, you can modify the date format for the system dates to include a time element as illustrated in the following image:
Export Ad Hoc Views and Reports
The maximum number of report pages that you can generate is 500 pages for both scheduled and unscheduled reports.
The limit applies to all reports and ad-hoc views that are exported using paginated options such as Excel (Paginated) and XLSX (Paginated). The limit is not applicable to the ad-hoc views exported using non-paginated export options such as Excel and XLSX.
The following image shows the export options that have a limit to the maximum number of pages that are exported.
Best Practices for Domains
We recommend the following best practices for using the domains.
Keep Domain Size Smaller than 2 MB
Avoid creating domains that are greater than 2 MB in size. On exceeding the size limit, you can get errors when editing the domain in the domain designer.
Do Not Exceed 50 Tables or Views in a Domain
Jaspersoft recommends that you include less than 50 tables or views in a domain else you can get errors when building the domain.
Do Not Build Jaspersoft Studio Reports Based on Domains
We recommend that you do not build your Jaspersoft Studio reports based on domains. Jaspersoft also recommends that you do not use domain-based Studio reports due to known performance and functional issues. Instead, develop your Jaspersoft Studio reports using direct SQL statements. If you build domain-based Studio reports, the report behavior can be different in the Studio client than when you publish it on the server.
Do Not Migrate the Default System Domains
Try not to migrate the out-of-the-box domains from one environment to another because custom objects and attributes added to the domain might exist in one environment but not in another. Run the Load Data Warehouse job in each environment so the domains automatically include the correct custom objects and attributes for that particular environment. Custom domains are not automatically updated to include custom objects and attributes so you must manually maintain these domains.
Migrate Custom Domains
Migrate custom domains from one environment to another by performing the following steps:
- Extract the domain file from the environment that you are migrating from.
- Edit the domain using the Domain Designer and select Export Design. Next, save the domain XML file locally.The following image shows the domain designer from where you can export the domain design.
- Edit the domain XML file using any XML editing tool for any differences in custom objects and attributes that might exist in the original environment but not in the migrated environment. Also, change the schema name to the new environment in the XML file.
- Create or update the domain in the new environment.
- In the new environment, select to Upload the domain XML file produced in step 2 above while creating or editing the custom domain. The following image shows how to upload the domain XML file.: Verify that the schema file name that you upload does not contain any spaces or special characters.
- Click Submit. If there are any differences between the environments you missed during step 3 above, you get an error showing the details of these differences. Fix your domain XML file and then try again.
Do not customize OOTB domains for Reporting
It is recommended to not customize the OOTB domains for Jaspersoft reporting.
Best Practices for Custom Report Development
Use Jaspersoft Studio to build and manage your own custom reports in Advanced Reporting. As a prerequisite, set up the CA JDBC Adapter to connect to the
CA PPMtransactional or Data Warehouse schemas without a VPN connection.The adapter lets you run and test report queries faster and more efficiently.
The following guidelines for developing reports using Jaspersoft Studio represent the most efficient course of action. We highly recommend that you follow these best practices.
Start the Report
- Install and use the Jaspersoft Studio version that corresponds to theCA PPMversion that you have in your environment. Avoid installing more than one version of Jaspersoft Studio on the same computer. If you are using multiple CA products (for example, CA PPM and ITSM) that support different versions of the Jasepersoft server, we recommend that you install the latest version of Jaspersoft Studio. Next, configure the Jaspersoft Studio connection to work with the corresponding version of JasperReports library. If you are using a functionality that is only available in the latest version, you can encounter issues when trying the functionality on an earlier version.
- Try to use existing out of the box (OOTB) reports as examples when you start developing your custom reports. The PMO Accelerator Advanced Reporting content includes more than 50 reports that you can use as a starting point.
- Do not modify the reports under theCA PPMfolder. Make a copy of the OOTB report and modify the copy.
- Organize the folder structure for your custom content similar to theCA PPMfolder organization, taking into consideration how you want to manage security. You can allow access at the folder level or at the report level.
- Create your reports outside theCA PPMfolder, not under it. Create a folder under the organization for your own reporting content or use the Shared folder when the reports are available to all users.
- Provide an ID that identifies the report as your custom report when you create a new report. For example, start with your company name or abbreviation.
- When copying and modifying an OOTB report, use the same procedure that you use to create a report. Provide a new ID and use the JRXML file of the OOTB report to upload the copy.
- Follow the rules that are required by Jaspersoft, avoiding spaces and special characters when you create the JRXML file name that you use to upload the report. We recommend CamelCase Notation.
Design the Report
- Limit the number of input controls that you include in your report. Only include input controls that are relevant for the report.
- Always try to categorize the data when you have input controls with high cardinality (values that are uncommon or unique, such as resources or investments). Use cascading input controls when possible.
- Create resources in the repository, such as input controls and images, so that you can use them for more than one report. Take advantage of the resources that are already created and avoid redundancy. For example, input controls that are used by one report should be created outside the report unit so that you can use them in other reports that you create later.
- Create a report template that contains headers, styles, company logos, page footer, and other definitions before starting a project. Report templates save time and effort and help reports have a consistent look and feel.
- Keep a backup copy of the JRXML files that you create or modify. Store these copies in a source control tool or any other system or collaboration tool that you use to control source code.
Develop the Report
- We do not recommend that you develop reports in Jaspersoft Studio using domains as data sources. This practice can have a severe impact on performance. SQL is the preferred language for developing reports in Jaspersoft Studio.
- Limit the number of subdatasets (subqueries) in your report. The recommended limit is ten subdatasets.
- Limit the number of subreports that are called by the main report. The recommended limit is ten subreports.
- Use the "Print When Expression" option, when possible, to avoid unnecessary calls to subreports and subqueries.
- Use frames to group elements, especially when you have several fields in the report layout. Avoid hiding elements using rectangles or other elements. Frames can be invisible and they keep fields aligned when exporting to Microsoft Word and to other export options.
- Always use the "Text Field" element, even if you are defining static text. The "Text Field" element gives you more flexibility and allows for internationalization.
- Always define the "Pattern" property for the "Text Field" element when it returns values of type other than text and date.
- Avoid overlapping fields (for example, making the width of an element extend over the next element). Fields that are overlapping do not display when the report is exported.
- Create your own images or use the images included in the PMO Accelerator Advanced Reporting content. Use the "repo:/<PATH_TO_THE_IMAGE>/<IMAGE_ID>" syntax to reference the image inside the report.
- When working with images, we recommend that you use one image element associated to an IF-THEN-ELSE expression to display different images instead of having several image elements.
- Use Groovy as the language for the report. In Jaspersoft Studio, select the report name at the top of the hierarchy in the Outline view and change it on the Properties, Report tab. When you use Groovy, you can use built-in functions and simpler expressions. You can still use Java in your expressions, if necessary.
- Use the following OOTB built-in parameters in your report queries. These parameters are passed through the integration betweenCA PPMand the JasperReports Server.
- ppmUserUITheme. The UI Theme selected as default inCA PPM.
- ppmUser. The user name of the user who is logged in toCA PPM.
- ppmUserLanguage. The user language of the user who is logged in toCA PPM.
- ppmDBName. The database name that is used for Microsoft SQL Server.
- ppmDBSchema. TheCA PPMdatabase schema.
- ppmDBVendor. TheCA PPMdatabase vendor (oracle, mssql).
- dwhDBSchema. The Data Warehouse database schema.
- dwhDBVendor. The Data Warehouse database vendor.
- dwhDBLink. TheCA PPMDatabase Link as defined in CSA.
Publish the Report
- You do not need to publish a report every time that you edit it. After you create the report unit, you can upload the JRXML file from Jaspersoft Studio instead of publishing the report.
- Avoid publishing reports with subreports from Jaspersoft Studio. When you modify reports that include subreports, download the reports to your local server and upload the updated JRXML file. Follow this procedure for the main report and subreports. You can also upload a local JRXML file from the Set Up the Report page when you edit the report. However, the report server does not validate the JRXML file when you upload it. Use Jaspersoft Studio to validate the JRXML before uploading it.
- Compile the report in Jaspersoft Studio to validate it before uploading. Click the Compile Report icon that is located in the upper right corner of the Design area. The following image shows the Compile Report icon.
- When you publish a report, the Select Resources to Publish window appears. Select Ignore from the menu instead of Overwrite for all resources included on this window that exist in the repository.