Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: group issue Post Reply Post New Topic
Page  of 2 Next >>
Author Message
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Topic: group issue
     Posted: 25 May 2009 at 4:38am
Hi all,
 
I have a report with the following information:
 
fund year: 01/07/2008 - 30/06/2009      (group 1)
     Parent fund:  XX  XXXXXXXX               (group2 )
          Fund details:  XXXX_XX_XXXXXX    XXXXXXXXXXXXXXXXXXXX  (Detail)
 
There is no link between parent fund and Fund Details in the databse. However, my customer asked me to group Fund details under parent fund XX.
At the moment, my report looks like the following:
 
Financial Year: 01/07/2008 - 30/06/2009
   Parent Fund:  AC    Anne Carlin
         0809_AC_HOS   0809 -- Anne Carlin -- Hospitality
         0809_DM_DIR    0809 -- Denise Morgan -- Directorate
 
   Parent Fund:  DM    Denise Morgan
         0809_AC_HOS   0809 -- Anne Carlin -- Hospitality
         0809_DM_DIR    0809 -- Denise Morgan -- Directorate
 
so on, it will continue repeating under each different Parent Fund. I have tried to use Mid function and instr function to conditionally group without success.
It looks as though some of the code like AC and DM are between '_' and '_', but not always, there are codes like XXXX_XX, or XXXX_XXX. It's very annoying situation where the codes are not consistently entered.
Could anyone who has experience in this scenario please advise a workable approach? Thanks in advance.
John
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 May 2009 at 7:41am
Hey John,
Looks like you could use the name instead of the initials.
YOu have the correct name at group level 2 which makes me think that name is in the one table and the detail row has the name in the unlinked table.
You could conditionally suppress the details where instr(details name field,Head2name field)>0
grouping won't get rid of the other details rows. You really should do this matching at the join level to avoid that. 


Edited by DBlank - 25 May 2009 at 7:42am
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 25 May 2009 at 7:10pm
Hi DBlank,
 
I followed your advices with the following(conditionally suppress):
instr({FUND_DETAILS_VIEW.FUND_NAME}, previous({INST_PARENT_FUND_VIEW.PARENT_FUND}))>0
with the above, I have got the records with the same name suppressed, all the rest records with unrelated names are still under the group 2. I meant to get rid of the records with unrelated names. could you please advise where I can improve? Thanks again.
John
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 25 May 2009 at 11:30pm
Hi DBlank,
I changed "instr({FUND_DETAILS_VIEW.FUND_NAME}, previous({INST_PARENT_FUND_VIEW.PARENT_FUND}))= 0" , then I can group associated records with the same code and name under the group2 header.
However, somehow, oen record which has the name of the first group 2(header) appear under the second header (group2):

 

Parent Fund:  AC    Anne Carlin

    CNV_AC_HAB_E      CNV -- Anne Carlin_Hair & Beauty_Electronic
   CNV_AC_HAB_G      CNV -- Anne Carlin_Hair & Beauty_General
 
Parent Fund:  DM    Denise Morgan
     CNV_AC_HAB_E   CNV -- Anne Carlin_Hair & Beauty_Electronic
     CNV_DM_DIR_S   CNV -- Denise Morgan_Directorate_Serial
     CNV_DM_RIC_E    CNV -- Denise Morgan_RIC_Electronic
 
I can't find the reason, could you advise?
Thanks,
John
 
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 26 May 2009 at 12:00am
Hi DBlank,
 
I found the reason: I should not use 'previous' function in the Instr function. After removing it, it seems OK.
By the way, is it possible to suppress the group header2 when there are no associated records to display under it?
Thank you again.
John
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 26 May 2009 at 6:07am
Hi DBlank,
 
I managed to suppress the group2 header when there are nothing in the detail section with the same line of code used in Detail section to suppress unrelated records.
Now I have got "Total for XXX XXX" at the group2 footer still appearing when there are nothing in the detail section. I don't know how to remove it. 
 As each person has allocated fund to buy things, including committed, balance. I know it's going to give me headache when calculating subtotals for each person, then another total for each period in this scenario.
 
e.g. from the report run:
Parent Fund:  SB    Selina Black
           0809_SB_ITC   0809 -- Selina Black -- Info Technology
           Total for Selina Black  (this is OK)
           
           Total for Veneta Stiles  (this has to be removed/suppressed)
 
Financial Year: 01/07/2008 - 30/06/2009
Parent Fund:  AC    Anne Carlin
            0809_AC_HOS   0809 -- Anne Carlin -- Hospitality
            0809_AC_RET    0809 -- Anne Carlin -- Retail
            Total for Anne Carlin   (this is fine)
 
