| Author |
Message |
apuente
Newbie
Joined: 26 Nov 2012
Online Status: Offline
Posts: 15
|
   Topic: Suprres GH1 based on GF2 formula results
Posted: 04 Apr 2014 at 10:22am |
Hi Everyone, i need help with a report i am trying to create, but i am having trouble suppressing unwanted information. the report looks at transaction history for customer inventory. the report is grouped by customer and then by inventory part. i run a formula that calculates the last date a part had a transaction to it. if the date on any customer part is 3 or more months ago then i want to see the the goup header(customer) and group footer (part) for all of their inventory. Basically i want to conditionally supress GH1 if there are no invenory parts whose last transaction date (calculated at GF2 as the MAX of transaction date) is not 3 months or older. additionally, i dont want to supress the GF2 information if any of the GF for that Customer have an inventory part that is 3 month or older. thanks for any help.
|
|
Thank You,
Antonio
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Apr 2014 at 10:36am |
i don't know what you mean by 3 months or older so you will have to choose the < or > based on waht you mean by older.
Bascially I think that you can use the max of the transaction date at G1 and suppress using that
suppression criteria is something like:
dateadd("m",-3,currentdate) > Maximum(inventory.date,group1 field)
|
|
IP Logged |
|
apuente
Newbie
Joined: 26 Nov 2012
Online Status: Offline
Posts: 15
|
Posted: 04 Apr 2014 at 12:52pm |
i am using a similar formula to identify the items that are older than 3 months. the issue is that i want to supress GH1 (customer) if none of the parts are older than 3 months (summarized on GF2 by Part). i would also like to not supress GF2 if any the parts attributable to that customer parts are older than 90 days. thank you for your response
|
|
Thank You,
Antonio
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 07 Apr 2014 at 5:02am |
|
it would seem that the group header 1 suppression, you would look at and with a max({table.field}, {g1}). if that date > dateadd("d", -90, currentdate) or maybe < then suppress the group header.
gf2 is much simpler, since everything is in the group.
HTH
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Apr 2014 at 3:59am |
again I do not see why you cannot just use the max of the transaction date at group 1 (customer) to suppress the GH1.
It really does not matter which part (group 2) it is in, just that it exists for that customer. unless I am not understanding something it is pretty straight forward.
|
|
IP Logged |
|
apuente
Newbie
Joined: 26 Nov 2012
Online Status: Offline
Posts: 15
|
Posted: 08 Apr 2014 at 4:41am |
|
It is more complicated than that because there are several parts per customer and one parts max transaction date can be over 90 days and anothers cannot. because of this the report would need to evaluate at each parts group footer (GF2) for that max transaction date.
sorry for the confusion
|
|
Thank You,
Antonio
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 08 Apr 2014 at 4:47am |
|
sorry, can't be done...best you could do would be to have a subreport that returns whether or not to suppress the group header.
CR does not retroactively suppress, so if you cannot determine at the first record of the group if the group is to be suppressed, then you cannot suppress it. Hence the subreport...but then you are running the report twice.
If you can use a stored procedure, then you could calculate in the procedure if the group is to be suppressed and return that value as a column in the dataset. Since the value is already calculated, at the first record of the group it can be determined if the group is to be suppressed.
I know, seems circular, just how one can play the game with CR.
HTH
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Apr 2014 at 5:19am |
So i understand, you want to see the customer header when (using today's date) when
A. any maximum part transaction date for that customer is > 1-8-2014
or
B. all maximum part transaction dates for that customer are > 1-8-2014
or
C. some other thing?
|
|
IP Logged |
|
apuente
Newbie
Joined: 26 Nov 2012
Online Status: Offline
Posts: 15
|
Posted: 08 Apr 2014 at 5:43am |
|
A
|
|
Thank You,
Antonio
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Apr 2014 at 6:05am |
Then I say again you can use
NOT(maximum(transactiondate,customer)>dateadd('m',-3,currentdate))
as your suppression criteria.
It does not matter in the case of the customer group header which item it was just that any transaction for that customer happened in the last 3 months
|
|
IP Logged |
|
|
|
