Contents
Overview
SharePoint data can drive Cognos reports through connecting SharePoint “Filter Provider” web parts to the QueryVision Report Viewer
QueryVision’s Report Viewer web part can be connected to Microsoft supplied and custom “Filter Provider” web parts including the QueryVision Cognos Report Drop Down List Filter Provider and SharePoint List Drop Down Filter Provider. In SharePoint 2013, there are a number of filters supplied with the standard Enterprise SharePoint installation (e.g. Choice Filter, Text Filter, etc.).
As web part connections between (Filter) Providers and Consumers/Targets are standardized, the guidelines provided here apply for connecting any Filter Provider to the QueryVision Report Viewer Web Part.
Filter behavior with the Report Viewer
Multiple Filters, Multiple Reports
The Report Viewer Web Part can accept one or more values from one or more filter parts. In addition, you can connect a single filter part to one or more reports.
So (for example) a report with 5 parameters can have 5 filter providers. A page with 4 Reports can have 2 filters drive one or more of the reports on the page.
For cases where there are multiple filters on a page, you can control default values and filter behavior through the “Filter Actions Button” (SP 2007, SP 2010). Not this has been renamed to “Apply Filters Button” for SP 2013.
Optional/Mandatory Report Parameter Control
You can control whether a Report Viewer will run the report based on whether it has required (non-Optional) parameters defined within the report. You can use the Report Viewer Force Parameter Level to control this behavior.
When parameters are not forced the report viewer will allow the report to run even if parameters are not passed to it. When the report is executed, Cognos will prompt the user for any parameter values that are mandatory and that have not been supplied by filter web parts on the SharePoint page via the Report’s Prompt Page (whether authored as part of the Report or automatically – see IBM Cognos documentation on Report Prompts for details).
Filter parameter to Report parameter matching
For all Filters, the parameter name (Filter Name field) assigned to the Filter part is important and it is recommended that it should match that of a parameter in the Cognos Report. However, this is not mandatory. When the report has multiple parameters, you will be asked on connection to specify which report parameter to map the Filter Name (parameter name) to.
For example, the following shows a Report Studio which shows Product, filtered by Product Type. The filter uses a (single value) parameter productType. Multiple filter providers can be connected to a Report Viewer web part, each supplying a different parameter.
The following shows a Cognos Report being edited in Report Studio, showing how the parameter definition is defined in a filter (productType).
Connecting Filter Providers to a Report Viewer
The following applies to any Filter provider/Report Viewer combination. In this example we’ll use the SharePoint “Choice Filter” web part.
Add a Choice Filter web part to the left of a QueryVision Report Viewer web part on a standard web part page. For the Report Viewer, select a report with parameters. In this case we’ll pick a report with a single parameter “productline_param” which requires a Product Line value from the Cognos sample databases.
Now open the Choice Filter Web Part Editor by finding and clicking on the down arrow in the top right hand area of the web part and selecting Edit Web Part
Now we update the following fields (initial view on the left, edited version on the right). The following has been provided:
- Filter Name is the name of the parameter. If no [Web Part] Title is added then the Filter Name will also be the name of the Web Part.
- Filter values for the user to select are listed (one per line) in the dialog box as instructed
- Advanced Filter Options – have checked “Require user to choose a value” and added a “Default Value”
- Title – have added a web part title. For this to appear you need to select “Chrome” from “None” to “Title Only” or “Title and Border”. Note that the Title and the Filter Name will be displayed
On clicking OK in the Choice Filter Web Part your web page should look like the following:
Connecting the filter web part to the Report Viewer. While in Page Edit mode right click the Choice Filter part drop down (top right) and select connections
The following shows the “Configure Connection” box that will pop up if the source/provider and consumer/target web parts are compatible.
The parameters the consumer/target can accept are listed in the “Filtered Parameter” drop down list.
The parameter(s) available from source/provider are listed in the “Filter” field.
Once connected, selecting the drop down arrow on the Choice Filter will pop up the list of available values and on clicking OK will drive the report.
