Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Question on shared variables Post Reply Post New Topic
<< Prev Page  of 3 Next >>
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
Nav522
Senior Member
Senior Member


Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
Quote Nav522 Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
Nav522
Senior Member
Senior Member


Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
Quote Nav522 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Jan 2011 at 11:25am

What are your 3 queries?

IP IP Logged
Nav522
Senior Member
Senior Member


Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
Quote Nav522 Replybullet 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)

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)

 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
Nav522
Senior Member
Senior Member


Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
Quote Nav522 Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
 
IP IP Logged
<< Prev Page  of 3 Next >>
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.