Tag: MDX

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

MDX #5 – Product Sales Growth Analysis

Business Question 1: What are the top(n) products with highest sales growth  from previous year in each sales territory?

M01

Firstly, we need get the sales of previous year using ParallelPeriod method and then calculate the sales growth (%) by comparing the sales of current year aganist previous year. We will then combining GENERATE and TOPCOUNT functions to get the top (n) products within each sale territory.

WITH
MEMBER [Measures].[Sales Prev Year] AS
AGGREGATE
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CURRENTMEMBER

)
,[Measures].[Sales Amount]
)

MEMBER [Measures].[Sales Current Year] AS
AGGREGATE
(
[Date].[Calendar].CURRENTMEMBER, [Measures].[Sales Amount]
)

MEMBER [Measures].[% Sales Growth] AS
IIF ([Measures].[Sales Prev Year]=0, NULL,
([Measures].[Sales Current Year] - [Measures].[Sales Prev Year])/[Measures].[Sales Prev Year]
), FORMAT_STRING="Percent"

SELECT {
[Measures].[Sales Prev Year],
[Measures].[Sales Current Year],
[Measures].[% Sales Growth]
} ON COLUMNS,
NON EMPTY{
GENERATE(
[Sales Territory].[Sales Territory].[Country].MEMBERS
,TOPCOUNT(
[Sales Territory].[Sales Territory].CURRENTMEMBER*
[Product].[Product Categories].[Product].members, 3, [Measures].[% Sales Growth]
)
)
} ON ROWS

FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2008]

 

Business Question 2: What are the  products having sales growth over 10% in UK

M02

To get the set of products having sales growth over 10%, we need use FILTER function to filter on the products and set the condition as [Measures].[% Sales Growth]>0.1

WITH
MEMBER [Measures].[Sales Prev Year] AS
AGGREGATE
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CURRENTMEMBER

)
,[Measures].[Sales Amount]
)

MEMBER [Measures].[Sales Current Year] AS
AGGREGATE
(
[Date].[Calendar].CURRENTMEMBER, [Measures].[Sales Amount]
)

MEMBER [Measures].[% Sales Growth] AS
IIF ([Measures].[Sales Prev Year]=0, NULL,
([Measures].[Sales Current Year] - [Measures].[Sales Prev Year])/[Measures].[Sales Prev Year]
), FORMAT_STRING="Percent"

SET [Products with Sales Growth over 10%] AS
Filter
(
EXISTING
NONEMPTY ([Product].[Product Categories].[Product].members,
[Measures].[Sales Prev Year])
, [Measures].[% Sales Growth]>0.1
)

SELECT {
[Measures].[Sales Prev Year],
[Measures].[Sales Current Year],
[Measures].[% Sales Growth]
} ON COLUMNS,
{
Order
(
[Products with Sales Growth over 10%]
, [Measures].[% Sales Growth]
, BDESC
)
} ON ROWS

FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2008],
[Sales Territory].[Sales Territory].[Country].&[United Kingdom])

 

Business Question 3: Linear regression trends based on the sales on the previous 12 months

52

51

A built-in MDX function LinRegPoint has been provided for Linear Regression Analysis. Firstly, we need get the last 12 months (in the example below, we presume current month is Sep, 2007) which will be shown on the x-axis on a chart. Then, we need get the rank of the monthes which represents the value on the x-axis. Next, we need call the LinRegPoint function to calculate the value of the y-intercept in the regression line.

WITH
//presume current month as Sep, 2007 as demo
SET [Last 12 Months] AS
{[Date].[Calendar].[Month].&[2007]&[9].LAG(12):[Date].[Calendar].[Month].&[2007]&[9]}

MEMBER [Measures].[Month Rank] AS
RANK(
[Date].[Calendar].CURRENTMEMBER
, ORDER ([Last 12 Months], [Date].[Calendar].CURRENTMEMBER.properties("key"), BASC)
)

MEMBER [Measures].[Liner Regression Trend Point] AS
LinRegPoint([Measures].[Month Rank]
,[Last 12 Months]
,[Measures].[Sales Amount]
,[Measures].[Month Rank])
,FORMAT_STRING = "Currency"

MEMBER [Measures].[MonthValue] AS
[Date].[Calendar].CURRENTMEMBER.membervalue

Select {[Measures].[MonthValue],[Measures].[Sales Amount]
,[Measures].[Liner Regression Trend Point]} on columns,
[Last 12 Months] on rows

from [Adventure Works]

MDX #4 – Sales Ratio aganist Parent Levels

Business Question 1: How much sales percentage does each product sub-category take aganist the total sales of ‘Components’ cateogry in 2007

t1

In the example above, the .parent is used to return the current product’s parent category. Alternatively, ANCESTOR function can also be used which could specify the level of the category (e.g, we hope to get the percentage of current product aganist top-level product category).


WITH
MEMBER [Measures].[Product Sale] AS
SUM(
[Product].[Product Categories].currentmember,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Total Sale in Category] AS
AGGREGATE(
[Product].[Product Categories].currentmember.parent,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales % of Parent Category] AS
IIF ([Measures].[Total Sale in Category]=0,
NULL,
[Measures].[Product Sale]
/[Measures].[Total Sale in Category]
)
, FORMAT_STRING="##.#%"

