Paginated Reports eBook Introduction, Resource and Sample Data Downloads
When you add a new report to an SSRS project, you get a completely blank canvas on which to work. In many organizations or departments, however, paginated reports must have a certain look and feel. A company logo and custom color palette may be required, a disclaimer may need to be displayed, parameter values listed, a code routine stored, and more. Instead of spending time setting all this up for every report, you might consider creating a custom template that can be used as a base for all new reports. Maybe you’ll need a certain template for a long running project, or a separate template for each department. You could even have one template for landscape and one for portrait orientation. With several templates to keep track of, you might consider creating one project to maintain all your templates.
Recipe Ingredients
Product Versions
- Visual Studio 2017 with the Reporting Services Project extension
What You’ll Need
- A connection to a data source
- Moderate VB coding skills
- An image for a logo
Difficulty Level: 300
Contributed by Kathi Kellenberger
Create a Base Report
Creating a report template is no more difficult than creating any SSRS paginated report. The only difference is that it is stored in a special folder when complete. Later, when adding a new item to your Visual Studio project, you’ll see the template in the list of options.
Before you learn where to save the template, first spend some time creating it. The report template will have a header and a footer. It will also contain some custom code that is needed for displaying parameter values and some global variables for custom colors. Follow these instructions to create a template, but feel free to modify it for your own environment.
Open Visual Studio and create a new Report Server Project named Templates.
Add a Shared Data Source to the project pointing to the PaginatedReportData database.
I recommend using shared data sources in your project for sources that you will reuse. Inside your report template, you can create a data source reference that points to this one.
Note: As of this writing there is a bug in the report designer that causes the shared data source to lose its credentials settings. After creating the shared data source, open it back up and correct the settings on the Credentials page. Once you save the second time, the setting should stick.
Add a new report by right-clicking the Reports folder and selecting Add New Item. Select Report from the list of items. Name the report MyCoTemplate.
In the Report Data window, add a new data source that points to the Shared Data Reference you created in step 2.
As long as your new projects have the same shared data source in place, the data source reference should just work when you use a template.
Add a Page Header and Page Footer to the report from the Report menu.
The header and footer show up on each page unless you modify the properties. Here you can display important information for the end user, especially when the report is printed.
From the Report Data window, Built-in Fields folder, drag the Report Name to the header. Increase the font size to 20. If there is a specific font you are supposed to use, go ahead and change it now.
By using this built-in field, the name will dynamically appear.
To add your company logo to the header, add an Image control from the Toolbox. You can either embed the image, get the image from a URL or a database. For this example, import the TransparentLogo.png file or your own file.
The benefit of using a URL is that you can change the image in one place to automatically update many reports, as long as the size and shape if the image are not different.
You could do several things to “dress up” the header, but for now just add three horizontal lines of width 5pt. My report header looks like Figure 1.
Figure 1
- Add the Execution Time and Page Number to the page footer from the Report Data’s Built-in Fields section. Add them to the top of the footer.
- Under the execution time and page number, add a text box and fill in this text for the value:
“This report is the confidential information of Our Company. If you find this report in a public area, please return to the owner or dispose of in the designated shredder.”
The footer of my report looks like Figure 2
Figure 2
The default margins are 1 inch (2.54 cm) which is almost always too large. Change them now in the Report Properties to 0.25 or 0.5 inch to save time later.
You now have a template you could use, and it would save you some time. Next you’ll learn how to add custom colors to the template.
Using a Custom Color Palette
SSRS provides a substantial list of named colors from which to choose for any property needing color. It also provides a color circle and a color square where you can select any color. You can also type in the RGB properties for any custom color that your company may require.
To follow along with this example, you’ll just select three custom colors from the color circle, one for each horizontal line. Notice that the property will have the hex representation of the color. Figure 3 shows the properties of one of the colors.
Figure 3
It’s easy enough to save the horizontal lines with the company colors, but you’ll probably want to reuse them throughout the report for group headers or other accents. To make it easy to reuse these colors without needing to figure out the hex code each time, you’ll create variables to store the codes. Follow these steps to learn how.
Open the properties of your first horizontal line and copy the LineColor hex code.
Make sure that the report canvas is selected and open the Report menu. Select Report Properties and choose the Variables page.
Click Add and fill in a name and value for the first color. My variable looks like Figure 4.
Figure 4
Repeat the process for the other two colors.
To try this out, change the background color of the Report Name to the lightest color. Select Expression for the Background Color property.
In the Expression dialog, click Variables and double-click the color. The Expression will look like Figure 5, but with the color name you have chosen.
Figure 5
Now when previewing the report, you can see the custom color as shown in Figure 6.
Figure 6
Displaying the parameters chosen at runtime is crucial for understanding just what the report data represents. Next, you’ll learn some options for displaying the parameters on the report.
Working with Parameters
Displaying a single-valued parameter on a report is simple. You can just drag the parameter to the header from the Report Data’s Parameter folder. Parameters are not always simple, however. You might have multi-valued parameters or you might want to display the user-friendly label instead of the value. You can accomplish all this using a simple expression, but you might want to customize this a bit.
To get started, follow these instructions to create a sample parameter.
In the Report Data window, add a new Parameter named Sample.
On the Available Values page, select Specify Values and add these items:
|
LABEL |
VALUE |
|
Red |
1 |
|
Blue |
2 |
|
Green |
3 |
|
Yellow |
4 |
Drag the parameter from the Parameter folder in the Report Data window to the header.
Run the report, and you’ll be prompted to select one of the colors. Instead of the color name, you’ll see the Value property which is a number.
Chances are, your end-users will not want to see an ID number; they will prefer the name instead. Right-click the textbox and select Expression. Change the formula to
=”Sample: ” & Parameters!Sample.Label
When you rerun the report, you’ll now see the color instead of the number.
Note that it’s easy to just drag the parameter to the header, but that method doesn’t work with multi-valued parameters. To demonstrate, enable the “Allow multiple values” property of the parameter. Instead of displaying the chosen parameters, you’ll now get an error.
Right-click the new textbox and select Expression. Add this formula which will work for mult-valued parameters:
=”Sample: ” & Join(Parameters!Sample.Label,”, “)
Rerun the report. The textbox should now behave as expected.
You may be wondering how all this applies to templates. In the next section, you’ll create a custom function that gives you even more flexibility.
Building a custom parameter handler
The previous example was simple, but you may have some additional requirements or concerns. Maybe you would like to show only the first 10 items and would like the values numbered? To add this functionality to your parameters, whether single- or multi-valued, follow these instructions.
Open the Report Properties from the Report menu and select, Code.
Paste in the following function:
Public Function ShowParamValues(ByVal param as Parameter, ByVal Name as String) as String
Dim s as String
Dim max as INT32 = 10
s = Name + “: “
If param.IsMultiValue then
For i as integer = 0 to param.Count-1
If i < 10 then
s = s + CStr(i + 1) + “. ” + CStr(param.Label(i)) + “, “
End If
Next
s = LEFT(s, LEN(s)-2)
If param.Count > max then
s = s + ” (first 10 items)”
End if
Else
s = s + CStr(param.Label)
End If
Return s
End Function
Change the expression of the textbox to this:
=Code.ShowParamValues(Parameters!Sample, “Sample”)
You can add any function that you think will be useful. Keep in mind, however, that there is no IntelliSense in the Code window. You’ll also have to run the report to debug it.
Saving the Template
Now that you have created your template, it’s time to put it to work.
In the Solution Explorer, right-click the template report and select View Code. Scroll down to the bottom of the file and view the ReportID. You’ll need this later.
Figure 7
Locate the report file. If you’re not sure where the RDL file is located, right-click on the report name in the Solution Explorer to get the Full Path property.
Copy the file to the template directory.
C:\Program Files (x86)\Microsoft Visual Studio\<version>\<edition>\Common7\IDE\CommonExtensions\Microsoft\SSRS\ProjectItems\ReportProject
In my case, the path ended up being
C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\CommonExtensions\Microsoft\SSRS\ProjectItems\ReportProject
Inside the folder, create a copy of the Report.ico file and rename it to the same name as your template. In my case, it was MyCoTemplate.ico. You could also create a custom icon if you wish.
You’ll need to add the report to the ReportProjectItems.vsdir file. This file may be locked down so modify the permissions in order to edit it.
Open the file in Notepad or another text editor. Add this line to the file:
<Template Name>|{ReportID}|#205|25|#206| | | |Report.rdl
The line I added looked like this:
MyCoTemplate.rdl|{5eb099ba-a82a-4b68-8412-b66c2761e1ed}|#205|25|#206| | | |Report.rdl
Tip: For more information about vsdir files, see this article:https://docs.microsoft.com/en-us/visualstudio/extensibility/internals/template-directory-description-dot-vsdir-files?view=vs-2019.
Close the Template project.
Create a new project and add a shared data source for SSRS2019Recipes. Make sure that it has the same name as the shared data source used in the Template project.
Right-click on the Reports folder and add a new item. You should see your template in the list.
Figure 8
When you select the template, you’ll start with a report that looks just like the template you created in this chapter!
Final Thoughts
Companies or departments are sometimes particular about the look and feel of all their reports. They may require the use of the official company colors and logo, for example. That makes sense because some report developers may not have a good eye for color or might even be color blind. There are several items like footers that take time whenever you begin a new report. In this chapter, you learned how to create a template that will save you several steps for each report. You may even want to create several templates for different projects, departments or paper sizes.