Search code examples
parameter-passingmdxpentahomdx-query

How to filter a table component by dates in range of parameters in Pentaho CDE?

I'm creating a dashboard, which includes some selectors, table component and I should add date input or date range input (would be even better) to filter the data in the table. However I can't figure out how to pass the parameters in the mdx query - where and how should I compare the date from the table with the two date parameters, which comes from the date picker? Any help will be appreciated :)

I'm using Pentaho 8.2.

Here is what I have:

WITH SET[~ROWS_Objects_Objects.object] AS { [Objects.object]. 
 [object_name].Members } SET[~ROWS_Date_last_Date_last.Date_last_iso] AS {  
 [Date_last.Date_last_iso].[day_date].Members } SET[~ROWS_Date_Date.Date_iso] AS {  
 [Date.Date_iso].[day_date].Members } SELECT NON EMPTY { [Measures]. 
 [sum_qtyprice], [Measures].[sum_qty] } ON COLUMNS, NON EMPTY  
 NonEmptyCrossJoin([~ROWS_Objects_Objects.object], NonEmptyCrossJoin(${  
 typeParameter }, NonEmptyCrossJoin([~ROWS_Date_Date.Date_iso],  
 NonEmptyCrossJoin([~ROWS_Date_last_Date_last.Date_last_iso], ${ companyParameter })))) 
ON ROWS
Solution

  • There are a couple of ways that you can filter based on a range. Either you provide the range in the where clause. In this case you will not see the data breakup by date.

    Select [Measures].[Internet Sales Amount] on columns,
[Product].[Category].[Category] on rows 
from [Adventure Works]
where {[Date].[Date].&[20130101]:[Date].[Date].&[20130110]}

    enter image description here

    Or you can filter in your Rows or columns axis. This will show breakup by date 

    Select [Measures].[Internet Sales Amount] on columns,
({[Date].[Date].&[20130101]:[Date].[Date].&[20130110]},[Product].[Category].[Category] )
on rows 
from [Adventure Works]

    enter image description here