question

NishchalG-5579 avatar image
0 Votes"
NishchalG-5579 asked Joyzhao-MSFT commented

Conditional formatting in ssrs

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

sql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT commented

Hi @NishchalG-5579 ,
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.


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Joyzhao-MSFT

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

Warm regards
Nishu

0 Votes 0 ·

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 ?

0 Votes 0 ·

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.

1 Vote 1 ·

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

0 Votes 0 ·
Show more comments