Solved

USING EXCEL AS DATASOURCE TO CRYSTAL REPORTS

Posted on 2008-10-17
4
3,591 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

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…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

739 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