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...
6 comments
Read more

How to do Error Handling for Transaction using Try Catch Block

Issue : Some time we get requirements where we have to use transaction to do some DML operations to make sure the unit of work should be done completely. But there could be error while doing this. So, how we can cope up with errors to achieving this? Solution: We can use Transaction in the Try Catch block to make sure that the transaction either successful or rollback if there is any error. Below is an example for this: BEGIN TRY --Start the Try Block.. BEGIN TRANSACTION -- Start the transaction.. UPDATE [dbo] . [Table] SET salary = salary * 1.1 WHERE MID = 6 --DML operations to be done in the transaction COMMIT TRAN -- Transaction Success! END TRY --End of the Try Block BEGIN CATCH --Start the Catch Block IF @@TRANCOUNT > 0 --check the error count for the errors ROLLBACK TRAN --RollBack in case of Error --to raise the error message when there is an issue. Declare the variables and se the values for them DECLARE @Er...
Post a Comment
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