Pivot Tables and CR10?

[tbear6602]

I am running CR10 against an Oracle 9i database. I am trying to convert a report from excel that uses pivot tables into CR10 to produce a chart.

Basically, I have data as follows:

CloseDate TicketCount
1/2/2006 2
1/3/2006 3
1/5/2006 1

etc.....

The pivot in excel is used to summarize the data daliy and then chart the data for the month i.e.:

CloseDate TicketCount
1/1/2006 0
1/2/2006 2
1/3/2006 3
1/4/2006 0
1/5/2006 1
1/6/2006 0
1/7/2006 0
1/8/2006 0
1/9/2006 0
1/10/2006 0
1/11/2006 0
1/12/2006 0
1/13/2006 0
1/14/2006 0
1/15/2006 0
1/16/2006 0
1/17/2006 0
1/18/2006 0
1/19/2006 0
1/20/2006 0
1/21/2006 0
1/22/2006 0
1/23/2006 0
1/24/2006 0
1/25/2006 0
1/26/2006 0
1/27/2006 0
1/28/2006 0
1/29/2006 0
1/30/2006 0
1/31/2006 0

When I try to chart it straight from the database, the charts only reflect the actual data (no suprise there), but I need to show the dates and nulls that are currently being created in the pivot table in excel.

I hope this post makes sense. Basically it boils down to: can crystal create pivot tables or something similar to chart the nulls in the data?

 

[synapsevampire]

A pivot table doesn't automatically create dates either, that's just the functionality of Excel.

A standard means of handling this is to create a period table (standard fare in a Data Warehouse), and gthen left outer join the period table to your data. This resolves long term for many concerns, including yours.

I have some sample T-sql to create one which can be easily modified for Oracle in this whitepaper:

faq767-4532

-k