Skip to content

Using Reporting Services with DirectQuery models

March 20, 2012

Power View is the primary reporting client for visualizing query results for DirectQuery models. After all, it is the only graphical client that issues DAX queries.

However, it is possible to use other reporting clients, including Reporting Services running in native or SharePoint integrated mode, to get results from a DirectQuery model. To do this, supply a DAX query in the place where an MDX query should be. For more information about using DAX queries, consult Chris Webb’s six part series on getting started with DAX queries (1, 2, 3, 4, 5, 6) and also Marco Russo’s post on parameterizing DAX queries.

Reporting Services is interesting because it has disabled text entry for Analysis Services data sources. This is a problem, as that means you cannot simply switch the MDX query designer to text and enter a DAX query in there (the “intuitive” way to enter a DAX query, as intuitive goes for DAX query editing in SQL Server 2012). Instead, you must enter the DAX query in the DMX query designer. Since Report Builder doesn’t support the DMX query designer, you must therefore use SSDT to build reports that query DirectQuery models.

To create a report that connects to a DirectQuery model:

  1. Launch SSDT.
  2. Create a new Reporting Services project. I will use the Reporting Services Project Wizard in this example.
  3. Enter the connection information for your DirectQuery model and then click Next.
  4. Click the Query Builder button to construct the DAX query.
  5. Click the Data Mining button to start the DMX query editor:

    image

  6. Click Yes to dismiss warning about losing changes.
  7. Click the funky ruler button to go into text editing mode:

    image

  8. Type your DAX in the text box as shown, and then click OK to shut the query builder.

    image

  9. Finish constructing your report as usual.

Now when you run your report, you see results returned directly from the relational data source. Voila, you’ve just cut Power View out of the picture when connecting to DirectQuery models. Not sure if this is entirely supported, but it works.

Advertisements
One Comment
  1. Jon permalink

    Good information, and I love the low-res graphics 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: