Dynamic Recipient/Multiple Value Parameter in Publication using Crystal Reports for Enterprise as Source

[former_member667968]

Using Crystal Server:

SAP BusinessObjects BI Platform 4.1 Support Pack 5

Version: 14.1.5.1501

Publishing a Crystal Report for Enterprise report within Crystal Server, purpose of bursting to email destination for multiple dynamic recipients.

The report uses 2 parameters that require multiple values (1 or more)

Example:  multi-value param:  {?region}

region north, region south, region east, region west

Some recipients must see data from more than 1 region for this parameter.

*****************************************

We use a Crystal Reports Dynamic Recipient Provider for the Dynamic Recipients.

The recipient fields  include:

1) Recipient Identifier   (number)

2) Full Name   (string)

3) Email   (string)

report parameter fields:

4) Region[s] (string)

5) District[s]  (string)

There is actually one way we can get this to work  (but unfortunately the setup of the data into the CR Dynamic Recipient report is not easy to do and this is why we are asking if there is another way).   This is what works now:

Dynamic Recipient Provider Report setup that works:

recipient identifier	full name	email	region	district
1	John	john@email.com	north	district1
1			east	district2
1				district3
2	Sarah	sarah@email.com	west	district1
2			north
3	Frank	frank@email.com	west	district3
3			north
3			east
3			south
4	Jane	jane@email.com	south	district1

If we use the above formatting in the Dynamic Recipient Provider Report, where Dynamic Recipient Mapping is setup under "Personalization" mapping from the above fields to the Report Parameters....this works as desired.   Each recipient in the list gets their individualized report with their proper data filtering.   Example:  Sarah would receive her report at her email address including data from regions west and north and district1

What makes this unworkable is that the data in the recipient list (regions and districts) are always changing and needs to be refreshed, and we have found no easy way to create the above formatting with our database...we have to use an ETL process to get it so the dynamic recipient crystal reports is formatted as above.   There cannot be any line breaks, hidden sections or hidden groups in the report as this does not get fed into the publication properly.  e.g. The Dynamic Recipient list in the publication will show blank rows and choke if we used a group.    The data has to be as exactly as shown so that no value in a field is repeated for a single recipient ID

EXAMPLE:

If the table shown as below (as interpreted by the publication from the recipient provider CR report) as viewed in the dynamic recipient list in the properties or schedule of the publication), the publication would fail (repeated values in a field for a recipient and/or an empty row):

recipient identifier	full name	email	region	district
1	John	john@email.com	north	district1
1	John		east	district2
1	John			district3
2	Sarah	sarah@email.com	west	district1
2			north	district1
null	null	null	null	null
3	Frank	frank@email.com	west	district3
3			north	district3
3			east	district3
3			south	district3
4	Jane	jane@email.com	south	district1

QUESTION:

Is there another supported method for bringing in multiple values into a parameter in a dynamic recipient mapping for personalization?

I have also tried having the district and division values concatenated into a single comma separated string:

Example:   north, east    (in the single region parameter field)

recipient identifier	full name	email	region	district
1	John	john@email.com	north, east	district1

This did not work.  The publication only read the first value.

I also tried including a parameter in the source report (the report being published) that fed into a "sql command" object...so that the single string (this time formatted as 'north','east' for MySQL) would be passed to the SQL query:

WHERE

region.name IN ({?region})

AND district.name IN ({?district})

or specifically with the above example with John:

WHERE

region.name IN ('north','east')

AND district.name IN ('district1')

This actually works within Crystal Reports for Enterprise designer...if I enter the string      'north','east'     into the parameter when running the report.

Also, if I copy and paste the full SQL Command into a MySQL client with the where clause including the string ('north','east') for region.name...the query works.   CR for Enterprise uses the 32bit MySQL ODBC driver

Unfortunately it chokes when running it through the crystal server publication.   The 64bit MySQL ODBC driver is used and we get a sql error from the "failed" publication run:

The database returned the following error: [MySQL][ODBC 3.51 Driver][mysqld-5.1.73-0ubuntu0.10.04.1]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')

AND district.name IN ()

We are looking at resolving the above error somehow, but I have looked for guidance and best practices for handling multiple-value parameters in a crystal reports publication with Dynamic Recipients and I have not found anything specific that addresses "how to" do this.  We'd like to make sure we are using best practices or there isn't an easier way that we are missing.

Any help is appreciated.

Thanks

[Former Member]

Jonathan,

Were you able to get resolve this issue? I am having the same issue with trying to pass multiple values to a dynamic parameter in publication. I even tried you first screenshot to create a duplicate row with only the additional value, but could not get that to work. My publication works fine when only passing one value to the parameter, but fails when passing multiple values.

Thanks!