subreports and parms 3

[jwa6]

hello

CR 8.5
windows xp
Oracle 9i db


I have a sub report in a main report. I would like to use the same parms for both sub and main. I am prompted for the parms 2x when running the main report
IE:

county
year
county
year

this is not as I had expected. I see no work around in the help files.

The county field comes from a pick list in the parm itself.

the record selection:
{VW_COUNTY_CODE.CCAP_CNTY_NAME} = {?County} and
The year fiels is a formula like so:
(Year ({DAR_INCIDENT.INCIDENT_DT}) = ToNumber({?Year}))

that year is taken out odf an oracle date/time.

mm/dd/yyyy:hh:mm:ss

If I could - I would pass these as a "shared" to the sub
and use a record select statement in the sub - but 'shared' varibles arent allowed unless 'printing records'.

I could use a link to the sub...( which needs to run in the main report footer) but...I cant see how to get the year out of the date field( formula) to a formula doing the same for a date field on the sub for selection. There is only the database fields to pick for the links from main to sub.

straight dates wont work it seems - we use oracle date/time here.

help!!!!

 

[synapsevampire]

Remove the parms from the subreport and point the main report parms at the table fields in the subreport.

-k

 

[Madawc]

If you want to find the year for a date, try Year({your.date}). I think this would work even for dates derived from formulas.

Madawc Williams (East Anglia, UK)

 

[jwa6]

thanks for both the posts!

I have tryed to hook up the parms from main to the db table fields on the sub.
IE:
link:
{?County}(main) = {VW_COUNTY_CODE.CCAP_CNTY_NAME} (sub)

{?Year}(main) as '2003' = ({DAR_INCIDENT.INCIDENT_DT})

({DAR_INCIDENT.INCIDENT_DT})is an oracle date/time mm/dd/yyyy:hh:mm:ss

I see no way to link the {?Year}(main) parm to the sub db field...I would be linking '2003' to a date time...not just the year. I have created a formula field to extract the year on the subreport but it does not show up in the 'linking wizard'

 

[JasonL99]

Make sure you link the params in the main report to the params in the subreport. To make sure you have the right items linked the subreport parameter field to use should have the question mark in front of the field name (they are also usually near the end of the list).

If you don't link directly to a subreport field or to a subreport parameter you will get multiple prompts for the parameters (for both the main and sub reports).

 

[jwa6]

I assumed what you say is correct when I first started the subreport hookup. Its seems to follow that you could hook up
main parms to sub parms.
I do not see the parms in the subreport linkup wizaed as you suggest.

I do have the parms for the sub in the sub header and in the record select statement.

 

[rosemaryl]

Dear Jaw6,

Right click on your subreport and choose Change Subreport Links.

There are two panes visible on the screen. Choose the Main report parameter and move it to the right side pane.

Now, look at the bottom of the screen. There is a drop down box, what you might not notice is that just below the drop down arrow there is the tiniest (thinnest) spinner control you ever saw. Click the bottom to scroll down.. down and you can now select your subreport's parameter upon which to link.

Click that and then click ok on the screen to exit.

Hope that helps,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[jwa6]

I just tried this:

I hooked up the main parms {?County} and {?Year} to the sub parms( same names as above)

but I didnt click the "select data in subreport based on field"

I hooked up {?Year}to ?pm-{?Year} and {?County} to ?pm-{?County}. Then clicked 'ok'. Then opening up the 'links' wizard again, I see the {?Year} only link and no {?County}
link.

Since I have a record select in the sub based on these 2 parms I thought it should really be that easy.

IE: parm(main) to parm(sub) not based on a tables field to tie to in the sub

 

[rosemaryl]

Dear Jwa6,

What linking the Main Parameter to the Subreport Parameter does is populate the Subreport parameter with the value passed to the main report parameter.

It does not affect the selection of the data. If the intent is that you need to filter data in the subreport based upon the parameter values, then you will need to edit the selection formula in the subreport. I bet you will find your ?County parameter in the report fields list and that it will be populated with the value passed to the main report parameter.

Best regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[jwa6]

thanks ro.....

yes that was what I was hoping to accomplish:

"What linking the Main Parameter to the Subreport Parameter does is populate the Subreport parameter with the value passed to the main report parameter."

I did want to do record selection on the sub by the parms on the main report.

I didnt find the ?County parameter from the main report in the report fields list in the sub.

thanks for your efforts!!!
jim

Im on:

CR 8.5
windows xp
Oracle 9i db

 

[rosemaryl]

Did you link main report ?County to Subereport ?County?

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[jwa6]

I hooked up the main parms {?County} and {?Year} to the sub parms( same names as above)in the link wizard

but I didnt click the "select data in subreport based on"

opening up the 'links' wizard again, I see the {?Year} only link and no {?County}link.
why is the linking not saving the county to count parm link?



I am getting the year only from the main parm for a time/date oracle field


{?Year}(main) as '2003' = year({DAR_INCIDENT.INCIDENT_DT})

so the record select needed in the subreport..
{VW_COUNTY_CODE.CCAP_CNTY_NAME} = {?County} and
(Year ({DAR_INCIDENT.INCIDENT_DT}) = ToNumber({?Year}))


goal:
IE: parm(main) to parm(sub) not based on a tables field to tie to in the sub

 

[rosemaryl]

Are the parameters the same type?

I can't think why it wouldn't link. However, let's move past that....

Another way to get the value of a parameter into a subreport for use without prompting a second time is to do the following (I have used this when I have a date parameter and a datetime field in the subreport ... since they are different types I couldn't link).

Ok...

In main report create a formula called County:

//@county
//now the only value would be the parameter {?County}
{?County}

Save and close formula.

Go to Subreport/Change Subreport links.

move the formula named county to the right pane and it will generate a parameter {?PM-County}

Click ok.

Now, in the subreport, change the selection criteria to use this newly available parameter.

Next, delete the County parameter from the Subreport, changing any references anywhere in the report to the County parameter to the newly available {?PM-County}.

Try that and see if it works for you,

regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[jwa6]

thank you soooooo much for sticking w/ me on this.

this is the record selection for the sub that I had intended.


{?Year} = Year ({DAR_INCIDENT.INCIDENT_DT}) and
{?County} = {VW_COUNTY_CODE.CCAP_CNTY_NAME}


this is what the links wizard produced which ofcourse will not work.

{DAR_INCIDENT.COUNTY_CD} = {?Pm-?Year}
and
{DAR_INCIDENT.COUNTY_CD} = {?Pm-?County}

but what I did was a manual edit to:

ToNumber ({?Pm-?Year}) = Year ({DAR_INCIDENT.INCIDENT_DT}) and
{?Pm-?County} = {VW_COUNTY_CODE.CCAP_CNTY_NAME}

this did in fact work. what I was doing wrong was trying to create matching parms in the sub. link the main parms to the sub parms. I did not see the sub parms in the link wizard at all. this bit by you was what gave me the 'light'



Save and close formula.

Go to Subreport/Change Subreport links.

move the formula named county to the right pane and it will generate a parameter {?PM-County}

Click ok.

Now, in the subreport, change the selection criteria to use this newly available parameter.

Next, delete the County parameter from the Subreport, changing any references anywhere in the report to the County parameter to the newly available {?PM-County}.

Try that and see if it works for you,

I suppose being an old bald guy....I take this all to much straightforward. Its not. you have to be very creative.

thanks again! you are a great asset to this site!