[Mondrian] Using parameters in Pentaho Report Designer 3.5 -- issue with formulas

Thomas Morgner mondrian at sherito.org
Fri Nov 6 20:05:06 EST 2009

Although some of the 4 pickers could be modelled by using the ordinary
MDX data sources, I do have the feeling that this is not a perfect
solution (too much thinking involved on the user side, no visual
query designer yet).

For the ordinary member case (1 and 4 in the list), we can adapt
the existing parameters quite easily.

So I would opt for creating a set of special purpose data sources in
PRD that solve the problem of picking members by whatever means
are needed. Basically, this would boil down to a simple process
of letting the user specify the connection, cube and hierarchy and
maybe a few filters.

Internally, this would then probably just do an MDX query or query
the cube's properties directly (whichever is easier for me to write :))
but the user is then shielded from the need to think about writing
the MDX queries themselves.

The returned resultset can be easily given in a format that then
allows us to have the IDs for the members to look them up, and also
enough information in it to build a tree-view.

(Speaking of tree view: That would not be a thing limited to
MDX/Mondrian data sources. The implementation would be separate from
the data-source problem. Even in plain SQL-cases selecting a few
thousand products produces quite a challenge for the user when
put into a ordinary list.)

For the date picker and date-range picker case, I see this more
or less a UI level problem with only little implication in the
underlying engine(s). With proper hints in place we should be
able to do the Date-Object to Member-String conversion easily.
The hinting could happen entirely on the client side, via coded
conversion rules - if we play the cheesy game.

Or we can push the coding down to Mondrian via the DateToMember
function, which would mean that the schema (or anything equally
down below the reporting engine) knows about how to get a date
into a member string.

Of course, I would prefer the last option, as this enables an
admin or other knowledgeable person to define the rules and all
the client tools can just use it without having to know the details
or having a chance to screw up.

Priority for me, however, would be the member selection first,
as this is can be a cheap way to get a quick and dirty solution
to the date-picker problem as well. There should be no Mondrian
work involved with that, as I have the feeling we can do this
with what we have today.

I have not the slightest hint of an idea how one would specify
date-ranges in MDX at the moment or whether this is more than
just having 2 date-parameters.

Pedro played with the thought of using the reporting data sources
for CDF, so CDF can then use the same implementation we use.
Of course I would not touch JavaScript with a ten-foot pole, so
the UI for it is left to him to solve. :)

Julian Hyde schrieb:
> Looking at the general problem of getting member-valued parameters into
> mondrian. Off the top of my head, here is a list of requirements. Please
> comment on whether we have them, and their importance.
> First, we need a member-picker. E.g. if the parameter is a product, it would
> allow you to browse the manufacturer, brand, product, SKU hierarchy.
> For dates, you could either use that picker or, for better usability, choose
> dates using a calendar. The picker could then map date values to members of
> the date hierarchy using Mondrian's StrToMember. That's a tricky process,
> because you have to use the metadata about whether each level is a year,
> quarter, month etc. I could help create a UDF for that:
> DateToMember(
>   <date string>,
>   <date format>,
>   <hierarchy>) --> member of hierarchy
> Some people might define a report that takes levels other than the date
> level. E.g. a report that can either take a year or a quarter. The
> calendar-based picker should deal with those levels too.
> Some reports might have date ranges. It might make sense to have a picker
> for date ranges. Or at least some logic that ensures that the second date is
> after the first.
> Mondrian now supports parameters that are lists of members (in latest
> perforce, coming in 4.0). We will need a picker for those. Probably the
> member picker but with multiselect.
> To summarize, there are 4 pickers that would be useful:
> 1. member picker for general hierarchy
> 2. date picker
> 3. date range picker
> 4. member list picker
> Ideally, PRD & CDF should have all of these. How many do we have, and can I
> help build the rest of them?
> Julian
>> -----Original Message-----
>> From: mondrian-bounces at pentaho.org 
>> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Phil Cole
>> Sent: Wednesday, November 04, 2009 3:42 AM
>> To: mondrian at pentaho.org
>> Subject: Re: [Mondrian] Using parameters in Pentaho Report 
>> Designer 3.5 -- issue with formulas
>> Pedro was a bit of a legend and worked out a way for the date 
>> picker to feed the current date into an MDX query.
>> First you have to change the java date returned by the date 
>> picker to a java string using open formula in the report. 
>> E.g. MDXDateStr equals [Date].[All 
>> Years].[2009].[Q1].[MAR].[23] and then pass this in as a 
>> parameter to an MDX query in a subreport. 
>> Pretty painful!
>> Phil
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

More information about the Mondrian mailing list