Multiple Excel Worksheets as one Datasource

[marcusw]

I'm connecting to an Excel Spreadsheet with 8 individual worksheets. Each worksheet identifies a geographical region. I'm doing this through ODBC and it connects without error. However, each worksheet appears as a seperate table (each with the same field structure).

What I want to be able to do is concatenate each worksheet together so that I just have one datasource/table. Then I can group on geographical location code. I'm having problems doing this?

Can anyone help please?

Regards,

Marcus Wade.

 

[Ngolem]

short of combining your spreadsheets into one and redefining the section of the resulting worksheet to be used as a database, I don't think you can do it....in my limited experience Jim Broadbent

 

[IdoMillet]

Marcus,

Go to Crystal SQL Designer and create a UNION query as the source for your Crystal report.

Assuming each worksheet has a named range
(Region1, Region2, ...)
The query should be something like:
----------------------
SELECT * FROM Region1
UNION ALL
SELECT * FROM Region2
UNION ALL
SELECT * FROM Region3
etc...
----------------------

I threw together a simple example to
test this and it works fine.

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[marcusw]

Thanks Ido,

The Union worked fine! However, once the .qry file is created try doing an Add Database to a report that already has an Access Table in it as a datasource. The original Access Table dissapears in the visual linking expert.

My scenario is as follows:- I have a report that displays a list of branches (From an Access table). For each branch I want to get further details from an Excel spreadsheet (a worksheet exists for each branch). So the union would be a great, but I am unable to link my Access 'BranchCode' to my .qry 'BranchCode'. This looks like a bug in Crystal. Can you give it a try and let me know how you get on please?

Thanks for your help so far!

Marcus

 

[Scooby62]

Hi Marcus.

I would link all of the tables to an Access data base and relate the tables in Access. This will ensure better control and everything will work.

Hope this helps.

 

[IdoMillet]

Create a main report using the Access Branches table.

In the Branch section (Detail or Group section) insert a linked subreport using the UNION qry data source.

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[marcusw]

Thanks Ido & Scooby!

Ido, not sure if you've tried this but the .qry fields that are displayed in the Sub Report aren't displayed when you try linking the sub-report. Can you let me know if you have this problem please...? Phew, you'd think this'd be so easy!

Scooby, thanks..... your fix is next on the list!

Thanks both for your help so far!

Marcus

 

[IdoMillet]

Do you see Field names and data if you just create a simple report based on the UNION qry file?

Do you get proper display of data with an Unlinked subreport (each branch would show data for all branches)?

What version of Crystal?

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[marcusw]

Yep, I can see the field names and put them into an ordinary (or sub-report) without a problem.

Yep, if I run the report with the embedded sub-report, but without a link, every Excel worksheet row returns. However, the Excel .qry fields are not shown when I try to link the main report to the sub-report. The version is 8.5 Developer.

Cheers,

Marcus

 

[IdoMillet]

Assuming you know what do do with the dialog box
that comes after you:
1. Right-click the subreport
2. select "Change Subreport Links..."
I have no idea what the problem is.

Sorry,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[marcusw]

It's wierd, when you select the sub report link dialog box I am able to select the Access Table (Branch.BranchCode) but there are no field names in the .QRY file to link to. Even though the fields already exist in the Sub report.

I've done this hundreds of times before with some extremely complex sub reports no problem!

Bizarre!!!

Thanks for your time anyway!

Marcus.

 

[marcusw]

Ido,

Sorry, just one more thing. I think I have found the problem. If I create a simple report based on this .qry I can add all the fields from the spreadsheet and display them. However, when I go into the formula editor I can only see the numeric columns in the worksheet. Anything textual and it's not displayed! My subreport link is based on a text field. You ever seen this?

Cheers, Sorry to bother you again!

Marcus.

 

[IdoMillet]

Marcus,

Sounds like the string columns are coming in
as longer than allowed.

I don't know if this is going to work but try,
in your query, to truncate them:

SELECT LEFT({field1}, 25), LEFT({field2}, 25) FROM Region1
UNION ALL
SELECT LEFT({field1}, 25), LEFT({field2}, 25) FROM Region2
etc...

hth,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[IdoMillet]

One more note: if you try the approach above, be sure to assign field names to the expressions
(using "As FieldName&quot:

SELECT LEFT({field1}, 25) as Customer, LEFT({field2}, 25) As Product FROM Region1
UNION ALL
SELECT LEFT({field1}, 25), LEFT({field2}, 25) FROM Region2
etc...

hth,
- Ido
CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[IdoMillet]

One more thing:
The 25 argument above was just an example.
The allowed limit is 254 characters.

I'm quite sure that the reason your text fields are not showing up in the formula editor is that they are coming in with a length greater than that limit.

Once you fix that problem, you should be able to go back to plan A and link directly (no subreports required).

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[marcusw]

Hmmm... Unfortunately, trimming the field down doesn't solve the problem. Although I thought it would as well.

Displaying it in the report just shows the 5 characters (for example) but it still doesn't appear in the formula editor or SR Link.

Hmmmm.... Crystal Decisions me thinks! Any other ideas?

Marcus

 

[IdoMillet]

That is the typical behavior of memo fields.
They show up on the report but they are not
available for Formulas.

How did you do the "Trimming"?

Did you Refresh the query or the Report after changing
the query (just in case you used the Save data with Query option ...)?

This sure sounds like a text length issue to me.

One more though: how are the cells formatted within Excel: General, Text, Custom? Are they all formatted the same?

Cheers,
- Ido CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:

http://sobpc02.bd.psu.edu/CUT/CUT.htm

 

[marcusw]

Tried all of the above I'm afraid. The cells have been formated as General, Text and even forced 'em to be Numeric format(Out of desperation!). The only ones appearing in the formula editor are numeric columns and dates.

The trimming is done in the SQL Editor using LEFT(fieldname, 10) as you suggested. Returns exactly what they are trimmed to but still in Memo format it would appear. I have seen this with BLOB & CLOB fields in Oracle as well. The difference here is that it appears to be the SQL Designer doing the converting.

I refresh every time just in case....

I'll plug away for a little while (tomorrow morning!)... it's 22:45 here in England.... Keep ya posted!

Cheers,

Marcus.