We help IT Professionals succeed at work.

Crystal XI, creating a Monthly "bucket" column report

JMO9966
JMO9966 asked
on
696 Views
Last Modified: 2011-09-20
Hello,
 
I will be using Crystal XI to create a report for Monthly Sales - Budget vs Actual.

For the actual figures, I will be retrieving Invoices from a SQL database based on Invoice_Date.  I will need to group the Invoices by Month and then total ACTUAL Sales amount per Month.

The filter window for this report will need to have a Date Range selector but I'm not sure the best way to do this. I'm thinking I'll do a From and To DateTimePicker (or whatever it's called in Crystal). The user may select one month or two or six consecutive, whatever they want.

The trickier part may be that I have to retrieve the BUDGET Sales from a seperate database and I need to create this table.  Here's the layout I came up with but I'm concerned about how to link to this table. Customer ID would be easy enough but how to link a Month and Year from my Crystal filter selection following through to this tables to retrieve correct budget values.

Customer ID - (String-10) PK
Year - (Int-4) PK
Type - (String-1)
Jan - (Money-8)
Feb - (Money-8)
Mar - (Money-8)
...
Dec - (Money-8)

Any tips or comments would be greatly appreciated.

Thank You,
JMO9966
Comment
Watch Question

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
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

Author

Commented:
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

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
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
Sr. Business Intelligence Consultant/Architect
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.