Conditional formatting in ssrs

NishchalG 41

Hello :)
I have two questions.

1] In SSRS can we have conditional formatting ?

Also suppose there is a table

SR.NO. PRODUCT QUANTITY RATE TOTAL

1 LED BULB 10 100 1000

2 LED TUBE ITEM CANCELLED *** CANCEL ORDER NUMBER CO-0125

3 LED SPOT LIGHT 05 250 1250

If 2nd item is cancelled then I need to print text as shown above....

2] A SQL Server stored procedure returns two tables.

can we have data from both tables? In two different data regions ?

Warm regards..
Nishu

Joyzhao-MSFT 15,566 Reputation points

2021-11-10T01:47:27.327+00:00

Hi @NishchalG ，
If you have any questions, please feel free to let me know.
Regards,
Joy

1 answer

Joyzhao-MSFT 15,566 Reputation points

2021-11-08T03:37:45.663+00:00

Hi @NishchalG ,
Usually we use IIF Fuction and Switch Fnction in SSRS to set the color, visibility, etc. of the report. Please see: Conditional Formatting for SQL Server Reporting Services Reports.

A report can have multiple data regions, but one data region corresponds to a dataset. If you want to obtain data from multiple datasets at the same time, you can use Lookup Function or LookupSet Function, etc. For more information, please refer to: Expression examples in paginated reports (Report Builder).
At the same time, we can use Subreports , Nested data regions , Drillthrough report and Drilldown action in the report. For more information, please see: Drillthrough, drilldown, subreports, and nested data regions in a paginated report (Report Builder).
Best Regards,
Joy

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
NishchalG 41 Reputation points

2021-11-08T07:26:30.563+00:00

Thanks Joyzhao-MSFT

I will check the resources you suggested. I will let you know if they were helpful.

Warm regards
Nishu

NishchalG 41 Reputation points

2021-11-08T08:41:01.91+00:00

Joyzhao-MSFT I am sorry I did not make my problem clear... Along with conditional formatting, my problem is multiple tables come from a single stored procedure. There is a stored procedure on SQL SERVER which receives some parameters and after making some checks returns FOUR TABLES. Now when I create a data set and make query type as stored procedure only first table's fields are visible. How can I handle other tables ?

Joyzhao-MSFT 15,566 Reputation points

2021-11-08T09:23:12.167+00:00

Do you mean that your stored procedure returns four result sets, but only one result set is returned when creating a dataset query?
Unfortunately, as the documentation explains here:

If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored.

Therefore, I suggest using one of two possibilities:

1.Split the procedure into separate procedures - for example,one which returns data from EMP, and one from DEPT - and access the new procedures as two separate datasets.

2.Union the two separate queries (with an additional column to indicate which query produced each row) and filter or conditionally format your report appropriately. The unioned query might look something like this:

SELECT EMPID ID, ENAME NAME, JOB JOB_LOC, SAL, DEPTID, 'EMP' SOURCE FROM EMP UNION ALL SELECT DEPTID ID, DNAME NAME, LOC JOB_LOC, NULL SAL, DEPTID DEPTID, 'DEPT' SOURCE FROM DEPT

if I misunderstand what you mean, please feel free to correct me.

NishchalG 41 Reputation points

2021-11-08T11:53:55.87+00:00

Joyzhao-MSFT you understood my problem correctly.

As you pointed , if multiple result sets are retrieved through one query then only first set is processed and rest sets are ignored. That is exactly what I experienced. Its is really very unfortunate.

That means if we have to migrate to SSRS then we will have to make may changes...

AS YOU SUGGESTED UNION IS THE ANSWER TO THIS PROBLEM.

Thanks for your reply and valuable advice...

Warm regards.
Nishu

Joyzhao-MSFT 15,566 Reputation points

2021-11-08T14:12:05.333+00:00

Hi @NishchalG ,
Thanks for your feedback.
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. Thank you.
Best Regards,
Joy
Sign in to comment