SSAS data source without MDX

Question

When I use an SSAS cube in Excel the command text is just the name of the cube itself and does not require an MDX query.  Is it possible in SSRS to avoid writing an MDX query?

Answer 1

Hi Matt

When you create a connection to the cube in SSRS you will need to provide a query of some sort as SSRS does not support dynamic query building the way Excel pivot tables do. However you would not have to write the MDX yourself as the SSRS does have a query designer where you can drag and drop filters, attributes and measures similarly to how you would in Excel but this would be at design time(When you build the report) and not at run time.

Answer 2

Thanks Michael.  The query designer isn't very friendly and assumes a lot of things (NON EMPTY, etc).  I'm very new to MDX and was hoping for an easy way out!

Answer 3

What you can do it create the report in Excel, then use Profiler look at the MDX that is being generated. Unfortunately the MDX generated by excel is not very efficient but may work for you, you would just need to paste it into the query window and add any parameters.