Group on calendar dates

[Varco]

In Crystal v9, can grouping be created based on calendar dates, with the date range being entered via parameter? There may or may not be data associated with each date in the range, the group still needs to be displayed. Thanks.

Jim Varco
jimv@varcconsulting.com

 

[synapsevampire]

If you don't have data, you won't have anything to display.

There are various workarounds, but I suggest that you permanently sidestep these types of issues by building a Periods table in the database which contains a date for every year, going back as long as you need it, and into the future as far as you might ever.

Now you can select against the Periods table, and Left Outer your data to this table, supplying the dates that you require, and data where available. This also alleviates the need to compute weekends/Holidays/Business days, and I include things in my period tables like Month, Quarter, Fiscal month, Fiscal Quarter, etc. This is standard stuff in a Data Warehouse.

If this means doesn't suit you, let me know, and I'll show you the protracted, code intensive Crystal solution...

-k

 

[Varco]

Thanks for the suggestions. Unfortunately in this case I will need the code you referenced, if you could provide it I would appreciate it. I am presently doing this without groups, using a formula that prints each date and the data if available. For upcoming expansion of the report I need to change to using actual groups. Thanks again.

Jim Varco
jimv@varcconsulting.com

 

[synapsevampire]

Here's the Crystal whitepaper:

http://support.crystaldecisions.com/library/kbase/articles/c2008492.asp

-k

 

[lyanch]

I create my date ranges on the fly when I need them in a stored proc, which is halfway inbetween creating a table and crystals long drawn out solution. If you can create SP's that would be one day to go. I don't have to worry about holidays etc (so far) since everything here is 24hr and everything is recorded GMT.

If you can write stored procs let me know and I will paste some SQL that can point you in the right direction.

Lisa