Can We Use Background Color For Parameter In Ssrs Tool

Setting alternating row colors in SSRS (SQL Server Reporting Services) is an of import visualization configuration for end-users and then that they can easily view their reports. All the same, setting alternating colors in SSRS is non a click of a push button configuration like in the Microsoft Excel.

The post-obit screenshot shows the output in Microsoft Excel when the Alternating row color is ready for a table.

However, in SSRS this is non straight forward equally in Microsoft Excel shown in the above screenshot.

Allow united states configure a report in SSRS.

SSRS Sample Written report

Post-obit is the SSRS project solution.

First, the data source is created for the AdventureWorks sample database in any SQL Server instance. After the data source creation, the next footstep is to create a information set with the following t-SQL lawmaking.

1 2 three iv five 6 7 8 9 10 11 12 13 xiv 15 16 17 18 xix

SELECT So . SalesOrderID , And so . RevisionNumber , SO . OrderDate , P . NAME AS ProductName , SOD . OrderQty , SOD . UnitPrice , SOD . LineTotal FROM Sales . SalesOrderDetail AS SOD INNER JOIN Sales . SalesOrderHeader AS SO ON SOD . SalesOrderID = And so . SalesOrderID INNER Bring together Production . Production AS P ON SOD . ProductID = P . ProductID WHERE SOD . SalesOrderID IN ( 43660 , 43663 , 43666 , 43672 , 43682 , 43685 ) ORDER Past And then . SalesOrderID

Delight note that only six orders are used for demonstration purposes. Likewise, the information set is sorted past the order past OrderID for the sit-in purposes.

Next is to create a tabular array in the SSRS report and link with the data set and you volition run across the post-obit report.

No major formatting was done to the report except for the rounding the Line full to the ii decimal points.

Alternating Row Colors in SSRS for Table

The next chore is to prepare alternating row colors in SSRS in the to a higher place SSRS Report.

Right-click the data row as shown in the below screenshot, click F4 or properties window in the View menu.

When selecting this, you volition run into the BackgroundColor option. By default, information technology is No Colour, which ways that there is no color for the background and use tin can select any colors. However, those options volition provide a written report with the aforementioned colors for all rows, non for alternate row colors in SSRS Study as required.

You tin select the Expression… option in the listed options which will give you a screen when yous can enter an expression.

In the expression, ROWNUMBER function is used. ROWNUMBER will be the row number for the row. The method used in this case is that the odd row numbers are lite blue while the fifty-fifty row numbers are blue. Odd rows are establish by for the rows which has reminder 1 when the row number is divided by 2 and similarly, the even rows are the rows which will be the reminder 0 when the row number is divided past 2. This unabridged logic is used with the IIF and ROWNUMBER functions as shown in the to a higher place screenshot.

Then the SSRS report is shown as following which has alternate row colors.

Now this will be same as what you get in Microsoft Excel.

Alternate Row Colors in SSRS When Grouping

In SSRS, typically y'all add groups to the detail records. For example, in the above study, the Sales Guild ID is repeated in the in a higher place data set. This means that the study can exist grouped by the Sales Order ID and when the grouping is washed, the written report volition exist looked like the following screenshot.

You lot volition observe that groundwork color has gone wrong for the grouping rows. This is because an boosted row is introduced to the group column. To avoid this, the background color of the grouping row should be modified accordingly.

Then the report will look like the following screenshot.

Alternating Row Colors in SSRS Matrix Command

Matrix is an SSRS control from which yous can have dynamic columns and rows. This means that unlike in the previous example of tables, in the matrix control, columns will grow.

In the following report, order numbers are in the columns while the production names are in the rows. In instance you get a new order number that volition appear in the side by side column.

Due to this dynamic nature of the report, the previous unproblematic dominion volition not work for matrix. If you apply the same rule, the alternate color will occur not at the row level but for every value in the matrix.

This means we need a new approach for the reports with matrix control.

1. Add a column outside the dynamic columns which are shown in the following screenshot and highlighted column is the newly added cavalcade

   

2. Then add together an expression to the newly added cavalcade as shown in the below screenshot. This column is Textbox10

   

   Expression is : =IIF(RUNNINGVALUE (Fields!ProductName.Value,CountDistinct,Nothing) Mod 2, "LightBlue", "Blue")

   It is similar to the previous expression, just only thing is, RUNNINGVALUE for the grouped column which is the Product Proper name is used.

   Now you lot volition get the color into the text value as shown in the below screenshot:

   

3. Next step is to get the groundwork color from the value of the newly added column. Since the colour is available the newly added cavalcade, that color can exist set to the background of the matrix row

   

4. Next is to hibernate the newly added column since this column is used only as an internal column to the study. This can exist done by setting the Hidden option to Truthful

   

Finally, the study volition look like the post-obit screenshot.

In both the modes, a new the column is added, and information technology will automatically become the necessary background color so that it does not need any additional configurations. Besides, instead of the proper noun of the colour, you can utilize the color code which can exist taken from the RGB Color Codes Nautical chart

• Author
• Contempo Posts

Dinesh Asanka is MVP for SQL Server Category for final eight years. He has been working with SQL Server for more than than xv years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always bachelor to learn and share his knowledge.

View all posts past Dinesh Asanka

Can We Use Background Color For Parameter In Ssrs Tool,

Source: https://www.sqlshack.com/alternate-row-colors-in-ssrs/

Posted by: harmonhareand.blogspot.com

Share this post