Details on filtering reports with user-defined fields.
Dashboards are for monitoring, measuring, and analyzing relevant data in key areas, and, they’re not just for staff site. With the right reporting and security infrastructure, you can push out Dashboards for consumption by key stakeholders, who are not staff, without sacrificing a Full User license. Further, it’s possible to customize what data those key stakeholders see based on user-defined values. I recently worked on a project that does this and the purpose of this article is to explain how I implemented this completely out-of-the-box.
Requirements
The client needed an axis point for a group of Directors to view more than a dozen custom reports. In order to make the reporting relevant for each Director, report data depends on a user-defined value in a demographics table: the Director’s region. This presented a challenge because we were faced with having to either set up a unique Dashboard for each Director or making a copy of each report per Director and hardcoding filter values. Certainly, this is possible but a lot of content to maintain. Instead of setting up multiple dashboards or creating multiple copies of each report, I used IQA to send, or pass, filter values to the reports. Queries were also set to use a dynamic run-time filter that displays results based on the Director’s unique iMIS ID.
Report Display and Queries
The first step for me was to create a content record and configure the Report Display iPart for each report. I also made sure the option for “Inspect query string for parameters” was enabled on the Report Display iPart. Then, I made a shortcut for each content record to use in my query display.
The most important filter in the query is the one that returns results based on the Director, or user’s, iMIS ID. To achieve this, I entered NetContact.iMIS ID as a property and “@SelectedId” as the value. Using the prefix “@” on certain query filter values creates a dynamic filter, or, a filter that automatically takes a value at run time.
In order to use a query to pass filter values I needed to make sure that the query display included fields that matched the report filters. In this case, to match the @Region report parameter, I added the Director’s region to the query display and entered an Alias of [code_region]. Entering an Alias with the syntax of “[code_XXX]” hides the column from view but allows IQA to use it for passing parameters. The reports also call for a General Lookup Table value that isn’t tied to the Directors at all. As a workaround I added CsGeneralLookupTables as a source and did not add a relation, but filtered on the table name and code. This allowed me to include the lookup table value in the query display to be passed to the report.
Building Links
Next, I used the report page shortcuts I created to build links in the query display. Query links are usually entered on an existing property and the link text is the property value. But, I needed the property value to return the title of each report. To achieve this I entered each report title encased in single quotations as a SQL Expression in IQA. Then, in the Link fields on my ‘custom’ properties I entered the short cut name and each report parameter name with the corresponding property Alias:
~/Shortcut Name?Report Parameter 1 Name=[Query Property 1 Alias]&Report Parameter Name 2=[Query Property 2 Alias]
For example:
~/RevenueReport?State=[code_state]&FiscalYear=[code_FY]
Going Live
The final piece was to put together the Dashboard and push it out to the member site. I put the customized report link query on a content record along with Progress Trackers and Query Chart Viewers that displayed general data and restricted access to only the Directors based on a dynamic IQA security group. In this instance, a sitemap item with the same dynamic IQA security group applied was added to the member site for easy access.
More Information
I hope I’ve given you some tools to use in building your own customized Dashboard. For more information on filtering Report Writer reports using URL parameters click here.
