Run The SSRS Dataset based on the rendering options like CSV

Question

I have two different datasets in SSRS report which gives different outputs. I have used one dataset for summarized information (i.e Chart) and other is used for actual data (Table).

1) Chart will display output at the time of report execution. 2) Table should execute only when the report renders into CSV.

Requirement: I need to execute the one dataset SP at report run time. Other Dataset SP should not Execute when the report is running it should run only when the report rendering into CSV format

Example: Dataset1 executes Sp1 Dataset2 executes Sp2

Chart uses Sp1 and Table uses Sp2

Normally while executing report Chart will display output. But SP1 and SP2 are executing at the same time.so report takes more time to display output.

So I need to restrict the Actual data dataset when we running the report.

niktrs niktrs · Accepted Answer · 2018-05-04T10:39:51

There is no way to create a conditional Dataset based on Globals!RenderFormat.Name and let reporting services handle it automatically

The only alternatives to your question:

Use SP2 as a common dataset in order to save SP1 execution time and let reporting services summarize the data in the chart

OR

Insert a format variable with 2 values (eg. 1. Chart, 2. Table for CSV) and let the user choose the right one before running the report. Use an Iif condition to control dataset execution e.g.for the chart
= Iif( Parameters!Format.value=1, "SP1 @run=1", "SP1 @run=0")

Note that this requires adding a parameter to your stored procedures and use them to control the execution of the query

SELECT ...
FROM ...
WHERE ...
@run = 1

Run The SSRS Dataset based on the rendering options like CSV

1 Answers