How Do I Get the MDX Query for my AdHoc Crosstab Report?

Starting with version 4.1 of JasperReports Server (w/ OLAP and Ad-Hoc functionality enabled) you can create Crosstabs in the browser. What happens if you want to see the generated MDX for use in traditional MDX views?

Simply log in as superuser, go to the Manage => Log Settings menu and change:

com.jaspersoft.ji.adhoc.strategy.MDXDataStrategy to DEBUG

Then in ../WEB-INF/logs/jasperserver.log you should see the MDX (and text results):

WITH
SET [SelectedMeasures] AS '{[Measures].[Avg Sale Amount], [Measures].[Avg Time To Close (Days)]}'
SELECT
{[SelectedMeasures]} ON COLUMNS,
{Hierarchize({[Close Period].[Year].Members,
              [Close Period].[Quarter].Members,
              [Close Period].[Month].Members})}
ON ROWS
FROM [SalesAnalysis]
 
2011-05-23 17:56:10,482 DEBUG MDXDataStrategy,http-8080-5:114 - mdx query result:
 
|                      | Avg Sale Amount | Avg Time TO Close (Days) |
+------+---+-----------+-----------------+--------------------------+
| 2002 |   |           |       $3,426.11 |                       23 |
|      | 4 |           |       $3,426.11 |                       23 |
|      |   | November  |       $1,288.00 |                       12 |
|      |   | December  |       $3,770.97 |                       25 |
Feedback