Reporting Services Charts using OpenQuery to write MDX

There is currently a limitation in Reporting Services when working with MDX Cube queries.

Suppose we want to display the following report:

The challenge with the above chart is that the “Drivers” (i.e. NPS, Navigation, etc. found on the X-Axis) that are used for grouping are actual Measures in the Cube, and the “Days of Week” values (i.e. Monday - Sunday) are actually based off a single date Dimension.

In effect this means that when we create a new Dataset in the Reporting Services designer the following fields would be generated:

This won’t help us group the “Drivers” in the chart because we would actually need a single column to group by.

So within the context options of the report the following is what we would hope to achieve:

That means that our Dataset must look like this:

This is extremely easy to achieve in MDX where we would write the following keeping in mind that we want our Measures as rows and our single “Days Of Week” Dimension in the columns.

The following MDX query would provide what we are looking for:

SELECT 

      NON EMPTY {

            [Measures].[NPS], 

            [Measures].[Navigation], 

            [Measures].[Accessibility], 

            [Measures].[Availability], 

            [Measures].[Checkout], 

            [Measures].[Staff], 

            [Measures].[Cleanliness]} ON ROWS, 

      NON EMPTY  CROSSJOIN({ 

            ([Days Of Week].[Order Index].[Order Index] ) }, 

            { ([Days Of Week].[Day Of Week].[Day Of Week] ) }) ON COLUMNS 

FROM [Database]

This would return the following result:

You would think that this would then naturally be simpler to bind to the Chart now that we have the correct columns to bind to our “Category Groups” in the Chart’s context option.

Unfortunately, however, Reporting Services doesn't like this because having a Dimension displayed as columns would result in the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

In order to solve this problem we will need to generate a Dataset using OPENQUERY.

This can get tricky when working with multiple servers that host the SQL Server database and the Analysis Services database.

The first step would be to create a Linked Server on SQL Server. This will create a connection from SQL Server to Analysis Services so we can actually write MDX queries from a SQL Server query:

Once this is created we can now use OPENQUERY as follows:

SELECT * FROM  OpenQuery([LINK],'

SELECT 

      NON EMPTY {

            [Measures].[NPS], 

            [Measures].[Navigation], 

            [Measures].[Accessibility], 

            [Measures].[Availability], 

            [Measures].[Checkout], 

            [Measures].[Staff], 

            [Measures].[Cleanliness]} ON ROWS, 

      NON EMPTY CROSSJOIN({ 

            ([Days Of Week].[Order Index].[Order Index] ) }, 

            { ([Days Of Week].[Day Of Week].[Day Of Week] ) }) ON COLUMNS 

FROM [Database]

')

That will provide us with the following result in SQL Server Query Analyser:

Since Reporting Services will consider this a simple table rather than  a complex MDX query, it can now be used in the report when creating a dataset in order to generate the following fields:

Naturally that means needing to map the “Monday” field to “[Days Of Week].[Order Index].&[1].[Days Of Week].[Day Of Week].&[Monday]” and so on as shown below in the Fields section:

One important note to make is that Integrated security may seem to be the easiest security credentials to work with when connecting from, for example, a ReportViewer control in an ASP.NET site to the Reporting Services report, which then in turn connects to the SQL Server database, and then to the Analysis Services database via OPENQUERY. However this will not necessarily work since each connection along the path witll have its own Windows credentials.

Ideally the Data source’s connection string in the Report Server should actually be set to a SQL login so that both the Report can work either directly from a browser or through the ReportViewer.

The Linked Server settings should be set to use a specific security context for a login that has read access to the Cube. This can be set in the Linked Server as follows: