mitai
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are the man! This works great!
Glad to help
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