Would you be able to advise if there is a good approach to suppress the group footer when there are no records in the detail section?
 
Thanks again.
 
John
           
 
     
 
 
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 26 May 2009 at 6:29am
Hi DBlank,
 
My approach by using the same line code is wrong, which suppressed most of group2 header with associated records under it after having a close look at the report.
Please ignore my previous post. I still need a fix to this problem where the group2 header can be suppressed when there are nothing under it.
Thanks in advance.
john
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 May 2009 at 7:36am
I think you can handle both the footer and header in the same way.
If I understand your need correctly, you can "flag" each detail with a 1 or 0 then use the group Sum of that to show/hide the footer or header conditionally. Make the row a 1 if there is a ercord to be shown and 0 if there are no matching records.
Create a formula field called "Flag" (or whatever).
if instr({FUND_DETAILS_VIEW.FUND_NAME}, {INST_PARENT_FUND_VIEW.PARENT_FUND})= 0 then 0 else 1
from here create a Sum of {@Flag} at the Group 2.
Conditionally suppress both the header and footer where Sum{@Flag,group2}=0.
You can also place this formula in the details to row to validate that you are getting your 1 and 0 correctly for each row.
Note that I may have inverted the 0/1 process here so adjust as needed.
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 27 May 2009 at 5:17am
Hi DBlank,
 
Your approach works nicely! Excellent!
Now I am dealing with summary of budget next to each person
I have got the following results:
 
Financial Year: 01/07/2008 - 01/07/2009                                         Budget
  Parent Fund:  SB    Selina Black
       0809_SB_ITC  0809 -- Selina Black -- Info Technology               3,000
       Total for Selina Black                                                                3,000
 
Financial Year: 01/07/2008  30/06/2009 
   Parent Fund:  AC    Anne Carlin
          0809_AC_HOS   0809 -- Anne Carlin -- Hospitality                  3,000
          0809_AC_RET    0809 -- Anne Carlin -- Retail                          3,000
        Total for Anne Carlin                                                             70,000
 
I don't know where this 70,000 come from!
When I check the formula @total_bdgt which is in the detail section (after unsuppressed), the second value for '0809_AC_RET'   is 6,000,  but the formula @display_bgt_total displayed for Anne Carlin as 70,000?
 
I have three formulae:
@reset_subtotal  (which is put in the group header 2, suppressed)
whileprintingrecords;
CurrencyVar bdt_amt := 0.00;
 
@total_budget   ( put in the detail section, suppressed)
whileprintingrecords;
CurrencyVar bdt_amt;
bdt_amt := bdt_amt + {bgt_amt};
 
@display_bdt_total  (put in the group footer 2)
whileprintingrecords;
CurrencyVar bgt_amt;
 
I also should point it out no matter how many rows of records in the detail section associated with their owner, it always displays 70,000!
 
I think this might be due to all those suppressed records, how to deal with that?
 
Could you please advise where is wrong in my report? Thanks in advance.
John
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 May 2009 at 7:33am
You are correct that it is including the suppressed items which always are going to =70,000. Suppressing an item does not exculde it from totalling unless  you indicate in your code to so that. Variables are not my thing.
I would handle this as a running total, but if lockwelle reads this he can tell you how to fix your variable. I believe the problem is in your @total budget. You need an if then statement in there to add 0 when suppressed condition is met and add the bgt_amount when not suppressed.
Something like:
@total_budget   ( put in the detail section, suppressed)
whileprintingrecords;
CurrencyVar bdt_amt;
bdt_amt := bdt_amt +
(if instr({FUND_DETAILS_VIEW.FUND_NAME}, {INST_PARENT_FUND_VIEW.PARENT_FUND})= 0 then 0 else {bgt_amt});
 
You can also do this easily with a conditional Summed Running Total.
Create a Running Total as "Total Budget" (or whatever).
Field to summarize={table.bgt_amt}
Type of Summary=Sum
Evaulate as "Use a formula" - use your condition for not suppressed as the actual formula with no "if then". I think you decided on:
instr({FUND_DETAILS_VIEW.FUND_NAME}, {INST_PARENT_FUND_VIEW.PARENT_FUND})> 0
Reset "On change of group"= Group level 2
Place this on GF2 (Running totals do not work on headers)
If you need a grand total create another RT the exact same way but Reset should be set to NEVER and then place it in the Report footer.
IP IP Logged
Page  of 2 Next >>
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.