iMIS Report Writer: Best Practices

Review the following best practices to understand how to best work with the iMIS Report Writer.

Data sources

Keep Report Writer sources organized and separate from IQA sources

If there are sources that should only be used in Report Writer and are not with IQA queries, it is best practice to create a separate folder for the Report Writer sources, so that the sources are kept organized. Do the following to create a separate folder for Report Writer sources:

1. Go to RiSE > Document system.
2. Navigate through the following folder path: $/Common/Query Sources
3. Click New > Folder.



4. Enter a Folder name, such as Report Writer Sources.
5. Choose the Allow only the following types of objects in this folder (select all that apply) radio button.
6. Hold down Ctrl on the keyboard, then choose the following options:
   • Business Object
   • Design Business Definition
   • Folder
   • Query
   • Shortcut
7. Click Save. Any shortcuts to business objects or queries that are specifically meant for Report Writer should be added to this folder, so that they are not accidentally modified. Be sure to not edit the Report Writer Sources folder after creation to avoid errors.

Do not change the name of Business objects that are being used in a Report Writer report

It is important to not modify the name of business objects used as sources in Report Writer reports. When the business object names are modified, the reports that reference the business objects will no longer open for edit.

Know when to choose a business object or an IQA query as the data source

Review the following to understand if you should use a query shortcut or a business object shortcut as a report data source:

• Query - Use IQA queries as the data source for the following:
  • When properties may need to be added or removed.
  • When the data is dynamic, meaning properties are likely to change.
  • To combine multiple business objects and queries into one data source.
  • To take advantage of query filters or security controls.
• Business object - Use business objects for the following:
  • When the data properties are not likely to change.
  • When the data is reference information.
  • When the Business Object shortcut will not change.
  • To take advantage of if/then/else SQL expressions.

Using multiple instances of a data object

Report Writer does not permit using the same data object more than once in a report. For example, if you are displaying orders or invoices and need to display both Bill To contact information and Ship To contact information - you cannot add the Contacts Data object twice to represent two different contacts.

Instead, you need an additional data object that represents the same or similar data. For example, out-of-the-box data objects that are similar are Contacts, Individuals, Organizations, or CsContact, CsContactBasic. If the data object you need is not available out of the box, add an additional IQA query or BO shortcut in the appropriate folder under $/Common/Query Sources.

USE THE TOP/BOTTOM FILER FOR REPORTS USING IQA

When using an IQA as the report's data source, it is suggested to use the Top/Bottom report filter (Report Options > Filters > Top/Bottom) instead of enabling Limit the report to the top/bottom values. Using the Top/Bottom report filter will ensure no data is missing in the report.

TAKE ADVANTAGE OF DATA SOURCE CONFIGURATIONS

The primary locations where data can be configured for reports are Repot Writer, Intelligent Query Architect, and Business Object Designer. For more granular control over specific data, take advantage of configurations at the query or business object levels. For example, configuring filters and security controls at the query level and defining if/then/else SQL expressions at the business object level enable you to tailor reports to your organization's unique requirements.

Formulas

Formatting dates

To format dates in Report Writer reports, do the following:

1. Right-click the cell with the date, then choose Format Cells.
2. Click the Date category.
3. Choose the desired format from the list.

Note: For information about each Date/Time Format, see Date Functions.



4. Click Okay.

If you are using a formula, open the Formula Editor (Right-click cell > Edit Formula) and use the DateCustomFormat function.

To make sure the date respects the user's browser language for date formats, use the formula GlobalDateFormat or GlobalDateTimeFormat.

Example: GlobalDateFormat({Payments.PaymentDate}).

Formulas not being saved in the Edit Formula window

If formulas are not saving in the Edit Formula window, edit formulas directly from the cell itself:

1. Double-click in the cell.
2. Type the formula, starting with the equal sign "="
3. You can also go back-and-forth from the Edit Formula window and the cell by copying and pasting as necessary until you are satisfied with the formula.

Error: 'Aggregate of aggregates is not permitted'

A formula like AggSum(AggSum(xyz)) is not permitted in Report Writer formulas. To work around this, do the following:

To format dates in Report Writer reports, do the following:

