We help IT Professionals succeed at work.

passing parameter for IN clause in command

metropia
metropia asked
on
I want to parameterize a "in" condition of the SQL. For eg:

SELECT
b.name AS 'CUSTOMER NAME',
a.journal_customer_id AS 'CUSTOMER ID',
a.entity_id AS 'ENTITY ID',
a.recv_invoice_id AS 'INVOICE ID',
a.invoice_date AS 'INVOICE DATE',
a.amount_15 AS 'AMOUNT',
c.COMMENTS AS 'REMARKS',      
c.LAST_CONTACT_DATE AS 'LAST CONTACT DATE'
FROM RG_AR_COLLECTION a
LEFT OUTER JOIN RECEIVABLES_CUSTOMER b
      ON b.id = a.journal_customer_id
LEFT OUTER JOIN RG_AR_COLLECTIONS_LOG c
    ON c.customer_id = b.id
    AND c.entity_id = a.entity_id
    AND c.invoice_id = a.recv_invoice_id
WHERE a.related_currency_id = 'USD'
AND a.entity_id IN ('10', '20', '30')  ---  (?parameter)

My goal is to parameterize the ('10', '20', '30') section.

Can somebody explain this on how to do this?
Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you doing this in the database, a Crystal command, or the Crystal select expert?

You cannot pass a multiple value parameter to the database or a Crystal command.

mlmcc

Commented:
CR doesn't support the use of multi value parameters from within crystal commands.

However there are a number of workarounds, here's one technique

http://www.tek-tips.com/faqs.cfm?fid=6779

Author

Commented:
crystal.

then how can I handle that type of selection?

Commented:
is there a reason for uisng a command and not simply joining the tables in CR ? That way you could use a a standard CR parameter that accepts multiple values

Author

Commented:
because it seems to me that it is easier to control the sql by using a command
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I believe you can use a standard parameter with a command it just won't be passed to the database for evaluation and you will get all the records returned.

You could have the user enter a comma separated list of values as a string and change the SQL to
InStr({?Parameter},a.entity_id) > 0
or whatever the appropriate syntax is for your database

mlmcc
Sr. Business Intelligence Consultant/Architect
Commented:
Crystal Reports 2008 and 2011 allow for multiple value command parameters.  What version of Crystal Reports are you using?

If you're using Crystal Reports XI R2 or lower, here's a sample report I wrote that demonstrates how to use multiple value parameters with SQL Commands: CRXIR2 Example - Multiple value parameters with SQL commands
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
BTW - there's nothing complicated enough in that SQL Query that it can't be done explicitly in Crystal Reports. As such, I agree with GJParker's suggestion to just do it by joining the tables in Crystal and creating a multiple value parameter.  The SQL will pass just fine, unless what you wrote above is just an example.

Examples of when I would use SQL instead of tables would be if you have to use an INLINE JOIN:

LEFT OUTER JOIN RECEIVABLES_CUSTOMER b ON b.id = a.journal_customer_id
AND (b.receipt_date IS NULL OR b.receipt_date = '01/01/1900')

Open in new window


In this scenario, a filter has been added to a left join.  This is a very common scenario, BTW.  You might want a left join so you get all records from your primary table, regardless of a match, but you only want a subset of data from the left joined table.  You cannot efficiently do that in Crystal Reports, so SQL is appropriate.

SELECT
  'QUERY 1' IDENTIFIER
  FIELDS
FROM
  TABLE
WHERE
  CONDITIONS

UNION ALL

SELECT
  'QUERY 2' IDENTIFIER
  FIELDS
FROM
  TABLE
WHERE
  CONDITIONS

Open in new window


In this example, you might have two sets of tables with similar structures, but different data.  A common example would be data warehouse tables that isolate records by fiscal year.  Another example would be a set of tables that capture accounts receivable and another that captures accounts payable.  Using a UNION or UNION ALL lets you join multiple queries into a single recordset for reporting purposes, but group records based on the identifier field you create in each individual query.  Since you can't efficiently do this in Crystal Reports, this is an appropriate use of SQL.

Author

Commented:
attached is the version of CR. cr version

Author

Commented:
What I am trying to do is to get customer information such as name, id, invoice id, etc...

This data I get it from two different tables:

RG_AR_COLLECTION
RECEIVABLES_CUSTOMER

The third table contains all the comments (remarks) that an invoice can have. A customer id can have multiple invoices, and an invoice can have multiple remarks.

Right now I am able to get all the customer basic info, but it gets clogged when I try to retrieve the comments.

e.g.

When I retrieve the remarks for invoice id 250423, that invoice remaining fields repeat on the report making it hard to read, confusing and misleading, specially in regards to the amount column.

