Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Summing Issue Post Reply Post New Topic
Page  of 5 Next >>
Author Message
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Topic: Summing Issue
    Posted: 24 Oct 2013 at 3:17am
I have been asked to create a report that will show how many po's for a specific vendor are late/ontime during a specified time period. Our po's can have multiple lines and each line can have multiple receiving dates. I need to use the first receiving date for each line to determine if the line is late. The premise is: if one line is late the whole po is late.
 
I created a minimum formula to calculate if the line is late:
 
if Minimum ({RECEIVER.RECEIVED_DATE}, {PURC_ORDER_LINE.LINE_NO})>{PURCHASE_ORDER.DESIRED_RECV_DATE} then 1 else 0
 
This seems to be working fine. I then created a formula to tell me if the entire po is late:
 
if {@Late Line}>0 then 1 else 0
 
This too seems to be working. The problem is that I now need to add up how many po's are late and get a percentage of how many are late/ontime over all the po's.
 
Any ideas? I know that you can't sum the the 2nd formula or create a running total. I have also tried making this as a sub-report to carry the total through shared variables. That isn't working either.
 
I also wondered if there was a way to evaluate the first formula in the select criteria.
 
Appreciate any suggestions as this report was supposed to be finished this past July and I'm very late with it. (although everyone knows the issues of why it's late)
 
Thanks!
 
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 24 Oct 2013 at 3:35am
HI
 
Insted of saying '1 else 0', use late or intime like :
 
if Minimum ({RECEIVER.RECEIVED_DATE}, {PURC_ORDER_LINE.LINE_NO})>{PURCHASE_ORDER.DESIRED_RECV_DATE} then 'Late' else 'Intime'
 
Now go in running totals and create summary for Late like :
 
Select field as your above formula and, summary option would be : count
Evaluate : use formula and give the below conditon :
 
if {@Late Line}='Late'
 
Reset : Never
--Do the same for 'InTime'
 
Now count all orders like : count(Order_NO).
 
find the percentage
Thanks,
Sastry
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 24 Oct 2013 at 4:28am
Thank you so much for your response.
 
I went in and change the fields to late and on-time as suggested. I then went to running totals to create a summary but my @ late line (if Minimum ({RECEIVER.RECEIVED_DATE}, {PURC_ORDER_LINE.LINE_NO})>{PURCHASE_ORDER.DESIRED_RECV_DATE} then 'Late' else 'Intime') is not available to select.
 
Did I misinterpret something?
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 24 Oct 2013 at 4:40am
Yes you are right.  my falut.
 
Create the following formulas for Late
 
Place below formula in your detail and you can suppress it.  But make sure that you have this formula placed in your report seciton.
@late1
Whileprintingrecords; 
Numbervar late;       
if {@Late Line} = 'Late' Then
late:=late+1;
 
Place this formula where you want to see the total late.
@late2
Whileprintingrecords;
Numbervar late;
 
Create the following formulas for Ontime
@Ot1
Whileprintingrecords;
Numbervar late;
if {@Late Line} = 'On-Time' Then
OT:=OT+1;
@ot2
Whileprintingrecords;
Numbervar OT;
 
 
To get Average for Late :
 
 
Count(po_no) /@late2*100
 
Average for ON-TIME
 
Count(po_no) /@OT2*100
 
 
 
 
Thanks,
Sastry
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 24 Oct 2013 at 8:56am

I created the formula's but something is still off or I have them in the wrong sections.

The late1 formula is working well. It shows each line of the po as late or ontime.
 
I created the ontime1 formula and it's telling me that it needs a
number, currentcy amount, booleen, date, time, date-time, or string for the ontime that comes after the 'then'.
 
I then created the ontime 2 formula and it has no errors but is only giving me 0 as a result.
 
 
Not quite sure what else to do. I have created many reports before this one but nothing like this.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Oct 2013 at 9:17am
I think the "OnTime formula" should be using Numbervar OT;
However, I would caution on the current approach as I do not believe that it accounts for the 'multiple lines per po'. If I ma correct on that the varaibale formula evaluations would need to be changed to account for this using a previous() or next() functions.
 
Another option would be to create two running Totals.
one to count all records and a second to count late records. This can derive on time records.
(all-late)=on time.
I suggest this as your definition of a late PO may allow for a PO to vascilate between late and on time as the DESIRED_RECV_DATE field changes from po receiving line to po receiving line.
 
Running Total 1
name=Late_po
field to summarize=PO #
summary type = distinct count
evaluate= use a formula
 Minimum ({RECEIVER.RECEIVED_DATE}, {PURC_ORDER_LINE.LINE_NO})>{PURCHASE_ORDER.DESIRED_RECV_DATE}
reset=never
place in report footer
 
Running total 2
name=total_po
field to summarize=PO #
summary type = distinct count
evaluate= for each record
reset=never
place in report footer
 
late % formula is
{#Late_po}%{#total_po}
 
ontime%  formula
({#total_po}-{#Late_po})%{#total_po}


Edited by DBlank - 24 Oct 2013 at 9:19am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Oct 2013 at 9:21am
also for either the shared variable or Running Total options to work they must be used/placed in the group footers (or detail section). If you place them in the group headers they only evaluate on the first row of the group.
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 24 Oct 2013 at 9:26am
ok I will try this and see how it works out. I will let you know. Thank you for your time.
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 25 Oct 2013 at 3:45am
The running totals seem to be working perfectly! Thanks you soo much. Such a simple way to acheive this. I have  bit more testing to do to confirm all is working well. It seems though that this has now brought up something new. I was working with a 3 month period but in testing I checked totals a month at a time.
 
It seems that when one of the po lines has multiple receiving dates and they cross over months that it will bring in just those lines and count them late or ontime as requested.
 
I wonder if there is a way to filter in the parameters (possibly?) that if the first receivng line is not in the requested date period not to include any info for the entire po.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Oct 2013 at 4:11am
so you have a select statment that limits based on a date range. This is limiting your results and bring back partial sets of rows for a whole PO.
However you want the data to include all records for for any PO that had any subset row that falls in your select date range.
is that correct?
IP IP Logged
Page  of 5 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.032 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.