Generalizing Charts in Reporting Services

I recently had to create a chart in
SSRS which nneded to be have some generalized settings – in this case the X-axis of the chart needed to be adjusted
as per the supplied parameters. The X-axis can be any type of values : Category values, Year or Month values. I
have a tablix in my report and based on the action performed on it, the Chart
should change the X-axis. A simple solution is to create three
different charts and show/hide them by controlling the visibility property. However,
I wanted a more flexible solution.

Solution

Let’s take a simple scenario of a retail store. There are
two tables Product and Sales, which represents the Product list and the Sale of
the products respectively.

The below script will create these tables:

CREATE TABLE Product (  ProductID int,
                                    ProductCategory varchar(100),
                                    ProductName varchar(100)
                                )

CREATE TABLE Sales      (     ProductID int,
                                    SaleDate datetime,
                                    ActualSales bigint,
                                    TargetSales bigint
                              )

After executing the table creation script, let’s insert some
data in both the tables.

INSERT INTO Product(ProductID,ProductCategory,ProductName)

VALUES (1,'Media','Books'),
      (2,'Media','DVD'),
      (3,'Media','Music'),
      (4,'Apparel and Accessories','Clothing'),
      (5,'Apparel and Accessories','Shoes'),
      (6,'Electronics','TeleVisions'),
      (7,'Electronics','Refrigerators'),
      (8,'Electronics','Mobiles'),
      (9,'Electronics','Laptops'),
      (10,'Consumables','Vegetables'),
      (11,'Consumables','Fruits'),
      (12,'Consumables','Grocery'),
      (13,'Consumables','Snacks'),
      (14,'Consumables','Drinks')
GO

;WITH CTE_DT as
(
      SELECT GETDATE() SaleDt
      UNION ALL
      SELECT DATEADD(M,-1,SaleDt)
      FROM CTE_DT
      WHERE Year(SaleDt)>=2005
)

INSERT INTO Sales(ProductID ,SaleDate , ActualSales , TargetSales )
SELECT Prod.ProductID,Dt.SaleDt,ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))/1000
,ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))/1000

FROM Product Prod
CROSS JOIN CTE_DT DT
WHERE Year(SaleDt)>=2005

Inserting data in Product table should be straightforward,
however, as we need some real time data in the Sales table, I have used a
function to populate random values for the Sales figures, in additione due to the volume of data required I have used a CTE to recursively populate the
Date values.

We have now two tables Product and Sales, and we will create
a tabular report on these tables. In Product we have the Product Category, Product
Name and in Sales we have the Actual and Target Sales for the Products.

Create an SSRS report, Insert a TABLIX in it.

Next, create a report dataset as shown below:

 The query used in the dataset is shown below:



SELECT Prod.ProductCategory,
Prod.ProductName,
SaleYear = YEAR(Sls.SaleDate),
SaleMonth = MONTH(Sls.SaleDate),
TotalActualSales = SUM(Sls.ActualSales),
TotalTargetSales = SUM(Sls.TargetSales)

FROM Sales Sls

INNER JOIN Product Prod
ON Prod.ProductID = Sls.ProductID

GROUP BY Prod.ProductCategory,
Prod.ProductName,
YEAR(Sls.SaleDate),
MONTH(Sls.SaleDate)

We are grouping the Sales on Product Category, Product, Year
and Month and calculating the Total Sales. A Few rows of the output of the query
is shown below;

After creating the dataset, use the dataset in the Tablix
inserted in the report. Add the columns in any form ( I have added them as
below, to show a grouping format).

Here, I have created three row groups: Product Category,
Product Name and Sale Year.

Previewing, the tablix, and drill down to the Product Name :

Drilling down further to Sale year and Sale Month:

Once this tablix is created, insert a Bar Chart in the
report. This is where out actual problem solution starts.

We need to show three things in the chart :

1 When
Product Category is clicked on the tablix, the chart should display the Sales for all
the Product Names of the selected Product Category with Category shown on the X-Axis,
and Total Actual and Target sales in the Series.

2 When
Product Name is clicked on the tablix, the chart should display the Sales for all the
Sale Years of the selected Product with Sale Year on the X-Axis.

3 When
Sale Year under the Product is clicked, the chart should display the Sales for all
the Sale Months of the selected year with Sale Month on the X-Axis.

To make this possible, I have created a procedure, which
will be called in the report dataset for the chart.

