Merging Excel file with info in a Crystal Report

[pokerace]

I have created a report in Crystal 10 that pulls data from an ODBC compliant database. One of the fields is a bunch of codes, i.e. ABC, XYZ, etc. I have an excel spreadsheet that lists the codes in one column and their corresponding definations in another column.

I want to tell Crystal to do something like this:
If ServiceCode matches one of the codes (ServiceCode) in the excel spreadsheet, then replace the code with the proper name (ServiceName) for the code. Does that make sense? Some help or pointing in the right direction would be appreciated!

Thanks.

 

[Turkbear]

Hi,
Add the Excel data source to your report and link by the ServiceCode field ( assuming such a link is possible between those data sources) - if not, export the Excel data to your ODBC database ( if allowed).





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could add the Excel spreadsheet as a datasource in a subreport. Let's say you want the description at the detail level. Insert a second detail section_b, and have your regular detail fields in this section. Insert the subreport in detail_a and format that section to "Underlay following sections." Link the subreport to the main report on the code field and then in the subreport create a formula:

whileprintingrecords;
shared stringvar codedesc := {table.desc};

Place this on the subreport canvas. Then in the main report, create a formula:

whileprintingrecords;
shared numbervar codedesc;
if codedesc = "" then
totext({table.code}) else
codedesc;

Place this in detail_b instead of the code field. You will also need to create a reset formula:

whileprintingrecords;
shared numbervar codedesc := "";

This should be placed in the report header AND in a detail_c section--the section can be suppressed.

-LB

 

[synapsevampire]

Turk has the rigth idea if you lect to use Excel, hoever keep in mind that Excel is a horrible data source.

You may find that creating an Access db and linking (not importing) your ODBC compliant tables and the Excel table within will allow you to generate an Access query as your Crystal Report data source, and it will perform faster and be easier to maintain longterm as you can edit/add/delete from the tables within Access.

-k

 

[pokerace]

Turk, I would rather not import that info in the main database.

lbass, I mostly follow what you are saying but I have some questions. When you say to put the formula:

whileprintingrecords;
shared stringvar codedesc := {table.desc};

on the subreport canvas. What do you mean by the subreport canvas?

Thanks.

 

[lbass]

Place it in some section of the subreport. The description won't be shared if you just create the formula, but do not place it on the subreport. You can then suppress each section of the subreport, but NOT the main report section in which the subreport is located (detail_a).

-LB