MDXParameter is a nice little utility that captures MDX queries sent from SSRS reports and replaces parameter placeholders with the actual values. I’ve been using the excellent Darren Gosbell’s Query Capture sample which is one of the first utilities I install when starting a new project. MDXParameter has a few more features that you might find appealing, including saving the queries in a database and showing the parameters separately. Installing MDXParameter is simple:
- Download MDXParameter from Codeplex and unzip it.
- MDX Parameter requires a SQL Server database to capture the queries. It could have been nice to include an option to write the queries to a file or just the screen but currently you must create an empty SQL Server database. You can name the database anything you want.
- Double-click MDXParameter.exe to start it. You will be prompted to specify the connection details. Note that MDXParameter will automatically create the table schema.
- Click OK to launch the main interface and click Start to start the trace.
- Go to your SSRS report and run it. Make sure to delete the *rdl.data file in the SSRS project folder to avoid running the report with cached datasets.
- Click Stop to stop the trace. You should see the all report MDX queries captured. Select a query to see its statement in the MDX tab. If the query has parameters, MDXParameter will replace them with the actual values (if the Replace Parameter checkbox is selected). This of course is the main reason why you need MDXParameter as it saves you a great deal of time if you want to execute the query with many parameters in SSMS. The tabParametersGrid shows you a grid with the actual parameter values. The Execute MDX button is currently not functioning.
MDXParameter supports SSAS 2005, 2008, and R2.