Ad Hoc Views and Custom Report Development
This page provides best practices for you to observe when working with your Advanced Reporting views, domains, and custom reports developed in Jaspersoft Studio.
Running and scheduling ad hoc views and ad hoc reports in
Reports and Jobsis not supported. Only Jaspersoft Studio reports are supported for running and scheduling from
Best Practices for Ad Hoc Views
To reduce performance issues and generate the desired results for your advanced reports, follow these best practices when using the Ad Hoc Viewer and the domains:
- Do Not Select All Domain Fields Into Ad Hoc Views: TheClassic 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. Too many fields can cause 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 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 an ad hoc view.
- Select the Correct Metrics: TheClassic PPMdomains, except for the Custom Master Objects domain, contain three different period types. The domains also contain 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 otherClassic 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 contains financial planning and financial transactions data. This data is stored at a different level of granularity and does not have a direct relationship with other data. 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 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: The Advanced Reporting domains display dates without the time element, except for system dates. InClassic PPMand Ad Hoc Views for Time Zone DifferencesClassic 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 areCreated DateandLast Updated Date. The following dates are not considered system dates; instead, we refer to these dates as business dates:
Classic 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 inClassic PPMas date (without a time element), they match the dates in the Advanced Reporting domains. Custom attributes that you create as dates inClassic PPMare not considered system dates and display as date (without a time element) in Advanced Reporting. These dates never adjust according to time zone.
- Investment start and finish dates
- Task start and finish dates
- Assignment start and finish dates
- When youexecutea 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 you inClassic PPMunderHome,Account Settings.
- When youschedulea report and select a time zone to create the report schedule, no time zone adjustment occurs 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 inAdvanced Reporting,Output Optionswhen creating a schedule.
By default, the date fields that you select in the ad hoc views appear 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 shown 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.
Show the View SQL Query Button
In Jaspersoft 6.4.2 and 7.1, the View SQL Query button does not appear for report users (even with ROLE_ADMINISTER) by default.
Follow these steps:
- Log in to the Jaspersoft report server as a superuser.
- In the main menu, clickManage,Server Settings,Adhoc Settings.
- Select theEnable View Query in Ad Hoc Editorcheck box and clickChange.
Users with ROLE_ADMINISTER can now view the
View SQL Querybutton in ad hoc views.
Best Practices for Domains and Reports
- Do not make any changes to the default domains, permissions, or reports that come with the application and advanced reporting solution:Any changes you make can cause problems or be impacted by the upgrade process. Instead, always make a copy of a domain or report in a test environment and then make your changes outside of theClassic PPMproduction folder structure.
- Avoid creating domains over 2.0 MB:If you exceed this size limit, you can get errors when editing the domain in the domain designer. Jaspersoft recommends that you include fewer than 50 tables or views in a domain. If you exceed this limit, you can get errors when building the domain.
- Do not build your Jaspersoft Studio reports based on domains.Jaspersoft 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 original default system 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 selectExport Design.
- Save the domain XML file locally. The following image shows theExport Designoption in the domain designer:
- 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, Upload the domain XML file produced in Step 2 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.
- ClickSubmit. If there are any differences between the environments you missed during Step 4, an error appears showing the details. Fix your domain XML file and then try again.
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
Classic PPMtransactional database 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 theClassic 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,Classic PPMand 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.
- Use existing stock 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 theClassic PPMfolder. Make a copy of the OOTB report and modify the copy.
- Organize the folder structure for your custom content similar to theClassic PPMfolder organization, considering how you want to manage security. You can allow access at the folder level or at the report level.
- Create your reports outside theClassic 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 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 syntax rules that are required by Jaspersoft. Avoid spaces and special characters in the JRXML file name that you use to upload the report. We recommend Camel Case 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.Example:repo:/ca_ppm/resources/images/stoplightRed
- When working with images, we recommend that you use one image element that is 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 betweenClassic PPMand the JasperReports Server.
- ppmUserUITheme. The UI Theme selected as default inClassic PPM.
- ppmUser. The user name of the user who is logged in toClassic PPM.
- ppmUserLanguage. The user language of the user who is logged in toClassic PPM.
- ppmDBName. The database name that is used for Microsoft SQL Server.
- ppmDBSchema. TheClassic PPMdatabase schema.
- ppmDBVendor. TheClassic PPMdatabase vendor (oracle, mssql).
- dwhDBSchema. The Data Warehouse database schema.
- dwhDBVendor. The Data Warehouse database vendor.
- dwhDBLink. TheClassic 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.
Run the Report
You can run the report under the Home menu by selecting Advanced Reporting or Reports and Job.
To run reports directly from
Classic PPMunder Home, Reports and Jobs, complete the following steps after developing the reports:
- Create report filters as lookups under Administration, Data Administration, Lookups. See Configure Lookups.
- Create report definitions under Administration, Data Administration, Reports and Jobs. See Configure Reports and Jobs.
- To secure the reporting data, grant the required advanced reporting access rights to the appropriate users and groups. See Advanced Reporting Access Rights.