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.

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

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.

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.

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.

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.

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

 

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

 

• SSIS/SSAS/SSRS/MDX