Creating PowerView Style Filters on SQL Server Reporting Services Reports

Creating PowerView Style Filters on SQL Server Reporting Services Reports

PowerView, the new commer of Microsoft reporting tools family, empowers report builders to create eye-catching reports with wow-factors in a rather simple way.

powerview_filter_01

One nice feature offered by PowerView is the image-based filter.

powerview_filter_02

Obviously, it would produce much better user experience compared to the drop down style filter in SSRS. Not only it looks much nicer, but also users don’t have to click the “View Report” button every time they changed their selections.

powerview_filter_04

It would give SSRS reports many wow-factors if we could add this PowerView style filters on SSRS reports. In this post, I will go through the steps to build up a PowerView style filter like the snapshot shown below.

powerview_filter_03

Firstly, we need have our report body ready with some charts or tables. As an example, I create a chart connecting to the sample Adventure Works cube and presenting the sale amounts of each product sub-category under a primary category which will be selected by end-users on our PowerView style filter. This is the query to fetch the data required by the chart.

SELECT
	NON EMPTY { [Measures].[Reseller Sales Amount]} ON COLUMNS,
	NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) } ON ROWS
FROM
	(SELECT
		STRTOSET(@ProductCategory, CONSTRAINED) ON COLUMNS
	FROM [Adventure Works])

As you can see above, a parameter (“@ProductCategory”) need to be passed into the query. We need define the parameter on report level and get the available values using the following query.

WITH
	MEMBER [Measures].[ParameterCaption] AS
		[Product].[Category].CurrentMember .MEMBER_CAPTION
	MEMBER [Measures].[ParameterValue] AS
		[Product].[Category].CurrentMember.UniqueName
	MEMBER [Measures].[ParameterLevel] AS
		[Product].[Category].CurrentMember .Level .Ordinal
SELECT
	{ [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS,
	{[Product].[Product Categories].Children} ON ROWS
FROM [Adventure Works]

Then we add a horizontal list onto the table which is the host of the PowerView style filter (Creating horizontal list is out of scope of this post. Please just google it and tons of articles are there about this topic). On the item template, add an image placeholder and a textbox placeholder.

powerview_filter_06

We then need a dataset to get the product categories and also the image associated with each category. Unfortunately, there is no image information associating with primary category stored in the sample Adventure Works DW database. Therefore, I have added an Image column to the DimProductCategory table and assign a sample image for each category. The query below fetch the category name and image.

SELECT [ProductCategoryKey]
      ,[EnglishProductCategoryName]
      ,[Image]
FROM [dbo].[DimProductCategory]

After the product category image placeholder and name placeholder on the horizontal list are associated with the dataset. Right-click the image placeholder, go to properties and select the Action panel. Create a “Go to Report” type action and select the current report as the destination report.

powerview_filter_07

On the parameter section, select the ProductCategory parameter and set the value as:

 powerview_filter_08

After that, we have our PowerView style filter on the SSRS report.

powerview_filter_03

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s