Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Dynamic Column Specification for MDX Query

  1. #1
    Join Date
    Jun 2012
    Posts
    11

    Default Dynamic Column Specification for MDX Query

    Dear All

    I am trying to create a report that would give me the last 5 weeks worth of production data from our factories. If I create the MDX query manually it looks like this.

    Code:
    SELECT
    NON EMPTY {[Dates.Calendar Financial - Weeks].[Fin Week No].[34]:[Dates.Calendar Financial - Weeks].[Fin Week No].[38]} ON COLUMNS,
    {
    [Measures].[Export],
    [Measures].[Detained],
    [Measures].[Discarded],
    [Measures].[Private]
    } ON ROWS
    FROM [Production Cube]
    WHERE
    (
    [Factories].[Factory Name].[Windhoek],
    [Dates.Calendar Financial - Years].[Fin Year].[2017]
    )
    And the results I get look like this.

    Name:  Saiku 5 week production.PNG
Views: 226
Size:  5.6 KB

    To run the query in Report Designer and following the sample report for SteelWheels, I came up with this query that works:

    Code:
    SELECT
    NON EMPTY {
    
    
    strToMember(Parameter("firstWeek",STRING,"[Dates.Calendar Financial - Weeks].[Fin Week No].[34]")):
    strToMember(Parameter("lastWeek",STRING,"[Dates.Calendar Financial - Weeks].[Fin Week No].[38]"))} ON COLUMNS,
    
    
    {
    [Measures].[Export],
    [Measures].[Detained],
    [Measures].[Discarded],
    [Measures].[Private]
    } ON ROWS
    FROM [Production Cube]
    WHERE
    (
    [Factories].[Factory Name].[Windhoek],
    [Dates.Calendar Financial - Years].[Fin Year].[2017]
    )
    In the report designer's data explorer it is then represented like this:

    Name:  Report Data Explorer.png
Views: 240
Size:  16.9 KB

    Pulling the fields on to the report page looks like this.

    Name:  Report Designer Page.jpg
Views: 237
Size:  11.8 KB

    When I run the report for weeks 34 to 38 all the data shows up in the report. However, if I select a different range of weeks, the data is not display. The screen grabs below shows data up to week 38 and then up to week 35.

    Name:  Report Designer full data.jpg
Views: 247
Size:  20.4 KB

    Name:  Report Designer half data.jpg
Views: 236
Size:  17.7 KB

    Looking at everything I presented here, if makes sense that data will not be displayed unless it falls within weeks 34 to 38. How can I make this dynamic. I have played around with the MDX to see if I can obtain column aliases (like one can in SQL), but to no avail. Not that I am an MDX expert. I am quite new actually.

    Can anyone give me some advice on how to make the dynamic so that the report can move on as the year goes on?

    Thanks
    Louis

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Yeah, MDX combines data in the column names and PRD expects column names to be static. That is a recipe for "fun".

    Simple solution: Edit->Preferences then enable "Display the index columns .." in the General settings panel. You now get second set of fields with generic names (::column:by:index ..) along with your original columns. Use those "::column:by:index" fields and your report will run happily.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Jun 2012
    Posts
    11

    Default

    Dear Taqua

    Thanks for your response. Even with your method I am not able to make it dynamic. From the report designer it seems that the engine maintains the link between the MDX results and the column number.

    Name:  Report Data Explorer Updated.jpg
Views: 227
Size:  37.5 KB

    What I have done now is to run the query for the full year. In MDX it is not a lot of data, fortunately. Then I updated the numeric fields with formulas to select which part of the data needs to be viewed.

    Name:  Report Designer field reference.jpg
Views: 223
Size:  7.8 KB

    The formulas for the five columns that are in view are as follows:

    Code:
    =CONCATENATE("[Dates].[Dates.Calendar Financial - Weeks].[All Dates.Calendar Financial - Weekss].[";max(1;[periodEnd]-4);"]")
    =CONCATENATE("[Dates].[Dates.Calendar Financial - Weeks].[All Dates.Calendar Financial - Weekss].[";max(1;[periodEnd]-3);"]")
    .
    .
    =CONCATENATE("[Dates].[Dates.Calendar Financial - Weeks].[All Dates.Calendar Financial - Weekss].[";max(1;[periodEnd]);"]")
    This is working well.

    I have now uncovered a new problem though. Report designer is not inheriting the data formatting from the MDX results. As such all of the data is formatted the same, but in may case, as you go down the column, I have different types of numbers. Some in the thousands, some in the millions and some as percentages. The report designer clearly assumes that data will always be displayed in vertical columns and data can be formatted per column. In my case it is displayed horizontally. The only way I can think of managing the formatting is to group results in bands and apply formatting according to the bands with a formula.

    Again, thanks for your reply. If you have an example in which your recommendation works, I would love the see the file. I really struggled with making the parameter that follows the 'periodEnd' parameter. In the end I made it a formula, but that may be part of my problem, as I used the formula as an input parameter for the query. I don't know if the parameter in the query gets updated when I change the selection of the 'periodEnd' number. Would you be able to comment?

    Thanks
    Louis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.