SELECT
{
[Measures].[Product Sale],
[Measures].[Sales % of Parent Category]}  ON COLUMNS,
ORDER
([Product].[Product Categories].[SubCategory].members
,[Measures].[Sales % of Parent Category], BDESC) ON ROWS
FROM
(SELECT [Product].[Product Categories].[Category].&[2] ON COLUMNS
FROM [Adventure Works]
)

Where [Date].[Calendar].[Calendar Year].&[2007]

 

Business Question 2: List product sub-categories which make up 80% of total sales in 2007

t2

We could acheive this using TOPPERCENT function.

WITH
MEMBER [Measures].[Product Sales] AS
AGGREGATE(
[Product].[Product Categories].currentmember,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Total Sales in Category] AS
AGGREGATE(
[Product].[Product Categories].currentmember.parent,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales % of Parent Category] AS
IIF ([Measures].[Total Sales in Category]=0,
NULL,
[Measures].[Product Sales]
/[Measures].[Total Sales in Category]
)
, FORMAT_STRING="##.#%"

SELECT
{
[Measures].[Product Sales],
[Measures].[Sales % of Parent Category]}  ON COLUMNS,
TOPPercent(
[Product].[Product Categories].[SubCategory].members
,80
,[Measures].[Sales % of Parent Category]) ON ROWS
FROM
(SELECT [Product].[Product Categories].[Category].&[2] ON COLUMNS
FROM [Adventure Works]
)
Where [Date].[Calendar].[Calendar Year].&[2007]

 

Business Question 3: ABC analysis on the product Sales in UK in 2007

123

We want to classfy the products based on their sales amount in UK in 2007 into three categories: ‘A’ (make up the top 5% of total sales), ‘B’ (make up the top %5-20%), ‘C’ (the others). Firstly, we need rank the products based on their sales amount in UK in 2007 and calculate the cumulative sales amount by the rank. Then, we need calculate the percentage of the cumulative sales amount for each product and check which category is fallen into.

WITH
SET [Products on Sales] AS
ORDER
(
EXISTING NONEMPTY ([Product].[Product Categories].[Product].MEMBERS,
[Measures].[Sales Amount])
, [Measures].[Sales Amount], BDESC
)

MEMBER [Measures].[Cumulative Sales Amount by Rank] AS
SUM (
HEAD ([Products on Sales]
, RANK ([Product].[Product Categories].CURRENTMEMBER,
[Products on Sales])
)
, [Measures].[Sales Amount]
)

MEMBER [Measures].[Cumulative Sales % of parent category] AS
[Measures].[Cumulative Sales Amount by Rank]
/SUM ([Products on Sales],[Measures].[Sales Amount])
, FORMAT_STRING = "percent"

Member test as
[Products on Sales].CURRENTMEMBER.LAG(1).name

MEMBER [Measures].[Classfication Category] AS
CASE
WHEN [Measures].[Cumulative Sales % of parent category]<=.05 THEN 'A'
WHEN [Measures].[Cumulative Sales % of parent category]<=.2 THEN 'B'
ELSE 'C'
END

SELECT {[Measures].[Sales Amount],[Measures].[Classfication Category]} ON COLUMNS,
[Products on Sales] ON ROWS

FROM [Adventure Works]

WHERE ([Date].[Calendar].[Calendar Year].&[2007]
,[Sales Territory].[Sales Territory].[Country].&[United Kingdom])

MDX #3 – Year-Over-Year Comparison within Given Period

Requirement: Users specify a date range by giving a Start Date and a End Date, and expect to make Year-Over-Year comparison of a measure (e.g. Sales Amount) in the given period. For example, a user input start date as “04/07/2007” and end date as “04/11/2007”, and want to compare the sales between the period “04/07/2007 – 04/11/2007” and the same period in last year “04/07/2006 – 04/11/2006”.

MDX_01

First, we need get the data range using the range operator ‘:’ on the given start date and end date, and aggregate the sales occurred within that period.

MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

Then we could use PARALLELPERIOD to get the same period in last year. Please note that we could use range operator within a PARALLELPERIOD function, so we have to separately PARALLELPERIOD the start date and the end date, and use range operator afterwards.

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060211]")
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060911]")
)
}
, [Measures].[Reseller Sales Amount]
)

Below is the complete script:

WITH
MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@StartDate, CONSTRAINED)
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@EndDate,  CONSTRAINED)
)
}
, [Measures].[Reseller Sales Amount]
)

SELECT {
[Measures].[Sales in Given Period],
[Measures].[Sales in Given Period Last Year]
} ON COLUMNS,
{
[Product].[Product Categories].[Category]
} ON ROWS
FROM [Adventure Works]

Tip: when using STRTOMEMBER function to convert the data string into Member value, the data string has to be in correct format. We could directly pass data parameter into MDX query and build the data string to the correct format in the MDX script. However, a better way is to build the data string at SSRS report level (using expression in the parameter of dataset) as the snapshot shown below:

MDX_02

MDX #2 – Top (n) Products Sales in each Sales Territory

To get top (n) products with highest sales in every sales territory, the GENERATE function is exactly what we need which applies a set to each member of another set.

SELECT
	{[Measures].[Reseller Sales Amount]} ON Columns,
	GENERATE(
		[Sales Territory].[Sales Territory].[Country].MEMBERS
		,TOPCOUNT([Sales Territory].[Sales Territory].CURRENTMEMBER
			*[Product].[Product].[Product].MEMBERS
			,5
			,[Measures].[Reseller Sales Amount]
		)
	) ON ROWS
FROM [Adventure Works]

MDX03