?
Solved

Beginner - How can I create a YTD sales comparison of last year to date grouped by Sales person, Customer #, Month, then account?

Posted on 2005-03-28
13
Medium Priority
?
579 Views
Last Modified: 2010-05-18
I'm a beginner to Crystal but a good programmer in vb and access.  This problem is multilayered.  The first issue is this year we just went with a new ERP system based on SQL.  Last years data I've taken out of the old system (unaccessable through ODBC) and put it into access.  I can get the old data into a form that matches - close enough - our new data scheme so I can report on similar fields.  At this point I need a little help with Crystal reports to get the data into the format for our sales team.  I'm almost convinced I'll need to create some code to combine the tables (this years info and last years info) within access (so as to not influence our new database system) so crystal can pull the report correctly.  However I'm unfimiliar with Crystal and I do not even know how to pull last years data side by side with this years even if they were in the same table.  

I need the report in a format of a crosstab with the Customer name and sales $ in the row  with the multileveled columns of : sales rep, month, and account (or type of sale).

Where do I begin?
0
Comment
Question by:JMorsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 13644153
What version of Crystal?

You may have to use temporary tables in your Access database since Crystal doesn't generally support multiple data sources (especially when they are different database types MS SQL and Access) within a single report.

mlmcc
0
 
LVL 3

Author Comment

by:JMorsch
ID: 13644216
Crystal 10.   I can pull a query from access in Crystal that draws the info out of SQL, so it would be coming from the same data type - access.

Is there a good way to create a table that combines two sources when the report is run?
0
 
LVL 3

Author Comment

by:JMorsch
ID: 13644228
or would I have to create a form with an event that runs the mirge and then runs the report.
0
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 
LVL 23

Accepted Solution

by:
Ido Millet earned 2000 total points
ID: 13644612
1. In Access, create a UNION query that combines the historical data from the local table and the current data from the linked ERP table.

SELECT....  FROM .... WHERE ...
UNION ALL
SELECT....  FROM .... WHERE ...

2. It sounds like all you need is a CrossTab approach in the report to display the desired info.

Cheers,
- Ido
0
 
LVL 3

Author Comment

by:JMorsch
ID: 13645868
Alright, I've created the union and a crosstab report.  however, everytime I attempt to preview the report Crystal reports has a critical error and shuts down...  I thought maybe it couldn't pull information from the union query, so I created a temporary table as you suggested populated by the union query, and drove a crosstab report from crystal off of this, but I still get the same error.  
The error is just a windows message saying that "crw32.exe has encountered a problem and needs to close.  We are sorry for the inconvenience."
0
 
LVL 3

Author Comment

by:JMorsch
ID: 13645884
Second Question, in a crosstab, how do you show this months info next to this month's info last year?
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 13645906
Try to use Database, Verify Database in Crystal to fix the situation if the query columns changed after the report was created.

Do you get the same error with a new report creeated against the same data source?

hth,
- Ido
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 13645933
One option is to restrict the records (record selection formula) to these 2 months and set the CrossTabs to show columns for each month.

Another option is to use Conditional Totals.  

I suggest you start a new thread, since this is a different question.

Cheers,
- Ido
0
 
LVL 3

Author Comment

by:JMorsch
ID: 13645963
I get the same error with a newly created report against the same data source....

I will post a new thread for the ytd - lytd question thanks..

should I post a new thread for this error?
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 13646010
New report, using a table in Access, failing?  

Is it a simple report where you simply place a few fields on the report layout?
0
 
LVL 3

Author Comment

by:JMorsch
ID: 13646067
no it's a crosstab report.  with multiple columns.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 13646091
Then my guess is that something about the report is causing the failure.

Start stripping away formulas and simplify the report until it stops failing.
This would give you a sense of where the problem is.  e.g., divide by zero, ...
0
 
LVL 3

Author Comment

by:JMorsch
ID: 13646303
The summary option that gives a percent of sales was creating the error.  though the calculation is correct, as I have used it in the past...  

Therefore I'm going to close this thread, and pursue my other issue with the dates and grouping month next to last years month.  since I am only struggling with that at this point.  Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question