Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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 identifierfull nameemailregiondistrict
1Johnjohn@email.comnorthdistrict1
1eastdistrict2
1district3
2Sarahsarah@email.comwestdistrict1
2north
3Frankfrank@email.comwestdistrict3
3north
3east
3south
4Janejane@email.comsouthdistrict1


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 identifierfull nameemailregiondistrict
1Johnjohn@email.comnorthdistrict1
1Johneastdistrict2
1John

district3

2Sarahsarah@email.comwestdistrict1
2north

district1

nullnullnullnullnull
3Frankfrank@email.comwestdistrict3
3northdistrict3
3eastdistrict3
3southdistrict3
4Janejane@email.comsouthdistrict1



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 identifierfull nameemailregiondistrict
1Johnjohn@email.comnorth, eastdistrict1


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

Tags:
Not what you were looking for? View more on this topic or Ask a question