Hacking MDX Query Designer Parameters

/2 Comments/in /by

[View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/simpleparameters.zip]Continuing our intrepid journey in the land of SSRS-SSAS integration, after we’ve figured out how to retrieve the cube metadata, we are ready to tackle another unchartered territory: query parameters.

Issue: You need to parameterize the MDX query by a measure or a simple parameter. Take for example the following report (attached).

022609_0224_HackingMDXQ1

This report shows the Internet Sales Amount measure broken down by the Employees parent-child hierarchy. What’s more interesting is the Show Descendants parameter. It lets you filter the report for a single employee (Show Descendants = False) or the descendants of the selected employee including the selected employee (Show Descendants = True). So, what’s the issue? You can whip out such a report in no time if you source data from a relational database. However, the MDX Query Designer insists that the parameter is resolved to a single dimension member or a set of dimension members. Apparently, simple parameters or parameterizing by measures were considered uncommon scenarios. So, we need a sleek hack.

Solution: Luckily, there is a way to gain more control over the MDX query parameters but the design mode (the one that lets you drag and drop stuff) won’t cut it. So, it may make sense to author the report query in design mode as much as you can before you jump into the cold waters of MDX query mode because there is no going back to drag and drop, sorry.

  1. To start with, you need to define a query parameter for the ShowDescendants parameter. Switch to MDX mode and click the Query Parameters toolbar button (enabled only in MDX mode).
  2. Add a new ShowDescendants “hanging” parameter that doesn’t reference any dimension. Make sure that the query parameter name matches the report parameter name. Otherwise, the MDX Query Designer will treat the parameter as a new parameter and it won’t associate it to the existing ShowDescendants report parameter. Note that the parameter name is case-sensitive.

022609_0224_HackingMDXQ2

  1. Leave the Dimension and Hierarchy columns empty. Luckily, they are not required in MDX mode. Either type in a measure name, such as [Measures].[Sales Summary], or enter DEFAULT in the Default column. In the latter case, the default cube measure will be used when you test the query in the MDX Query Designer. See this blog by Chris Webb for ideas about how to speed up the default measure if needed. Click OK.
  2. Back to the query, change it as follows:

SELECT NON EMPTY { [Measures].[Reseller Tax Amount] } ON COLUMNS, NON EMPTY

IIF(@ShowDescendants=False, StrToMember(@EmployeeEmployees), DESCENDANTS(StrToMember(@EmployeeEmployees)))

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS 

These changes are specific to the report requirements. In our case, the IIF expression checks the value of the ShowDescendants parameter. If the user has selected the False option, the query brings only the selected employee on rows. Otherwise, we use the Descendants function to bring the descendants of the selected employee.

Looks hacky? Join me and make a wish on connect.microsoft.com to improve the SSRS-SSAS integration. Don’t be surprised if you are not active that the MDX Query Designer will continue to sneak from one version to another unchanged.

Report Builder 2.0 ClickOnce

/2 Comments/in /by

One important change that Service Pack 1 for SQL Server 2008 (public CTP available here) will bring is ClickOnce deployment of Report Builder 2.0. By default, however, Report Manager or SharePoint (if you have SSRS installed in SharePoint integration mode) still point to Report Builder 1.0. Here is how to reconfigure SSRS to launch Report Builder 2.0 instead. The following procedure assumes SharePoint integration mode but the steps are similar if SSRS is running in native mode.

CAUTION   Steps 1 and 2 below to copy folders are for the CTP bits of SQL Server 2008 SP1 only because the CTP build doesn’t support RB 2.0 ClickOnce in SharePoint integration mode. SQL Server 2008 SP1 RTM will include an installer to make the appropriate folder changes automatically. Therefore, DO NOT manually copy the folder content with the final bits or undo your changes before you install SP1 RTM so the installer doesn’t fail.

  1. SharePoint Integration Mode Only. Back up the content of the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
  2. SharePoint Integration Mode Only. Copy all files from the C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder folder to the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\ReportBuilder folder. This step is not needed with native mode.
  3. SharePoint Integration Mode Only. If you install SQL Server 2008 SP1 this will provide an update for native mode only.  In order to get Report Builder ClickOnce for SharePoint integration mode working, you need to download the RB 2.0 ClickOnce update for the RS SharePoint add-in from the feature pack page.  This is necessary because the ClickOnce bits must reside on the SharePoint server. As of the time of this writing, the latest add-in is April 2009 which you can download from here. Install rsSharePoint.msi (or rsSharePoint_x64.msi for x64) to update the SharePoint add-in. Then install RB2ClickOnce.msi which copies the Report Builder 2.0 ClickOnce files to the SharePoint site.Note (7/15/09): There is a known issue that RB2ClickOnce.msi fails to install on x64 environment. Microsoft should fix this in Cummulative Update 3 for SQL Server 2008 Service Pack 1. Meanwhile, if you have a report server running in SharePoint integration mode on x64, manually copy the Report Builder 2.0 files by following steps 1 and 2.
  4. SharePoint Integration Mode Only. Open WSS Central Administration. Click the Application Management tab. Click the Set Server Defaults link found under the Reporting Services section.

