Solved

USING EXCEL AS DATASOURCE TO CRYSTAL REPORTS

Posted on 2008-10-17
4
3,595 Views
Last Modified: 2013-11-15
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
0
Comment
Question by:mitai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 3

Expert Comment

by:Cootser
ID: 22745923
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
0
 
LVL 3

Accepted Solution

by:
Cootser earned 500 total points
ID: 22745950
SELECT *
 FROM   `Sheet1$` `Sheet1_`
WHERE `Sheet1_`.`code` = 'a001'

This * also works

For your MTM$ sheet you could put

SELECT *
 FROM   `MTM$` `MTM_`
WHERE `MTM_`.`your_field` = 'some_value'
0
 

Author Closing Comment

by:mitai
ID: 31507285
You are the man!  This works great!
0
 
LVL 3

Expert Comment

by:Cootser
ID: 22765362
Glad to help
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question