0

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
Vega
  • 27,856
  • 27
  • 95
  • 103
Veronika Florova
  • 15
  • 4

1 Answers1

0

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

MoazRub
  • 2,881
  • 2
  • 10
  • 20
  • Thank you :) I was wondering if you may help me with one more thing. I need to do this filtering with parameters from CDE Date Range Input Component, but it looks like I can't find the right way to do it. Do you have some ideas about that? Thanks in advance. – Veronika Florova Jan 29 '19 at 21:56
  • Sorry to let you down, I dont know about "CDE Date Range Input Component". So I wont be able to help you there. However if you have any other problems with respect to MDX, feel free to ask. – MoazRub Jan 29 '19 at 22:02
  • Thanks :) So since I have the opportunity I'll ask one more question. Could you tell me how should my where clause looks with 4 parameters? My query works fine with two, but I get some strange error when I try to add more. – Veronika Florova Jan 29 '19 at 22:19
  • Sure, Lets say we need 1 Jan , 5 Jan , 10 Jan and 20 Jan. It will look like {[Date].[Date].&[20130101],[Date].[Date].&[20130105],[Date].[Date].&[20130110],[Date].[Date].&[20130120]}. The brakets {} indicate i am providing a set. Now lets say I want data for bikes for these dates. ([Product].[Category].[Bike],{the entire thing above}). () these indicate that This is my tuple. – MoazRub Jan 29 '19 at 22:24
  • Well, I've tried this {${fyearParameter}, ${fmonthParameter}, ${lyearParameter}, ${lmonthParameter}}, but it's not working, perhaps it's not the right way to pass them. These 4 parameters come from selectors, which should filter the whole table, so when I use two of them (no matter which) it's ok, but when I add the third the log says that it's wrong date format (however none of the parameters is in date format) . – Veronika Florova Jan 29 '19 at 22:38
  • Try your completed query on your OLAP server, check if that works. Since your MDX is getting genrated there could be an issue in the genrated query. – MoazRub Jan 29 '19 at 22:45
  • I did't understand what exactly should I do. May you explain it in details, please. – Veronika Florova Jan 30 '19 at 08:44
  • @VeronikaFlorova Either {,,,} format is not applicable in pentaho or there is some issue in the way you are passing parametrs. Try the following. 1)Except ([DIM1].MEMBERS, {[DIM1].[A], [DIM1].[B], [DIM1].[C]} ) 2) FILTER([Product].[Product Family].MEMBERS, [Product].[Product Family].CurrentMember NOT IN {[Product].[All Products].[1], [Product].[All Products].[2]}) – MoazRub Jan 30 '19 at 19:22