Sunday, August 4, 2013

SSRS - How to implement alternate background color in rows



1.     Background

The purpose of this article is to provide a way of showing different color in alternate rows in the report. It will make report more interactive and simplified the report for the business users. Here we are going to use SSRS function – RowNumber and Modulo function for alternate row coloring.

2.     What is RowNumber and Modulo function in SSRS?

  • RowNumber Function:  returns a running value of the count of rows within the specified scope. Each row is having a count under a specified scope. Row/Column Groups are good example of scope. In the below example, we are using RowNumber (Nothing) as expression for a text box (row).



It will return the below values for the rows:

 


  • Modulo Function: Returns the remainder of one number divided by another.
Syntax: dividend % divisor

Below is the example showing dividend is divide by divisor and getting reminder as result:
SELECT
'1%2' AS [dividend % divisor] ,1%2 AS [Result]
SELECT '2%2' AS [dividend % divisor] ,2%2 AS [Result]
SELECT '3%2' AS [dividend % divisor], 3%2 AS [Result]
SELECT '4%2' AS [dividend % divisor],4%2 AS [Result]
SELECT '5%2' AS [dividend % divisor],5%2 AS [Result]
SELECT '6%2' AS [dividend % divisor],6%2 AS [Result]
SELECT '7%2' AS [dividend % divisor],7%2 AS [Result]
SELECT '8%2' AS [dividend % divisor],8%2 AS [Result]
SELECT '9%2' AS [dividend % divisor],9%2 AS [Result]
SELECT '10%2' AS [dividend % divisor],10%2 AS [Result]


It will return reminder as 1 or 0.
 
  • Final Expression : =IIF(RowNumber(Nothing) Mod 2 = 0,"Magenta","Silver")
It means every row count will be divided by 2 and reminder will be calculated as 1 or 0. If reminder is 0 then row should be displayed in “Magenta” color and if reminder is 1 then row should be displayed in “Silver” color.

 

3.     Steps to add alternating row color in the report:

Below is the simple Sales report in which we will implement alternate row coloring:

 

                       I.            Select the rows in which alternate row color need to be implement:



                     II.            Go to the background property and select expression:

 

                   III.            Write the below expression box and click ok.
=IIF (RowNumber (Nothing) Mod 2 = 0,"Magenta","Silver")




                   IV.            Preview the report. We can see the alternate color rows.
 

4.     Conclusion

By using SSRS RowNumber and Modulo (MOD) function, we can implement coloring of alternate rows.
----------------------------------End of Document----------------------------------






7 comments:

  1. Background removal services of product images. This service helps E-commerce websites. Good looking product images improve sales… hire background removal service

    ReplyDelete
  2. I like your post. It is good to see you verbalize from the heart and clarity on this important subject can be easily observed... https://www.markedcardsforsale.com/marked-cards.html

    ReplyDelete
  3. This is my first visit to your web journal! We are a group of volunteers and new activities in the same specialty. Website gave us helpful data to work. https://www.cardslenses.com/marked-cards.shtml

    ReplyDelete
  4. I really appreciate this wonderful post that you have provided for us. I assure this would be beneficial for most of the people. Thanks for sharing the information keep updating, looking forward to more posts.
    High Quality Product Images

    ReplyDelete
  5. If you are looking for more information about flat rate locksmith Las Vegas check that right away. dice for dungeons and dragons

    ReplyDelete
  6. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. metal dice

    ReplyDelete