Compiling data from different tables in a cross tab

[shaleen7]

I need to compile data in a cross tab table.
For example:
In the month of Sept., the following employees had these stats.

# of apps #of orders # of requests
Stevens 3 4 5

Hill 6 5 8

Lin 6 0 9


Shone 2 7 1

Each column of data comes from different tables in the database..
Is it possible to accomplish this using the cross tab expert or do I need to create a manual crosstab table. And if a manual cross tab needed, where do I find samples?


# of apps count(app) from appstable where appsdate is between 9/1–9/30
# of requests count(requests)from requests stable where requests date is between 9/1–9/30
#of orders count(orders) from orders table where ordersdate is between 9/1–9/30

 

[kenhamady]

There is a description of manual cross-tab technique on the "Formulas" page of my web site. However this will not be easy with a regular or a manual cross-tab.

The problem you face is that you have 3 different tables that are all a 'one to many' relationship to your employee. They will not combine very will in Crystal because each will inflate the other two. And your date criteria is going to make so that someone with no sales in the month will not show up at all in the other 2 columns.

The sourece for this report would ideally ba a Union Query of the three tables, but this requires you to do some work in the database, or if you have v9, that you write your query as a command.

You could use subreports in CR, but it would be pretty slow and more complex.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[shaleen7]

I have Crystal Report 9. What do you mean by write the query as a command?

 

[kenhamady]

If you know how to write SQL statements, you can write the statement you need and past it into the 'command' window to use as the basis for a report. Go into the Database Expert and you will see "Add Command" above the tables in your database.

The trick is writing your SQL statement as a UNION query.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com