passing parameter for IN clause in command

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

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

crystal.

then how can I handle that type of selection?

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

because it seems to me that it is easier to control the sql by using a command

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

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

Select allOpen 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

Select allOpen 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.

attached is the version of CR.

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.

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

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

Thank you much.

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.

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.

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.

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

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

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

where would i see the changes in your report?

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

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.

Capture.PNG

Capture.PNG

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.