This is what I see on the report:
column headers
A COMPANY      001034      10      250423      2011-09-27      1040.000      test1      2011-12-06 12:59:19.533
A COMPANY      001034      10      250423      2011-09-27      1040.000      test2      2011-12-06 12:59:32.047


Ideally I would like to see for each invoice:

column headers
A COMPANY      001034      10      250423      2011-09-27      1040.000
column sub-headers:
test1      2011-12-06 12:59:19.533
test2      2011-12-06 12:59:32.047

If I could get some help, or guidance on how to approach to this task that would be so nice. I am struggling trying to understand how to make it work.

Thank you very much.

Author

Commented:
This is a copy of my report.

The SQL I am using is still a command, but right now I am hard coding the value.

SELECT
b.name AS 'CUSTOMER NAME',
a.journal_customer_id AS 'CUSTOMER ID',
a.entity_id AS 'ENTITY ID',
a.related_currency_id AS 'CURRENCY',
a.recv_invoice_id AS 'INVOICE ID',
a.invoice_date AS 'INVOICE DATE',
a.amount_15 AS 'AMOUNT',
c.COMMENTS AS 'REMARKS',      
c.LAST_CONTACT_DATE AS 'LAST CONTACT DATE'
FROM RG_AR_COLLECTION a
LEFT OUTER JOIN RECEIVABLES_CUSTOMER b
      ON b.id = a.journal_customer_id
LEFT OUTER JOIN RG_AR_COLLECTIONS_LOG c
    ON c.customer_id = b.id
    AND c.entity_id = a.entity_id
    AND c.invoice_id = a.recv_invoice_id
WHERE a.entity_id IN ('10')
AND a.related_currency_id = 'USD'

CollectiongsLog.rpt

Author

Commented:
I uploaded the report just in case getting help with the formatting is feasible, and attainable.

Thank you much.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
1)  You're using XI R2, so multiple value input parameters aren't available.  FYI - this is a SQL limitation, not a Crystal Reports limitation.  In Crystal Reports 2008, Business Objects basically built in code that concatenates multiple values into a single selection that is compatible with the IN keyword in SQL.

2)  All of the various formatting questions are really extraneous to the original question. Even though they pertain to the same report, you might need to start a separate question.  I'll defer to the mods, of course.

Author

Commented:
Does that mean that the report sample you sent me would not work for my task?

I am opening a separate question regarding the formatting.

Thank you for your help.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
The report sample will work in regards to your original question of how to use multiple value parameters with a SQL Command. It's written against the same version of Crystal Reports you're using.

Commented:
Group1 : customer
Group2 : invoice id

place the customer and invoice details in G2 header section, place the remarks details in the details section
CollectiongsLog-1-.rpt

Author

Commented:
I made some changes to my report, added a sub-report to show the remarks details.

The SQL query has a GROUP BY to get rid of the dups

Could it be possible that you can show me how to instead of the customer name appearing multiple times, it shows only once?

here is a copy of my current report.
CollectiongsLogGP.rpt
Commented:
move the customer name field from group header 2 to group header 1

remove the group #1 name field from GH1

right mouse click on left hand side and open section expert

select Group Header #1 section

Uncheck Hide (drill down ok)

check underlay follwoing sections

Author

Commented:
Hi guys,

I have a question regarding this report.

Right now my drop down menu uses a default value of 10.

How can I modify the command so that a default value of "Select Location" can be assigned without running the report when it opens the first time?
CollectiongsLogGP.rpt
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try this one

mlmcc
CollectiongsLogGPRev1.rpt

Author

Commented:
where would i see the changes in your report?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I gave the parameter a default value

If you mean can you run the report without getting prompted, that is possible only through an application that passes the values or if you don't have parameters

mlmcc

Author

Commented:
i have a parameter defined in the SQL command: entity_id

and I have another parameter, same name defined in the parameter section in the report.

do I need to have the parameter declared in two places?

also i see that below the drop down menu, there is a text input box that gets automatically place there, would you mind explaining me why this?


Thank you much.

Author

Commented:

Author

Commented:

Author

Commented:
i think i need to delete my parameter in the sql command window and just have the one on the crystal menu, located to the right of the crystal screen.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You only need it one place or the other.

It makes more sense to use the one in the command since it will then be passed to the database for filtering.  If you use a report parameter the command will return all the data and do the filtering after.

Set the default value to '10'

>>also i see that below the drop down menu, there is a text input box that gets automatically place there, would you mind explaining me why this?

Where do you see this?  Is this when ypu run the report and are prompted for a value?

mlmcc
Commented:
The parameter field in the report was created automatically when you created the parameter in your command and the two are linked and should not be deleted.

You should set your default value in the report parameter and change 'Allow Custome Values' to false to remove the text input box you see at runtime.