Passing Array to a sub report with SQL Command

[porsche2k7]

Hi All,

I have a parameter with multiple values called First_Name with values like: John, Mike, Paul).Crystal treats the multiple values parameter as an array. I need a formula to join these to a string like: 'John','Mike','Paul' .

I plan to use this string as a parameter within a sub report to obtain distinct count of Sessions for these first names. Something like : Select count(distinct Sessions) from table.name where Column_FirstName in {?First_Name}

I could use join function in the main report and split function in the sub report, however due to distinct counts & better performance, I intend to use the SQL command. Please suggest.

 

[Madawc]

Any parameter you use in the main report can also be passed down to the subreport Edit > Subreport Links. Have you tried this?

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Commands do not except multiple value parameters, so you would have to enter values like this: 'Dave','Joe','Bill' at the prompt, and then in the command use: table.name in ({?Names}). Then you can link the subs on the name field as Madawc suggested.

-LB

 

[BobDennison]

I have the same issue. I have tried what you suggested, but the subreport gets the data as a string so when it tries to select records based on this string of values it finds nothing that matches. How do I change that string to a set of data to use in the selection wizard?

 

[lbass]

After you run the command the names will appear in the body of the report in separate rows or as separate groups, so you would then link the name field (not the parameter) to the subreports.

-LB

 

[porsche2k7]

Thanks Madawc, lbass, bob.

It makes sense to enter the parameters as 'Dave','Joe','Bill'. However, the parameter is a List of Values from Business Views and lot of values which would make it difficult for the user to enter multiple names as a string. My current design uses a main report which reads the list of values and then passes them to a sub report which uses a sql command for improved performance. I wanted to create a formula which outputs the values of the multiple value parameter as 'Dave','Joe','Bill' and pass the same to the sub report.

 

[briangriffin]

Not sure how completely this would solve your issue, or if it's even an option for you, but one of the best features of CR2008 is multivalue parameters in command objects.

 

[lbass]

So I had it backwards, and you are asking how to create a string generated by a main report that will be accepted by a command in a subreport? The following faq might be helpful. Although it describes using a sub to feed a command in a main report, you should be able to adapt the principles: faq766-6779.

-LB

 

[porsche2k7]

Yes I was asking about how to create a string generated by a main report that will be accepted by a command in a subreport.

Thanks much lbass for the reference. I will look into it.