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

Dear all,

I think that everything is said in the title:
I'm new to Crystal Report 2008 and I have to make some tests in order to see if some functionnalities found in Cognos are possible to use in Crystal Report 2008.
One of the most important thing to me is passing a multiple value parameter to my function in a oracle package.
I have tried a lot of things, and also cast the "array" of the parameter as a comma separated string with quotes, It could be really usefull to me but I can't pass it too to the function.

Thanks for your help.
csi-consultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wykabryanCommented:
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.
0
csi-consultingAuthor Commented:
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
0
wykabryanCommented:
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.
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

James0628Commented:
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
0
wykabryanCommented:
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.
0
mlmccCommented:
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
0
James0628Commented:
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
0
csi-consultingAuthor Commented:
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 ....
0
wykabryanCommented:
There are differences between cognos and crystal. This is one of them. There are benefits and draw backs to both from a functionality stand point. I currently work in a Cognos 8 environment as well, but my background is in Crystal. I have to say I am more impressed with the streamline of crystal than the choppiness of Cognos. But this thought only highlights the bias that I show. I believe you will be able to get alot of similarities between the two but do not be surprise on the differences.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
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
0
csi-consultingAuthor Commented:
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
0
James0628Commented:
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
0
mlmccCommented:
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
0
James0628Commented:
Well, if I'm following correctly, all he wants to do is pass a string containing multiple values to a function/procedure in Oracle and that function/procedure would interpret it, so CR doesn't need to "handle" the string.  CR just needs to pass it along to Oracle.  The only trick here is getting the multiple values into the string, which is what his app would do.  Then the app would pass the string to CR, which would then pass it to Oracle.

 I think.  :-)

 James
0
mlmccCommented:
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
0
csi-consultingAuthor Commented:
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
0
James0628Commented:
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
0
mlmccCommented:
You shouldn't need the server version.  You should be able to run the reports from ASP or HTML on your web page.  

Multiple parameters are passed in an array and not as comma separated strings.  So if that is required you will have to build them yourself in the web page and pass them as single parameters to Crystal.  Crystal can then pass them to the database and it can use them as comma separated strings.

mlmcc
0
csi-consultingAuthor Commented:
Hi mlmcc,

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

Regards,
vinc.
0
mlmccCommented:
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
0
csi-consultingAuthor Commented:
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.
0
James0628Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.