×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

import data to parameter

import data to parameter

import data to parameter

(OP)
HI Everyone,
Currently the user types the parameters into the report manually. I want to convert the report to gather the data from the fields of the .csv file. I've added to the .csv to crystal report what I don't understand is exactly how to make it read the values in the place of the parameters.

Here's what is currently in Crystal:
{Order_Master.STATUS_10} < "4" and
{Order_Master.TYPE_10} = "MS" and
{Order_Master.CUSORD_10} = {??salesorders}

Here I input the 11111..222222 and 1234567..2345678 into the ??salesorders dialog and this is what crystal sends to the server.
Onyx
SELECT "Order_Master"."ORDNUM_10", "Order_Master"."TYPE_10", "Order_Master"."STATUS_10", "Order_Master"."CUSORD_10"
FROM "Onyx"."dbo"."Order_Master" "Order_Master"
WHERE "Order_Master"."STATUS_10"<'4' AND (("Order_Master"."CUSORD_10">='11111' AND "Order_Master"."CUSORD_10"<='222222') OR ("Order_Master"."CUSORD_10">='1234567' AND "Order_Master"."CUSORD_10"<='2345678')) AND "Order_Master"."TYPE_10"='MS'



any help is appreciated. I've been round and round the net trying to find this. I'm sure it is something pretty basic but I have very limited knowledge of crystal/sql so find just the right search is proving to be most difficult.

Thanks

RE: import data to parameter

Since your order numbers are in a .csv, in the Database Expert you'll do an inner join from Order_Master.CUSORD_10 to the order number field in the .csv file. You'll then take "and {Order_Master.CUSORD_10} = {??salesorders}" out of the Select expert.

The problem with doing it this way is that the filter will no longer be sent to the database - instead Crystal will pull all of the data that meets your other filter criteria into memory, do the join to the .csv file in memory and get rid of the records that don't meet the join. Depending on how much data is in your tables, this can significantly slow down your report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: import data to parameter

(OP)
{Order_Master.TYPE_10} = "MS" and
{Order_Master.STATUS_10} < "4" and
{Order_Master.CUSORD_10} >= '{MSClose40_2003_csv.startON}' and
{Order_Master.CUSORD_10} <= '{MSClose40_2003_csv.endON}'

After a lot of head scratching last night I came up with this. Which I think would meet the criteria. I did try to join the MSClose_2003_csv.orderNUM to the Order_Master.CUSORD_10 but it failed for some reason. I'll try to figure out how to do the inner join....hopefully that is the key to making it stick.

Thanks!

RE: import data to parameter

(OP)
Well I got it to work...kinda. I switch from csv to excel. the problem with the csv is CR read it as a number instead of a text. I'm sure there is a quick and dirty way of fixing that, but this is my first rodeo so I'm focusing on the goal and saved it as an excel and CR made the link.

Now my problem is that CR prompts me to pic from a the list of parameters instead of playing nice and working through the fields.

What I need to know is how to have CR accept the parameters without prompting.

{Order_Master.TYPE_10} = "MS" and
{Order_Master.STATUS_10} < "4" and
{Order_Master.CUSORD_10} >= {?startONP} and
{Order_Master.CUSORD_10} <={?endONP}


SELECT "Order_Master"."ORDNUM_10", "Order_Master"."TYPE_10", "Order_Master"."STATUS_10", "Order_Master"."CUSORD_10"
FROM "Onyx"."dbo"."Order_Master" "Order_Master"
WHERE "Order_Master"."TYPE_10"='MS' AND "Order_Master"."STATUS_10"<'4' AND ("Order_Master"."CUSORD_10">='200755270101' AND "Order_Master"."CUSORD_10"<='200755279999')


RE: import data to parameter

If the Excel file only contains the order numbers that you want on the report, you can take the last two lines out of the filter and remove the prompts. If you've joined from the Order_Master table to the Excel file, it will automatically filter the data based on the file.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: import data to parameter

(OP)
Hilfy,
First thanks for your help and time on this. I went back and made the modifications. The report finally ran, but no data. I then cried just a little, but then I went back and looked at everything I finally tried EXTERNAL JOIN MSClose40_.startON={?Onyx: Order_Master.CUSORD_10} which gave me an output!

Then I checked the report by matching it against the original report with the manually imputed parameters.

The original report has 82 records
The new report has 3 records
Here's what CR says:

{Order_Master.STATUS_10} < "4" and
{Order_Master.TYPE_10} = "MS" and
{Order_Master.CUSORD_10} >= {MSClose40_.startON} and
{Order_Master.CUSORD_10} <={MSClose40_.endOR}


---
C:\Users\administrator\Desktop\MSClose40\MSClose40-cr.xls
SELECT `MSClose40_`.`startON`, `MSClose40_`.`endOR`
FROM `MSClose40$` `MSClose40_`
EXTERNAL JOIN MSClose40_.startON={?Onyx: Order_Master.CUSORD_10}


Onyx
SELECT "Order_Master"."ORDNUM_10", "Order_Master"."TYPE_10", "Order_Master"."STATUS_10", "Order_Master"."CUSORD_10"
FROM "GNC5"."dbo"."Order_Master" "Order_Master"
WHERE "Order_Master"."CUSORD_10"={?C:\Users\administrator\Desktop\MSClose40\MSClose40-cr.xls: MSClose40_.startON} AND "Order_Master"."STATUS_10"<'4' AND "Order_Master"."TYPE_10"='MS'





I'm over the hill now and I have to check with the wife, but if she's up to it I'll name my next born child Hilfy....guaranteed!

RE: import data to parameter

LOL! Glad I could help.

Hilfy is actually a character from the Chanur series of books by CJ Cherryh. She's a sentient space-faring cat.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: import data to parameter

(OP)
Just a quick update, the problem was crystal linked the first field so it limited the output based on the the number of records. Once I cleared the links things went smoothly.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close