Applied Reporting Services 2008 Online Training Class with Teo Lachev

Some of you shared with us your frustration with training budgets cut in half. Many of you couldn’t attend industry conferences this year. Many of you asked how we can help.

We are excited to offer online Business Intelligence classes – no travel, no hotel expenses, just 100% content delivered right to your desktop!

Our first class is Applied Reporting Services 2008. This three-day intensive event teaches you the knowledge and skills you need to master Reporting Services to its fullest.

072809_2347_AppliedRepo1 Applied Reporting Services 2008 Online Training Class
with Teo Lachev

Date: August 31 – September 2, 2009
Time: Noon – 4 pm EDT; 9 am – 1 pm PDT
12 training hours total for only $799!

072809_2347_AppliedRepo2

Attend this class for only $799 and get a free paper copy of the book Applied Microsoft SQL Server 2008 Reporting Services by Teo Lachev!

For more information or to register click here!

Excel Page Numbering Issue

An issue was reported about page numbering and exporting reports to Excel with Reporting Services 2008. Specifically, if the page header or footer uses a placeholder for the page number, when exported and previewed in Excel, the report will show Page 1 on all pages.

Page [&PageNumber] of [&TotalPages]

While waiting for Microsoft to fix this, use an expression that concatenates the PageNumber member of the Globals collection.

= “Page” & Globals!PageNumber & ” of “ & Globals!TotalPages

To enter the expression, select the textbox item, right-click and choose Expression.

Google – The Best Thing that Ever Happened to Microsoft

On a different subject, you’ve probably heard the news: Google will release an operating system called Google Chrome OS which will challenge Windows and instill fear into the Microsoft camp. To the contrary, I think Google is the best thing that ever happened to Microsoft. How come?

I remember reading somewhere that after the perestroika, Mikhail Gorbachev had supposedly told Ronald Reagan “we’ll now do the worst thing to you (USA); we’ll leave you without enemy”. Perhaps, too extreme but paraphrased to business, completion is a good thing. When challenged, good companies become better, products improved and consumers benefit. So, I hope Google will continue expanding their ambitions. Similarly, I hope Microsoft gives Google a run for its money by competing relentlessly to increase their share of the search market. I’d personally love to see business intelligence features added to the search results. Why not, the first two letter s of bing are BI, right? How come I can’t even sort the search results by date to see the most recent matches on top? A chart showing the popularity of the search item over time? People who searched for X searched also for Y?

It will be interesting to see how this mega competition will evolve in time. As Chinese say “may you live in interesting times”.

How to Test SSRS MDX Queries in SQL Server Management Studio

I often need to capture an MDX query that a report sends to Analysis Services and execute it in SQL Server Management Studio (SSMS). I usually do this to understand what parameters the report passes to the query and to troubleshoot the query itself. Unfortunately, SSMS doesn’t support parameterized MDX queries so you have to resort to the following technique:

Capturing MDX Queries

The easiest way to capture an MDX query is to use the SQL Server Profiler.

  1. Start SQL Server Profiler from the Microsoft SQL Server <version>-> Performance Tools program group.
  2. Click File -> New Trace or press Ctrl+N.
  3. In the Connect To Server dialog box, choose Analysis Services, enter your server name, and click Connect.
  4. In the Trace Properties dialog box, click the Event Selection tab. Check the Show All Events and Show All Columns checkboxes.
  5. Optionally, if you want to see only your events, that is events triggered by your actions only, click the Column Filters button. In the Edit Filter dialog box, select NTUserName. Expand the Like tree node on the right and enter your Windows login surrounded by %, e.g. %t_lachev%.
  6. Back to the Trace Properties dialog box, click Run to start the trace.
  7. Now run the report whose queries you want to capture and watch the profiler output for a Query Begin event.

