How to control non-existent data items in SSRS chart at runtime?

2
votes

we have created a SSRS report with a big chart that have multiple entries in values

its a line chart so it is having many lines ( as number of entries in the value section)

we need to control the lines that shows in the chart from our ASPX page

its ok to create a chart with one line graph in it and its ok to create a chart with multiple line graphs in it

but the thing is the user should be able to select he want one line , 2 lines , 3 lines in the graph

so lets say the chart shows ( expenses, revenue , profit) as line graphs in one chart

if the user selects to see only revenue it should only show the line graph in the chart but if the user selects expenses & revenue so both line graphs should be shown in the same chart ( showing where they are intersecting if they are)

how to control this can i controle this by parameters and how ?

1
sql-servervisual-studiosql-server-2005reporting-servicesreportingservices-2005

1 Answers

6
votes

Chart lines in SSRS report can be made visible or hidden with the help of Expressions on the range values. Following example show how this can be done in SSRS 2005. Steps 1 - 9 show how this is achieved in SSRS 2005. Step 10 shows how the same functionality has been made easier in SSRS 2008 R2.

Step-by-step process: SSRS 2005

  1. The example assumes that you have already created an SSRS report and the report shown in screenshot #1 will be used to demonstrate how to add parameters and hide the Expenses, Revenue or Profit.

  2. Click on the menu Report --> Report Parameters... as shown in screenshot #1. Create three Boolean type parameters namely ShowRevenue, ShowExpenses and `ShowProfit' as shown in screenshots #3 - #5. Configure the values and default value as shown in screenshots.

  3. Once the variables have been created, right-click on the Chart and select Properties as shown in screenshot #6.

  4. On the Chart Properties, select the Data tab. Click on Revenue under the Values section and click Edit... button as shown in screenshot #7.

  5. On the Edit Chart Value dialog, click on Expression button against the Value as shown in screenshoit #8.

  6. Configure the expression for Revenue value series as shown in screenshot #9. This condition will check the value of the parameter ShowRevenue. If set to Yes/True, the value will be displayed, otherwise the line will not be shown. Click OK twice to get back to the Chart Properties dialog.

  7. Similarly, configure the expression for Expenses and Profit as shown in screenshots #10 and #11.

  8. Screenshot #12 shows the sample report execution when all the parameters are set to Yes.

  9. Screenshot #13 shows the report execution when the parameter Show Expenses is set to No. You can notice that the blue line corresponding to Expenses is no longer visible.

  10. The same can be achieved much easily in SSRS 2008 R2 with the help of Visibility property available at the Values level. Screenshots #14 - #16 shows how this can be achieved in SSRS 2008 R2. The report also hides the legend label accordingly.

Hope that helps.

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6

Screenshot #7:

7

Screenshot #8:

8

Screenshot #9:

9

Screenshot #10:

10

Screenshot #11:

11

Screenshot #12:

12

Screenshot #13:

13

Screenshot #14:

14

Screenshot #15:

15

Screenshot #16:

16