CREATE PROCEDURE sp_GenericChart
@ProductCategoryFilter varchar(100) = '-1',
@ProductNameFilter varchar(100) = '-1',
@SaleYear varchar(100) = '-1'

AS

SELECT ChartCategoryGroupName, ChartCategoryGroupValue,TotalActualSales, TotalTargetSales

FROM
(
SELECT DISTINCT 'Product Category' ChartCategoryGroupName,
                        Prod.ProductCategory ChartCategoryGroupValue,
                        TotalActualSales = SUM(Sls.ActualSales),
                        TotalTargetSales = SUM(Sls.TargetSales)

FROM Sales  Sls

INNER JOIN Product Prod
      ON Prod.ProductID = Sls.ProductID

WHERE CASE WHEN @ProductCategoryFilter = '-1' THEN '-1' ELSE Prod.ProductCategory END = @ProductCategoryFilter
AND CASE WHEN @ProductNameFilter = '-1' THEN '-1' ELSE Prod.ProductName END = @ProductNameFilter
AND CASE WHEN @SaleYear = '-1' THEN '-1' ELSE YEAR(Sls.SaleDate) END = @SaleYear

GROUP BY Prod.ProductCategory,
            YEAR(Sls.SaleDate)

UNION ALL

SELECT DISTINCT 'Product Name',
                        Prod.ProductName,
                        TotalActualSales = SUM(Sls.ActualSales),
                        TotalTargetSales = SUM(Sls.TargetSales)

FROM Sales  Sls

INNER JOIN Product Prod
      ON Prod.ProductID = Sls.ProductID

WHERE CASE WHEN @ProductCategoryFilter = '-1' THEN '-1' ELSE Prod.ProductCategory END = @ProductCategoryFilter
AND CASE WHEN @ProductNameFilter = '-1' THEN '-1' ELSE Prod.ProductName END = @ProductNameFilter
AND CASE WHEN @SaleYear = '-1' THEN '-1' ELSE YEAR(Sls.SaleDate) END = @SaleYear

GROUP BY Prod.ProductCategory,  
            Prod.ProductName,      
            YEAR(Sls.SaleDate)

UNION ALL
SELECT DISTINCT 'Sale Year',
                        CAST(YEAR(Sls.SaleDate) as varchar),
                        TotalActualSales = SUM(Sls.ActualSales),
                        TotalTargetSales = SUM(Sls.TargetSales)

FROM Sales  Sls

INNER JOIN Product Prod
      ON Prod.ProductID = Sls.ProductID

WHERE CASE WHEN @ProductCategoryFilter = '-1' THEN '-1' ELSE Prod.ProductCategory END = @ProductCategoryFilter
AND CASE WHEN @ProductNameFilter = '-1' THEN '-1' ELSE Prod.ProductName END = @ProductNameFilter
AND CASE WHEN @SaleYear = '-1' THEN '-1' ELSE cast(YEAR(Sls.SaleDate) as varchar) END = @SaleYear

GROUP BY Prod.ProductCategory,
            Prod.ProductName,
            YEAR(Sls.SaleDate)           

UNION ALL

SELECT DISTINCT 'Sale Month',
                        CAST(MONTH(Sls.SaleDate) as varchar),
                        TotalActualSales = SUM(Sls.ActualSales),
                        TotalTargetSales = SUM(Sls.TargetSales)

FROM Sales  Sls

INNER JOIN Product Prod
      ON Prod.ProductID = Sls.ProductID

WHERE CASE WHEN @ProductCategoryFilter = '-1' THEN '-1' ELSE Prod.ProductCategory END = @ProductCategoryFilter
AND CASE WHEN @ProductNameFilter = '-1' THEN '-1' ELSE Prod.ProductName END = @ProductNameFilter
AND CASE WHEN @SaleYear = '-1' THEN '-1' ELSE cast(YEAR(Sls.SaleDate) as varchar) END = @SaleYear

GROUP BY Prod.ProductCategory, 
            Prod.ProductName,
            YEAR(Sls.SaleDate),
            MONTH(Sls.SaleDate)

)Chart

In this procedure, there are three parameters for the three
distinct values which can be selected from the tablix and passed as a parameter
to the chart. Based on the parameters supplied, the procedure will return the
data, and above all if it is -1, then everything is to be returned. 

There are four different queries in the procedure,

  1. Grouped on Product Category
  2. Grouped on Product Category + Product Name
  3. Grouped on Product Category + Product Name + Sale Year
  4. Grouped on Product Category + Product Name + Sale Year + Sale Month

