Select your .odc file and click OK. On the Import
Data screen, select a PivotTable Report, place the table
in the Existing Workbook, and click OK. Select a
couple of items from the PivotTable Field List to create
a report. For the AdventureWorks example, we’ll use
the Internet Gross Profit and Internet Sales Amount
for the Values, and we’ll select Customer Geography,
Country as our Row Labels.
Before we actually publish this report to our
Reports Library, we want to name the PivotTable.
Click PivotTable Tools, Options and give the Pivot-
Table a distinct name. While you’re at it, you might
want to add some visualization to the report. Select
the values for Internet Gross Profit (cells B3:B8), then
select Home, Conditional Formatting, Data Bars and
select the blue data bars. Repeat the same process for
the Internet Sales Amount (cells C3:C8), but choose
the green data bars this time.
One last piece of formatting that will give the report
a little nicer look is a PivotTable style. Click PivotTable
Tools, Design and select Pivot Style Medium 16.
Now that the report is all dressed up, let’s give it
somewhere to go. Click the Office Button, then Publish,
Excel Services. In the Save As dialog box, change
the location to Reports Library. If you don’t have a
direct link to the Reports Library, paste in its URL
and press the Tab key to display it. Next, configure
Excel Services Options. When you click the Excel
Services Options button, you have two tabs to choose
from. Choose the first tab, which lets you select which
workbook parts to publish.
You have three options. You can publish the entire
workbook, which will render everything in the workbook
in Excel Services. You can also publish individual
sheets from the workbook. This option is useful if
you’re doing calculations based on some proprietary
business logic that’s located on a separate sheet. You
can publish the result of that logic in your report
without exposing the actual calculations that produce
the report.
The option we’ll select for our example is to publish
individual named items in the workbook. I named my
PivotTable ISALES so that when I select Items in the
Workbook, I’ll see that option under All PivotTables.
Select the PivotTable you created, as Figure 4 shows.
If we had
created a report
that had parameters
(e.g., year),
we could publish
them as parameters
and make
them available to
the users of this
report by adding
them on the
Parameters tab.
For our example,
we do not need to add any parameters, so simply
click OK.
Name your report and click Save. If you’re saving
your report in the Reports Library, you’ll be prompted
to choose a document type. There are two views in the
Reports Library: One is called Dashboards and is used
for the dashboard pages and the other is called Current
Reports and is for reports. For our document, we want
to choose Current Reports.
Two things happen when you click OK at this step.
Obviously, the report will be saved to the library. Not
so obviously, a copy of the report will be cached for
display in Excel Services. Because we didn’t clear the Open in Excel Services check box when we saved the
document, the report will automatically open in Excel
Services. Figure 5 shows a report in full-screen mode;
you can also view it as part of a dashboard page.
There’s one small problem left to deal with. In
the Excel Services rendering of the PivotTable, click
Update and select Refresh All Connections. You’ll get
an error stating the data refresh failed.
Because the Excel Services server was set up in a
farm, the Access Model defaulted to Trusted Subsystem
when SharePoint was installed. You must set
the Access Model to Delegation to allow the data
in the new workbook to be refreshed. Change the
Access Model by using the Stsadm command-line
tool. Open a command prompt and navigate to
C:\Program Files\Common Files\Microsoft Shared
\Web server extensions\12\BIN. To change the settings
we need, enter (on a single line)
stsadm -o set -ecssecurity -accessmodel delegation
-ssp sharedservices1
The only variable here is the SSP. If you’re working
with a different SSP, be sure to replace sharedservices1
with that name. Otherwise, you should be able to type
the command, press Enter, and get a response that says Operation completed successfully.
Stsadm jobs are timer jobs and might take a few
moments to execute. If you want to force the jobs in
the timer queue to run immediately, you can type the
following command:
stsadm -o execadmsvcjobs
Important: After the job has executed, you must
also run iisreset.exe, as follows:
C:/>iisreset /noforce
Now the access model will be in Delegation mode,
and your report should execute and refresh.
A Robust Solution
Microsoft says it invests more than $700 million each
year in research and development in its Office programs,
servers, and services. There can be no doubt
that the company spent a good amount of that effort
on Excel Services. As a management tool, it’s a robust,
centrally managed document system with individual
file-level security. As a delivery tool, it’s a high-end
Excel rendering engine that maintains a high level of
visual fidelity and doesn’t require Excel 2007. As a
development platform, the new EWS provides a reusable
framework that provides a foundation for custom
development with Excel data.
All of these things combine to make the first generation
Excel “server.” It’s a tool many developers will
agree was far too long in coming.