Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: summary on Group problem 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: summary on Group problem
    Posted: 25 Apr 2009 at 3:14am
Hi
I ahve a report with some fields like:
 
         Acc_no   Campus  Collection   CalL_num       Num_times_borrowed
 title
 XXXXXXXXXXXXXXXX (GH1 Title)
 XXXXXXXXXXXXXXXXX(GH2 ITN.ID) suppressed
      xxxx1       xxxxx       xxxxx         xxxxxxx                          1
      xxxx2        xxxxx      xxxxxx       xxxxxxx                           2
 
num_times_borrowed was calculated by a @number_times_issued:
if {OND.IRN} = 0 THEN
    0
ELSE
    DistinctCount ({OND.IRN}, {ITN.ID})
 
Then I created another @formula2;
in order to summaries items in the detail section:
whileprintingrecords;
NumberVar number_issued_per_title;
number_issued_per_item := number_issued_per_item + {@number_times_issued};
 
another forumla simply to display the value:
whileprintingrecords;
NumberVar number_issued_per_item;
 
and final formula to reset the number count:
whileprintingrecords;
NumberVar number_issued_per_item := 0;
 
I found I only can put summary value (3 in the above example)in the Group foot; I wish  I could put the summary value with the group header where the Book title is.
Could anyone who has experience in this scenario please advise ?
Thanks in advance
 
John
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 27 Apr 2009 at 6:28am
You can put the num_times_borrowed in the header, as it is an aggregate function, and Crystal is OK calculating and displaying it.  The item that you wish to display, needs to be calculated as Crystal reads through the data and displays the report.
 
If there is a way to create an aggregate, sam SUM({field}, {group}) then you can display in the header.
 
DBlank usually gives this type of answer, so if I have gotten it wrong, I am sure he will correct me.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Apr 2009 at 7:57am

You are correct Lockwelle. Smile

John, is the {OND.IRN} field reused and this is why you cannot just do a DistinctCount of it rather than what you are doing? I was having a little trouble tracking the overall data process and display needs here...
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 27 Apr 2009 at 4:17pm

Hi Lockwell, DBlank,

the problem is I cannot create an aggragate by using SUM({field,{group}) because when I tried to insert a summary for @number_times_issued, it cannot pick from the list, it's strange..

the {OND.IRN} is the key link to ITD.IRN which is in detail

John
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Apr 2009 at 9:45am
Hey John,
The aggregate is not available because of the formula you are using. Crystal does things in a certain sequence. This sequence is precluding the use of the aggregate before the whilereadingrecords processing.
Perhaps this is another approach that can give you the same end result...
can you post a few lines of relevant sample data and how you want it counted (logical rules) including the issue of the 0 in the IRN field? Maybe we can assist better from that side of things.


Edited by DBlank - 28 Apr 2009 at 9:47am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 29 Apr 2009 at 6:05am
In the very worse case, you can create a subreport to return the aggregate in the header.  I have been forced to do this a couple of times.  As a subreport, you can have the formula run as desired.  why is worse, it will hit the database more often (once per item) which depending on the number of groups and server traffic can have performance hit.
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 29 Apr 2009 at 10:17pm

Hi Lockwelle,DBlank,

I was busy doing something else. I will respond ASAP.
Thank you for your patience and assistance.
 
J
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 30 Apr 2009 at 3:20am

Hi DBlank, Rockwelle,

the following are some data:

ITN TABLE

ITD.IRN     ID
4345094  A20821840B (this one and the next one reocrds are not in       the ond table, which means
4388362  A25025740B  they have never been borrowed)
4027111  A32990235B
563284    A34355286B

OND TABLE

OND.IRN     BRW       BIBISS     ITM         lAST LOAN DATE
4427798   4302092 3267119  4027111 2006-10-18 00:00:00.000
4459293   NULL        3267119 4027111 2000-07-18 00:00:00.000
788056     476968    222451 563284 2003-07-21 00:00:00.000
816580     412772    222451 563284  2007-04-19 00:00:00.000
828957     412772    222451 563284  2007-05-08 00:00:00.000
844968      412772    222451 563284 2007-04-30 00:00:00.000   
    
    


HOLDING LINK TABLE

IRN          BIB    
4027111  3267119 
563284    222451 

 

HEADING TABLE (WHERE THE TITLE IS)

IRN           HEAD
3267119  ASHRAE handbook heating, ventilating, and air-conditioning applications
222451    First steps in a retail career

ITN links to OND on irn = itm
ITN links to HOLDING LINK ON irn = irn
HOLDING LINK link to HEADING on BIB = IRN

this report's major purpose is allowed to retrieve records where 0 borrowed < 1, or < 2 or 3 borrowed, in a sense, e.g.
no. of times borrowed < 1
XXXXXXXXXX title     0
    xxxxxxx                0
    xxxxxxx                0
 
no. times borrowed < 2
 
XXXXXXXXXX title2     1  (summary)
   xxxxxxxx                 0
   xxxxxxxx                 1
 
John
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Apr 2009 at 8:30am

Couldn't just be 2 tables Wacko

You have:
Heading table with one row per book/IRN #
Holding table with one row per book/BIB #
ITN table with one row per book/IRN #
OND table with some where no match to ITN as never borrowed and possible multiple rows per book /itm#
 
Heading table <inner join bib=irn > Holding table
Holding table <innerjoin itn=itn > ITN table
ITN table <left outerjoin irn=itm > OND table (I assume you need to show allr ecords from ITN even if no related record is in OND to get your never borrowed items).
As long as you have it grouped I think you can just do a record count (not a distinctcount) on any field in OND.
First I would get rid of the double grouping (Title then ITN.ID). IN order to deal with items that may have the same title but are differnert books and still wanting to sort alpha by title I would make a formula field combing header.head and header.irn and group on that. YOuc an then suppress it and put the header.head field in that group to display the title only. 
Next I think the ITN.ID group is not needed. If I understand your data correctly it should be grouped appropriately already by using the formula field recommended above.
From here you should just be able to choose any field from the OND table and do a count on it and place that in your header. Am I missing something that precludes this from working?
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 30 Apr 2009 at 4:13pm
Hi DBlank
without grouping on the ITN.ID, it has duplicate ITN.ID.
Is it possible to pass a parameter to record group selection < 1 or 2 or 3?
 
John
IP IP Logged
Page  of 2 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.078 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.