Pro SQL Server 2012 Reporting Services

CHAPTER 10  MANAGING REPORTS

312

Designing the Log Report

We knew we would need another report that contained all the execution log information and was easy

for administrators to analyze. Thus, we created a matrix-style report, called Report Execution Log, using

data from a single query.

To measure performance from information contained in the execution log, you need several

statistics:

• Total time to retrieve the data: How long did it take to retrieve data?


• Total time to process: How long did the report take to process?


• Total time to render: How long did the report take to render?


• Byte count: How many bytes are in the report?


• Row count: How many rows of data are in the report?

In addition, knowing when the report was executed is also useful. For the row groupings in the

matrix, we want to see what report was executed, who ran the report, and from which client machine the

report was run. For the column group, we want to have two possible selections: rendering format, such

as HTML 4.0, Word or PDF, or source types, meaning how SSRS generated the report (live, cache, or

snapshot, for example). Source_Type is an important field to monitor because how SSRS generates

reports directly impacts performance. Generating reports from a cached copy or a snapshot, which are

both pre-processed copies of reports, is a performance benefit. If SSRS is always generating live or on-

demand reports for users, performance may suffer.

To accomplish the dynamic column groupings in the matrix, you’ll use a parameter called

Column_Group that takes the values of the field names in the query, Format or Source_Type. You’ll use a

default value of Format so that the report is automatically rendered when previewed. Both the column

grouping and heading values will use the following expression to make the column dynamic based on

the parameter:

=Fields(Parameter!Column_Group.Value).Value

When the report is rendered, as you can see in Figure 10-27, it will default to the Format field, but

you can change it dynamically by changing the parameter drop-down selection to Source_Type.

l