After a million or so complaints about the complexity of building parametrized queries with the PARAMETER function within MDX, today I indulged to the demands of the crowd.
Beginning with Milestone 2 of the Pentaho Report-Designer 3.6,we now support parameter injection via the ${parameter} syntax. Parameter values injected via that syntax will not be checked in any way, so it is the designer’s responsibility to ensure that everything is quoted correctly to cause no harm or to break the query. With great powers comes great responsibility.
The ${parameter} syntax for MDX is not just a toString() conversion. It follows the MessageFormat syntax and thus allows to format Date and Number objects properly before inserting them into the MDX query. An extended format rule allows to produce quoted MDX-string literals by specifying the subformat string. These strings start and end with a double-quote and all double-quote characters found in the original string get escaped according to the MDX grammar.
So now I can finally answer the question on how to parametrize a Date-Axis from a Date-parameter. To produce a member string like [2009].[10].[4] from a parameter called dateparam use [${dateparam,date,"yyyy"}].[${dateparam,date,"MM"}].[${dateparam,date,"dd"}] in your MDX query.
I still haven’t found out how to do the same with the PARAMETER function.
Support for the PARAMETER function will remain there (as in theory it is a good idea to have prepared/explicit parameter).
You can test this functionality with either the latest CI build or with the upcoming Milestone 2 of the Report-Designer 3.6.
Ok, so then is it possible to simply place a ${variable} in a MDX query to have dynamic rows or columns? Thus,
select ${myVariable} on COLUMNS,
{[Measures].[MyMeasure1], [Measures].[MyMeasure2]} on ROWS
from [Monthly Data]
Where myVariable can be [Client].[Location].[All Locations] or {[Client].[Location].[WASHINGTON], [Client].[Location].[COLORADO]}
I have been trying to get this in PRD and have not been able to do so.
Ok, so then is it possible to simply place a ${variable} in a MDX query to have dynamic rows or columns? Thus,
select ${myVariable} on COLUMNS,
{[Measures].[MyMeasure1], [Measures].[MyMeasure2]} on ROWS
from [Monthly Data]
Where myVariable can be [Client].[Location].[All Locations] or {[Client].[Location].[WASHINGTON], [Client].[Location].[COLORADO]}
I have been trying to get this in PRD and have not been able to do so.
Yes, the ${..} is a mindless string replacement.
According to the examples on the MSDN documentation for MDX, you always have to use the curly braces (ie the set syntax).
http://msdn.microsoft.com/en-us/library/ms146052.aspx
Therefore, if myVariable is “[Client].[Location].[All Locations]” you probably get a syntax error.
Interestingly, JPivot seems to strip curly braces and some other “complete” member identification. I need to test a bit more, but find that if I submit {[Client Location].[Client Location.Clients].[All Clients].[COLORADO]} it will reformat the MDX to [Client Location.Clients].[COLORADO].
In PRD, I have found that once a query is parameterized and you cannot get to the variables anymore, PRD can lose connection to the field variables. You can copy and paste information from one report to another, and the second report will not work. I have had to pull the variables from the PRD query to get the fields to place on the report.
I really like 3.6. I have been using Pentaho off and on for 4 years and this version rocks. I am already hearing 3.7 and 4.0 upgrades that will be even mo’ better yet.