Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: how to get base report parameter in sub report? Post Reply Post New Topic
Author Message
sur200
Newbie
Newbie
Avatar

Joined: 18 Feb 2009
Location: India
Online Status: Offline
Posts: 11
Quote sur200 Replybullet Topic: how to get base report parameter in sub report?
     Posted: 19 Feb 2009 at 3:27am

hi all,

 
First of I would like to make the attention to moderators/owners of this site.
 
I was creating a new post and have almost done but accidently pressed CTRL + R and it has gone for new post again and my previous post that i was going to post.
and when i press back button to check if I can get it again because below is the post that i have typed and lost becoz of CTRL + R then the edit box for writing the post was empty
 
So I request if possible then either prompt before showing navigating to new post again, so that accidently users can't loss there posts before posting it.
 
 
I hope i have cleared what I want to say.
 
 
Now here is my question again and hope this time I will do copy after typing some text and will not press any other shortcut key:
 
I have main report and a sub report and i am accepting one parameter in my main report say nRecNo.
e.g. main report command:
select * from mytable where recid = {nRecNo}
 
now in my sub report command I want to access this parameter (nRecNo) to fetch records
something like this:
select * from myOtherTable where someField = {baseReportCommand.nRecNo}
 
I have related the sub report with main report by using (Report > selection formula > record):
{Command.code} = {?Pm-Command.recid}
but I need to put base report parameter in sub report command's where condition (if it is possible).
 
Is it possible to access base repot parameter in sub report's command?
 
TIA,
Surinder Singh
 
 
 
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 19 Feb 2009 at 3:52am
Hi
What you can do is create a formula place the parameter {?RecNo} in it,Then in design view right click Sub-Report  and select Change Subreport Links now move the formula field that you have created to Fields to link to once done that a new parameter will be created named {?PM-ParameterName} also make sure you uncheck the box Select sub-report data based on field in below right.
 
Then go to sub-report record selection formula and add filter
{Table.Fieldname} = {?PM-ParameterField Name}
 
Cheers
Rahul
IP IP Logged
sur200
Newbie
Newbie
Avatar

Joined: 18 Feb 2009
Location: India
Online Status: Offline
Posts: 11
Quote sur200 Replybullet Posted: 19 Feb 2009 at 4:14am

Thanks Rahul,

 

currently i am doing in the same way and this is the formula that i am using to relate this report with base report

{Command.recno} = {?Pm-Command.nRecNo}

But my question was if i can put ?Pm-Command.nRecNo parameter in where condition of sub report command i.e.

select * from someTable where recno = {?Pm-Command.nRecNo}
but here i am sure that SQL server will not get {?Pm-Command.nRecNo} and will give me error.
 
Now Question is why i want to do it?
 
I think the sub report command will return me all the rows without where condition and then will relate to the base report parameter.
and that will be really not a good thing for any program to fetch all the records and after that show only some records to user.
 
 
In my main report I will get only one record and in sub record i can get more than one record based on main reports parameter i.e. nRecNo here.
 
so i just want to fetch only those records in sub report that match with base report parameter (nRecNo), which is currently i am doing by
 
{Command.recno} = {?Pm-Command.nRecNo}
 
 
I hope I have made my question clear.
 
TIA,
 
 
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 19 Feb 2009 at 5:34am
Hi
 
If performance is issue then why don't you create a view using SQL because subreports are basically like joins ,see if you can create a view and eliminate sub-report all together.
 
Cheers
Rahul
IP IP Logged
sur200
Newbie
Newbie
Avatar

Joined: 18 Feb 2009
Location: India
Online Status: Offline
Posts: 11
Quote sur200 Replybullet Posted: 19 Feb 2009 at 7:54am
Thanks Rahul for your valuable suggestion!
 
 
 
I will come up with more questions later Smile
 
 
 
 
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