022509_0300_rbclickonce20

5. Enter the URL address to the ReportBuilder_2._0_0_0.application file. In SharePoint mode, assuming you followed steps 1-2, the URL address will be:
/_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application

For a report server configured in native mode, go to Report Manager, click the Site Settings menu link and enter the following to the Custom Report Builder launch URL setting:

/ReportBuilder/ReportBuilder_2_0_0_0.application

At this point, SSRS is reconfigured to launch Report Builder 2.0 instead of Report Builder 1.0. From the end user perspective, the user would launch RB 2.0 just like they used to launch RB1.0, that is, click New->Report Builder Report in SharePoint or click the Report Builder button in Report Manager. The first time the user does this, the Report Builder 2.0 bits (almost 50 MB) will get downloaded and installed in the ClickOnce local application cache, which is a hidden directory under Local Settings for the current user. The ClickOnce application cache will not be cleared when the user clears the browser cache. Subsequent requests will use the local copy.

UPDATE Now that SQL Server 2008 SP1 is released, it includes a readme file with steps how to configure and troubleshoot Report Builder 2.0 ClickOnce in SharePoint integration mode.

Reporting Services Tracer

/0 Comments/in /by

To make a humble contribution to open source, I’ve just uploaded the Reporting Services Tracer (RsTracer) project to CodePlex. RsTracer helps you trace the URL and SOAP traffic to/from a report server. Given the sheer number of Web methods supported by Reporting Services, you might find yourself asking which web method should you choose for the management task at hand and how should you call it? At the same time, it is likely that Report Manager or Management Studio already supports some aspect of the management feature you want to implement.

Wouldn’t it be nice to be able to peek under the hood and see what APIs Report Manager or a custom application calls? This is exactly what the RsTracer sample was designed to handle. RsTracer intercepts the server calls and outputs them to a trace listener, such as the Microsoft DebugView for Windows. RsTracer helps you see the APIs that a Reporting Services client invokes and what arguments it passes to each interface. It also intercepts the server response. Armed with this information, you can easily reproduce the same feature in your custom management application. You can use RsTracer with SSRS 2000, 2005, and 2008.

Enjoy!

Retrieving Cube Metadata in Reports

/0 Comments/in /by

An interesting question popped up on the discussion list the other day about how to retrieve a list of the cube measures so the report author can display them in a report parameter.

Solution 1: If you just need a list of measures but not the measure metadata, such as display folder, etc., you can use the following (not trivial, ok hacky) query, which I borrowed from my esteemed colleague MVP and MDX guru Chris Webb:

WITH SET MYSET AS HEAD([Date].[Date].[Date].MEMBERS, COUNT(MEASURES.ALLMEMBERS)-1)

MEMBER MEASURES.MeasureUniqueName AS  MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).UNIQUENAME

MEMBER MEASURES.MeasureDisplayName AS MEASURES.ALLMEMBERS.ITEM(RANK([Date].[Date].CURRENTMEMBER, MYSET)-1).NAME

SELECT {MEASURES.MeasureUniqueName, MEASURES.MeasureDisplayName} ON 0,

MYSET ON 1

FROM [Adventure Works]

 

Solution 2: This approach is especially useful if you need the measure properties. In this case, you must use the OLE DB Provider for Analysis Services instead of the SSRS Analysis Services Provider (the one that gives you the nice MDX Designer). This approach uses the MDSCHEMA_MEASURES rowset, which is one of the built-in SSAS schema rowsets. Darren Gosbell provides a great overview of the SSAS rowsets here and Vidas Matelis gives more insights here. Thanks to the schema rowsets, getting a list of measures can’t simpler.

SELECT * from $System.MDSCHEMA_MEASURES

The user will require Read Definition rights (on Role Properties General tab, check Read Definition) to obtain the metadata. If the user doesn’t have this right, an empty dataset will be returned.

There are of course additional schema rowsets, such as MDSCHEMA_KPIS if you want to get a list of all KPIs or DBSCHEMA_TABLES in case you want to discover the cube dimensions and measure groups. BOL provides a full list here. Aren’t SSAS schema rowsets cool?