BusinessObjects Board

Crosstab - show all months in master detail report

building reports desktopintelligence
#1

Using version 5.1.8

The old problem of showing all months in a crosstab even when there is no measure returned…

Creating a second data provider which just returns every month and using this in the crosstab normally solves the problem. However, it does not work in Master / Detail reports.

I’ve looked at KB articles 345 and 13095, but these do provide a solution. Using Breaks instead of Sections is not an option.

Any suggestions?

anorak :uk: (BOB member since 2002-09-13)

Fixed cross tab columns in sectioned report...
Missing months
#2

you have not clean solution.

the way we recently solve the problem is to modify the sql of the query (do not forget to click “do not regenerate the sql of the query” after it!) making an union from the first query with a second one replacing in it measure by 0 in order to make an artificial cartesian product.

select
mesure1
mesure2
ind1
ind2
ind3
from
XX
yy
where
etc

union

select
0
0
ind1
ind2
ind3
from
XX
yy
where
etc

bernard timbal :fr: (BOB member since 2003-05-26)

#3

Thanks for this, it pointed me in the right direction. With help from an SQL expert, we came up with the following:

(
SELECT
  DM_CONFORM.ACCOUNTS_HIERARCHY.COMPANY_URN,
  TO_DATE(DM_FINANCE.MV_CACM_REVENUE_DETAILS.CALENDAR_MONTH,'MM/YYYY'),
  SUM(DM_FINANCE.MV_CACM_REVENUE_DETAILS.ACTUAL_REVENUE)
FROM
  DM_CONFORM.ACCOUNTS_HIERARCHY,
  DM_FINANCE.MV_CACM_REVENUE_DETAILS,
  DM_CONFORM.TARGET_ACCOUNT_LISTS
WHERE
  ( DM_CONFORM.TARGET_ACCOUNT_LISTS.ACCHIE_ACCOUNTS_HIERARCHY_ID=DM_FINANCE.MV_CACM_REVENUE_DETAILS.ACCOUNTS_HIERARCHY_ID  )
  AND  ( DM_CONFORM.ACCOUNTS_HIERARCHY.ACCOUNTS_HIERARCHY_ID=DM_CONFORM.TARGET_ACCOUNT_LISTS.ACCHIE_ACCOUNTS_HIERARCHY_ID  )
  AND  (
  DM_CONFORM.ACCOUNTS_HIERARCHY.COMPANY_URN  =  290173
  )
GROUP BY
  DM_CONFORM.ACCOUNTS_HIERARCHY.COMPANY_URN, 
  TO_DATE(DM_FINANCE.MV_CACM_REVENUE_DETAILS.CALENDAR_MONTH,'MM/YYYY')
UNION  
SELECT
  DM_CONFORM.ACCOUNTS_HIERARCHY.COMPANY_URN,
  TO_DATE(CALENDAR_MONTH,'mm/yyyy'),
  0
FROM
  DM_CONFORM.ACCOUNTS_HIERARCHY,
  (SELECT DISTINCT calendar_month FROM DM_FINANCE.MV_CACM_REVENUE_DETAILS)
WHERE
   DM_CONFORM.ACCOUNTS_HIERARCHY.COMPANY_URN  =  290173
)

anorak :uk: (BOB member since 2002-09-13)

#4

cool ! :smiley:

bernard timbal :fr: (BOB member since 2003-05-26)