Typically, a report would execute several MDX queries because there will be queries for the report parameters. You can use the profiler find function (Ctrl+F) to search for the query text you need in the TextData column in order to find the report main query (that one that supplies the report with data).

  1. Selec t the Query Begin event of the query you want. In the lower pane of the profiler, you will see the query MDX statement followed by Parameters and PropertyList nodes, such as:

    WITH MEMBER MyKPI as StrToMember( @KPISelector)

    SELECT NON EMPTY {MyKPI} ON COLUMNS,

    NON EMPTY StrToSet(@Dataset)

    * StrToMember(@StartPeriod) : StrToMember(@EndPeriod) ON ROWS

    FROM Viking

    WHERE (StrToMember(@Organization) )

    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    <Parameters xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns=”urn:schemas-microsoft-com:xml-analysis”>

    <Parameter>

    <Name>KPISelector</Name>

    <Value xsi:type=”xsd:string”>[Measures].[Return On Average Assets]</Value>

    </Parameter>

    <Parameter>

    <Name>StartPeriod</Name>

    <Value xsi:type=”xsd:string”>[Date].[Month].&amp;[20060101]</Value>

    </Parameter>

    <Parameter>

    <Name>EndPeriod</Name>

    <Value xsi:type=”xsd:string”>[Date].[Month].&amp;[20061201]</Value>

    </Parameter>

    <Parameter>

    <Name>Dataset</Name>

    <Value xsi:type=”xsd:string”>{ [Dataset].[Dataset].&amp;[-2],[Dataset].[Dataset].&amp;[-1] }</Value>

    </Parameter>

    <Parameter>

    <Name>Organization</Name>

    <Value xsi:type=”xsd:string”>[Organization].[Organization Hierarchy].&amp;[-1]</Value>

    </Parameter>

    </Parameters>

    <PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>

    <Catalog>VANTAGE_CUBE_M_BASELINE DEV</Catalog>

    <LocaleIdentifier>1033</LocaleIdentifier>

    <Format>Tabular</Format>

    <Content>SchemaData</Content>

    <Timeout>0</Timeout>

    <ReturnCellProperties>true</ReturnCellProperties>

    <DbpropMsmdFlattened2>true</DbpropMsmdFlattened2>

    </PropertyList>

  2. Click anywhere in the lower pane and press Ctrl+A to select all text, then Ctrl+C to copy it to the clipboard.

Testing MDX Queries

Now that you have the query, you are ready to test it in SSMS.

  1. Open SSMS and connect to the Analysis Services server.
  2. Right-click on the SSAS database and click New Query -> MDX to open and create a new MDX query. Press Ctrl+V to paste the query text that you captured in the profiler.
  3. Since the profiler escapes certain characters, first you need to unescape them. For example, replace (Ctrl+H) all &amp; with &.
  4. One by one, go through the parameters listed in Parameters node and replace the parameter placeholder in the MDX query with the actual value. For example, press Ctrl+H to search for
    @KPISelector (note that the parameters are prefixed with @ in the MDX query) and replace all occurrences (Replace All option) with the actual parameter value. Since typically SSRS MDX queries use StrToMember or StrToSet functions, you need to surround the parameter values with quotes, e.g. “[Measures].[Return On Average Assets]”.
  5. Once you replace all parameters, comment the Parameters and PropertyLists nodes by selecting all text in these nodes and clicking the Comment Out the Selected Lines toolbar button. The only text that it not commented should be the MDX query text.

At this point, you can execute the query by pressing Ctrl+E or clicking the Exclamation toolbar button.

UPDATE

When things get tedious, the developer writes a program. As Greg Galloway pointed out, Darren Gosbell has already written a small but incredibly useful utility, SSAS Query Capture, that automates the process of capturing the queries and replacing the parameters. Moving forward to Visual Studio 2008 and SQL Server 2008, you need to:

  1. Upgrade the source code to Visual Studio. To do so, just open the C# source project in Visual Studio 2008 and accept the defaults.
  2. Delete the Microsoft.AnalysisServices and reference Microsoft.AnalysisServices.dll which you can find in the \Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder.
  3. Build the project and run QueryCapture.exe in the bin\debug folder.

A cautionary note: The utility always encloses the parameter values in quotes because it assumes that they will be inside the StrToMember and StrToSet functions which SSRS MDX Query Designer generates when you add parameters. If you reference parameters literally, as I demonstrated in this blog, remove the quotes surrounding these parameter values. Otherwise, the chances are that the query will return no data.

I couldn’t imagine how much time Darren’s Query Capture utility could have saved me if I only knew about it! I hope he adds it to the BIDS Helper project.