Summing Issue
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=20150
Printed Date: 06 May 2024 at 8:01pm
Topic: Summing Issue
Posted By: cheryla
Subject: Summing Issue
Date 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 mailto:%7b@Late - {@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!
|
Replies:
Posted By: Sastry
Date 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 mailto:%7b@Late - {@Late Line}='Late' Reset : Never --Do the same for 'InTime' Now count all orders like : count(Order_NO). find the percentage ------------- Thanks, Sastry |
Posted By: cheryla
Date 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?
|
Posted By: Sastry
Date 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 mailto:%7b@Late - {@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 mailto:%7b@Late - {@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 |
Posted By: cheryla
Date 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.
|
Posted By: DBlank
Date 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}
|
Posted By: DBlank
Date 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. |
Posted By: cheryla
Date 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. |
Posted By: cheryla
Date 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.
|
Posted By: DBlank
Date 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?
|
Posted By: cheryla
Date Posted: 25 Oct 2013 at 4:31am
|
Yes that sounds correct. |
Posted By: DBlank
Date Posted: 25 Oct 2013 at 4:44am
|
you can use a
1. stored procedure with run time parameters or
2. a crystal command with run time parameters or
3. a group select
optiosn 1 and two are more efficient but require more techincal skill and possibly rights tio the DB you so not have.
option 3 is less efficient but easier.
your choice
Update: The run time parameters are assuming you want to be able to change your date range each time you run the report.
|
Posted By: cheryla
Date Posted: 25 Oct 2013 at 4:49am
| ok tyvm I will look into these options. appreciate the help! |
Posted By: DBlank
Date Posted: 25 Oct 2013 at 5:17am
|
i or someone else can help you with any of the options if you need it.
just wanted to make sure you picked which one works for your set up.
|
Posted By: cheryla
Date Posted: 28 Oct 2013 at 4:45am
|
I think it would be best to try the 3rd option (easier). getting into permissions, etc can get tricky.
This report would need to be able to run with different dates each time. I believe it will be run once a month but not for sure on this as the user may change their mind on how they want to use it.
Please advise how I can solve this issue with the date info.
Thanks again for your help.
|
Posted By: DBlank
Date Posted: 28 Oct 2013 at 4:59am
|
creaet a start date and end date param.
ifyou want to hard code these to the last month youc an chaneg it, either wise you will want to use 2 paramters fields as date types
create a formula field as
//groupflag
if {PURCHASE_ORDER.DESIRED_RECV_DATE} in {?startdate} to {?enddate} then 1
sum this at the group PO level
any group hat has a value >0 has at least one row of data that falls in your run time parameter dates so you can use this to do a group select
open the select expert and expans it to show formula if it is not already
select the 'group selection' radio button
add your group selection here
sum(groupflag,po)>0
This measn you are always pulling in all of your PO rows first, grouping and then doing a group select (not efficient). If you have a way to limit the data on PO that you know will not meet your group selection use it too as it will speed thing up.
Also note that all groups still appear in your group tree even if they do not appear in the report (omitted via the group select). The group tree is created before the group select is applied.
summarization formulas are applied to all of the data (hence the ability to use it in the group select statement) but Running Totals and variable formulas should only be applied to data that 'is left' after the group select is applied.
|
Posted By: cheryla
Date Posted: 28 Oct 2013 at 6:40am
|
I already had parameters set up for beg date and end dates. I created the "groupflag" formula as suggested and entered into my group footer for my po level. It looks like every po has a one now.
I then opened the select expert and clicked on radial button. I'm questioning the formula that you used. sum(groupflag,po)>0
I don't seem to have an option for the po part of this. Could I have placed it in the wrong area? So for now I entered sum((@groupflag))>0
I'm not seeing anything different for now.
Please advise what I'm missing. Thanks!!
|
Posted By: DBlank
Date Posted: 28 Oct 2013 at 6:58am
|
1. I forgot to mention you have to remove the date condition from the 'record select' statement. If you don't remove that then youa re not expanding your overall data set to look past the range (this is why 'every group seems to have one').
2. don't just drop the 'groupflag' formula field onto your group footer, you have to insert a summary using that formula field.
select the summary button (blue sigma/E)
use the flag formula as the field to sumamrize
set it to a sum
summary location as the group footer (for the po group)
once you create it will appear on your group footer
if you select it on your group footer then click on the select expert button it will be the default field to be used
set it to 'is greater than' and enter "0" in the value
|
Posted By: cheryla
Date Posted: 28 Oct 2013 at 7:06am
| ok I will try this and let you know thx!! |
Posted By: cheryla
Date Posted: 28 Oct 2013 at 8:02am
|
Ok I'm more confused. I deleted the select expert formula that select the info per the date range asked for by user. I summed up the groupflag into the group footer as noted. I then went into select expert under group selections and entered sum ( - 0 - {@groupflag})>0 . when I run the report it is still asking me for a date range (which I enter) then it is giving me all kinds of data form dates I don't need to see. It looks like the sum groupflag formula is adding up each receivng line of the po (over all po lines). How do I get the report not to show me all the info not within my date range?
|
Posted By: cheryla
Date Posted: 28 Oct 2013 at 8:06am
|
ugh I tried editing that formula it's really
sum( - 0 - {@groupflag})>0
|
Posted By: DBlank
Date Posted: 28 Oct 2013 at 8:15am
|
I just reread your other posts and I think I missed part of what you wanted to achieve. You wanted to exclude any PO where the first row of data is NOT in the date range. you will need to add in another group select condition. first though are you grouping on the month at all? and if so is your PO group 1? or below the month? |
Posted By: cheryla
Date Posted: 28 Oct 2013 at 8:25am
|
actually I need to select via a specified date range a group of po's that can have multiple lines and multiple receiving dates for each of those lines. Part of my criteria is to check if the po is "late". Meaning we need to look at the first receipt date per line per po. If any one of the lines are late then the whole po is late. I was able to get the report to see all of the late po's. The problem I ran into is that when I ran my report by a 3 month time period It looked fine but when I ran each month individually it didn't match up to the 3 month report. I noticed that if there was a recpt date (out of multiple dates) that fell into my date range it would grab it even though it really should've fallen into the previous months data.
ie:
po 123
line 1 recvd 1-1-12
line 1 recvd 2-2-12
this po would should up during a january month and the february month reports when it should only show up on the january report.
I hope this all makes sense. Let me know if I can clarify any further.
Thanks!
|
Posted By: DBlank
Date Posted: 28 Oct 2013 at 8:32am
|
so when you run a 'date range' you want to see any PO that had the minimum date associated to it falls in that range?
so you need to see all of teh PO detail rows (even if they fall outside the range, which I believe would always have to be later than the date range)?
|
Posted By: cheryla
Date Posted: 28 Oct 2013 at 8:35am
|
yes! :)
and hopefully not see the latter dates on the next month
|
Posted By: DBlank
Date Posted: 28 Oct 2013 at 8:42am
|
change your group select to
minimum(datefield,po) in ?startdate to ?enddate
this should give you any PO that "starts" in the raneg you enter.
let m eknow if that works for you and then what you need to do with the any 'extra' rows you see that are part of that PO but fall outside your range. NOte that if yourun it for a month at a time Any given PO should only show up one time in in the month that it 'started' in and never in any month that the extra rows appear in.
Sorry for the confusion.
|
Posted By: cheryla
Date Posted: 28 Oct 2013 at 8:46am
|
I think I just figured out my solution!!!!
I entered the select criteria to select on the minimum (recvr.recvd. date), purc_order_line_no) in beg date and end date and it all seems to be working!
Woohoo!!
I rechecked by running the report by a 3 month periord and then again by each indivisual month and everything seems to be matching/adding up correctly!!
I'm going to add my finishing touches (cosmetics, etc)
Thank you sooo much for all your help!! This site is amazing! I always find something to help me. This project finally made me have to sign up.
:)
|
Posted By: DBlank
Date Posted: 28 Oct 2013 at 8:50am
glad you got it.
 |