1. Right-click the cell with the date, then choose Format Cells.
2. Click the Date category.
3. Choose the desired format from the list.

Note: For information about each Date/Time Format, see Date Functions.



4. Click Okay.

If you are using a formula, open the Formula Editor (Right-click cell > Edit Formula) and use the DateCustomFormat function.

To make sure the date respects the user's browser language for date formats, use the formula GlobalDateFormat or GlobalDateTimeFormat.

Example: GlobalDateFormat({Payments.PaymentDate}).

1. Add AggSum(xyz) to a cell (and hide it if you do not want it to display).
2. Use a formula like AggSum([A1]), where A1 is the cell that contains AggSum(xyz).

Note: This method does not always avoid the aggregate of aggregates error, depending on where in the report grouping it is located.

Filters

 Reports that have URL parameter filters cannot be executed in the Report Writer window

Reports that are intended to be used in content with a filter value passed in from the URL cannot be run from Report Writer. If you need a version of the report that can be run from Report Writer, then you will need to create a separate report that does not use @URLParamterName@ in any filters. See also Filtering reports using URL parameter values.

Report Writer does not support queries with dynamic filters

Dynamically filter the report using a URL parameter instead. See Filtering reports using URL parameter values.

Grouping filters

When a data row is validated against report filters, each data field is validated against all other filters for the report. The data field must satisfy every filter condition in order to be shown on the report.

Do the following to instead specify that a data row must satisfy only one of several filter groups:

1. Select the cell to filter.
2. Select the filter icon.
3. From the drop-down, select OR With Next Filter.
4. Enable Group With Next Filter.

Simplifying the filter execution window

To simplify the filter prompt window so that the data sources do not appear, update the Filter Execution Window setting:

1. Open the Advanced report.
2. Click the Advanced button and choose General Options.



3. From the Filter Execution Window drop-down, choose one of the following:
   • Default: Default is Standard.
   • Standard: Display the standard filter window, which includes the data sources where users can add, delete, and modify existing filters.

   

   • Simple with Operator: Display a simplified filter window that only allows the filter operator and value to be changed.

   

   • Simple without Operator: Display a simplified filter window that only allows the filter value to be changed.

   Tip! To filter the Value to only display data based on the ID in the URL, add a filter that does not prompt for value, with a filter value of "@ID@". See Filtering reports using URL parameter values for an example.

   

4. Click Okay, then save the report.

Using parameters to accomplish optional filters or formatting options

If a filter value is left blank at run time, then the report produces no results. To provide a filter that can be left blank and still return results, you can instead add a Parameter . A Parameter can also be used to offer formatting options to the end user when the report is run.

To add a Parameter, do the following:

1. Open the Advanced report.
2. Click the Advanced button, then choose Parameters.



3. Enable Prompt for each parameter you wish to act as a filter than can be left blank at run time.
4. Click Okay.

If a parameter is set to Prompt, the user is prompted for the values before the filter window appears. These values can be modified or left blank, unlike filters.

The Parameter value that the user enters can be used in the report, referring to it as @ParameterName@. This can be used in filters, or in Suppress Section or Suppress Row conditional formatting in a cell.

Formatting

Use expressview to format reports

ExpressView reports are recommended in most scenarios for their simple, intuitive report-writing interface. ExpressView offers the following:

• Grouping, sorting, filtering, and summarizing data
• Defining formulas
• Creating visualizations
• Exporting reports

If you require more complex functionalities, Advanced reports are available.

Grouped sections use a Sort property to group on

When adding a grouped section (Group Header, Group Footer, or Repeating Group), the properties available to group are the Sort properties and the unique keys for report data sources. For this reason, prior to adding a grouped section, you should add the property you want to group to the Sorts.

Note: A formula can be used as a Sort, and thus you can also group on that formula.

Conditionally hiding information in the report

Rows and sections of the report can be conditionally hidden at runtime by formatting a cell within the row or section.

Do the following to hide information:

1. Locate the cell you wish to hide.
2. Right-click Format Cells > Conditional tab > Add > Suppress row or Suppress section on the cell.
3. Click fx to create a formula that defines the condition under which the row or section will be suppressed. If the formula evaluates to True for the current data, the row or section will be hidden.