Passing multiple value parameter to Oracle table function in Crystal Report 2008

in short, it wont be pretty.
the long version is this. You can not pass multiple values like a list of something in the convensional way. Crystal doesnt know how to do that. But....You can create a parameter that is a text/string and pass that in. A text/string only accepts what is entered right, so what happens when you enter something like this '123asd','12345add','dfowe'. Crystal looks at it as one string. This can be passed to the procedure. In the procedure you will need to break it appart so that it is 3 different values. Simple enough?? Not to my knowledge, it is more of a pain than anything. Additionally with this way you will introduce a new way for your end users to break the report. This is the only "work around" (if you want to call it that) that I am aware of.

Hi wykabryan,

Thanks for your answer.
I think that for the Oracle side, it shouldn't be a problem: I'm used to work with Cognos, and I've managed to pass multiple values parameters to table function / procedures. (but it seems easier in the end to work with cognos, for the moment)
As soon as I get in the end a comma separated string that I can pass as a parameter to my procedure, I'm fine.
In a test, I found something to "cast" one of the parameters as a comma separated string, with the "Join" function of crystal; but it is in the "Formula fields" category, not really parameters
So It's clear that I don't know how to use it as a parameter, if it is possible.

The other "critical point" is that the parameters have to be cascading.
I've managed to pass single value parameter to a procedure (for the moment, I give up with the table function as nothing was working), but I don't even know how to make the parameters for this procedure as cascading ones.

Thanks,
Best regards

well cast is a function of sql to make a field appear like something else.
For example: cast(12345 as varchar(5)) number_cast

Next the Join is a function of crystal, which allows you to join a string array with a delimiter. So one field would have to have all the elements in it.  Something like this:
1 straw hay weeds

and not like this:
1 Straw
2 hay
3 weeds

Your final critical point can not been done in this situation. If it was not based around a procedure, it would have no issue at all but that is a limitation of the application.

To get CR to pass a string containing multiple values, you have two basic choices:

 1) Have the users enter the values that way.  The report would have a simple string parameter and the user would enter something like "1,2,3,4" (without the quotes).

 2) Use a subreport.  The main report would just be a shell that accepted a multi-value parameter and the user would select the values they wanted.  The main report would have a formula that used Join, for example, to take the values from the multi-value parameter and combine them in one string.  That formula would be passed to the subreport as a simple string parameter.
 This is necessary because you can't use a formula as a parameter directly.  What you can do is pass the results from a formula to a subreport and the subreport can then use that as a parameter.


 I don't know what you mean by cascading parameters, so I can't really comment on that one.

 James

cascading parameters, James, is like this..

One parameter name called city.. The first prompt in this parameter is state, whereby when you select Florida it will automatically filter the second parameter called city down to just those that exist in Florida rather than every city in the USA. It is a new feature as of 11.

Cascading parameters were added with CR XI.

They will work correctly if you choose single values.  If you  allow multiple values then the second parameter can have all the valid values for all the primary parameters.

mlmcc

Thanks wykabryan.  I haven't used CR XI (or later), so I haven't seen parameters like that, but I've seen them described, so I'm more or less aware of them.  My comment was partly because I don't know if that's what the OP was actually talking about.  The obvious guess is that he's talking about the same kind of thing, but you never know.

 James

Hi all,

Thanks for your answers.
. for cascading parameters: yes, it is exactly what I was taliking about and expecting. But it seems to be quite difficult or impossible to get it my situation.
. for passing multiple value parameters to procedure: I'll try the idea n°2 with a subreport.
But to be clear, the "final target" is to see if we can get the same functionnality as in Cognos:
I call a table function in a SQL like:
"
select *
from table(my_function(parameter1, parameter2, parameter3, etc...) )
"
In Cognos, the multiple values parameters can be passed as comma separated string, so then it's easy to work on it in Oracle.
But I fear I won't be able to get the same functionnality in Crystal ....

Re: the subreport idea

 One thing I forgot to mention is that you can not have nested subreports in CR.  A subreport can not contain a subreport.  So, if your report requires a subreport, you won't be able to use the "shell" idea and have another report accept a multi-value parameter, convert that to a string and then execute your report as a subreport and pass it that string.

 James

Hi,

Ok thanks James0628, that's a really good information; so I won't try anything like that because I'm in this situation.

So, I keep on with an other question:
-> and what about calling this report from an other application ?
I've seen (quickly, I don't have much details on it) that Crystal reports can be "called" from others applications built in C++ or C# for example, so is it possible to work on parameters in this situation and so pass them comma separated string ?
Is it possible to launch a report from an other web based interface, like in Cognos using particular parameters in URL.

Thanks

I believe it's possible to build a parameter like that if you use your own code to run the report, but I don't run reports that way, so I can't say for sure.  Hopefully someone with experience in that area will jump in.

 James

Yes you can get an application to build a string like that and pass it to Crystal.  The trick will be to get Crystal to handle it correctly.

mlmcc

What application tools do you have?

Would you be using a combo box with multiple selections or several fields that the user could choose from?

mlmcc

Hi all,
Yes James0628, it is exactly like that.
I'll have a "personnal" (home made) web page, containing all the objects and datas in order to "filter" the report, and I hope that I can pass paramaters as string containing multiple values (comma separated) to CR; it is the same I've done for Cognos (still with Oracle as DB), and it is working fine.

But I don't know so many things from CR (just a few things, I'm beginning): I've seen that there is a crytal report 2008 server version, or something like that; It sounds like something I would need; If I correctly understand, I would be able then to launch report using URL, isn't It ?
Am I correct ?

If yes, are there particular specification for this, example of correct syntax ?

Thanks

Sorry.  I can't answer those questions.  I just run reports from CR, and I'm using CR 10 and haven't used the later versions.  mlmcc can probably answer some of your questions.

 James

Hi mlmcc,

Thanks for your answer. Any example possible for launching CR report from web page ?

Regards,
vinc.

I don't see any for CR2008.  You might check the Crystal download site for Samples.

https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/bobj_download/main.htm

mlmcc

Hi,
The "process" is stopped for the moment: we don't know if crystal report will be still choosen by the client, or if cognos finally will be implemented.
I still didn't find any example for launching CR report from web page, but I must admit that I've stopped searching for the moment.
I think I should close and award points to those who helped me.

Thanks again.

Based on the OP's last comment, I'm thinking an even split between all 3 of us, wykabryan, mlmcc and James0628.  As for exactly which posts to count as the solution, that's trickier, in that quite a few of them seemed to contribute something.

 James