| Author |
Message |
lima
Newbie
Joined: 05 Jul 2012
Location: United Kingdom
Online Status: Offline
Posts: 14
|
  
Topic: HELP: multi value dynamic params and SP
Posted: 05 Jul 2012 at 10:27pm |
Hi,
I'm working with Crystal XI. My problem is with parameters setup between Crystal and Oracle stored procedure. I need to have many parameters, most of them have to be dynamic, multple value ones. What I intend to do is to have main report where I accept parameters, transform multi value ones into strings and then pass them to a subreport which would in turn link to stored procedure. That's is my approach, maybe there is a better solution, if so, please let me know.
Now to the problems:
1) I need to be able to setup more than one multi value, dynamic parameters. Additionally, they have to have default value of Null/All.
For one parameter I managed to do it by using Command (where I UNION ALL table with 'ALL' record), then linking table field to command field, and then parameter. However I do not know how to do it for the next parameter.
2) I have FROM and TO dates as input parameters too. I don't know how to define them so they can be set to null. Please remember, this is the same report as described above ( the main is defined in crystal so I can set up these multi value, dynamic parameters )
Thanks! Edited by lima - 05 Jul 2012 at 11:48pm
|
|
lima
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 06 Jul 2012 at 5:02am |
As far as I know, this is the ONLY way to get multiple value params into a stored procedure - I have used it a number of times. A UNION command with an ALL record is the only way to get the All option into your prompts. You can create multiple commands that are not linked together to fill your prompts. Crystal will give you a warning that they're not linked, but that's ok. For the dates, I would set a default value of something like 1/1/1900 and make the prompt optional. This way you have a default value you can work from if a date is not entered. -Dell
|
|
|
| IP Logged |
|
lima
Newbie
Joined: 05 Jul 2012
Location: United Kingdom
Online Status: Offline
Posts: 14
|
Posted: 09 Jul 2012 at 9:54pm |
|
Thanks a lot - I will try these out.
|
|
lima
|
| IP Logged |
|
lima
Newbie
Joined: 05 Jul 2012
Location: United Kingdom
Online Status: Offline
Posts: 14
|
Posted: 13 Jul 2012 at 4:55am |
Hi again,
I've setup multiple value, dynamic parameters and it's all good - thanks. However, I am still having problems with allowing null/setting default values for dates. What I have at the moment is:
- main report with commands that link to the tables for multiple parameter values. command allows the use of 'all' for parameters. The reason for the main is to preprocess multiple parameters and transform them into strings acceptable by stored procedure.
- subreport that calls stored procedure. The parameters are passed via sub-report links from the main to the subreport. And here is where the problem starts. I cannot set the date on the main report to have a default value. Preferably, I would like the date parameter to be optional, i.e. Null allowed. However, I don't think it possible. Please let me know if it is. If not, how do I set the default for the date parameter. I would like it to be set to yesterday. I was setting it in the parameter Settings to CurrentDate - 1 but it does not work.
Please help.
|
|
lima
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 13 Jul 2012 at 5:50am |
Unfortunately, it doesn't look like you can set date params to optional, although you can set a default value on them. I would set the default value to something like 1/1/1900 and change the prompt text to include something like "(1/1/1900 for any date)" so that users will know what that means. In your main report, have a formula that looks something like this: If {?DateParam} = DATE(1900, 1, 1) then CurrentDate - 1 else {?DateParam} You'll link to the subreport parameter based on this formula instead of the date parameter. -Dell
|
|
|
| IP Logged |
|
lima
Newbie
Joined: 05 Jul 2012
Location: United Kingdom
Online Status: Offline
Posts: 14
|
Posted: 16 Jul 2012 at 12:23am |
|
Thanks. It is not pretty but it works...
|
|
lima
|
| IP Logged |
|
lima
Newbie
Joined: 05 Jul 2012
Location: United Kingdom
Online Status: Offline
Posts: 14
|
Posted: 20 Jul 2012 at 1:05am |
The plot thickens...
The setup is as I described in the previous posts - using Oracle stored procedure.
Now I have a report where I have 4 different views (i.e. subreports). I've created main report with commands where I accept and preprocess multi value, dynamic, cascading parameters. These parameters are common to all the subreports. I've also created a main (default) subreport which requires just these parameters. So far, so good.
The problem starts with other subreports. When other than default view is selected I have to prompt for and accept additional parameters. These parameters should not appear on the default view but only on relevant subreport. They are dynamic, multi value parameters and they are cascading parameters. They have dependency on at least one parameter in default subreport.
I'll provide an example so it's clear
main report
param a - multi value, dynamic
param b - multi value, dynamic, cascading (depends on a)
default subreport 1
param a and b passed through subreport links - OK
subreport 2
requires prompts for:
param c - dynamic, multivalue, cascading (depends on the param a of main report)
needs param a and b as well
I am using Crystal XI but switching within a day or two to Crystal 2011. Is it possible in Crystal 2011? Or I will just have to develop several reports - client prefers one with many views.
Can you please help again?
Thanks
|
|
lima
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 23 Jul 2012 at 4:56am |
Crystal does not handle "optional" parameters well, so you will probably be better off doing this in multiple reports even if that's not what the client wants. -Dell
|
|
|
| IP Logged |
|
lima
Newbie
Joined: 05 Jul 2012
Location: United Kingdom
Online Status: Offline
Posts: 14
|
Posted: 30 Jul 2012 at 10:49pm |
Hi again. Thanks for your help, I still have the following questions. Can you please help:
1) In Crystal 2011, is it possible to suppress parameter prompts based on a condition?
I've got a main report where I accept several parameters which are common to all the different views of the report - the report has several views. Each view has one or more additional parameters. How do I suppress the prompts for these based on the view? I tried adding a section/subreport, putting parameters relevant to the view in the section/subreport, suppressing based on the view. However, I still got prompted for these parameters anyway. What is the correct way to deal with these?
2) My report calls stored procedure. One of the stored procedure parameters is of type Date. On Crystal side it appears as Date Time while I just want Date - I don't want the user to be prompted for the time component. If I make the Crystal parameter of Date type and then want to link it to the subreport, the Date Time parameter of subreport is not selected for linking as they are of different types. How is it possible to have Date on Crystal side and then pass it on to the stored procedure through Date Time parameter?
|
|
lima
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 31 Jul 2012 at 2:20am |
1. No. There is no way I know of for sure to suppress these. One option you might play with is to use on-demand subreports. Your user would then have to click on what looks like a link in order to see the data and you could suppress these so that only the subreports that are valid for that run are available. The problem with this approach is that it only works interactively - it won't work well for scheduled reports. 2. Is the stored proc in a subreport? If so, create a date prompt in the main report, convert it to datetime in code and then link on the formula to get the format that Crystal wants for the param. -Dell
|
|
|
| IP Logged |
|
|
|
