Link to home
Start Free TrialLog in
Avatar of mitai
mitaiFlag for Canada

asked on

USING EXCEL AS DATASOURCE TO CRYSTAL REPORTS

Hello all,

I am trying to use a excel workbook as input to my crystal report.  Here is the issue, I want to be able to use the add command and do a select instead of just selecting the sheets I want.  IE I have 3 sheets and they are all summed at different levels.  I want to be able to create a command (much like i would with a database) that says something like select group, value from MTM group by group.

But when I go to do the select * from mtm it says it doesn't know what that table is.  Does anyone know the proper syntax for excel tables?

I have attached a screenshot.....

Thanks in advance....
crystalbmp.bmp
Avatar of Cootser
Cootser

Hello mitai

Iam using cr 9 but this may apply to you also

In my example the excel file has Sheet1$

What i did to get the syntax (see below) was first added the actual Sheet1$ into the report, and placed some fields into the details section.  I then ran the report and when the data was displayed i went to Database > Show SQL Query (this displays the actual SQL query that Crystal uses to get data). I copied that text and went into Database expert where i removed Sheet1$ and selected Add Command, pasted the text in there and it accepted it.  I typed the WHERE clause myself

So now my report uses that command to get its data.

One thing to note

Have a look at the example code below

It says `Sheet1_`.`code` the ` character is from the button to the left of the number 1 on my QWERTY keyboard, and i have to press it twice to get it to display
AND
in the WHERE clause i can use a normal ' to enclose the value i.e. 'a001'

SELECT `Sheet1_`.`code`, `Sheet1_`.`desc`, `Sheet1_`.`amount`
 FROM   `Sheet1$` `Sheet1_`
WHERE `Sheet1_`.`code` = 'a001'

Best of luck

Cootser
ASKER CERTIFIED SOLUTION
Avatar of Cootser
Cootser

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mitai

ASKER

You are the man!  This works great!
Glad to help