I have to produce a report in Pentaho Analyzer (server) with the following components: - dimension: store - dimension: date - measure: sales
My aim is to present all stores and all dates, and when there is no sales in that date we put number 0 (by default)?
In Pentaho Schema Workbench I can do this with 1-dimension (calculated member: COALESCEEMPTY() function). COALESCEEMPTY((Measures.[Sales], [Date].[Year].CURRENTMEMBER, [Store].[Store].CURRENTMEMBER), 0)
But I am not able to do it with 2 dimensions, because when I call dimensions in Pentaho Server (version 5) they relate between them according to existing sales (facts), and only appears the stores that has sales in some date (even if I don't call the measure).
Simply speaking, I want to make a LEFT JOIN from 2-dimensions to fact table, showing empty cells with a default value.
Thank you.