SSRS conditional execution of dataset

2
votes

I have two different datasets in SSRS report which gives different number of output fields.I have used this two data set into two different tables in report.

1) One table will display output at a time in execution based on condition.

Actually while running the report two datasets are executing the SP and it takes more time to display the output.

Requirement: I need to execute the one dataset SP at time based on condition.Other Dataset SP should not Execute.

Example: Dataset1 executes Sp1 Dataset2 executes Sp2

Table1 uses Sp1 Table2 uses Sp2

Normally while executing report Table1 will display output(Based on default parameter selection)

But SP1 and SP2 are executing on same time.so report takes more time to display output.

I need to execute 1 SP at a time based on condition.so that other dataset SP will not execute.

4
sqlsql-serverreporting-servicesssrs-2008ssrs-2008-r2
Have you tried the Visibility for your tablix? put a check condition with certain date and time in the expression – LONG
yah based on visibility i showing two diffrent tables. but Two data set SP are Executing at same time.so it takes huge time to display single table output – Hell Boy
I am not sure there is a way to let SSRS works more efficient, sometimes it even gives me timeout error – LONG
You will need to rewrite your datasets so that all the data for both tables can be pulled from one dataset. If your second SP is never called it won't populate the dataset, and therefore will display no data. – bushell
@bullshell--Both dataset SP are giving different no of output fields.so we cant use it in same SP – Hell Boy

4 Answers

3
votes

Step1: First Create Dummy SP for Dataset .That SP should have same input parameters and same output fields as original SP1 but gives zero output rows.Do the same for SP2 for Dataset 2

Step2: In Dataset properties select StoredProcedure Icon and in Fx column add below code

=IIF(Parameters!ManagerID.Value= -1,"SP1","DummySP")

Note: DummySP created should be same like SP1 Do the same for Dataset 2..and this works.

0
votes

You must control the visibility of tablix depends on the received parameter, in additional you can create parameters on both queries to control where execution to avoid the execution, like 

Where @Condition = 1 AND ( Your WHERE )
0
votes

As for that sack of performance, you don't want to run other SP at all, in this case, as per my experience, the best way is to modify your stored procs to add a new parameter based on what you are deciding what data set to call (means what data to pull).

So that, if the parameter says that the specific SP is not required, don't run the code in SP using IF-ELSE combo and return blank single row.

And then, on top of that, you can hide or show your tablix.

Hope that makes sense?

And if the column count and data types are same, you can always use an expression to decide what stored-proc to call.

0
votes

I have seen this answer, it's nice, but to create two SP's, it is sometimes very cumbersome. Sometimes the report uses a SP's many. This means that all SP's must be duplicated. And if you double the reports many... This means that they need to be maintained and improved throughout their lives.

Instead I have another solution:

Step 1: I create one SP, and use one of the parameters that already exists anyway.

Step 2: In the mapping of the DS\Report's parameters, instead of sending to DS the parameter sent to the report as it is, you can write an expression, which says that if I want to display the data of this SP I send the parameter sent to the report, and if I do not want to display this data, I will send different parameter , Which will cause the SP not to return data.

All this of course only if it is a procedure with parameters that allow the above. It is also possible to create a special parameter for this.

The following is a picture and example of an expression: enter image description here

and the expression:

=IIf(Parameters!Display_xxx.Value=1,Parameters!yyy.Value,0)