Report Design: Question on shared variables

Print Page | Close Window

Question on shared variables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12194
Printed Date: 28 Apr 2024 at 9:37pm

Topic: Question on shared variables

Posted By: Nav522
Subject: Question on shared variables
Date Posted: 31 Jan 2011 at 7:45am

Hello Folks,

Am having trouble in pulling the shared variables from the subreport to the main report and suppress the main report records based on the shared variable.

I have the main report with Report Header suppressed,Page Header Suppressed.Detail section shows field called code.

Report Footera has the subreport1

and ReportFooterb has the subreport2

Subreport1 has everything suppressed except details. And i have the shared variable placed in the reportheader suppressed and it is

@shared1 is

whileprintingrecords;
Shared Stringvar subroline;
subroline := mailto:%7b@Substring2 - {@Substring2 };

subreport2 has the same thing but i have different variable name

@shared2 is

whileprintingrecords;
Shared Stringvar subro;
subro := mailto:%7b@Substring2 - {@Substring2 };

So finally am bringing in the two shared variables into the main report

@sharedmain1 is

whileprintingrecords;
Shared Stringvar subroline;

@sharedmain2 is

whileprintingrecords;
Shared Stringvar subro;

and i have placed them in the main report page header and suppressed them

Ultimately Under suppress of

Detail section of the main report has the condition if @sharedmain1 = left(code,21) then True Else False.

But for somereason i still see the records that are supposed to be suppressed. I dont know wat am missing. Any help?

Replies:

Posted By: DBlank
Date Posted: 31 Jan 2011 at 7:52am

You stated that you have the Main report page header suppressed and that the sub report is placed int eh page header.

When you supress a sectiont hat a subreport resides on it does not run the sub report at all.

Also I assume this is a static result fromt her sub report so by placing it on a page header you are running it multiple times when it only needs to run once.

To get it tested move your shared varibals in the main report onto your detailsectino to see if you get the expected variable results

Posted By: Nav522
Date Posted: 31 Jan 2011 at 7:56am

Hi there Thanks for getting back.

No I have the shared variables placed in the main report page header

i.e.

@sharedmain1

and @sharedmain2

Subreports are placed in reportfooters.

And btw i tried to check what you had said. I tried to pull in the @sharedmain1 and @sharedmain2 into the detail section and they show blank. And also there is no linking to the subreports.

Posted By: DBlank
Date Posted: 31 Jan 2011 at 8:00am

Let me make sure i understand.

You are trying to suppress rows inthe main reprot based on values from the sub report using shared variables correct?

If the value is being generated from the sub report, the sub report has to reside in a section above the location that is is going to be used (e.g. report header). Then it can be passed to the main report in a section below it (report header B).

and then those values can be used in the sections below it (group headers, details or any footer).

Posted By: Nav522
Date Posted: 31 Jan 2011 at 8:07am

Yes thats correct.

----------------------------

Okay i tried to place the subreports in ReportHeader.

Now the shared variable is populating the data in the detail section but its duplicating.

and after investigating the duplicating record seems to be the 1st record of the subreport1.

I dont think its pulling in all the records correclt into the main report?

Posted By: DBlank
Date Posted: 31 Jan 2011 at 9:28am

BY placing a subreport in a header it will always only return one value because it only runs once.

It soundsl like you are trying to supress detial rows in the main report that have mathcing rows in the sub reports.

Maybe if you start frm scratch on exoplining your data and what you need to do?

Posted By: Nav522
Date Posted: 31 Jan 2011 at 9:44am

Okay i will try to explain.

The report object "code" in the main report detail section has the data as following.

1TRAN053-0000134-2011C

1TRAN033-0000123-2011C

1TRAN023-0000123-2011C

The subreports report object has the data of kind

1TRAN053-0000134-2011Y

1TRAN033-0000123-2011Y

My requirement is if the subreport rows matches the main report rows then subreport has precedence over the main report data i.e. code with 'Y' stays on the report(Subreport Rows) and the code with 'C' is suppressed(Main report detail rows)

So in our case for example

1TRAN053-0000134-2011C

1TRAN033-0000123-2011C

should be suppressed in the main report because those two rows are present in the subreport with Y only the change.

so thats the reason i have the formula in the main report detail section under Suppress saying

@sharedmain1 = left(code,21) then True Else False.

Does it makes sense now?

Posted By: DBlank
Date Posted: 31 Jan 2011 at 9:53am

What is the common element to know that

1TRAN053-0000134-2011C

is matching to

1TRAN053-0000134-2011Y

Posted By: Nav522
Date Posted: 31 Jan 2011 at 9:58am

