I have 2 fact tables with 2 different granularities (one at an order line level and another at a day level).
I have 1 conformed dimension (Calendar) and 1 non-conformed dimension (product). I have 2 measures: "Sales Amount" (from order line fact) and "No. Working Days" (from day fact). Reason I have a fact table to reflect working days is because I have cases in which I don't have order lines in a working day, and I need to use "No. Working Days" in multiple calculations in Framework Manager (i.e. Average Orders by Working Day, etc.)
When creating a report in Query Studio or Report Studio with: Year/Month, Product, Sales Amount and No. Working Days:
- If I create the report in a list, the "No. Working Days" is repeated by Product - Year/Month Combination. Example: Working days in April 2012 are 19, for each product I see 19 days in the list. The final aggregation still shows 19 days, so this is a behaviour I am expecting.
- If the report is created in a crosstab, the "No. Working Days" does not appear repeated and only appears for the first occurrence of Product - Year/Month combination. Example: Working days in April 2012 are 19. First Product is "Product A", which appears with 19 days. "Product B" is the next one and "No. Working days" appears empty.
Any ideas why this is happening? Any way of getting the "No. Working Days" from the Calendar dimension that allows me to have same behaviour in both list and crosstab?