Bill Pearson continues his overview
of the Managing phase of the Reporting Services development life cycle. In
this article, we examine report execution and standard subscriptions.
About the Series ...
This is the fifth article of the series MSSQL Server
2000 Reporting Services. The series is designed to introduce MSSQL
Server 2000 Reporting Services ("Reporting Services"), with the
objective of giving a preview of its features, as well as sharing my conviction
in its role as a new paradigm in enterprise reporting. As I advise clients on a
more and more frequent basis these days, this is the future in a big way. I hope
you will consider my input valuable, and that you will investigate closely the
savings and advanced functionality that will soon be available to anyone with
an MSSQL Server 2000 (and beyond) license.
Important: For information concerning the applications to which you will
require access to benefit the most from our series, please see our initial Database
Journal article, A New
Paradigm for Enterprise Reporting.
For purposes of this and subsequent articles, we assume
that you have uploaded the sample reports that ship with the current version of
Reporting Services. For a detailed upload procedure, see Managing
Reporting Services: Data Connection and Uploads, where we uploaded the
reports to which we refer in this and subsequent articles.
last article, Managing
Reporting Services: Data Connection and Uploads, we introduced Managing as the post-Authoring
phase of the Reporting Services development life cycle. In beginning a set of Managing
articles, we began by looking at data connections within Reporting
Services. We set up a shared data source item for the sample reports
set, both as practice and for supporting the reports for later uses within our
series. We then practiced the upload process with each of the sample
reports, to gain familiarity with the steps involved.
then associated the sample reports with the new shared data source, for
the purpose of returning the reports to a working status, as well as for the
benefit of practicing a method of re-establishing connections to a data source,
in cases where a previous connection has been deleted or altered. Finally, we verified
each of the newly established data connections through initial report viewing,
ensuring that each report brought back data as expected.
this article, we will introduce two further considerations within the Managing
phase of the reporting lifecycle: the execution of reports (including
viewing options), and the creation and maintenance of standard subscriptions.
We will review the process of running reports, partially touched upon in our
last session, and then we will examine viewing reports from a web browser. We
will then examine the steps involved in setting up a standard subscription as
a means of delivering reports to information consumers.
objective of this article is to continue our overview of Managing, and,
as was the case in our previous article, the primary focus of the remaining Managing
phase articles is central management of the reporting function, from the
perspective of the objects that we can manage and the actions that we can
perform with, and upon, those objects. We will return to most of the
activities we touch upon here, just as we will return to many of the topics we
explore within our other life cycle overviews, as we get involved in creating
reports to accomplish illustrative business needs. As I stated in the Authoring
overview, I intend to make this a series on enterprise reporting in the
widest sense. Reporting Services presents a unified, common platform from
which I can show the techniques and methods I have accumulated over the years
to support robust and creative business intelligence, and I look forward to
sharing the knowledge I have accumulated in the Business Intelligence arena.
A Return to the Managing Phase
discussed in the introduction for Managing
Reporting Services: Data Connection and Uploads,
once we have
completed the Authoring phase of the report development life cycle, the
next phase is Managing, where the report is processed and rendered,
(assuming, of course, that the reports concerned are managed in the
first place). We noted, in general, that publication of the new report
definition to a Report Server designates it as a managed report,
and that managed reports are associated with meta data, and have properties,
that allow actions to be taken with them. Examples of these actions include scheduling
(one of the items we partially expose in this article), linking to other
reports, application of security, movement to various locations, and various
aspects of file maintenance.
We discussed processing
as taking place in a general sequence that includes extraction of the data
specified in the report definition, and the subsequent marriage of data and
report layout, prior to the presentation of the data. In this article, we will
begin an examination of the generation aspects of the reports we have uploaded from
Report Manager, and then the viewing of those reports both from Report Manager
and from the browser interface, together with other operations we can perform
within Report Manager.
Let's continue our
overview of the Managing phase, and get a deeper look into this exciting
area within Reporting Services. Because this article focuses on management
of reports that are already designed (and in keeping with our objective
to make articles "free-standing" with regard to readers being able to
participate in each without having joined us in previous articles), we will continue
to work with the set of report samples that accompany Reporting Services.
Executing and Viewing Reports
We can execute a report
within Reporting Services through two general means. First, we can run a given
report on demand, which simply means that the report executes any time
we access and open it. Alternatively, we can dictate that a report is subscription
based, whereby we specify information that is used to run the report at the
time we dictate, together with delivery information to enabled Reporting
Services to "push" the report to us after execution.
Execution and viewing
often become very closely related, particularly in the case of an on-demand
scenario, where we are triggering execution by an attempt to view.
Let's take a look at the options for executing and viewing our reports.
We can view reports on
demand via the Report Manager or through a browser. Let's take a look at the
steps involved in a simple scenario. First, we will view a report using Report
Manager, something we have already touched upon in previous articles. Our first
step is to start Report Manager.
the Reporting Services program group that installs within a
typical setup. The equivalent on my PC appears as shown in Illustration 1.
Illustration 1: Navigate
to Report Manager ...
Manager to initialize the application.
NOTE: If Report Manager does not
appear in the manner shown, whether because you declined setup of the program
group, a disablement of the feature, or other, unknown reason, simply get there
by typing the appropriate URL into the address bar of your web browser. The
default URL is as follows:
As an example, my <webservername> would be MOTHER1,
the name of my server, and would appear, in this approach, in my browser address
line as depicted in Illustration 2.
Illustration 2: Navigate
to Report Manager ...
arrive at the Report Manager Folder view, as shown in Illustration
Report Manager Folder View
sample reports we have published appear as we left them in our last lesson.
NOTE: If you did not complete the last
lesson, of if you have worked with Reporting Services other than within the
last lesson, the Folder view will appear different from the above. To
populate the view with the sample reports, which we will be using throughout
our examination of the Managing phase of Reporting Services, please
refer to the steps taken in Managing
Reporting Services: Data Connection and Uploads.
is obvious, Report
is, itself, a web-based interface. In addition to serving as a management
tool for Reporting Services, it also acts as a viewing tool. An
advantage in its use for viewing is that the involvement of the report server, which underlies the
capability to view a report in a web browser, is minimal. This comes in handy
at times, particularly for developers who, for whatever reason, may not have
access to a report server.
execute and view a report here, then take a look at the same process via the
browser. Clicking the report name triggers execution, as we shall see in the
Click the link
for the Product Line Sales report, as shown in Illustration 4.
Illustration 4: Executing
the Report from Report Manager (Partial Folder View)
report executes when we click the link in Report Manager's Folder view,
as evidenced by a Report is being generated message. The report then
appears, as shown in Illustration 5.
Illustration 5: Viewing
the Report Executed from Report Manager
also view reports directly from a browser, simply by typing in the URL address
of the report. Within the URL is embedded the Web server, report server
virtual directory, and the fully qualified report name. An example (the report
we have accessed, based upon its URL on my machine), is as follows:
could type this address directly into the browser, or establish a link in
another page, a shortcut based upon the URL, or other options to gain similar
execution and subsequent access to the report. The address I have shown above
would be referred to in the Reporting Services documentation as a "complex
URL," meaning that it contains encoded characters to handle spaces in the
path (as in the report name Product Line Sales), as well as for the
passage of parameters (Product Line Sales is a parameterized report, and
requires parameters to function as its authors intended).
These comprise the basic options
that we have for executing and viewing a report on demand. We can also view
the data obtained by Reporting Services using a desktop application, such as MS
Excel, instead. We will touch upon a method for doing this in our next section,
where we will focus upon subscriptions.
From within the
Product Line Sales report, click the Home link (as depicted in Illustration
6) to return to the Home page / Folder view from whence we
entered the Product
Line Sales report.
Illustration 6: Return
Home, and Close the Product Line Sales Report
reports, by their nature, allow us to perform "refreshes" based upon
the most recent data in the data source through their easy execution, and thus
they provide current information upon viewing. There are scenarios where a
Reporting Services administrator might want to designate that a previously
generated report be presented, instead of running the report every time an
information consumer makes use of the respective link or other execution
option. These previously generated reports are often referred to as static
reports. Static reports might be used when the reports concerned are
resource intensive, or perhaps where the use of static reports otherwise increases
performance, in situations where data is not frequently updated at the database
level, and so forth. We will examine the use of static reports in subsequent
have mentioned earlier, in addition to on-demand reports, Reporting
Services allows us to execute reports in an alternative way, through the use of
subscriptions. We will look at subscription-based reporting
next, from the perspective of a standard subscription.
Within the context of subscription-based
reporting, our designated report server uses information that we provide it to
schedule and deliver the report to designated individuals in the organization,
through the channels that we also specify. Subscriptions are established as "standing
orders" to Reporting Services to deliver reports in one of two ways:
subscriptions are based upon either an event or a schedule.
One of the obvious
benefits that subscriptions provide is the elimination of the need of the
designated information consumer(s) to perform any action to obtain an updated
report. This "push" capability can also be useful in establishing
reporting based upon data, and other, events, whereby a designated user can be
informed of the occurrence of the event with little additional effort, freeing
them to pursue other activities in the meantime. Regardless of the trigger
for report generation, subscriptions can provide highly useful, automated
delivery of updated information to intended consumers in a reliable manner.
There are two kinds of
subscriptions from which we can choose in Reporting Services, standard
subscriptions and data-driven subscriptions. Standard subscriptions
are usually established and maintained by the information consumers for whose
benefit they are created. Data-driven subscriptions, on the other hand,
generate their subscriber lists when they are executed, along with other
delivery options, and are typically created and maintained by Reporting
Services administrators and other operatives with familiarity in report creation
Let's take a look at
subscription basics in the following hands-on exercise steps. While our focus
in this exercise will be the definition and operation of a standard
subscription that delivers a single instance of an on-demand report, we
will delve into data-driven subscriptions, which are a bit more
involved, in a later article.
Let's say, for purposes
of illustration, that we have been tasked with establishing a subscription
whereby we will "push" the FoodMart Sales report to a designated
group of information consumers. (Up until our establishment of the
subscription, the report was regularly distributed manually to users, by
attaching the respective report file to individual e-mails, and mailing these,
once this was accomplished).
Ideally (according to
the information consumers), the report will be "parked" in a shared
folder that interested parties can access at will. The report would be most
useful, for various analysis purposes, as an MS Excel spreadsheet. The report
is parameterized, a fact that we will leverage in our exercise, because the
intended audience consists of managers who are concerned with how Non-Consumable
products are faring within the FoodMart chain, and have little interest in
members of the other product families.
Click the link
for the FoodMart Sales report, as shown in Illustration 7.
Illustration 7: Accessing
the FoodMart Sales Report (Partial Folder View)
report executes, as we have discussed earlier, when we click the link in Report
Manager's Folder view, and we again see the Report is being generated
message. The report then appears, defaulted to the Drink Product
Family in the parameter selector dropdown list appearing atop the report.
Let's set the parameter to the appropriate selection.
Select the Non-Consumable Product Family in the parameter selector dropdown list.
Click the View
Report button to refresh the report.
report regenerates to accommodate the new parameter we have supplied, and
appears as depicted in Illustration 8.
Illustration 8: The
FoodMart Sales Report, Filtered to Non-Consumables
purpose here is merely to get an idea of "where we want to go," and
to create a report against which we can compare our subscribed report, to
ascertain that it is selecting the parameter that has been designated by the
information consumers in the business requirement that we have received.
we begin the setup of a standard subscription in a report, we need to
ensure that the following conditions are in place. They are likely to be easily
accommodated for our ends, assuming that settings have not been altered in the
sample reports / environment, but it is never too early to be exposed to
- We must have access to / permission
to view the report;
- We must have capability
within the context of our role assignment to create subscriptions;
- The report must either require no
credentials to run, or have the necessary credentials stored within
NOTE: We will cover these topics from
numerous perspectives as we progress in the series.
Security for Our Exercises
will make adjustments to ensure that the credentials requirement above presents
no issue within our immediate exercise, assuming "out of the box"
Reporting Services / sample reports setup. First, let's modify security to
enable all capabilities from the outset, establishing ourselves as
complete "power users" for this and subsequent lessons, rather than
addressing all possible settings involved in each of the many exercises ahead.
NOTE: In establishing all rights and
privileges to the BUILTIN\Administrators group in my example, I am
assuming that you are 1) in a development or other non-production environment,
2) that you have authority to set security this way in your environment for
purposes of our exercises, and 3) that you have all necessary accesses /
capabilities to make and apply the modifications that we undertake. If your
environment or other situational factors vary from these assumptions, and for specific
roles for which you may need to perform modifications (perhaps other than BUILTIN\Administrators),
please review the Books Online for guidance in the setup of
security in a manner that will comply with your local requirements, while allowing
you the privileges and accesses you need to effectively complete the steps we
Click the Home
link atop the FoodMart
Sales Report view.
page appears, defaulted to the Contents tab.
Click the Properties
Click the Edit
link to the left of the BUILTIN\Administrators line item, shown circled
in red in Illustration 9.
Illustration 9: Editing
the BUILTIN\Administrators Role Assignments
Edit Role Assignment page appears.
Click the link
for the Browser role, shown circled in Illustration 10.
Illustration 10: Editing
the Browser Role ...
Edit Role page appears.
Click the checkbox
to the left of the Task heading, atop the page.
checkboxes become checked for the Browser role, as shown in Illustration
Illustration 11: Selecting
All Checkboxes for the Role
to accept all settings and return to the Edit Role Assignment page.
Perform steps 4
through 6 for each of the following roles:
- Content Manager
- My Reports
check box for each role on the Edit Role Assignment page by clicking the
checkbox to the left of the Role heading, atop the page.
have, in effect, given each role all its possible privileges, and are now assigning
all roles to our security group. The intent here, again, is not simply to
prepare for the steps ahead in this article, but to eliminate time-consuming
adjustments for articles that will follow this one in the Reporting
Edit Role Assignment page, all roles selected, appears as depicted in Illustration
Illustration 12: Selecting
All Roles for Our Group
Click the Apply
button to apply changes and to close the Edit Role Assignment page.
returned to the Home page - Properties tab.
Click the Contents
Click the link
for the FoodMart Sales report, once again.
Select the Non-Consumable Product Family in the parameter selector dropdown list, once more.
Click the View
Report button to refresh the report again.
now ready to undertake the creation of a standard subscription.
Using a Standard Subscription
Click the Properties
tab atop the FoodMart
Sales report view.
arrive at the Properties page.
Click the Data
Sources link in the left margin of the page.
arrive at the Data Sources page. Here we will embed our data source
access credentials, for purposes of completing the immediate exercise.
radio button labeled Credentials
stored securely in the report server in the Connect
Using: section (in the lower half)
of the Data
Type your actual Windows User
name and Password information into the respective boxes. (The
information that is being passed from Windows with integrated security in
NOTE: If you are using a different authentication
method than Windows integrated security, make the appropriate adjustments on
this page to embed your credentials into the report.
The Data Sources page appears as shown in Illustration
13, with my adjustments.
Illustration 13: Data
Sources Page: Settings to Store Credentials
to apply the settings.
Click the Subscriptions
tab (shown circled in the above illustration).
page appears, with a "no
items to show in this view" statement in place, assuming no subscriptions
have been created. Illustration 14 depicts this circumstance.
Illustration 14: Subscriptions
Page: No Subscriptions Defined
Because the report is
parameterized, and because we know the parameter that we wish to pass upon its
execution (we wish to present the Non-Consumables sales data), we need
to pass the parameter as part of the process of subscription; otherwise the
manual elements of report generation that existed before we obtained the new
business requirement will remain with us. We will begin our setup of subscription
for the FoodMart
Sales Report by taking the
Click the New
Subscription link (shown circled in the above illustration).
properties page opens. Here we make the settings for the subscription we
Server File Share in the Delivered by: selector box.
options on the properties page change to reflect those that are relevant to our
Report Server File Share delivery choice. It is important to keep in
mind that, although we can chose to have any report delivered to a respective
designated file share, any reports that contain interactive features (including
drill-down, drill-through, and so forth) will be delivered as static files.)
Name to reflect the following:
Foodmart Sales Report
In the Path
box, type in a valid share name.
name must be in UNC (Uniform
Naming Convention) format. For
example, my entry here is as follows:
A Books Online
reference specifically states that trailing backslashes (or "slatches,"
as I have been calling them awhile now) are not to be used here.
In the Render
Format selector, select Excel (unless you do not have MS Excel
on your PC. If that is the case, substitute Web Archive).
report file, with an .xls extension, will be delivered to the specified file
share with the name we have given it. Within the Render Format
selector, we can select various file formats (to the delight of many clients to
whom I have presented this feature to date, most of whom had to write macros,
etc., to handle the same functionality in Cognos, Business Objects, and other
enterprise solutions). Options include:
- XML file with report data
- CSV (comma delimited)
- TIFF File
- Acrobat (PDF) file
- Web page for IE 5.0 or later
- Web page for most web browsers
- Web archive
are certainly possible (Lotus, etc.), with the flexible API afforded by
Reporting Services, as well as through other easy approaches.
appropriate credentials to access the file share stipulated, if
Click the Select
Schedule button in the Subscription
Processing Options section (lower
half of the page).
The related properties
page opens, providing access to the scheduling settings for the subscription we
14. Click the radio button labeled Day in the Schedule details section.
15. Select a time about five minutes away, to trigger the Excel file creation.
16. Leave the settings in the Start and end dates section at default (Start date should default to the current system date).
NOTE: Remember to return after the session to reset the subscription settings we have made, as desired.
The scheduling-related settings of the properties page should resemble those depicted in Illustration 15.
17. Click OK to return to the main Subscriptions properties page.
18. Select Non-Consumables in the ProductFamily selector in the Report Parameter Values section near the bottom of the properties page.
The Subscription properties page should resemble that shown in Illustration 16.
Illustration 16: The Subscription Properties Page, with Our Settings
19. Click OK to accept and save the settings.
The properties page closes, and we arrive at the Subscriptions tab, initial page, where we see our new subscription listed, as depicted in Illustration 17.
Illustration 17: The New Subscription Appears
Editing a subscription's setting is accomplished here by simply clicking Edit, between the checkbox and the Description for the entry.
We can also view, modify and delete existing subscriptions from the My Subscriptions page. This is a "maintenance" center, as it were, and does not provide the capability to create subscriptions. My Subscriptions also shows (depending upon our security setup) only those subscriptions that we create - not those that are created by other users.
20. Click My Subscriptions to open the My Subscriptions page.
The My Subscriptions page opens, and appears as depicted in Illustration 18.
Illustration 18: The My Subscription Page
The organization of subscriptions in a central location provides us an efficient means of maintenance.
Let's take a quick look at the Excel file that our labors have produced.
21. Go to the file share designated as the Path in the standard subscription setup above.
The file appears in the folder I designated, as shown in Illustration 19.
Illustration 19: The Report File, as Generated and Stored
22. Click / double-click the Excel file, as appropriate to you PC's settings, to open it.
The Excel file opens, and appears, as shown in Illustration 20.
Illustration 20: The Report File, Formats Intact, in Excel
We note that the filter (Non-Consumables) has been passed correctly in the results that we obtain. Further, the formats appear to be intact, as well.
NOTE: Over the years, I have heard hundreds of individuals complain about issues with this particular capability in their experiences with Business Objects, Cognos, and the rest of the Big Sisters. Not a bad showing to get right, the first time, what other enterprise solutions have failed to accomplish over several years!
This is only a tiny, symbolic vestige of the advantages that Reporting Services promises those organizations that convert - in addition to the dramatic savings that cannot help but ensue.
23. Return to reset the subscription settings we have made, as desired
24. Close Report Manager, after reviewing the steps we have accomplished in this lesson - and after experimenting further, as desired.
While our exposure to basic subscriptions has focused on the creation of a standard subscription, with delivery via Report Server File Share, it is important to remember that far more can be accomplished with standard subscriptions, including the following:
Creation of a subscription to deliver reports via e-mail to individuals and / or groups;
Optional separate control of subscription processing by consumers (to enable selection of reports they wish to receive, etc.);
Bypass of browser viewing and delivery of reports to file shares for alternative viewing (similar to the example we performed in this lesson). This is often an effective strategy in the case of large reports that might be time consuming to load in a browser, etc.;
Consumer selection of PDF / Web archive formats for offline viewing;
Automated report archiving, established via file shares.
We can also achieve many more objectives with data-driven subscriptions, which we will examine in detail in the next article of the Reporting Services Managing phase articles group.
Summary and Conclusion ...
In this article, we continued our introduction to the Managing phase of the Reporting Services development life cycle, and introduced the main topics of report execution and viewing, together with standard subscriptions. We reviewed the process of running reports, partially touched upon in our last session, and then we discussed directly viewing reports from a web browser.
Next, we focused upon the setup and operation of a basic standard subscription, discussing conditions that must be in place before establishing a standard subscription before beginning our hands-on creation exercise. Throughout our setup of a standard subscription, with delivery via report server file share, we discussed options that abound within this robust capability as well as the details of the steps we took to bring it to fruition. Finally, in our concluding comments, we discussed additional options that standard subscriptions offer knowledgeable Reporting Services developers and users.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.