Exception when parsing WITH/SET keywords and empty HTML from TableRenderer

[joaosalle]

I solved my first issue by switching the single quotes (since that's what was causing the exception) to escaped double quotes \". Still, the second problem persists where the TableRenderer generates an empty HTML file for the query:

WITH SET [FilterYear] as ORDER(FILTER([Time].[Month].Members, [Time]. [Month].CurrentMember.Parent.Parent.Name = \"2017\" OR [Time]. [Month].CurrentMember.Parent.Parent.Name = \"2016\"), [Time]. [Month].CurrentMember.Name, BASC) SET [Cli] as {[Client].[Name].[CLIENT_NAME]} SET [Measure] as {[Measures].[UnitSales]} SET [Filter] as FILTER([Mov].[Ops].AllMembers, [Mov].[Ops].CurrentMember.Name = \"PARAM1\" OR [Mov].[Ops].CurrentMember.Name = \"PARAM2\") SELECT NON EMPTY Crossjoin([Measure],[FilterYear]) on COLUMNS, NON EMPTY [Cli] on ROWS FROM [CUBE_NAME] WHERE [Filter]

Original issue quoted below:

Exception in thread "main" org.pivot4j.mdx.ParseException: Fatal error parsing MDX:Couldn't repair and continue parse invalid symbol "'" at org.pivot4j.mdx.impl.CupParser.report_fatal_error(CupParser.java:658) at java_cup.runtime.lr_parser.unrecovered_syntax_error(lr_parser.java:409) at java_cup.runtime.lr_parser.parse(lr_parser.java:601) at org.pivot4j.mdx.impl.MdxParserImpl.parse(MdxParserImpl.java:38) at org.pivot4j.impl.QueryAdapter.parseQuery(QueryAdapter.java:629) at org.pivot4j.impl.QueryAdapter.initialize(QueryAdapter.java:99) at org.pivot4j.impl.PivotModelImpl.initialize(PivotModelImpl.java:215)

Above is the stacktrace of the exception, seems like it's something with the parser. The exception is thrown when MDXs with the WITH/SET keywords are used.

This is the MDX I used the first time I got the exception thrown at me:

WITH SET [FilterYear] as ORDER(FILTER([Time].[Month].Members, [Time]. [Month].CurrentMember.Parent.Parent.Name = '2017' OR [Time]. [Month].CurrentMember.Parent.Parent.Name = '2016'), [Time]. [Month].CurrentMember.Name, BASC) SET [Cli] as {[Client].[Name].[CLIENT_NAME]} SET [Measure] as {[Measures].[UnitSales]} SET [Filter] as FILTER([Mov].[Ops].AllMembers, [Mov].[Ops].CurrentMember.Name = 'PARAM1' OR [Mov].[Ops].CurrentMember.Name = 'PARAM2') SELECT NON EMPTY Crossjoin([Measure],[FilterYear]) on COLUMNS, NON EMPTY [Cli] on ROWS FROM [CUBE_NAME] WHERE [Filter]

The MDX is syntactically correct, and returns what I need it to return when I'm using Mondrian's Schema Workbench's MDX query terminal.

I tried fiddling with the .cup file in the .jar but, since I know absolutely nothing about cup or parsing in general, nothing I did worked out.

It's also worth noting that the MDX equivalent to the above without the WITH/SET keywords compiles and runs, but renders an empty html page. Seems like it doesn't return the resultset it should return.

Any ideas for fixes are greatly appreciated.

[joaosalle]

It seems that the problem lies in the ROWS axis. For some reason, if I filter the ROWS axis, the HTML output is blank.

[Client].[Name].AllMembers on ROWS renders into a normal HTML table, but if I use [Client].[Name].[Client_Name] on ROWS or FILTER([Client].[Name].AllMembers, [Client].[Name].CurrentMember.Name = "Client Name") on ROWS, output is a blank HTML file.

[mysticfall]

I tested with the following MDX on the Analytics client, but the expected result shows up :

WITH SET [FilterYear] AS 'FILTER([Time].[Quarter].Members, [Time].[Quarter].CurrentMember.Parent.Name = "1997")' 
SELECT {[Measures].[Sales Count]} ON COLUMNS, [FilterYear] ON ROWS FROM [Sales 2]

And I used double quotes without any escaping too. Could you try it again with the bundled FoodMart database, and see if you can reproduce the issue?

It's a quite difficult to debug this sort of a problem without being able to run it on my end, so it'd be very helpful if you could. Thanks.

[joaosalle]

Apparently, I was missing a single quote after the AS keyword (you can see in my code that I used WITH SET AS instead of AS ''), which works perfectly in the Schema Workbench from Pentaho, but doesn't in the pivot4j/olap4j APIs. My guess is that the workbench treats the query before parsing it.

Thanks for the response @mysticfall, I'll close this issue since it's been solved.

[mysticfall]

I'm glad that you've found a fix. And thanks for letting me know! :)