Select or Parameter issue

[luv2bike2nv]

Crystal Reports 8.5

I created a report that pulls the following information using 3 DBs.
Part Number, Description, order number, vendor ID, Transaction date, Original quantity, Transaction quantity, Unit of Measure, Stock ID, Transaction Code, Reference and User Define Reference.

There is only one Parameter (for Transaction date), two select statements – 1) order number between 700000 and 799999. 2) transaction date using the Parameter.

There are several Transaction Codes in the DB however the report is only picking up the R (received) Transaction Code. We are looking for the Transaction Code of T (Transfer)
To correct this problem (or try to correct this problem) I created a Parameter and set the Default Value to T (for Transaction), created a Select Statement with “is equal to” the Parameter %transaction code, when I run the report nothing is returned.

I am not sure where to go from here. Thanks in advance!

 

[luv2bike2nv]

Correction:

****To correct this problem (or try to correct this problem) I created a Parameter and set the Default Value to T (supposed to be TRANSFER not Transaction), ******

 

[lbass]

This is probably partly a join issue. What are your three tables and how are they joined (on what fields, with what kind of join, FROM what table TO what table). Then also show your actual selection formula showing table and fields names ({table.field}).

-LB

 

[luv2bike2nv]

DB's 3 - Order Master, Part Master, Transaction History

Order Master\PRTNUM equal join to Part Master\PRTNUM
Order Master\ORDNUM equal join to Transactin History\ORDNUM

Select Expert:
{Transaction_History.TNXCDE} = {?TRANACTIONCODE}


Parameter field -- Tranactioncode
Option: Discrete value
Set default values:
Browse Table: Transaction History
Browse Field: TNXCDE
Select or Enter value to add: T
Options: Length Limit NOT checked off
Display: Value and Description
Order: No Sort
Order Based on: Value (however that is grayed out)
Edit Mask: empty.



Thanks,
Robin

 

[lbass]

