Sales Comparision Report Design

[woody55]

Hi,
I'm working with CR v10 and am relatively new to reporting software and databases. I am trying to build a report that will allow me to compare sales from different periods. For example, I would like to know my stores did from week to week or month to month, etc. My database is SQL 2000 and records each transaction with a date time stamp (mm/dd/yyyy x:xx:xx AM/PM). With the help of books and online references I think I can figure out much of the detail, but I'm struggling with the general approach. Do I need to build some sort of summary table within a SQL table to ultimately accomplish this, or can CR with well built formulas accomplish the same thing? I'd like for the report to ask for parameter values to help define the time periods for comparision. Any general guidance would be appreciated. Thanks.

 

[synapsevampire]

Your question is very broad in scope, so I'll simply explain the concepts. Also you didn't state what this comparison might do, is it simply going to display them alongside each other, or take some action.

In general you're best served to supply example data and expected output rather than text which is intended to describe same.

Also how large the data set is and will become will help to define the architecture used.

The best approach is likely to create a data mart within SQL Server. This isn't as daunting of a task as you might think as SQL Server has tools to aid in this, whether you opt to use DTS to load a star schema or some simple aggregate tables, or use MS or other tools to construct a data mart.

If you simply want to compare sales by week or month, insert a cross-tab report and in the rows place the date and change the group option to weekly or monthly, and place the region or whatever in the columns and place the amount in the details.

-k

 

[Ngolem]

As SV has stated...your question is too broad to give a serious answer...we have no idea of the form of the report that you want.

I used to be a sales manager so I can imagine several potential reports on product sales

Here is a simple example

Product: XYZ Widget Sales Year: 2003

Month Store1 Store2 Store3 Store4
----- ------ ------ ------ ------
Jan 100.00 200.00 150.00 125.00
Feb 70.00 123.75 500.00 300.00
...
(rest of the months to Nov)
...
Dec 90.00 180.00 100.00 100.00
------ ------- ------- -------
Total 1200.00 1300.00 2450.00 1567.00



this is how you should present your problem...we can work with this and give you suggestions. Also it would be useful to indicate the various tables involved and how they are linked.

Failing to do this is a waste of time on our part....since we can only guess what you want.


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[woody55]

Hi,
Thanks for the candid feedback. Okay, here is some more detail.

Currently, I have about 120,000 transactions growing at approximately 150 to 250 per day. The relevant tables are a sales table, and 3 other tables which form a 3 tier parent/child heirarchy. I have reports that simply display data based on certain date ranges and time periods, like total weekly sales for all stores since 1/1/04. I'm currently using various cross-tab and standard reports for this. What I am looking for is a way to organize the data in a user defined manner and then observe trends or patterns in the historical data. For example, right now I know what each store has sold for every week since 1/04, and I could print out that report, but with 500 stores and 26 weeks of data the report becomes unmanageable. What I would like to do is see a report that provides more analysis:
Q1. What time interval would I like to analyze? Answer/Input: Weekly Sales
Q2. What time period of sales would I like to analyze? A: The last month.
Q3. What kind of alert criteria should be highlighted? A: Show sales that have dropped or increased by 25% in any given week from the preceding week.

Again, all of these types of inputs would have a good deal of flexibility in terms of inputs, so that the same report could be used if I wanted to see daily sales for the last week that dropped by 50% or Friday sales for the past 2 months that showed 75% improvement.

My output then looks something like this:

1/1/04 1/8/04 1/15/04 1/22/04
Store 101....$100....$110....$75....$25
Store 309....$100....$200....$0....$400

My report of 500 stores is now reduced to the handful of stores that have the type of activity that concerns me so that I can react appropriately.

K- When you mention "data mart" are you referring to using Cubes, MS Analysis Services, and Crystal Analysis. If so, I have already set that up. However, I either do not have the right data tables, proper formulas, or more likely, the right knowledge to pull this type of data out of either CR or CA. Please let me know whether or not one application is the hands down choice for appropriate application for this type of project.

Thanks for taking time to help.
-T