| Author |
Message |
jpterry
Newbie
Joined: 09 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
  
Topic: Parameters Passed to Subreport
Posted: 27 Jun 2012 at 3:08am |
|
My main report contains 5 parameters that are passed to a SQL stored procedure, 4 of which are common to the subreport that I would like to pass to a different stored procedure. As it is, when I start up the report, I'm prompted to answer both sets of parameter prompts (for both the main and subreport), but have to replicate my answers. Can I pass the duplicate answers on to the subreport obviating the need to answer the duplicate prompts? JPT
|
 IP Logged |
|
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Jun 2012 at 10:10am |
|
I am not sure about a stored procedure (SP). But if you have prompts (CR Ver 9 and beyond) set up in a command, I know work. I would think that if you data source is a SP, then you should be able to set up prompts in the sub report and it would not prompt you twice.
|
| IP Logged |
|
jpterry
Newbie
Joined: 09 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 27 Jun 2012 at 11:40am |
These are two different stored procedures (the same as if there was a different SQL statement in the command for both the main and the subreport). Are you saying that if the SQL in each command had the same declared variables that would only prompt once? JPT
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Jun 2012 at 11:51am |
|
Are you using a command or just the SP? If you are using a command and the parameters are defined in the command and you have linked the main report's parameters to the sub-report parameters, yes.
Also if you are just using the SP like a table (something I have never tried) and are using parameters, then you would also have to have parameters in the subreport that you can link to.
I will see if I can find a SP that I can test with.
I hope this helps.
|
| IP Logged |
|
jpterry
Newbie
Joined: 09 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 27 Jun 2012 at 12:03pm |
I have copied stored procedure SQL statements directly into a command, including the declared variables (the only exception being where the declaration happens, before or after the "AS" statement, which makes them global or local variables). In my case, they are before, making them global. When I try to link any or all of the variables to the subreport, it creates additional variables in the subreport, but as of this moment, can't find a way to link them to the variables already within.
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Jun 2012 at 12:46pm |
|
Well I did a little testing. When using the SP like a table and dropping into a report. It creates a prompt and it is just a matter of linking the main report prompt to the sub report prompt. I would need to so some testing with a SP in a command.
Lots of luck.
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Jun 2012 at 12:59pm |
|
I was able to get it working in a command. I just had to set up the parameter in the command and use it in the parameter list for the SP. Example: Exec GetCase {?ID}, the parameter name is defined as ID. I did the same for the sub report and linked the parameters.
I hope this helps.
|
| IP Logged |
|
jpterry
Newbie
Joined: 09 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 28 Jun 2012 at 3:31am |
|
In the Designer, I simply select and connect to a stored procedure just like I would any table in the database (there's no execute command involved). If I setup the variables between the "Create Procedure" and the "AS" statement, the Designer automatically recognizes these as global variables and prompts me for the values. If I declare the variables after the 'AS' statement then they are local variables and there is no prompt. I'm doing the former in both my stored procedures and therefore the Designer prompts me for both sets of variables. I'd like to link the common variables (there are 4 of the 5 that are common) between the main and subreport so that I don't have to duplicate answers. JPT
|
| IP Logged |
|
jpterry
Newbie
Joined: 09 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 29 Jun 2012 at 10:13am |
|
Discovered the answer: I just didn't know how to use the Subreport Links screen properly (I just had to de-select the "Select data in subreport based on field" for each parameter, and only then select the variable from the "Subreport parameter field to use:" drop-down list.
|
| IP Logged |
|
|
|
