Crystal XI, creating a Monthly "bucket" column report

Since you have the ability to create a table in the database, I would highly recommend that you create and populate a Calendar table.   You could then join this table to any number and type of reports in order to better and more efficiently filter data by date ranges.

Examples of fields you might have in a calendar table are:

Date - 01/01/2007
Day_of_Week - Monday
Week - 1
Month - 1
Quarter - 1
Year - 2007
Fiscal_Week - 1
Fiscal_Week - 1
Fiscal_Quarter - 1
Fiscal-Year - 1
Holiday - Y
Weekend - N
Workday - N
etc....

You would join such a table to other tables on the Calendar Date field using a LEFT JOIN such as:

Calendar                            Sales
---------                             --------
Date  -----------------           Sales_ID
Day_of_Week          |         Salesperson_ID
Week                      |        Amount
Month                      |----> Sales_Date
Quarter
Year
Fiscal_Week
Fiscal_Week
Fiscal_Quarter
Fiscal-Year
Holiday
Weekend
Workday

A benefit to using this method is that you can still easily group on various periods (months, for example) even if data doesn't exist for those periods.   Using such a table allows you to create monthly reports, quarterly reports, fiscal reports, weekly reports, etc...  If you need hourly statistics, then you would create 24 separate records (one hourly record for each date) within which you could identify shifts.  The possibilities for valuable reporting are immense.

In your scenario, you could create parameter that allow the user to select a From and Through Month and/or Year ranges.  This allows the user to select any number of values, irrespective of the actual dates.  It's provides for much simpler input.

~Kurt

Thanks Kurt,

That's great information, but I'm confused by who\how this Calendar table would be maintained and what values would be held in the table for these column

I can see my app needing two years defined at any one time to cover end of year and historical purposes.

I was thinking of building my budget table with a date column for each month and then a value column for that month since I know the first day of each month would be 12/1/06, 1/1/06, 2/1/06, etc...but I'd like to pursue you idea if that's a lot better way to go.

Thanks again,
JMO9966

The calendar table would be maintained on your primary database server.  You could create a script to initially populate the database for as many years as you have existing data.  You could then schedule a job to run yearly to populate data for the upcoming year.

At minimum, you'd have 365 records for a single year:

Date             Day_Of_Week  Week   Month   Quarter   Year   Holiday  Workday   etc....
---------------------------------------------------------------------------------------------------
01/01/2007       Monday           1         1             1      2007      Y           N         etc....
01/02/2007       Tuesday          1         1             1      2007      N           Y         etc....
01/03/2007       Wednesday      1         1             1      2007      N          Y         etc....
etc...

Normally, I would make this table as robust as you need to.  I would generally not recommend building a table in the manner you've suggested unless you have a data warehouse.  The type of table you've described is an aggregate table, which is great for reporting, but only if your environment supports it.  If it doesn't, then you add overhead to populating and maintaining that table through stored procedures and or scheduled jobs.

~Kurt