CR11 - Reading data from multiple Tabs within a single Excel file

[0sprey]

CR11
I have an Excel file on the WAN that stores sales data from East, West, North, and South operations and it is updated several times a week by the sales staff.

The data is held within 4 tabs within the Excel file.(East West North and South).
Is there a way to report on this data as if it was in one only tab ?

Right now I think I have to :
A make a copy of the Excel file
B create a 5th tab call National
C copy the data from each of the 4 other tabs to the new National tab
D Link CR11 to the National tab and design and run a National Report

Any more efficient way to do this task ? Thanks... 0Sprey

 

[SkipVought]

HI,

Well you could do a union query using each of the four sheets, adding a field for the region...
[pre]
SELECT *, [East] AS [Region]
FROM [East$]
UNION
SELECT *, [West]
FROM [West$]
UNION
etc......
[/pre]

Or you could add a QueryTable in the National sheet and run this query and then query the National sheet from CR.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[0sprey]

As I cannot modify the Excel file, the SQL Union Query would be my best option.
Is this the code (noted below) that I would place in the Record Selection Formula Editor as a first step in the process ?
(I have little knowledge of SQL).

Once in place I would refer to "NATIONAL Spreadsheet" to design the report ? i.e. {NATIONAL.salesperson}


SELECT *, [East] AS [NATIONAL]
FROM [East$]
UNION
SELECT *, [West]
FROM [West$]
UNION
SELECT *, [North]
FROM [North$]
UNION
SELECT *, [South]
FROM [South$]

 

[SkipVought]

If you cannot modify the Excel workbook, then you cannot have a National sheet! The only sheets you apparently have are East, West, North & South.

Your SQL has named what I referred to as "Region" as "National" which is not a sheet.






Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kenhamady]

The SQL doesn't go in the Selection Formula. When you create a new report and select the connection there is an option to "Add Command" above the nodes/tables in the connection. You move that over as if it were a table and on the way over it opens a window to allow you to enter the SQL. I think this is the SQL you want:

SELECT * FROM [East$]
UNION ALL
SELECT * FROM [West$]
UNION ALL
...

From there you will have a table called "command" that you use like any other table. You can rename later, if needed.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/