import data to parameter

[onyxtacular]

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

 

[hilfy]

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

http://www.decisionfirst.com

 

[onyxtacular]

{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!

 

[onyxtacular]

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')

 

[hilfy]

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

http://www.decisionfirst.com

 

[onyxtacular]

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!

 

[hilfy]

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

http://www.decisionfirst.com

 

[onyxtacular]

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.