Crosstab and empty month columns

building reports desktopintelligence
1

I want to create a crosstab with months across and company names down.
I have created the following query:

SELECT
SD.DW_CUSTOMER_DIM.CUSTOMER_NAME,
round(SD.DW_SALES_FACT.MONTH_YR/100,0),
SD.DW_SALES_FACT.MONTH_YR,
NVL(SD.DW_SALES_FACT.DOLLAR_AMOUNT, 0.0) FROM
SD.DW_CUSTOMER_DIM,
SD.DW_SALES_FACT
WHERE
(
SD.DW_CUSTOMER_DIM.ACCOUNT_NUMBER=SD.DW_SALES_FACT.ACCOUN T_NUMBER and

SD.DW_CUSTOMER_DIM.SUB_ACCOUNT_NUMBER=SD.DW_SALES_FACT.SU B_ACCOUNT_NUMBER )
AND (
SD.DW_CUSTOMER_DIM.CUSTOMER_NAME LIKE ‘A%’ )

This returns rows where DOLLAR_AMOUNTs for months in which there is an entry. E.G., in
this particular small query, it returns 199801, 199802, 199905, 19906, 199907. Columns
do not exist for other month/year combinations. I would like to show all months, even if
there is no corresponding dollar_amount. In that case, I would like the column to contain
a zero.

I created an Excel spreadsheet containing all possible values for month_yr and linked the 2
data providers on month_yr. Then I unlinked any other links with a third data provider, so
the above link was the only one. I created a crosstab and used customer_name, month_yr and
dollar_amount in the table. I only saw columns for dates that has a corresponding dollar
amount.

Any suggestions?

–
Michael Malone
Senior Consultant
WCI Consulting

Listserv Archives (BOB member since 2002-06-25)

2

You can either:

  1. use an outer join to a table with all the years and months that you want to appear on the report
    or
  2. create another data provider that returns all the years and months that your want to display on the report and link the month_year object in your original data provider with the one in the new one.

In both cases you’ll have to have something like a table that contains all the possible year-month combinations that you want to display.

Alfred

Listserv Archives (BOB member since 2002-06-25)

3

Michael,

Could you load your excel spreadsheet into another database table and add that table to your main query, doing an outer join to insure that all months are included even if there is no dollars?

Marian Cooney
McKessonHBOC
Malvern, PA

Michael wrote:

I created an Excel spreadsheet containing all possible values for month_yr and linked the 2
data providers on month_yr. Then I unlinked any other links with a third data provider, so
the above link was the only one. I created a crosstab and used customer_name, month_yr and
dollar_amount in the table. I only saw columns for dates that has a corresponding dollar
amount.

Any suggestions?

–
Michael Malone
Senior Consultant
WCI Consulting

Listserv Archives (BOB member since 2002-06-25)