Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Report/Subreports-Stored Procedure parameter issue Post Reply Post New Topic
Author Message
reubenCanowski
Newbie
Newbie
Avatar

Joined: 12 Dec 2012
Location: United States
Online Status: Offline
Posts: 5
Quote reubenCanowski Replybullet Topic: Report/Subreports-Stored Procedure parameter issue
     Posted: 12 Dec 2012 at 10:36am
I'm using CR 2008. I'm using the command line utility 'Crystal Reports Exporter' to convert my report to PDF. It's a large report with 6 sub reports. The report used to run with user input values for 2 parameters (date and a property ID) - the values were just inserted into the SQL code. But now I need the parameters to be shared between each section of the report so at the time of execution the two parameter values can be inserted via command prompt for the export.

I'm very new to CR and would appreciate any help! :)
I can only image I'm missing info that would make it easier to receive any help, so let me know if I can include any other details.
Thanks
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 13 Dec 2012 at 4:30am
1.  Set up the parameters in the main report.  If the main report uses a command instead of tables, you'll need to set them up in the Command Editor, not in the report itself.
 
2.  Set up the parameters in the subreports - again, set them up in the Command Editor if you're using commands.
 
3.  In the main report, right-click on the subreport and select "Change Subreport Links".
 
4.  In the Subreport Links window, move one of the parameters from the top-left to the top-right.  In the bottom-right UNCHECK "Select data in subreport based on field:".  In the bottom-left, select the corresponding parameter that's in the subreport.  This will set the subreport parameter value to be the same as the corresponding parameter value from the main report.
 
5.  Repeat Step 4 for the other parameter.
 
6.  Repeat Steps 3-5 for all subreports.
 
This should get you what you're looking for.
 
-Dell
IP IP Logged
reubenCanowski
Newbie
Newbie
Avatar

Joined: 12 Dec 2012
Location: United States
Online Status: Offline
Posts: 5
Quote reubenCanowski Replybullet Posted: 13 Dec 2012 at 4:35am
Thanks for the reply. I'll give that a shot.
IP IP Logged
reubenCanowski
Newbie
Newbie
Avatar

Joined: 12 Dec 2012
Location: United States
Online Status: Offline
Posts: 5
Quote reubenCanowski Replybullet Posted: 14 Dec 2012 at 10:01am
Whenever I set up my parameters in the command editor and click 'OK' to accept my changes, they disappear from the Parameter List upon re-entering the command editor.

I've made some changes to my initial plan of attack:
-No longer calling stored procedure. The report and sub reports all have their own commands; each command declares my two parameters.

Despite my parameters disappearing from the parameter list I think I've successfully linked up the parameters for each report. Upon execution of report ( or at least a print preview) I'm only prompted for  each parameter once - which is an improvement from what else I've experienced. However, I'm not retrieving any records to populate my report, only getting a blank report.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 17 Dec 2012 at 3:50am
I'm sorry, I should have been more clear in my response.  You have to use the parameters in the Command in order for them to "stay" in the Command Editor.  In general, when you write a command, you need to filter the data in the Where clause of the SQL instead of having Crystal do the filtering by using the Select Expert.  When you use the Select Expert with a command, Crystal will pull ALL of the data from the query into memory and filter it there.  When you filter the data using the Where clause of the SQL in the command, the filtering is pushed to the database and only the data that meets the filter requirements is returned to the report.
 
-Dell
IP IP Logged
reubenCanowski
Newbie
Newbie
Avatar

Joined: 12 Dec 2012
Location: United States
Online Status: Offline
Posts: 5
Quote reubenCanowski Replybullet Posted: 17 Dec 2012 at 7:23am
OK. Switched back to stored procedures for all of my report/subreports (hopefully for the last time) and all my parameters are "staying" in the CMD editor, as well as linked to each other. Upon previewing my report all results seem to show/seem correct. However, when I attempt to run report through Crystal reports exporter I receive error this error:
           "Error in File myReportFile.rpt: Operation illegal on linked parameter."
Each report uses the same 2 parameters. When passing parameters to report from command prompt I only send the 2 parameters once, but the prompt indicates that it's sending these values to the parameters for each report.

Any help would be greatly appreciated.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 17 Dec 2012 at 8:41am
What is "Crystal reports exporter"?  If it's third-party software, you'll may have to contact the folks who provided it.
 
Another option would be to preface the names parameters in the subreports with "PM-".  This would trick the software into thinking that they're regular subreport linking parameters instead of stand-alone parameters.
 
-Dell
IP IP Logged
reubenCanowski
Newbie
Newbie
Avatar

Joined: 12 Dec 2012
Location: United States
Online Status: Offline
Posts: 5
Quote reubenCanowski Replybullet Posted: 17 Dec 2012 at 9:29am
Figured it out!

Crystal reports exporter is for converting your CR to a different format (in my case .pdf) - http://www.rainforestnet.com/crystal-reports-exporter/

But back to my solution - I deleted all of my subreport  links and it worked perfectly. For some reason with Crystal Reports Exporter all I needed to do was pass my parameter values once for each parameter (Not sure why I only had to pass it once, but they are all named the same?) and the values were sent to the parameters for every report/sub report.

Thanks for your help hilfy.
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum