Paginated Reports eBook Introduction, Resource and Sample Data Downloads
If you are getting started using expressions in Paginated Reports, this is a good exercise to learn and demonstrate an essential skill you will need to use in more complex recipes and report designs.
There are two age groups of computer users: those who only use an ink jet printer only to make posters when their cat goes missing and those who remember “green bar” reports. Yes, there was a time when most reports were actually printed on continuous, pin-fed paper that was preprinted with alternating green and white bars. The fan-folded paper was fed from a box through a dot matrix carriage that moved back and forth as the printed reports piled-up behind the noisy thing. A sample of a preprinted green bar page is shown in Figure 1.
Figure 1. Pin-fed Greenbar paper
Although we don’t typically print business reports on continuous feed paper anymore, the same affect can be achieved with alternate row shading in a table to enhance the readability of a report with multiple lines of detailed information. I suppose it is a bit nostalgic but this type of report is commonly referred to as a green bar report.
Green bar reports help you read detailed information quickly and accurately. Although those impact and dot-matrix printers are mostly a thing of the past, the need to present detailed information using the green bar style still exists with the paginated reports of today.
This recipe provides you with detailed instructions for applying the green bar effect to your table-type Paginated reports. A more sophisticated technique may be used when row and columnar grouped information is presented using a matrix data region. If you need to graduate to that more complex technique, consider this recipe the entry level practice for Kirill Perian’s Alternate Row Shading (with Column Groups) recipe.
Recipe Ingredients
Product Versions (one of the following):
- SQL Server Reporting Services 2008 or newer
- Power BI Report Server
- Paginated Reports in Power BI
What You’ll Need
- Basic understanding of expressions in Reporting Services
Difficulty Level: 100
Contributed by Paul Turley
Designing the Report
This recipe demonstrates the concepts required for a green bar report. You will create a report that displays the detail for a particular sales order from the PaginatedReportData database. You will then modify the behavior of the report so that every other line is displayed with a shaded background. Figure 2 shows an example of the finished report.
Figure 2. Completed Greenbar report
While there are a few different approaches to achieve this behavior, the basic pattern is to create a table-based report and apply an expression to the background color property of the detail row, altering the background color based on the current row number, whether odd or even.
Create a New Report
Start by creating a new, blank report using Paginated Report Builder
Design a new data source using the Microsoft SQL Server provider that connects to the PaginatedReportData sample database.
Add a new dataset associated with this data source.
Construct the following query using the query designer or by entering the query as text into the query editor.
SELECT Sales.SalesOrderNumber ,Product.ProductName ,Sales.OrderQuantity ,OrderQuantity ,Sales.SalesAmount ,Sales.TaxAmt ,Sales.Freight FROM dbo.Sales INNER JOIN dbo.Product ON Product.ProductKey = Sales.ProductKey WHERE Sales.SalesOrderNumber = 'SO43902' ORDER BY Product.ProductName ASC;
Execute the query to ensure there are no errors.
Click OK on any open dialog windows until you return to the report design surface.
This query returns the sales order detail for a single sales order, SO43902. This simple report will display each sales order detail line in a table.
Insert a Table data region to the body of the report design surface. This creates a simple table layout comprised of one header row, one data row, and three columns.
From the Report Data window, drag the LineNumber field to the first column of the data row in the table.
Repeat this process for the remaining fields in the dataset, placing the fields in the following order: LineNumber, ProductCode, OrderQuantity, UnitPrice, SalesAmount, TaxAmt, and Freight. When complete, the report design surface should appear as displayed in Figure 3.
Figure 3. Report in design view
Run the report and review the results.
Notice how the data becomes difficult to read and to follow as you move across the page. When trying to keep track of a specific value from a column on the right side of the page, you may find yourself using your finger or mouse to trace the row back to the Product Name on the left side of the page. You may do something similar with a printed version of the report where a ruler or some other straight edge is typically used.
Explore the Background Color Property
In the following steps, you will make the report easier to read by adding the green bar effect to the sales order detail.
- Return to design mode and highlight the detail row. Be sure to highlight the entire detail row and not just a single cell within the table.
If the Properties window isn’t already displayed on the right side of the report designer, switch to the View ribbon using the menu tabs along the top of the main window.
Ensure that the Properties box is checked and then switch back to the Home ribbon.
Figure 5
Using the Properties window, locate and select the BackgroundColor property for the detail row. Notice the current value is No Color.
The default value for the BackgroundColor property is No Color, which actually translates to the named color reference: Transparent.
Using the Properties window, you can change this value to be any other named color reference, RGB (Red, Green, and Blue), or HSB (Hue, Saturation, and Brightness) color value. However, simply changing the value from No Color to Red, for example, changes the background color for every detail row in the table, which is not the desired result.
In addition to a fixed color value, the BackgroundColor property can also be set using an expression. Simple or complex logic can be applied within the expression in order to determine the appropriate color value. And since the expression is evaluated for each detail row in the report, you can exploit this functionality to toggle the background color and create the green bar effect.
Applying this concept to the report, the background color should be shaded for every other detail row. That is, every odd row should be one color and every even row should be another color.
The Visual Basic IIf function, which is short for “Immediate If,” can be used to achieve this:
=IIf(Expression As Boolen, TruePart As Object, FalsePart As Object)
The IIF function provides if-then-else functionality encapsulated within a single function call. The IIF function accepts three parameters. The first parameter, Expression, can be any simple or complex expression that evaluates to a Boolean value of True or False. The second parameter is the value to be used when Expression evaluates to True. The last parameter is the value to use when Expression evaluates to False.
For this recipe, the expression should evaluate to True for odd rows and False for even rows. Likewise, the TruePart should indicate the background color for odd-numbered rows and the FalsePart indicates the background color for even-numbered rows.
The following expression uses the IIf function to return the color value Gainsboro (a light grey) if the row number is odd; otherwise it returns the color value Transparent (No Color):
=IIf((RowNumber(Nothing) MOD 2) = 1, "Gainsboro", "Transparent")
The RowNumber function and modulo operator, MOD, are used to determine if the current row is an even or odd. The RowNumber function returns the running count of the number of rows in the dataset. The modulo operator, which divides two numbers and returns the remainder, can then be used to determine if the result returned by the RowNumber function is an odd or even number.
With this information, you are now ready to modify the background color to achieve the green bar effect on your report.
Set the Background Color Using an Expression
- With the BackgroundColor selected in the Properties window, click the dropdown button to display the color selection window.
Click the Expression link at the bottom of the color selection window, as demonstrated in Figure 4, to open the Expression Editor.
Figure 4
As shown in Figure 5, enter the following expression using the expression editor:
=IIf((RowNumber(Nothing) MOD 2) = 1, "Gainsboro", "Transparent")
Figure 5
Click OK to accept the change and return to the Properties window.
Run the report to review the results shown in Figure 6.
You can use the Line Number field in the first column to verify the behavior is as expected. Notice that it is much easier to follow the information on each row as you read across the page from Line Number to Freight.
Figure 6
Final Thoughts
In this recipe, you used an expression to control the background color of each row in a table and create a green bar report. This is a common pattern that you will utilize as you create more sophisticated reports—using expressions in the report definition to affect the data, format, and behavior of your report. In a subsequent exercise, you will implement a variation of the green bar report in which alternating colors are used to help identify groups of related information.
Credits and Related References
MDSN: Report Design Tips and Tricks http://msdn.microsoft.com/en-us/library/bb395166(SQL.90).aspx
Fang Wang, Robert Bruckner and Chris Hays