Problem
You have several report developers working on a large scale SSRS
2008R2 project and they are generating report after report. You would
like all the reports to have the same "look and feel", at least on the
header and footer sections, and even include some base queries for items
commonly included as parameters for each report. How can you create
such a template and make it appear in the New Project List?
Solution
Creating a report template is a several step process. First you need
to gather your header and footer requirements, next, you will want to
prepare a list of common parameters to be included in the template. Once
the header and footer requirements and the parameter queries are
documented, a basic report can be designed to address the requirements.
Last, you will deploy the template to the appropriate report server
template folder on the file system.
Gathering Requirements
Requirements gathering remains as one of the most important steps in
creating a template. The template designer, the report developers, and
the end user report consumers all need to brainstorm on what items
should and should not be included in the header, footer, and body areas
of every report. Some suggestions for the header include: a company or
division logo, a facility, location, or company name, a report title, a
time period for the report, and any parameter criteria that must be
prominently placed at the top of each page. Next, in the process is
scoping out the footer area.
Some suggested items for this area include:
page numbers (individual and in total), report run time and date,
report path from the report server, report name, report criteria, and
source information. Additionally, in the body of the report, either at
the beginning or end of the report content, you may set aside an area
for displaying extended report parameters or criteria. Your scope
process should also include a default report size and orientation as
these items will ultimately impact the placement of the other objects in
the header and footer. One potential idea is to actually use Excel to
prototype your initial template during the design phase; a sample of
such a design is displayed below.
Template Design
Start the actual report template by opening up Business Intelligence
Design Studio (BIDS), and then open either an existing Report Server
Project or Create a new Report Server Project.
Next create a new blank report RDL file. Be sure to add a report
header and footer by right clicking anywhere in the white area of the
report and then selecting each of these items as shown in the below
figure.
Based on your design specifications, go ahead and size your report
and set the report orientation, as noted in the image below, in order to
prepare for the addition of the rest of your design items. Pay very
close attention to footer and header space you use, as these areas
occupy this space on each and every page of the report. Also, be sure to
use colors and contrasting colors which are easy to see both online and
in print.
Next begin adding textboxes for the different header and footer
parts; it is best to add individual text boxes. As you can see from the
image below, the template additions are fairly detailed. We embed an
image in the upper left corner of the header and then add four Title
textboxes in the center of the header.
Further, notice we insert a thick
black line to distinguish between the header and the body of the
report. Of course you could also use a large rectangle object around the
entire header. Next a textbox is added at the bottom of the report body
to display any minor criteria for the report; note this textbox only
appears once whereas the page header and footer appear on every page.
Finally, the footer area is defined by inserting several textboxes which
contain SSRS built in "global" fields.
The upper left textbox in the
footer includes the Execution Time/Date while the upper right includes
the current page and total pages. The next footer line includes a list
of sources. The last footer line contains the built in fields for the
Report Server, Report Folder, and Report Name. This line will display
the exact report name that the user ran and the actual path from the
report server.
As displayed in the below image, the Built-in fields can be added
from the Built-in Fields list by dragging them directly from the list to
the report footer.
With all the report template objects added, we can go ahead and preview the report to be sure it looks as expected.
Moving the Template to the Visual Studio Report Project Folder
Once your template is complete, you will need to manually copy the
file to the appropriate SSRS ProjectItems directory. Below are the
default or common locations where the templates are stored. If you
changed the default installation directory for SQL Server, you will need
to adjust accordingly.
(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
(SSRS 2008) - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
Now the next time you select Add –> New Item from BIDS Report Server Project, your new template will appear in the list.
Upon Selecting New Item, a dialogue box will open, similar to
following image. Notice our new report template,
HeadsUpSoccer_Report_Template appears in the list and is ready to be
used. One important item to note, any changes to an existing template,
will need to be reapplied to any previously completed reports.
Source Collected from MSSQLTIPS.COM
No comments :
Post a Comment