Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Date range input component

  1. #1
    Join Date
    Mar 2011
    Posts
    140

    Lightbulb Date range input component

    Hello everyone,

    Does anyone know how to get the "Date range input component" working with a MDX query ?

    I would like to know how to feed the parameter to my MDX query ? Do I need to create another Hierarchy for this ? I already have a hierary as -> YEAR-QUARTER-MONTH-WEEK-DAY

    Simply adding the parameter ${paramRange} in the MDX query won't work I think.


    Any ideas ?

    Thanks in advance !

  2. #2
    Join Date
    Dec 2010
    Posts
    304

    Default

    Hi ObjectiveC,
    Date Range Input Component needs two parameters, i.e. "fromDate" and "toDate". Their values are in this format: yyyy-mm-dd.
    I'd use two other parameters, i.e. "fromDateMDX" and "toDateMDX", that you can update in the postChange function of the component using Dashboards.fireChange function.
    The values to assign to them will be in MDX form, i.e. [YourDateDimension].[yyyy].[mm].[dd]; yyyy, mm and dd are the date parts extract from "fromDate" or "toDate".
    Now in the MDX query you can define a range writing ${fromDateMDX} : ${toDateMDX}
    Sorry for my English, I hope I've been clear... and I hope it will work

  3. #3
    Join Date
    Jun 2012
    Posts
    2

    Default

    Hi,

    I'm also having difficulties on how to integrate the parameters from date input range component into my MDX query. I don't have much knowledge about MDX, so, if you could be more explanatory on how to do this it would be great

    Just complementing the post, I already have a MDX query that is used to build a line chart (time series) have other (Simple) parameters as input:

    select NON EMPTY({Descendants([Data].[Todas as datas] ,[Data].[Data])}) on ROWS,
    NON EMPTY({[Measures].[Quantidade]}) on Columns
    from [CubeName]
    where (${estadoParameter} , ${regionaisParameter} , ${cidadesParameter} , ${escolasParameter})

    My doubt is: how can I add the date input range parameters (${dataInicialParameter}:${dataFinalParameter}) to this query?


    Thanks in advance!

    Andre

  4. #4
    Join Date
    Mar 2011
    Posts
    140

    Default

    Hello lukolap,

    Thanks for your reply.
    I think I understand what you are trying to say.

    I still don't quite know how to filter out the year, month and day from the parameter, but I'll try to figure it out.

    @anascimento
    My MDX knowledge isn't that good aswell. I still need to find a good book about it.
    Perhaps you could try something like a Crossjoin in your where clause ?

  5. #5
    Join Date
    Dec 2010
    Posts
    304

    Default

    Quote Originally Posted by ObjectiveC View Post
    I still don't quite know how to filter out the year, month and day from the parameter, but I'll try to figure it out.
    Consider, for example, the parameter I called fromDate in my previous post.It's a string in this form: yyyy-mm-dd
    Use this javascript to filter out the date parts:

    var aDate = fromDate.split("-");

    The result is:
    aDate[0] = yyyy
    aDate[1] = mm
    aDate[2] = dd

  6. #6
    Join Date
    Dec 2010
    Posts
    304

    Default

    Quote Originally Posted by anascimento View Post
    select NON EMPTY({Descendants([Data].[Todas as datas] ,[Data].[Data])}) on ROWS,
    NON EMPTY({[Measures].[Quantidade]}) on Columns
    from [CubeName]
    where (${estadoParameter} , ${regionaisParameter} , ${cidadesParameter} , ${escolasParameter})

    My doubt is: how can I add the date input range parameters (${dataInicialParameter}:${dataFinalParameter}) to this query?
    Hi,
    if your date dimension appears on ROWS, you can't use the same dimension in the where condition.

    select NON EMPTY({${dataInicialParameter}:${dataFinalParameter}}) on ROWS,
    NON EMPTY({[Measures].[Quantidade]}) on Columns
    from [CubeName]
    where (${estadoParameter} , ${regionaisParameter} , ${cidadesParameter} , ${escolasParameter})

  7. #7
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi .. I also having the same problem.

    I'm having the following MDX query... I need to pass date range parameter for this MDX query. I can pass it to SQL query but I'm not with MDX.
    Can any body please suggest/post a working example with MDX+daterange parameter?

    In the query all are individual dimensions not hierarchies under dimension.

    Thanks.


    select NON EMPTY
    Crossjoin(
    {[DateYear].[All].[2012], [DateYear].[All].[2013]} ,
    Crossjoin(
    {[DateQQ].[All].[Q1], [DateQQ].[All].[Q2], [DateQQ].[All].[Q3], [DateQQ].[All].[Q4]} ,
    Crossjoin(
    {
    [DateMonth].[All].[JAN], [DateMonth].[All].[FEB], [DateMonth].[All].[MAR], [DateMonth].[All].[APR] ,
    [DateMonth].[All].[MAY], [DateMonth].[All].[JUN], [DateMonth].[All].[JUL], [DateMonth].[All].[AUG] ,
    [DateMonth].[All].[SEP], [DateMonth].[All].[OCT], [DateMonth].[All].[NOV], [DateMonth].[All].[DEC]
    },
    {
    [WeekofMonthOnDate].[All].[1], [WeekofMonthOnDate].[All].[2], [WeekofMonthOnDate].[All].[3], [WeekofMonthOnDate].[All].[4],
    [WeekofMonthOnDate].[All].[5]
    }
    )
    )
    )
    ON ROWS,
    NON EMPTY Hierarchize
    (
    Union(
    {
    [SADA320].[All-Mem]
    },
    [SADA320].[All-Mem].Children
    )
    ) ON COLUMNS
    from [Connections]
    where [Measures].[SADACount]

  8. #8
    Join Date
    Aug 2014
    Posts
    13

    Default

    Hello guys,
    I don't know if this is right thread, so if it's wrong, please feel free to move my question in right one.
    I have two Data Input Components named ComponenteDataInizio and ComponenteDataFine. I created two DtaeParameters named dataInizio and dataFine and I set them as Parameters of the two Data Input Components.
    Now, I have to use the two values of Data Input Components as parameters in a MDX query to filter result in according to their values (i.e. in WHERE condition I would use current values of ComponenteDataInizio and ComponenteDataFine to filter result).
    I added two parameters named dataInizio and dataFine on the MDX query, but I don't know hot to use their values inside the query.
    Can anyone helps me?
    I post my query:

    SELECT
    NON EMPTY {Hierarchize({[Elem.].[Elem.].Members})} ON COLUMNS,
    NON EMPTY
    CrossJoin([N. Acc].[N. Acc].Members, CrossJoin([Data prelievo].[Data prelievo].Members,
    CrossJoin([N. colata].[N. colata].Members, [Forno].[Forno].Members))) ON ROWS
    FROM [SALab1]
    WHERE <here i have to insert filter by date>,
    {[Elementi preferito.Codice preferito].[${preferito}]}
    )

    Thanks in advance
    newbie_
    Last edited by newbie_; 09-12-2014 at 08:51 AM.

  9. #9
    Join Date
    Aug 2014
    Posts
    13

    Default

    I follow lukolap suggest and I wrote this function in PostChange property of components:

    function() {
    var dateArray = $('#render_ComponenteDataInizio').val().split('-'); //get date value from component
    Dashboards.fireChange('dataInizioMDX', '[Calendario].[' + dateArray[0] + '].[' + dateArray[1] + '].[' + dateArray[2] + ']'); //set my MDX parameter with value: [Calendario].[yyyy].[mm].[dd]
    }

    Then I modify my query in th following manner:

    SELECT
    NON EMPTY {Hierarchize({[Elem.].[Elem.].Members})} ON COLUMNS,
    NON EMPTY
    CrossJoin([N. Acc].[N. Acc].Members, CrossJoin([Data prelievo].[Data prelievo].Members,
    CrossJoin([N. colata].[N. colata].Members, [Forno].[Forno].Members))) ON ROWS
    FROM [SALab1]
    WHERE ${dataInizioMDX} : ${dataFineMDX}, //dataInizioMDX, dataFineMDX = my parameters that I've set with PostChange properties
    {[Elementi preferito.Codice preferito].[${preferito}]}
    )

    This query is set as Datasource of TableComponent, but when I execute it, it gives me an error: "Error processing component".
    Any ideas??
    Last edited by newbie_; 09-12-2014 at 08:52 AM.

  10. #10
    Join Date
    Aug 2014
    Posts
    13

    Default

    In addition to what I've already write, in the MDX query:

    SELECT
    NON EMPTY {Hierarchize({[Elem.].[Elem.].Members})} ON COLUMNS,
    NON EMPTY
    CrossJoin([N. Acc].[N. Acc].Members, CrossJoin([Data prelievo].[Data prelievo].Members,
    CrossJoin([N. colata].[N. colata].Members, [Forno].[Forno].Members))) ON ROWS
    FROM [SALab1]
    WHERE ${dataInizioMDX} : ${dataFineMDX},
    {[Elementi preferito.Codice preferito].[${preferito}]}
    )

    the range ${dataInizioMDX} : ${dataFineMDX} is the values of "Data Prelievo" I have to retrive in the SELECT statement (i.e. using SQL it will be something like this: SELECT DataPrelievo FROM <table> WHERE DataPrelievo >= dataInizioMDX AND DataPrelievo <= dataFineMDX).

    I've tried to do this query too:

    SELECT
    NON EMPTY {Hierarchize({[Elem.].[Elem.].Members})} ON COLUMNS,
    NON EMPTY
    CrossJoin([N. Acc].[N. Acc].Members, CrossJoin({${dataInizioMDX} : ${dataFineMDX}},
    CrossJoin([N. colata].[N. colata].Members, [Forno].[Forno].Members))) ON ROWS
    FROM [SALab1]
    WHERE ({[Elementi preferito.Codice preferito].[${preferito}]})

    as lukolap suggest but, in this case, the query retrive no data (don't give me an error, but the result set of query is always empty).
    I still try to understand how to write this query, if anyone can give some help, it will be very appreciated.

    I hope I've been clear,
    newbie_
    Last edited by newbie_; 09-12-2014 at 08:52 AM.

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.