display all month even no data for few

building reports webintelligence
1

display all month even no data for few. I have only 3 months of data, but need to show all twelve months in my report. How can I show the missing data?

  • I tried implementing by adding calendar table and left outer join to sales table on Calander Date and sales Date and use Calander month instead of sales month period in the report.but when I try to join other dimension table then my outer seem not working.

  • Other way I tried bring calender month and calendar date in differen query. Merge calender month.
    When I try to bring other dimension it says Multivalue.

  • Is there a way I can replicate at report level

SELECT left(datename(month,getdate()), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-1,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-2,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-3,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-4,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-5,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-6,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-7,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-8,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-9,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-10,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-11,getdate())), 3)
UNION ALL
SELECT left(datename(month, DATEADD(MM,-12,getdate())), 3)

RUC :us: (BOB member since 2010-05-03)

2

Hi,

After bringing in the whole list of Calendar month and date in the seperate query, merge both calendar month and date. Use this merged value in the table and enable the option Show rows with empty dimensions in the table properties. This might work.

Thanks

KK123 :new_zealand: (BOB member since 2012-03-25)

3

I am still struggling with #Multivalue error. When I try to add a dimension then it throws this error.

This is what I tried.

Query 1 :

Result Objects :

Year Month, State, Revenue

Filter : Sales date between

Query 2 :

Year Month, Date value, Revenue

Filter : Date value between

-----Merged Year Month from 2 queries, When I tried to add State it throws multivalue.

  1. I tried creating State as details based on Year Month
  2. Tried State variable with foreach or for all

but no luck
Months-2.JPG
Months-1.JPG

RUC :us: (BOB member since 2010-05-03)

4

Hi

Please find attached the steps to resolve the Multivalue error. I am not quite getting the point why you are trying to get a dataset like that. You need to be careful at handling the measure values in these cases.

Hope this helps.

Thanks
Multivalue error.doc (177.0 KB)

KK123 :new_zealand: (BOB member since 2012-03-25)

5

If you’re on XI3.1 SP4 or above, the TimeDim function might help. Otherwise, try specifying a context for your multivalue dimension, or adding an aggregation to it (max, min).

HTH

NMG

mcnelson :uk: (BOB member since 2008-10-09)

6

Thanks a lot guys,

TimeDim is working fine for Dates, I need for Months. Is there a way I can get with Months.

RUC :us: (BOB member since 2010-05-03)

7

Did you read the document linked to?
It clearly says there is a 2nd parameter for this?

Mak 1 :uk: (BOB member since 2005-01-06)