Paginated Reports eBook Introduction, Resource and Sample Data Downloads
As you have seen in Recipe 01, “Alternate Row “Green Bar” recipe, you can now easily create alternate row shadings in a table data region. Using a matrix data region, however, it is oftentimes useful to apply alternate row shading for the ease of user consumption, specifically to help with tracing a single row of data across the page. It is even more useful when there are a few columns involved and especially when column groupings are applied to the report.
Handling the alternate shading is not a native feature in a matrix visual, but fortunately there is an easy workaround when a report contains column groupings.
Recipe Ingredients
Product Versions (one of the following):
- SQL Server Reporting Services (SSRS) 2008 R2 or newer
- Power BI Report Server
- Paginated Reports in Power BI
- Power BI Report Builder
What You’ll Need
- Moderate level familiarity with Power BI Report Builder
- Basic understanding of expressions in Reporting Services
Difficulty Level: 200
Contributed by Kirill Perian
Applying Alternate Row Shading
We are going to design and build out this solution from scratch, but the same technique applies if you are given a report without the row shading feature.
Connect to your data source
Open Power BI Report Builder.
In the Report Data pane on the left-hand side, right-click on Data Sources and select “Add Data Source”. This will take you to Data Source Properties dialog box.
Under General tab of Data Source Properties dialog box enter the name of your data source in the Name field (I used “PaginatedReportData”) and select “Microsoft Azure SQL Database” as connection type from the drop-down menu.
Click on Build button on the right-hand side.
Under “Connection Properties” screen, enter LocalHost as your Server name.
Selecting data for your report
Go to vSalesSummaryQuarterCountry View.
In order to use it as your dataset query, open the Query Designer, switch to Edit as Text and then type:
SELECT * FROM vSalesSummaryQuarterCountry
Setting up report layout
Next, you will add a matrix data region, add fields to the row and column groups, and then add two fields as values. You can either add the matrix and then drag and drop fields into the appropriate cells or use the Report Wizard according to the following steps.
- Select Dataset1 and then go to Insert tab on your Ribbon, click on Matrix drop-down menu and select Matrix Wizard. Choose your dataset on the next menu and click Next.
In the next step, drag your Available fields around into the Row and Column groups, as well as values as shown in Figure 6-1.
Figure 6-1. Field selections for new Matrix Wizard
On the next (Choose the layout) screen, leave the defaults, and click Next.
On the next (Preview) screen, click Finish. At this point, you should see something similar to the picture shown in Figure 6-2.
Figure 6-2. Design View with newly added matrix
Go to Home tab of your Ribbon and click Run button (or hit F5). You should see the following report (as shown in Figure 6-3):
Figure 6-3. Resulting matrix report
Note: Now you have the report to which you are going to add the row shading.
Adding row shading to the report
Adding an extra column that contains expression defining the colors for alternating rows
Here you will add a row shading to alternating rows of our report by creating an additional column and specifying the code inside that column for the desired shading colors.
- Insert a column to the right of the last dynamic column in the Design view (as shown in Figure 6-4).
Figure 6-4. Adding a column to a Matrix visual
Right-click on the dynamic row and select Text Box Properties… option from the context menu (Figure 6-5).
Figure 6-5. Selecting Text Box Properties
Tip: Take note of the field name you are using in the very first column (in our case, Year).
Add that field’s name to the following expression in the Value field of the General tab of Text Box Properties dialogue box and click OK:
=IIF(RUNNINGVALUE (Fields!Year.Value,CountDistinct,Nothing) Mod 2, "LightGray", "Gray")
Note: Instead of “LightGray” and “Gray” you can use the colors of your choice, but they must be valid colors that the program recognizes.
Tip: Take a note of what is displayed under the Name field. In our case it is “Textbox24”.
Click on Run view and you will see the color as a text value displayed in the newly added column (as shown in Figure 6-6):
Figure 6-6. Color names are displayed on each row of the new column
Setting the background color to values in new column
- Select the entire row of the matrix
Insert the following piece of code in the Properties pane, referring to the name field that you have noted earlier (Figure 6-7):
=ReportItems!Textbox24.Value
Figure 6-7. Adding textbox value to the expression for BackgroundColor property
Click on Run under the Home tab of the Ribbon and you should see the following matrix with alternate row shadings as per the colors we have specified (Figure 6-8):
Figure 6-8. Report with alternate shadings
Hide the extra column
The very last housekeeping item that is left to do is to hide the column with the newly created color expression.
- Switch to Design view and select the column in the Properties pane.
Go down to Visibility section and toggle True under the Hidden item of the menu (Figure 6-9).
Figure 6-9. Hiding the created expression column
Run the report again (Click on View).
Note: We can see that it looks just like we wanted, and that alternate shading of rows propagates across the entire row without any issues (Figure 6-10):
Figure 6-10. Final result or report with alternate row shading
Final Thoughts
As you can see, alternate row shading for reports with column groupings requires a little bit of extra work with adding another column to the report, specifying the alternating colors in a code string, and then creating a code string, specifying the BackgroundColor of the row by referring to the colors of the extra column designated to specify the desired shading colors. As a result, you can achieve better reading accessibility for your users and aesthetically pleasing report.
Credits and Related References
Power BI Report Builder (Peter Myers, Maggie Sparkman):
https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-power-bi
Alternate Row Colors in SSRS (Dinesh Asanka):