The most important logic of the procedure is to return two
columns used by the chart :

The ChartCategoryGroupName column will be used to decide what type Category Group on
the chart which is to be used on X- axis. These values will be hard coded into
the queries of the procedure. Here, we have four different groups:

  • Product
    Category
  • Product
    Name
  • Sale
    Year
  • Sale
    Month

The ChartCategoryGroupValue column is based on the Category Group, the Category Values will be populated. When the
Category Group is Product Category, the corresponding Category Values will be
‘Apparel and Accessories’,’Consumables’,’Electronics’,’Media’. Similarly for
Sale year, the values will be ’2012′,’2011′,’2010′ and so on.

These values will be actually shown on the
X-Axis, based on the filter on the chartcategorygroupname value.

All the generalized logic is on these two
columns.

When you execute the above stored
procedure, by passing -1, you will get the output below.

exec sp_GenericChart -1,-1,-1

Only few rows are shown here :

Once, the procedure is ready create a
report dataset as shown below:

This will create three parameters in the
report:

We need to create one more Parameter to the
report.

Create a new ChartCategoryGroupFilter for
the Generic chart and pass Product Category as the default value.

Set the chart data as shown below:

Add a filter to the chart based on the
ChartCategoryGroupNameFilter parameter created.

Since the filter has the default value of
Product Category, when you preview the chart for the first time, the chart will
show all the product categories.

Next, is to set action on the tablix
text boxes. Right click on the Product Category text box in tablix and select
Textbox properties.

Go to Action and select ‘Go to report’ in
the action, Select the same report, ‘GenericChart’. Then add the parameters
value and set the parameters for ChartCategoryGroupFilter as hardcoded values ‘
Product Name’ . We need to show all the Product Names for the selected
Product Category, so also pass the parameter value for Product Category from
the tablix itself.

Now, run the report, the first load will
show you the below chart

Select and Click ‘Consumables’ Product
Category from tablix.

In the chart, you can now see all the
Product Names under Consumables Product Category.

Similarly, set the Action on ProductName
and SaleYear columns in Tablix.

Preview the report, select and click
Drinks. The X-axis of the chart shows Sales for Sale years of Drinks zzzz;

Select and click Year ’2005′ in ‘Drinks’.
The X-axis of the chart shows Sales for Sale Months of Drinks for year 2005.

The generalization of the category groups on a chart can be
controlled in various ways. I have demonstrated just one way of doing this, this
procedure could be generalized further to take any category groups instead
of just the three queries.




Array

One Response to “Generalizing Charts in Reporting Services”

  1. I got it working! Added the following 3 tweaks:
    a) I added the following 3 output fields into each SELECT of the sproc:
    Prod.ProductCategory as ‘ProductCategory’,
    Prod.ProductName,
    YEAR(Sls.SaleDate) as ‘SaleYear’

    b) The above tweak allowed me to make a better chart title:
    =IIF(Fields!ChartCategoryGroupName.Value = “Product Category”,Fields!ChartCategoryGroupName.Value
    ,IIF(Fields!ChartCategoryGroupName.Value = “Product Name”, Fields!ChartCategoryGroupName.Value & ” : ” & Fields!ProductCategory.Value
    ,IIF(Fields!ChartCategoryGroupName.Value = “Sale Year”, Fields!ChartCategoryGroupName.Value & ” : ” & Fields!ProductCategory.Value & ” _ ” & Fields!ProductName.Value
    ,IIF(Fields!ChartCategoryGroupName.Value = “Sale Month”, Fields!ChartCategoryGroupName.Value & ” : ” & Fields!ProductCategory.Value & ” _ ” & Fields!ProductName.Value & ” _ ” & Fields!SaleYear.Value
    ,””))))

    c) To get the months to sort properly on the Chart, in the sproc in SELECT ‘Sale Month’ as ‘ChartCategoryGroupName’ piece, I used:
    right(’0′ + CAST(MONTH(Sls.SaleDate) as varchar),2) as ‘ChartCategoryGroupValue’,

    to make the month numbers be ’01′, ’02′, ’03′, etc.

    Here are 2 items I wish to improve:
    a) How to get the month names onto the chart axis instead of month numbers. Of course I could add another column of month name to the dataset, but you did NOT indicate such.
    b) How to keep the report tablix from refreshing on each “On Action” selection. It would be nice to leave the data where it was when one clicks a tablix cell and refreshes the chart. Is this possible?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |