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.
ccppmop155
This page provides best practices for you to observe when working with your Advanced Reporting views, domains, and custom reports developed in Jaspersoft Studio.
2
2
: Running and scheduling ad hoc views and ad hoc reports in 
Clarity PPM
 under 
Reports and Jobs
 is not supported. Only Jaspersoft Studio reports are supported for running and scheduling from 
Clarity PPM
.
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
    : The 
    Clarity PPM
    domains 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.
    The image shows how to add fields to your ad hoc view by clicking the icon to the right of the domain name and then clicking Select Fields.
  • 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. 
    The image shows how to select the correct data mode when creating a new ad hoc view
  • Select the Correct Metrics
    : The 
    Clarity PPM
    domains, 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 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 image shows how to select metrics from the Team set 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 
    Clarity PPM
    domains 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 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
    Clarity 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
    Clarity PPM
    as 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
    Clarity PPM
    as date (without a time element), they match the dates in the Advanced Reporting domains. Custom attributes that you create as dates in 
    Clarity PPM
    are 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 you in
      Clarity PPM
       under 
      Home
      ,
      Account Settings
    • When you
      schedule
      a 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 in
      Advanced Reporting
      ,
      Output Options
      when 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:
The image shows how you can modify the date format for the system dates to include a time element when creating an ad hoc view.
  • 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.
    The 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
:
  1. Log in to the Jaspersoft report server as a superuser.
  2. In the main menu, click
    Manage
    ,
    Server Settings
    ,
    Adhoc Settings
    .
  3. Select the
    Enable View Query in Ad Hoc Editor
    check box and click
    Change
    .
image2018-12-12_16-0-49.png
Users with ROLE_ADMINISTER can now view the
View SQL Query
button in ad hoc views.
image2018-12-12_16-5-33.png
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 the CA PPM production 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:
  1. Extract the domain file from the environment that you are migrating from.
  2. Edit the domain using the Domain Designer and select
    Export Design
  3. Save the domain XML file locally. The following image shows the
    Export Design
    option in the domain designer:
    The image shows the Domain Designer window
  4. 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.
  5. 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.
    The image shows how to upload the domain XML file to the new environment
    : Verify that the schema file name that you upload does not contain any spaces or special characters.
  6. Click
    Submit
    . 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 
Clarity PPM
 transactional 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.
4
4
Start the Report
  • Install and use the Jaspersoft Studio version that corresponds to the 
    Clarity PPM
     version 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, 
    Clarity 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.
  • 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 the 
    Clarity PPM
     folder. Make a copy of the OOTB report and modify the copy.
  • Organize the folder structure for your custom content similar to the 
    Clarity PPM
     folder 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 the 
    Clarity PPM
     folder, 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 between 
    Clarity PPM
     and the JasperReports Server.
    • ppmUserUITheme
      . The UI Theme selected as default in 
      Clarity PPM
      .
    • ppmUser
      . The user name of the user who is logged in to 
      Clarity PPM
      .
    • ppmUserLanguage
      . The user language of the user who is logged in to 
      Clarity PPM
      .
    • ppmDBName
      . The database name that is used for Microsoft SQL Server.
    • ppmDBSchema
      . The 
      Clarity PPM
       database schema. 
    • ppmDBVendor
      . The 
      Clarity PPM
       database vendor (oracle, mssql).
    • dwhDBSchema
      . The Data Warehouse database schema.
    • dwhDBVendor
      . The Data Warehouse database vendor.
    • dwhDBLink
      . The 
      Clarity PPM
       Database 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.
    This 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 
Clarity PPM
under Home, Reports and Jobs, complete the following steps after developing the reports: 
  1. Create report filters as lookups under Administration, Data Administration, Lookups. See Configure Lookups.
  2. Create report definitions under Administration, Data Administration, Reports and Jobs. See Configure Reports and Jobs.
  3. To secure the reporting data, grant the required advanced reporting access rights to the appropriate users and groups. See Advanced Reporting Access Rights.