I am hoping someone can help me with an issue that I can't figure out; I'm still pretty new to CR. I use MS Great Plains with SQL server. Here's the situation: It is a standard inventory status report. I want to pull on hand quantities but they are in one field (called QTYONHND) in a Inventory Master Qty's Table. We have 8 different "Warehouses" or LOCCODE. Call them Wherehouse 1, 2, 3, 4, etc...
I need three columns, one is Item #, O/H warehouse 1, O/H warehouse 2. The other 6 warehouses aren't factored in.
I currently use Select Expert to limit the entire report to Warehouse 1 (LOCCODE = 1). Each item # comes up with a quantity O/H for location code 1. If I don't use Select Expert, I get 8 different QTYONHND for each item #. Basically each Item # is listed 8 times in a row (sorted by item #) and each shows QTYAVAIL for each warehouse, most of which are 0 because we don't use them.
I can sort of get the report near what I want by selecting "LOCCODE is like 1 or 2). But I still get two of every item number. I want to see it all in 1 row.
Example:
When I run the report with no select expert, I get for every item #:
Item Number O/H WHSE
999999 1000 #1
999999 200 #2
999999 0 #3
999999 0 #4
999999 0 #5
999999 0 #6
999999 0 #7
999999 0 #8
I want it to display
Item Number O/H WH #1 O/H 2 WH #2
999999 1000 200
Is there a select expert at the field level and not the report level? I want to say O/H wh #1 should be QTY on HAND when LOCNCODE = #1 and O/H 2 WH #2 should be QTY on HAND when LOCNCODE = #2. THis is where I'm stuck.
Any help would be appreciated. It's probably that I don't know my CR/Basic syntax yet. I jsut can't quite figure out how to go about it.
Thanks in advance,
Kevin
IP Logged