What is the entire selection formula (report->selection formula->record?

Do you know for a fact that there are records that meet all of your criteria?

Are you sure that Transaction Code displays "T" as a result?

Are these the ONLY tables you are using? It might help to also see the SQL query: database->show sQL query.

-LB

 

[luv2bike2nv]

sorry i did not get back sooner. no rush on getting back to me today. I will be leaving soon and will return on Monday.

This is what is in the Select Expert for the three select statements:

{Transaction_History.TNXDTE_15} = {?Transaction Date} and
{Transaction_History.TNXCDE_15} = {?TRANACTIONCODE} and
{Transaction_History.ORDNUM_15} in "700000" to "799999"

I almost 100 % postive that not all records meet the criteria (this could be my problem)

Transaction code does display a T, along with a,c,f,i,k, etc.

from database>show SQL query
SELECT
Order_Master."ORDNUM_10", Order_Master."LINNUM_10", Order_Master."PRTNUM_10", Order_Master."VENID_10", Order_Master."ORGQTY_10",
Part_Master."PMDES1_01", Part_Master."BOMUOM_01",
Transaction_History."TNXDTE_15", Transaction_History."ORDNUM_15", Transaction_History."TNXCDE_15", Transaction_History."REFDES_15", Transaction_History."STKID_15", Transaction_History."TNXQTY_15", Transaction_History."UDFREF_15"
FROM
"MAXDAT"."Order Master" Order_Master INNER JOIN "MAXDAT"."Transaction History" Transaction_History ON
Order_Master."ORDER_10" = Transaction_History."ORDNUM_15" INNER JOIN "MAXDAT"."Part Master" Part_Master ON
Order_Master."PRTNUM_10" = Part_Master."PRTNUM_01"
WHERE
Transaction_History."TNXDTE_15" >= {d '2011-08-01'} AND
Transaction_History."TNXDTE_15" <= {d '2011-08-31'} AND
Transaction_History."ORDNUM_15" >= '700000' AND
Transaction_History."ORDNUM_15" <= '799999' AND
Transaction_History."TNXCDE_15" = 'T'
ORDER BY
Transaction_History."TNXDTE_15" ASC

 

[lbass]

Your very first task is to determine that there are records that meet all of these criteria. I think you should lay the fields out in the detail section and run the report. Then comment out each line of the selection formula by going to report->selection formula->record so that you can see which criterion is causing the problem.

-LB

 

[luv2bike2nv]

Thank You. I am working on it right now. I will post back with my results.

Robin

 

[luv2bike2nv]

I was given the wrong information about what the Transaction Code was. T means To and is used in conjuntion with F which means From. So I won't be using the Transaction Code in my report in a select or parameter statements.

With that information I am still having an issue with getting the information that is needed so I went to the Transaction History Pervasive SQL DB and did a query on the following fields (which are the same fields in CR): Order Number, Transaction Date, Stock Id and User Define Reference.

Below is the select statement I used ib Pervasive SQL:
SELECT ordnum_15,prtnum_15, tnxdte_15, stkid_15, UDFREF_15 FROM "Transaction History" WHERE stkid_15 = 'MS' AND tnxdte_15 > '2011-07-01'


The query came up with what is needed, so I know it can be done.

In Crystal Report report I did a test run with the following Select Statements:

{Transaction_History.TNXDTE_15} = {?Transaction Date} and
{Order_Master.ORDNUM_10} in "700000" to "799999"

DB/Show SQL Query
SELECT
Part_Master."PMDES1_01", Part_Master."BOMUOM_01",
Order_Master."ORDNUM_10", Order_Master."LINNUM_10", Order_Master."PRTNUM_10", Order_Master."VENID_10", Order_Master."ORGQTY_10",
Transaction_History."TNXDTE_15", Transaction_History."TNXCDE_15", Transaction_History."REFDES_15", Transaction_History."STKID_15", Transaction_History."TNXQTY_15", Transaction_History."UDFREF_15"
FROM
"MAXDAT"."Part Master" Part_Master INNER JOIN "MAXDAT"."Order Master" Order_Master ON
Part_Master."PRTNUM_01" = Order_Master."PRTNUM_10" INNER JOIN "MAXDAT"."Transaction History" Transaction_History ON
Order_Master."ORDER_10" = Transaction_History."ORDNUM_15"
WHERE
Transaction_History."TNXDTE_15" >= {d '2011-09-01'} AND
Transaction_History."TNXDTE_15" <= {d '2011-09-13'} AND
Order_Master."ORDNUM_10" >= '700000' AND
Order_Master."ORDNUM_10" <= '799999'
ORDER BY
Transaction_History."TNXDTE_15" ASC

I get results back however it is pulling all stockids.

When I added the select statment for StockID nothing comes back when i run the report (refresh) and all I did was added the select statement as you can see below. (using the Parameter StockID with Values of MS and NCMR - see below for the configuration of StockID)

SELECT
Part_Master."PMDES1_01", Part_Master."BOMUOM_01",
Order_Master."ORDNUM_10", Order_Master."LINNUM_10", Order_Master."PRTNUM_10", Order_Master."VENID_10", Order_Master."ORGQTY_10",
Transaction_History."TNXDTE_15", Transaction_History."TNXCDE_15", Transaction_History."REFDES_15", Transaction_History."STKID_15", Transaction_History."TNXQTY_15", Transaction_History."UDFREF_15"
FROM
"MAXDAT"."Part Master" Part_Master INNER JOIN "MAXDAT"."Order Master" Order_Master ON
Part_Master."PRTNUM_01" = Order_Master."PRTNUM_10" INNER JOIN "MAXDAT"."Transaction History" Transaction_History ON
Order_Master."ORDER_10" = Transaction_History."ORDNUM_15"
WHERE
Transaction_History."TNXDTE_15" >= {d '2011-09-01'} AND
Transaction_History."TNXDTE_15" <= {d '2011-09-13'} AND
Order_Master."ORDNUM_10" >= '700000' AND
Order_Master."ORDNUM_10" <= '799999' AND
Transaction_History."STKID_15" = ' MS'
ORDER BY
Transaction_History."TNXDTE_15" ASC


Parameter for StockID
Parameter field -- STOCKID
Prompting Text: Enter STOCKID
Value Type: String
Option: Allow multiple values and Descrete value(s)and allow editing of default values when there is more than one value
Set default values: Browse Table: Transaction History
Browse Field: STKID
Select or Enter value to add: MS and NCMR
Options: Length Limit NOT CHECK OFF
Display: Value and Description
Order: No Sort
Order Based on: Value (however that is grayed out)
Edit Mask: empty.

What do i need to do to correct this?
Thanks,
Robin

 

[lbass]

There is a space before "MS" in the SQL query, so it looks like you entered a space before entering MS at the parameter prompt. If the space is in the field, change your selection formula to:

trim({Transaction_History."STKID_15"}) = 'MS'

You can do this by going to report->selection formula->record and making the change there.

-LB

 

[lbass]

Should have said:

trim({Transaction_History.STKID_15}) = {?YourParameter}

-LB

 

[luv2bike2nv]

Ok I removed the space and MS works, however I do not get any results when I have the NCMR as one of the selected.

I went into Pervasive SQL and did a query like i did for MS and used NCMR and there were results. I checked and doubled checked to see if there was a space or anything what would prevent NCMR to show up and I can not see anything.

again I am at a loss and I really do appreciate all your help!

 

[lbass]

What you are selecting should have no particular relevance, once spaces are removed. As far as doing a query--you need to check whether the values appear in Crystal (not using other methods) when you remove only the following parameter clause from the selection formula:

Transaction_History."STKID_15" = {?YourParameter}

Another factor could be case--if the value appears in lower case and you are testing for upper case.

-LB

 

[luv2bike2nv]

LB,

I decided to start the report from scratch. I started with the Transaction History DB only, which had the most in the Detail line, added Date and Stock ID parameters and the Select Statements for the Date, Stock ID and Transaction Code. Everything worked fine so I added the Part Master fields (Description and Unit of Measure) and Order Master DBs (Order Number, Vendor ID and Original Quantity). All the fields have data in the detail line except for Order Number, Vendor ID and Orig Quantity which comes from the Order Master DB. I have tried a lot of different combinations in linking the DBs together however they are still not coming up. Any Idea what I need to do to fix this?
Again Thanks for your assistance!
Robin

below is the "Show SQL Query" from this new report:
SELECT
Transaction_History."PRTNUM_15", Transaction_History."TNXDTE_15", Transaction_History."TNXCDE_15", Transaction_History."REFDES_15", Transaction_History."STKID_15", Transaction_History."TNXQTY_15", Transaction_History."UDFREF_15",
Part_Master."PMDES1_01", Part_Master."BOMUOM_01",
Order_Master."ORDNUM_10", Order_Master."LINNUM_10", Order_Master."VENID_10", Order_Master."ORGQTY_10"
FROM
"MAXDAT"."Transaction History" Transaction_History LEFT JOIN "MAXDAT"."Order Master" Order_Master ON
Transaction_History."PRTNUM_15" = Order_Master."PRTNUM_10" AND
Transaction_History."ORDNUM_15" = Order_Master."ORDER_10" LEFT JOIN "MAXDAT"."Part Master" Part_Master ON
Transaction_History."PRTNUM_15" = Part_Master."PRTNUM_01"
WHERE
Transaction_History."TNXDTE_15" >= {d '2011-09-01'} AND
Transaction_History."TNXDTE_15" <= {d '2011-09-15'} AND
Transaction_History."TNXCDE_15" = 'T' AND
(Transaction_History."STKID_15" = 'NCMR' OR
Transaction_History."STKID_15" = 'MS')
ORDER BY
Transaction_History."TNXDTE_15" ASC

Select Statements:
{Transaction_History.TNXDTE_15} = {?DATE} and
{Transaction_History.TNXCDE_15} = "T" and
{Transaction_History.STKID_15} = {?STKID}

Parameter fields
?DATE = value type = Date Option: range value
?STKID + Value Type = String options: Allow multiple values, discrete values, default values:: table= transaction history, browse field = STKID, default values = MS and NCMR.

 

[lbass]

I have lost track of what the problem is. I have suggested looking at the data in the detail level, and then adding the select statements one at a time to pin down the problem, but I don't think you have reported back on the specific results. I'm not sure I can make any more suggestions without a better understanding of the actual data as it is returned.

-LB

 

[luv2bike2nv]

LB,
sorry i did not get back to you sooner. other issues came up to put this on the back burner. I hope to get back to this by mid week. I will do as you suggested and look at the data in the detail level etc and will see what happens. thanks again

 

[luv2bike2nv]

LB,

Below is a link to a file where I copied and paste print screens and gave a description of what happened when I did the select statements and what is needed.

The Transaction History DB allows the users to move parts from one Stock ID to another and assigns the transaction a code.

The Part Master DB has all the information about the part ie. our part number, how many in stock, reorder point informatio, stock ID. etc.

The Order Master DB has the information about the Order-how many parts, price, vendor id, vendor name etc.

I hope what I explained is the information that you need to help me out.

Thank you,
Robin

 

[lbass]

So how are records for transfers related to the order numbers (missing for the transfer code)? Are they paired with records that have the R code? Is there a field that links the original order to the transfer record?

-LB

 

[luv2bike2nv]

the records for transfer are NOT related to the order numbers even though both Transfer codes and Order numbers are in the same db. No they are not paired with records that have an R code. and no there is not a field that links the original order to the transfer record. this does not look good. huh?

 

[lbass]

How do YOU know which order number is being transferred then?

-LB