RRDItutorial2_Snap1

Configure Multiple Reports in One Report Specification

One report specification can contain different views of your data, essentially rendering different reports. But why would it make sense to set up one report spec to do this, you ask? This makes sense when the queries behind the pages share a lot of content, and it’s easiest to maintain that content in one location rather than two or more. For example, if you have a higher-level summary report that drills through to the detail of those summary level values, one report specification can contain both reports. Within one report spec, you can define multiple pages using the same or different queries, and then configure the appropriate pages to display with variables when the report is executed.

This method of putting parent and drill-through reports into the same report spec can be incredibly useful in environments where content is developed in one location and promoted to another location for production use. Only one report spec needs to be moved to maintain unbroken drill-through links, instead of multiple.

Also, if there are any complexities in the queries of a report, such as filters, unions, joins, or logic built into data items, those complexities can be used to deliver information in different layouts while defined in one place. Your set of complex queries can be the source of multiple pages, and the report can be configured to display the page you want when it is run.

A sample report with two pages – a summary page with revenue by retailer and a detail page with product information – will be used for this example. This report uses the Cognos sample data, and is featured in Version 10.2.2. In the sample report, both pages are sourced from the same query.

#1

When the report is run right now, both pages render.

Revenue by Retailer Page

Revenue by Retailer Page

Revenue by Product Page

Revenue by Product Page

Create a prompt to pass a parameter for which page(s) to display

  1. On the report prompt page, insert a Value Prompt object from the Toolbox tab of the Insertable Objects pane.insert a Value Prompt object
  2. Select the prompt in the layout to access its properties.
  3. In the Properties pane, click the ellipsis for the Static Choices property.Static Choices property
  4. Enter a static choice for each page you want to select from the prompt page.Enter a static choice for each page
  5. Set the Parameter property for the Value Prompt.Parameter property for the Value Prompt

The value prompt with static choices will look similar to this:The value prompt with static choices
Create a variable to use to determine which report page renders

  1. Navigate to the Condition Explorer and select Variables.Condition Explorer and select Variables
  1. From the Toolbox tab, drag a String Variable object to the Variables pane.
  2. In the expression editor, drag the new parameter you created to the Expression Definition.Expression Definition
  3. Click OK.
  4. In the Properties pane for the variable, set the Name property.set the Name property
  5. Add values to the Values pane that line up with the static choices defined in the prompt.Add values to the Values pane

Set the Render Variable property on the report pages

Render variable is a property that is on multiple objects in Report Studio. A variable defined within the Condition Explorer can be used for the render variable property to determine whether an object renders or not. We will be using this property on the report pages to determine which page renders when the report is executed.

  1. Navigate to the Page Explorer and select Report Pages.Page Explorer and select Report Pages
  2. In the Report Pages pane, select a page to access its properties.
  3. In the Properties pane, click the ellipsis for the Render Variable property.Render Variable property
  4. From the Variable drop-down, select the string variable created in the last set of steps.Under Render for
  5. Under ‘Render for’, leave the box(es) checked for the value(s) you want to display this page.
    The first page in our sample report is the summary level detail, so we want this page displayed when Summary is selected at the prompt.
  6. Repeat steps 2 through 5 for each additional page in the report.
    The second page in the sample report is the detail information, so it is set to display when Detail is passed from the prompt.

Now when you run the report, you are prompted to select Summary or Detail on the report’s prompt page. If Summary is selected, only the Revenue by Retailer page renders in the report output. If Detail is selected at the prompt, only the Revenue by Product page renders in the report output.

But what if you don’t want to give users the ability to view the detail output without drilling through? In other words, when a user runs the report, you only want them to get the summary information and have to drill-through to get more detail. Well…here’s how!

Set up drill-through definition from the summary page

  1. Navigate to the query the summary report is sourced from.
  2. Insert a data item to use for the detail drill-through value.
  3. Set the expression definition to the value used to render the detail page.
    Don’t forget to enclose the value in single quotes, as it is a string.Set the expression definition
  4. Navigate to the summary page in the report.
  5. In the work area of the page layout, select the item for which you want to set up drill-though.
  6. In the Properties pane, click the ellipsis on the Drill-Through Definitions property.Drill-Through Definitions property
  7. Add a new drill-through definition, and set the Target Report to the same report you are working in.
  8. Set the parameters for the report as necessary, ensuring that the p_DetailLevel parameter is set to your new data item defined for the ‘Detail’ value.
    If you passed the p_DetailLevel parameter back to the drill-through of the report, you would be passing ‘Summary’ as that is what was submitted to get to the summary page. We have to fabricate a data item and pass it so that the prompt is answered with ‘Detail’ when we drill-through. Set the parameters for the report
  9. Click OK twice.
  10. Save the report.

Now when we run the report and render the summary page, we can click on the drill-through link to get to the detail page. However, since our prompt is still visible on the prompt page with both ‘Summary’ and ‘Detail’ options, we can still get to the detail page without having to drill-through. If we hide the Detail Level prompt on the prompt page, we can still pass ‘Summary’ to the report when run by a user, while hiding the option for users to select ‘Detail’.

Hide prompt while still passing a value from it

  1. On the prompt page, select the prompt.
  2. In the Properties pane, click the ellipsis for the Default Selections property.Default Selections property
  3. Add a value to set the default for the prompt.Add a value to set the default for the prompt
  4. Navigate to the Condition Explorer and select Variables.Condition Explorer and select Variables 2
  5. Drag a Boolean variable from the Toolbox tab to the Variables pane.
  6. Set the expression definition of the variable to 0.
  7. Set the name to Show Prompt.Set the name to Show Prompt
  8. Navigate to the prompt page of the report.
  9. Select the Detail Level prompt to access its properties.
  10. In the Properties pane, click the ellipsis for the Style Variable property.In the Properties pane, click the ellipsis for the Style Variable property.
  11. From the Variable drop-down, select the Show Prompt variable.From the Variable drop-down, select the Show Prompt variable
  12. Click OK.
  13. Navigate to the Condition Explorer, and select the No value from the Show Prompt variable.select the No value from the Show Prompt variableThe Explorer bar turns green to indicate that there is a variable selected.The Explorer bar turns green to indicate that there is a variable selected
  14. With the Detail Level prompt still selected, select None from the Box Type property drop-down in the Properties pane.select None from the Box Type property drop-down.
  15. Save the report.

When the report is run now, the Detail Level prompt is not displayed on the prompt page.

Detail Level prompt is not displayed on the prompt page.

Summary is selected at the hidden prompt, so after clicking the Finish button on the prompt page, the summary page displays.

Summary is selected at the hidden prompt, so after clicking the Finish button on the prompt page, the summary page displays.

To access the detail page within the report, click a drill-through link from the summary page.

To access the detail page within the report, click a drill-through link from the summary page.

We’ve created a report that has multiple pages within the report specification, while report consumers have no visibility to the report containing multiple versions. This solution can be used to present polished reports to end users, while making maintenance of report specs simpler for developers.

For more info, watch our webinar recording on the topic here.

Share this post