Well i have created a formula for that as @substring

Left({Command.'1TRAN'||ECF.CLIENT_FIELD_DATA},21) in the main report.

Posted By: DBlank
Date Posted: 31 Jan 2011 at 10:00am

so these come from two different tables that you cannot join on hence the use of a subreport?

Posted By: DBlank
Date Posted: 31 Jan 2011 at 10:15am

I am asking because as it appears now you will have to run the sub report on multiple detail section which means at least one run per row in your report (maybe group header if you are grouping some how) and just crush performance.

If you can join or union these tables you can avoid the subreport altogther.

Posted By: Nav522
Date Posted: 31 Jan 2011 at 10:53am

Well am using the command option for all these reports. Because each query has different logic inside but looking at the same 3 tables. So if i use Group by and place the subreports will it work then. Now i understood the concepts of shared variables. Do u think we can acheive this with manipulation in the shared variables? or if we do union of these tables how can we acheive the precedence condition('Y' over 'C')

Thanks

Posted By: DBlank
Date Posted: 31 Jan 2011 at 11:04am

If you have a forced heirchy and can use outer joins i would use a command to outer join them join them together using a case statment to drop the last letter and join on that field (I'll call it PrimaryID).

Then use a fomrula field to get the value you want.

If NOT(isnull(Yfield)) then Yfield else Cfield

Or you can union them together.

create a formula field that drops the last letter of your field and group on that (called PrimayID)

Then group on that same PrimaryID suppress details and do a MAX value in the group footer to grab the Y over the C.

Posted By: Nav522
Date Posted: 31 Jan 2011 at 11:22am

I think i understand what you are saying but am pretty much amateur in query writing. Am not sure of rewriting 3 different queries into a UNION. I will give a shot though. But thanks for the idea.

Posted By: DBlank
Date Posted: 31 Jan 2011 at 11:25am

What are your 3 queries?

Posted By: Nav522
Date Posted: 31 Jan 2011 at 11:41am

select c.client_code,sh.change_date, c.client_name, '1TRAN' || ecf.client_field_data || 'C'

from event e, status_history sh, event_client_field ecf, client c

where e.event_id = sh.event_id

and e.event_id = ecf.event_id(+)

and e.client_id = c.client_id

and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'

and e.client_id = 3180

and sh.current_status_code = e.event_status_code

and sh.event_case_id is null

and sh.current_status_code in ('REJECTAPPR', 'CLOSEAPPR')

and (e.reject_code not in ('A/O-BR REJ', 'BRANCH REJ') or

e.reject_code is null)

and trunc(sh.change_date) = trunc(sysdate -1)

-----

select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'

from event_client_field ecf, event e, client c

where e.event_id = ecf.event_id(+)

and e.client_id = c.client_id

and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'

and e.client_id = 3180

and e.event_status_code = 'OPEN'

and trunc(e.created_date) = trunc(sysdate)-1

-----

select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'

from event_client_field ecf, event e, client c

where e.event_id = ecf.event_id(+)

and e.client_id = c.client_id

and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'

and e.client_id = 3180

and e.event_status_code = ('WAREHOUSE')

and trunc(e.created_date) = trunc(sysdate -1)

Posted By: DBlank
Date Posted: 31 Jan 2011 at 11:57am

select c.client_code,sh.change_date, c.client_name, '1TRAN' || ecf.client_field_data || 'C'

from event e, status_history sh, event_client_field ecf, client c

where e.event_id = sh.event_id

and e.event_id = ecf.event_id(+)

and e.client_id = c.client_id

and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'

and e.client_id = 3180

and sh.current_status_code = e.event_status_code

and sh.event_case_id is null

and sh.current_status_code in ('REJECTAPPR', 'CLOSEAPPR')

and (e.reject_code not in ('A/O-BR REJ', 'BRANCH REJ') or

e.reject_code is null)

and trunc(sh.change_date) = trunc(sysdate -1)

UNION

select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'

from event_client_field ecf, event e, client c

where e.event_id = ecf.event_id(+)

and e.client_id = c.client_id

and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'

and e.client_id = 3180

and e.event_status_code = 'OPEN'

and trunc(e.created_date) = trunc(sysdate)-1

UNION

select c.client_code,e.created_date, c.client_name,'1TRAN' || ecf.client_field_data || 'Y'

from event_client_field ecf, event e, client c

where e.event_id = ecf.event_id(+)

and e.client_id = c.client_id

and ecf.client_field_name = 'CLIENT LOSS IDENTIFIER'

and e.client_id = 3180

and e.event_status_code = ('WAREHOUSE')

and trunc(e.created_date) = trunc(sysdate -1)

Posted By: DBlank
Date Posted: 31 Jan 2011 at 12:00pm

or from a glance it looks like you can rework this into one slelect statment and use 2 case statements to decide on "C" or "Y" and change_date or create_date

with a WHERE based on your status_code

Posted By: Nav522
Date Posted: 31 Jan 2011 at 3:05pm

well i have used the 3 queries as a union and created a separate report. Droped the last letter of the field and created a formula. Grouped on that formula and suppressed the Detail section. FInally created a Max on the field in the group footer. Looks like it suppressed the Y digit rows rather than the C digit rows. Any ideas?

Posted By: DBlank
Date Posted: 01 Feb 2011 at 4:00am

so you have a maximum(C_or_Y_field,group_formulafield) in the group footer.

Is it giving you the field that you expect or want to show?

If so then you can suppress the details section as

NOT( maximum(C_or_Y_field,group_formulafield) = C_or_Y_field)

Posted By: Nav522
Date Posted: 01 Feb 2011 at 4:49am

Yes i have a maximum(C_or_Y_field,group_formulafield) in the group footer. And its not showing the record that i need. Btw I have suppressed the Detail section(which has (C_or_Y_field,group_formulafield) field in it) i.e. in the Section Expert i have checked the mark as Suppress. So do i have to Suppress the entire Group Footer and instead show the Detail Section with the formula under suppress as

NOT( maximum(C_or_Y_field,group_formulafield) = C_or_Y_field)

thanks for ur patience

Posted By: DBlank
Date Posted: 01 Feb 2011 at 5:01am

Lets focus on the MAX issue first.

If the detail section is not suppressed you have one field that has the string + Y or C in it like:

1TRAN053-0000134-2011C

1TRAN053-0000134-2011Y

Correct?

YOu used an insert summary fucntion on that same field as a MAXIMUM with a summary location in the group footer, correct?

In the group footer if there is any row for that group that has Y it is showing the Y value, otherwise it is showing the C value.

This part is not working for you?

Posted By: Nav522
Date Posted: 01 Feb 2011 at 5:11am

If the detail section is not suppressed you have one field that has the string + Y or C in it like:

1TRAN053-0000134-2011C

1TRAN053-0000134-2011Y

Correct?

Yes thats right

YOu used an insert summary fucntion on that same field as a MAXIMUM with a summary location in the group footer, correct?

Yes thats right

In the group footer if there is any row for that group that has Y it is showing the Y value, otherwise it is showing the C value.

This part is not working for you?

Yes i have a problem here. Here for each Detail the Group Footer is showing a record. And also instead of suppressing C value its showing Y value. How does it knows to suppress the C value. Does MAX takes care of that.

Posted By: DBlank
Date Posted: 01 Feb 2011 at 5:21am

You have one group level correct?

I think you are grouping on the C/Y field.

This group should grouped on the formula field that strips the C/Y

left({C_Y_field},len({C_Y_field})-1)

Posted By: Nav522
Date Posted: 01 Feb 2011 at 5:30am

I have only 1 group yes thats correct. And am grouping it on the formula which has

left({C_Y_field},len({C_Y_field})-1).

And having a maximum on {C_Y_field} in the footer. Looks like am doing everything right not sure what else is missing

Posted By: DBlank
Date Posted: 01 Feb 2011 at 5:35am

So using our sample data you should have something like

GH - 1TRAN053-0000134-2011

Det - 1TRAN053-0000134-2011C

Det - 1TRAN053-0000134-2011Y

GF - 1TRAN053-0000134-2011Y

but you get this

GH - 1TRAN053-0000134-2011

Det - 1TRAN053-0000134-2011C

Det - 1TRAN053-0000134-2011Y

GF - 1TRAN053-0000134-2011C

Is that correct?

Posted By: Nav522
Date Posted: 01 Feb 2011 at 5:45am

I think something is wrong in the queries that i had UNIONED. I get the Detail records both as similar. Please see below. Its not picking up the 1TRAN053-0000134-2011Y.

My Data:

GH - 1TRAN053-0000134-2011

Det - 1TRAN053-0000134-2011C

GF - 1TRAN053-0000134-2011C

I think i have to look into rewriting these queries. And then follow ur method.

Posted By: DBlank
Date Posted: 01 Feb 2011 at 5:49am

look at all of the groups and see if you have ANY "y" values.

If not your query is likely not doing what you want.

Otherwise it may be and the items you are 'testing' just all happen to not have any 'Y' rows.

Print Page | Close Window