Grouping on a Shared Variable 1

[Jumroo]

i have a main report and a subreport with a shared variable passed from Subreport to MAIN report. i'm using CR10 and Teradata


I need to group on this value. is it possible?

 

[lbass]

Not possible. You could consider making the subreport your main report.

-LB

 

[Jumroo]

Thanks Lbass for your response.

will it be possible to group on this value if i REVERSE the Main and Subreports?

like put all the displayed data in the subreport and have the main report calculate the shared value and pass it to the subreport?

 

[lbass]

Yes, you can create a shared variable in the main report and then pass it to a subreport. To answer the question of whether you can group (in the main report) on the value you originally intended to be the shared variable, I would need to know what the value is. I assumed you meant some database field, but if it's a summary of some sort, you would have to use a SQL expression in order to group on it. Note that you can limit subreport records by using subreport links, without having to use a shared variable for that purpose.

-LB

 

[Jumroo]

Thankyou Lbass for your response.

i will explain the actual issue i have.

we have a group summary field and the users wants to group the group summary based on the value > 0 or = 0.

since this is a summary field, it wasn't available for grouping and the developer went the subreport & shared variable approach thinking if that value was calculated and passed on to the main report, we can group on it.

unfortunatly since shared variables are available only when printing records....this is not a possible option.hence my original question.


hope i'm clear now. is it possible to group on a group summary value?

 

[lbass]

Is the group summary a formula or an inserted summary? If it's an inserted summary, you could use a group sort with a faux group header label. If not, then I'd need to know more specifics.

Please show the content of the formula if it's a formula. What is the group structure of the report--what fields are you grouping on? Are you using more than one table?

-LB

 

[Jumroo]

this is a formula....placed at the group level.

here are the 3 formulas.
one is a group....one is a formula and one is summary based on the formula and group.

GROUP {@Visit_Prov}
{Prov_Visit.PROV_NAME}+" - "+{Prov_Visit.PROV_ID}


@visit counts PCP
IF (
{TABLE.PCP_ID} = {TABLE.VISIT_ID}
) then 1
else
0


@PANEL PCP
If Sum ({@Visit Counts PCP}, {@Visit Prov Id})= 0
then
"Non Panel"
else
"Panel"



i need a grouping on the "PANEL" AND "NON PANEL" values for this report which is a group summary formula.

hope this makes sense to you. appreciate your help.

 

[lbass]

Can you clarify whether you just need the display by these two values "Panel" and "NonPanel" or whether you then need to create summaries for these two subgroups?

-LB

 

[Jumroo]

Yes, we need summaries at Panel and non-panel subgroup levels.

 

[lbass]

What are the actual table/field names for this formula?

@visit counts PCP
IF (
{TABLE.PCP_ID} = {TABLE.VISIT_ID}
) then 1
else
0

What fields are you grouping on in the main report? What is your record selection formula?

-LB

 

[Jumroo]

main report has 2 groups. one on the dept and one on the {@Visit_Prov} formula.


the table used is PAT_VISIT.


the selection filter just a date range filter.

{PAT_VISIT.visit_date} in (if {?Run Period Parameter}='Custom Date Range' then {?Start Date} to {?End Date}
else if {?Run Period Parameter}='Last Full Calendar Month' then LastFullMonth
else if {?Run Period Parameter}='Last Full Week' then LastFullWeek
else if {?Run Period Parameter}='Last 90 Days' then (CurrentDate-91 to CurrentDate)
else CurrentDate-1)


thanks

 

[lbass]

I was going to suggest a SQL expression, but you wouldn't be able to build in the parameter, and I'm not quite sure how to build those date criteria into a command object (see below), so the other opton would be the faux group route, but then you'd have to use variables to suppress various headers and also to collect the summary information. What would be best?

If you think you can figure out how to incorporate the date parameters into a command, that would be simpler. Then you could use a command like:

select count(PCP_ID), Prov_Visit.PROV_ID
from Pat_Visit, Prov_visit
where Pat_Visit.PCP_ID = Pat_Visit.Visit_ID and
Pat_visit.linkingfield = Prov_visit.linkingfield and
{PAT_VISIT.visit_date} >= {?StartDate} and
{PAT_VISIT.visit_date} < {?End Date} + 1
Group by Prov_Visit.PROV_ID

Then you could link this to the Prov_ID field and group on the count expression.

-LB

 

[Jumroo]

i have recommended the report to be converted to a Command based report. its upto the developer/user to decide.


Thank you Lbass . appreciate all your help.