Posted By: cheryla
Date Posted: 21 Nov 2013 at 3:23am
|
well I jusr received this report back from the requester with all the cosmetic changes that they want done. But now I have a new situation hopefully someone can help with.
I need to hide all ontime po's. I have been able to do this but with the concept of if one line is late the whole po is late.... how do I show all of the po lines when one is late and one is ontime? currently I can either hide all the ontime or all the late po lines but not if the po has one of each. Any ideas?
If I can solve this I will hopefully done with this report-never having to see it again :)
Thanks in advance!!
|
Posted By: DBlank
Date Posted: 21 Nov 2013 at 10:01am
| how do you define "if one line is late" in data terms? |
Posted By: cheryla
Date Posted: 25 Nov 2013 at 10:24am
|
I have been able to talk with the requester and they have decided that it is okay to only show the "late" line of the po.
I have figured out how to suppress the "on-time" lines using the section expert. So far so good....
Now the issue is that I want to suppress the po # for any po's that are showing as "on-time". the formula I'm using to suppress in the section expert is: @late_line = "on_time"
For example:
po# desired rcvd date act recvd date late/on-time
32165
line 1 5-15-13 5-14-13 on-time
line 2 5-15-13 5-16-13 late
I hope this is enough info to make sense of what I am trying to do. Thanks!
|
Posted By: kostya1122
Date Posted: 25 Nov 2013 at 10:40am
| just use the same formula it should work. |
Posted By: cheryla
Date Posted: 26 Nov 2013 at 2:27am
| Actually I tried that but what is happening is that it is suppressing the example above po#. I wonder if it's because the 1st line of the po is "on_time". so it'll show just line 2 but no po# and no line 1. THe missing line 1 is ok (I cleared it with the requester) but I need the po # to show for this type of situation. Any ideas on how to make this work? |
Posted By: DBlank
Date Posted: 26 Nov 2013 at 4:02am
|
I am guessing you mean to suppress an entire PO group where everyline is 'on time'?
you can use a group condition with a min or max...
minimum(@late_line,table.po) <> "late"
Is that what you are looking for?
|
Posted By: cheryla
Date Posted: 26 Nov 2013 at 5:15am
| hmm didn't think of that. let me try it. thank you! |
Posted By: cheryla
Date Posted: 26 Nov 2013 at 5:44am
| I tried it. It's telling me that @late_line can't be summarized. And yes I am trying to suppress an entire group where every line is "on_time". |
Posted By: kostya1122
Date Posted: 26 Nov 2013 at 6:12am
| what formula you use to display late/on-time field? |
Posted By: cheryla
Date Posted: 26 Nov 2013 at 6:16am
|
if Minimum ({RECEIVER.RECEIVED_DATE}, {PURC_ORDER_LINE.LINE_NO})>{PURCHASE_ORDER.DESIRED_RECV_DATE} then "late" else "on_time"
this is my late line formula
|
Posted By: kostya1122
Date Posted: 26 Nov 2013 at 6:49am
|
if you rewrite this formula as
{RECEIVER.RECEIVED_DATE}>{PURCHASE_ORDER.DESIRED_RECV_DATE} then "late" else "on_time" Dblank's solution will work, but i'm not sure if this will bring correct results. |
Posted By: cheryla
Date Posted: 26 Nov 2013 at 7:54am
|
Would I put this in the section expert or the po field itself? |
Posted By: cheryla
Date Posted: 26 Nov 2013 at 8:01am
|
I just realized what you were trying to do. sorry!
If I change the formula then I will lose the other part of what I am trying to accomplish. Each po line can have mulitple receiving dates. I want to compare the first receving date to the desired recvg date and say if it's then late else on_time. The other problem is that each po can have multiple lines. THe theory is if one line is late then the entire po is late. I have this part all worked out. It's just that I need to suppress the po #'s that aren't late. I hope all this makes sense.
I have tried suppressing the po field itself and using the section expert but it's been either all or nothing so far.
|
Posted By: kostya1122
Date Posted: 26 Nov 2013 at 8:35am
|
you could try something like
formula1 if receiving date > desired recvg date then 1 else 0 formula2 sum(formula1,po #) section expert not(formula2 >0) |
Posted By: cheryla
Date Posted: 26 Nov 2013 at 9:11am
| Is this in addition to my late line formula or instead of? |
Posted By: kostya1122
Date Posted: 26 Nov 2013 at 9:19am
| this is just for suppressing. PO# that were not late. |
Posted By: cheryla
Date Posted: 26 Nov 2013 at 10:14am
| ok I did what you suggested and it works (sort of). I'm going to see if I can try a couple of things and come back to it tomorrow. I'm sure I'll be back thanks again for your help. |
Posted By: DBlank
Date Posted: 27 Nov 2013 at 3:54am
|
not knowing your whole data set this might work, you will have to make sure though
Minimum ({RECEIVER.RECEIVED_DATE}, {PURC_ORDER_LINE.LINE_NO})<=Maximum({PURCHASE_ORDER.DESIRED_RECV_DATE}, {PURC_ORDER_LINE.LINE_NO})
|