Passing an array parameter from crystal reports to a sql stored procedure

40 pts.
Tags:
Array Parameters
Crystal Reports
Crystal Reports 10
SQL
SQL Server
Hi All,

I am having a problem in Crystal Reports with an array parameter. The user can select one warehouse code or multiple warehouse codes. I just need to display the detail from each warehouse selected (one, multiple or ALL). Right now I'm getting duplicates. I'm not sure how to set this up in SQL or Crystal Reports, does anyone have an idea how I can do this? I have 30 different warehouses the user can select from. The information I need is

ItemCode     WhsCode    Item Desc.     Qty On hand

001                  A               Red Bike              5

002                  B               Blue Bike             4 

003                  C                Green Bike         3

what I'm getting right  now is . . .

ItemCode     WhsCode    Item Desc.     Qty On hand

001                  A               Red Bike             5

001                  A               Red Bike             5

002                  B               Blue Bike            4 

002                  B               Blue Bike            4 

003                  C                Green Bike         3

003                  C                Green Bike         3



Software/Hardware used:
SQL. Crystal Reports 10

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    "what I'm getting right now is . . . " Without knowing how you are generating that information, it will be really hard for us to offer accurate suggestions...
    68,525 pointsBadges:
    report
  • Jeager66
    Sorry Carlos you're right. I generate the information in a stored procedure in SQL. I have tried using a command parameter in Crystal selecting warehouse code and itemcode to create the parameter then I join that back to the SP on item code. I'm really not sure what the best way to do this would be. Please let me know what other information you may need for an accurate suggestion. Thanks very much Tino SELECT L8QA .[Item Code], --OITM.validFor, L8QA.[Item Name], L8QA.[On Hand], OITW.WhsCode, Case When OITW.WhsCode = '1' then L8QA.[On Hand] When OITW.WhsCode = '3' then L8QA.[On Hand] When OITW.WhsCode = '6' then L8QA.[On Hand] When OITW.WhsCode = '8' then L8QA.[On Hand] When OITW.WhsCode = '14' then L8QA.[On Hand] When OITW.WhsCode = '20' then L8QA.[On Hand] When OITW.WhsCode = '26' then L8QA.[On Hand] When OITW.WhsCode = '32' then L8QA.[On Hand] End as [TEST_ONHAND], FROM L8_QuantityAvailable L8QA With (nolock) JOIN OITM With (nolock) on L8QA.[Item Code] = OITM.ItemCode JOIN OITW With (nolock) on OITM.ItemCode = OITW.ItemCode JOIN OITB With (nolock) on OITM.ItmsGrpCod = OITB.ItmsGrpCod
    40 pointsBadges:
    report
  • carlosdl
    So, that is the query that is returning duplicate records, right ? That behaviour is usually the result of incorrect or missing join conditions. Someone could suggest the use of the DISTINCT keyword, but you should really find the root cause of the duplicates, and modify the query accordingly.
    68,525 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following