Skip to main content

SSRS Reports - How to set chart category dynamically in SSRS Reports?

How to set a dynamic category in the charts:

Sometimes there is a requirement where we want to analyze the data based on different dimensions at run time.
So it’s like at run time we can analyze the same measure with different dimensions.


Solution:

We can achieve this by using a filter in the report which allows the user to select the dimension against which they want to analyze the data (Measures) at run time and using this value in the charts “category group” to dynamically set the values.


Create a data set for the filter:

  1. Filter for the Dimension Names: for Example

SELECT     'DepartmentGroupName' AS Dimension
UNION
SELECT     'OrganizationName' AS Dimension
UNION
SELECT     'ScenarioName' AS Dimension

Create a data set for the Report data:

  1. Data for the Report:  for example(Just selecting the top 1000 rows for the demo)

SELECT    
    TOP 1000
DDG.DepartmentGroupName,
DO.OrganizationName,
DS.ScenarioName,
FF.Amount
FROM
dbo.FactFinance as FF
INNER JOIN dbo.DimDepartmentGroup DDG
ON  FF.DepartmentGroupKey = DDG.DepartmentGroupKey
INNER JOIN dbo.DimOrganization DO
ON  FF.OrganizationKey = DO.OrganizationKey
INNER JOIN  dbo.DimScenario DS
ON  FF.ScenarioKey = DS.ScenarioKey


  1. Report Layout:
a.       Add a parameter in the Report:



b.      Specify the available value from the Dataset which returns the names of different Dimensions list.




c.       Specify a default value for the Dim Parameter. For example “DepartmentGroupName”




d.      In the Report layout add a chart:
For example a column chart

In the chart, Values select the measure which you want to analyze (e.g. Sum (Amount))

In the category Groups write an expression on “Group on” and “label” like:

=iif(Parameters!Dim.Value="DepartmentGroupName",Fields!DepartmentGroupName.Value,iif(Parameters!Dim.Value="OrganizationName",Fields!OrganizationName.Value,iif(Parameters!Dim.Value="ScenarioName",Fields!ScenarioName.Value,Nothing)))





In the chart title and X-axis Label use the Parameter value to dynamically change the values to be displayed in the chart.






  1. Preview the Report:


  1. Change the value in the filter to “OrganizationName” and run the report:




We can use the same approach for the Measures also and the chart will be totally dynamic!!

Note: Report data is taken from the AdventureWorksDW2008R2 sample database!
Labels:

Comments

  1. AnonymousJune 5, 2014 at 5:59 AM

    Thanks a lot sir! such simple & clear guidance!

    ReplyDelete
  2. sahilJuly 6, 2014 at 5:53 AM

    Hi Sir,

    I am just learning ssrs and I have been given a task to achieve wherein if there is total population of india is shown on pie chart state wise like UP,BIHAR,PUNJAB,MP etc if I click on one of the state then it should take me to another pie chart showing the population city wise for eg.If I click on UP state then it should take me another pie charts showing the population in cities in Up like merut,lucknow & allhabad and then if I click on one of the city then it should take me another pie charts showing the population of the cities area wise so basically its like drill down report but purely pie chart .Can we achieve that in SSRS like power view in ms excel? Please reply this

    ReplyDelete
  3. AnonymousMay 21, 2015 at 2:30 AM

    Great tutorial, fully solved my requirements.
    Thanks a lot !!! :)

    ReplyDelete

Post a Comment

Popular posts from this blog

SSRS Reports - Space/Gap issue between the objects in SSRS Reports

In Reports where some objects are set to be shown/hide based on some expression/filter condition etc. There Could be some white space issue between the objects on the report( i.e. when few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report). Following are few ways using which we can handle this kind of issue: 1. Remove the Gap between the objects in the report layout. 2. Or put a text box and make the border the same as the background color and apply the same show/hide action on it. 3. If you are using a table/matrix then instead of putting the space between objects, just add one static row above the column header for the table/matrix and make the border color the same background color. below is one approach: The report before any space handling: -----------------------------------------------------------------------------  Report Preview when all the o...
5 comments
Read more

SSRS Reports - When few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report

In Reports where some objects are set to be shown/hide based on some expression/filter condition etc. There Could be some white space issue between the objects on the report( i.e. when few objects are hidden in the report, the white space between the objects will accumulate and displayed as a big gap between the objects on the report). Following are few ways using which we can handle this kind of issue: 1. Remove the Gap between the objects in the report layout. 2. Or put a text box and make the border the same as the background color and apply the same show/hide action on it. 3. If you are using a table/matrix then instead of putting the space between objects, just add one static row above the column header for the table/matrix and make the border color the same background color. below is one approach: The report before any space handling: -----------------------------------------------------------------------------  Report Preview when all the obj...
Post a Comment
Read more

SSRS Reports - How to Create Static Rows in a Matrix in SSRS?

Today one of my team members asked me about how to create a matrix in which the rows will remain static/constant and the column group will grow as per the data available. SQL Server Reporting Services 1. Create a data set to get the required data for the matrix. 2. Drag a matrix object on the report body. 3. Add the number of required rows to outside the row group  (This is the main trick for creating the static rows in the matrix) 4. Delete the row group detail row from the matrix 5. Add the labels to the row lines headers, add the column group values and data values in the matrix 6. Run the Report and your Matrix is ready with Static Rows! Happy learning!!!
1 comment
Read more