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,
- Grouped on Product Category
- Grouped on Product Category + Product Name
- Grouped on Product Category + Product Name + Sale Year
- 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.
]]>