…brew your own Business Intelligence

Creating a Pareto Chart in SSRS

Recently, I created an SSRS report which used pareto charts to help a client analyze the impact of unplanned events in their production facilities across multiple dimensions (ex. Location, Equipment, Reason Code etc). In this post I’ll walk you through the steps to re-create this report based on the AdventureWorks sample dataset.

Create a Basic Source Query

Whenever I’m creating an SSRS report, the first thing I do is fire up SSMS and put together a basic source query. At this point I’m not concerned with parameters, filters or any of that jazz…I just want to make sure I have the logic and shape necessary to meet the requirement.

Below is the basic source query I hacked together for this report:

[sql] WITH
SET [Products] AS
ORDER(
[Product].[Product].[Product].Members
,[Measures].[Internet Sales Amount] ,DESC
)
MEMBER [Measures].[Level] AS
RANK(
[Product].[Product].CurrentMember
,[Products] )
MEMBER [Measures].[Internet Sales Amount – RT] AS
SUM(
HEAD(
[Products],
(
[Measures].[Level],
[Product].[Product].CurrentMember
)
),
[Measures].[Internet Sales Amount] )
MEMBER [Measures].[Internet Sales Amount – Total] AS
SUM(
[Products],
[Measures].[Internet Sales Amount] )
MEMBER [Measures].[Internet Sales Amount – RTPctOfTotal] AS
[Measures].[Internet Sales Amount – RT]/[Measures].[Internet Sales Amount – Total] ,FORMAT_STRING = "percent"
SELECT
NON EMPTY {
[Measures].[Internet Sales Amount],
[Measures].[Internet Sales Amount – RT],
[Measures].[Internet Sales Amount – Total],
[Measures].[Internet Sales Amount – RTPctOfTotal],
[Measures].[Level] } ON 0,
NON EMPTY {
[Products] } ON 1
FROM
[Adventure Works] WHERE
[Date].[Calendar Year].&[2007] [/sql]

And here’s a screenshot of the result set:

click to zoom

click to zoom

The result set includes more more measures than necessary which is often helpful for those new to pareto analysis or just simply sanity checks:

Internet Sales Amount: this is the internet sales amount for the product in the left column (which are listed in descending order by sales amount)
Internet Sales Amount – RT: this is the running total of Internet Sales Amount
Internet Sales Amount – Total
: this is the total Internet Sales Amount for all products listed
Internet Sales Amount – PctOfTotal: (not shown) is a simple percent of parent calculation [Internet Sales Amount]/[Internet Sales Amount – Total]
Internet Sales Amount – RTPctOfTotal: this is the running total of Internet Sales Amount – PctOfTotal
Level
: this is the rank of each product based on sales amount

Create a Basic Report

Once we have a basic source query, we can create a basic report.  Again, at this point we still aren’t concerned with the bells and whistles (parameterization, filtering, etc).  We are only concerned with getting basic source query displayed.

Here’s what my basic report looks like:

click to zoom

click to zoom

As you can see all I’ve done is create a data source (pointing to the OLAP database), create a dataset (based on the basic source query), and add a chart to the report displaying the data.

Note: I’m not going to get into the details of configuring the chart…but I will include a copy at the end of this post which you can reverse engineer if you’re curious how to set it up.

Parameterize the Report: Date Range

Now that we have our basic report put together and everything checks out, it’s time to add a few parameters.  One of our requirements call for providing the user with the ability to filter the data in the report by a custom date range.

First step is to add 2 parameters (pStartDate, pEndDate) to the report:

click to zoom

click to zoom

Next we can update our dataset and parameterize the source query:

click to zoom

click to zoom

Once the query text is updated, you need to add the corresponding entries to the parameter tab of the dataset:

click to zoom

click to zoom

Since our date parameters are of type date/time, we need to use expressions to get them into the format that’s compatible with our StrToMember function references in the source query.

click to zoom

click to zoom

Now we can flip over to the preview tab and test with a few different combinations of date values to make sure everything is wired up correctly.

click to zoom

click to zoom

Parameterize the Report: Top N

As you can see from the above screenshot, everything appears to be working correctly…but, the product set is quite large and it’s compressing the x-axis to the point that the visualization is worthless (other than noting that there is a very small subset of contributors that make up the majority of overall sales – which is in fact interesting in and of itself). So, in order to make this chart more a bit more useful, we’re going to add a Top N parameter.

The first step is to add a dataset that we’ll use to populate the Top  N parameter. In this case, since we are generating this dataset out of thin air, we’ll need to add a new OLTP data source:

click to zoom

click to zoom

Now we can create the Top N parameter based on that dataset we just created:

click to zoom

click to zoom

click to zoom

click to zoom

Next we need to refactor our MDX source query.  In this case we can use the TOPCOUNT function (which implicitly orders the set) and parameterize the 2nd parameter:

click to zoom

click to zoom

Now, just like with the date range parameters, we need to add the entry in the parameters tab of the dataset:

click to zoom

click to zoom

And when we run the report we see that the Top N parameter is now controlling the number of bars in the chart.  I also went ahead and added a tooltip on the chart series to display the corresponding product name and value when the user mouses over the individual bars.

click to zoom

click to zoom

 

But Wait, There’s More!

What we’ve done so far is nice but we can take it to the next level by parameterizing the actual measure we wish to analyze via the pareto charts.

The first step is to create a new dataset that will be used to populate our new “meaures” parameter.  In this case we are running it as a DMX query against the $SYSTEM.MDSCHEMA_MEASURES SSAS dynamic management view.  Vincent Rainardi (b | t) has a nice post (here) where you can read more about querying the SSAS dynamic management views.

[sql] SELECT Measure_Name AS ParamLabel
,Measure_Unique_Name AS ParamValue
FROM $system.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’ AND
MEASUREGROUP_NAME = ‘Internet Sales’
ORDER BY Measure_Name
[/sql]

click to zoom

click to zoom

Then we create the parameter and preview the report to make sure the parameter values are populating correctly:

click to zoom

click to zoom

click to zoom

click to zoom

click to zoom

click to zoom

Note: Not all measures makes sense in this type of analysis so consider each measure in the target measure group and decide whether you need to explicitly filter any of them out of the resultset.

Now we have the more difficult task of updating the source query of our dataset.  Unfortunately, we can’t simply pop an @Measure parameter in there like we did for @TopN.  Instead we have to turn the source query in to a dynamic expression…which can be a little painful in the SSRS expression editor.

click to zoom

click to zoom

click to zoom

The second screenshot above should give you a better idea of what I mean by painful…it’s a whole bunch of string concatenation..yick!  You should really try to avoid this as much as possible because it can be difficult to troubleshoot and maintain…but there are always exceptions 😉

Here’s what the report looks like with everything wired up:

click to zoom

click to zoom

click to zoom

click to zoom

Another thing to notice (you’ll see it better if you download the project) is that I’ve generalized the query and calculated member names.  This makes it easier to create copies of the dataset to use for different dimension attributes.

click to zoom

click to zoom

Here’s a copy of the project: SSRS-Pareto-Analysis.zip

 

Next Steps

If you want to take this to the next level, spend some time discussing logical drilldown paths with the SMEs (subject matter experts) for the report in question. Then build in that drilldown functionality by configuring drillthrough actions on the bars of the pareto chart.

Building from the example in this blog post, we might configure a drillthrough action on the bars of the pareto chart representing the sales amount for each product that opens a separate report on the showing sales information by customer filtered by the product from which the drillthrough action occurred. We could even parameterize the drillthrough action and offer the users a choice of drilldown paths.

Leave a Reply