Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Run Tls? How would you do what I am trying to do? Post Reply Post New Topic
Author Message
bspencer1
Newbie
Newbie


Joined: 26 Nov 2013
Online Status: Offline
Posts: 26
Quote bspencer1 Replybullet Topic: Run Tls? How would you do what I am trying to do?
    Posted: 15 May 2015 at 6:20am
Everyone, hello.  I have a report that is grouped by Sales Order and Sales Order Confirmation Date.  A Sales Order can have multiple confirmations.  I am supposed to created a report that shows the most recent, and next to most recent, confirmation based on the date range selected.  In my example below, if the date range was 8/1/13 - 9/30/13, then it would only show the 8/28/13 and 9/3/13 confirmations. 
 
In this example, I need to be able to filter out (or suppress) the 7/28/2013 confirmation.  I am interested to hear how you guys might go about doing that?  I thought I could use a running total, and then put it in my suppression formula for the section like COUNT < (maximum(COUNT)  -1), but you cannot calculate on the running total.  
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 May 2015 at 7:50am
do a distinct count of table.confirmationdate at the sales order
create a running total as
//suppessing
field to summarize = confirmationdate
type= distinct count
evaluate=on every record
reset= on change of group (sales order)
 
suppress the groups using
 
distinctcount > 3 and #suppress<=(distinctcount -2)
IP IP Logged
bspencer1
Newbie
Newbie


Joined: 26 Nov 2013
Online Status: Offline
Posts: 26
Quote bspencer1 Replybullet Posted: 15 May 2015 at 9:46am
Thanks.  I think I understood enough of your suggestion to make some progress.  I am now suppressing the 7/28/2013 confirmation you see in the image, however it has ferreted out another problem.  Users are able to pick a date range to get the confirmation details at a point in time.  My distinctcount by sales order formula, which evaluates to 3 in the above example, needs to evaluate based on the date range.  In other words, if the user was to select a date range of 07/01/2014 to 8/30/2013, then the distinctcount would need to evaluate to 2 so that it displayed the 7/28 and 8/28 confirmations (not the 9/3 one).  The parameter on the report is a date range parameter.  I tried this, but it still evaluates to 3:
 
IF ({AXTerritorySales_query.Date} >= MINIMUM({?Date Range}) AND {AXTerritorySales_query.Date} <= MAXIMUM({?Date Range})) THEN DISTINCTCOUNT({AXTerritorySales_query.Date})
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 May 2015 at 9:51am
the date range is not in the select expert, only in regards to the display?
Why, if you are already hiding all of the data that is outside it why waste the resources to pull it in?
IP IP Logged
bspencer1
Newbie
Newbie


Joined: 26 Nov 2013
Online Status: Offline
Posts: 26
Quote bspencer1 Replybullet Posted: 15 May 2015 at 10:22am

You are 100% right.  Sorry for my stupidity there.  I was thinking I had that in my selection criteria already and was perplexed on why it wouldn't work.  Turns out I deleted it for testing.  I just put it back and it works fine.  Thanks for your help!  I think I am good to go now.

IP IP Logged
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.