Sum Total for each record with Maximum Timestamp 2

[AnthonyMJ]

Hi,
I have a table consisting of the below columns. I need to sum up all Qty Passed per Product. This is simple. I just need to create a group and Insert Group Summary on Qty Passed. Now comes the tricky part. I also need to sum up all Qty Failed for each TIS # that has the maximum timestamp. In this case, it is 5 + 65 = 70.

Appreciate your thoughts.

Data
----
Product TIS# Qty Passed Qty Failed Timestamp
-------+------+-----------+------------+-------------------------
ABCD 01 100 10 02/01/2008 11:00:00 AM
ABCD 01 50 5 02/01/2008 11:30:00 AM <-- Max Timestamp for TIS #1

ABCD 02 20 25 02/01/2008 08:00:00 AM
ABCD 02 30 35 02/01/2008 08:30:00 AM
ABCD 02 40 65 02/01/2008 09:00:00 AM <-- Max Timestamp for TIS #2


Result
------

Product Qty Passed Qty Failed
-------+------------+----------------------
ABCD 240 70 (5 + 65)



XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

Use a variable for this:

//{@reset} to be placed in the product group header:
whileprintingrecords;
numbervar summax;
if not inrepeatedgroupheader then
summax := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar summax;

if {table.timestamp} = maximum({table.timestamp},{table.TIS#}) then
summax := summax + {table.qtyfailed};

Then in the product group footer use this formula:

//{@display}:
whileprintingrecords;
numbervar summax;

-LB

 

[AnthonyMJ]

Thanks lbass. That did the trick !

One more question, when I am summing up {@Display} in the Report Footer, CR tells me that I cannot summarize this field. Is there a way to display this total in the Report Footer ?



XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[dgillz]

Use variables to get a grand total as well. Add another variable in the detail section formula, and create another display formula. Do not add the new variable to the reset formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Exactly. Do as Don suggests.

-LB

 

[AnthonyMJ]

Thanks to both of you ..Problem solved !

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[AnthonyMJ]

Hello,

There was a change in the requirement and that I now have to summarize the Qty Failed on Product and TIS Levels based on the last timestamp for each Product, TIS#, Lot ID and Split Lot ID.

I have tried to work on it using the logic you provided but somehow wasn't able to get the correct result.

Appreciate your help again. Thanks.

Product TIS# Lot ID Split Lot ID Qty Failed Timestamp
-------+------+--------+--------------+------------+-------------------------
ABCD 01 0 0 10 02/01/2008 11:00:00 AM
ABCD 01 0 0 5 02/01/2008 11:30:00 AM <-- Max Timestamp for TIS# 01 Lot ID 0 Split Lot ID 0
ABCD 01 0 1 20 02/01/2008 11:25:00 AM
ABCD 01 0 1 15 02/01/2008 11:30:00 AM <-- Max Timestamp for TIS# 01 Lot ID 0 Split Lot ID 1


ABCD 02 0 0 25 02/01/2008 08:00:00 AM
ABCD 02 0 0 65 02/01/2008 09:00:00 AM <-- Max Timestamp for TIS# 02 Lot ID 0 Split Lot ID 0
ABCD 02 0 1 35 02/01/2008 08:30:00 AM
ABCD 02 0 1 40 02/01/2008 08:35:00 AM <-- Max Timestamp for TIS# 02 Lot ID 0 Split Lot ID 1
ABCD 02 1 0 50 02/01/2008 11:00:00 AM <-- Max Timestamp for TIS# 02 Lot ID 1 Split Lot ID 0



Result
------

Product Level

Product Qty Failed
-------+--------------------
ABCD 175 (20 + 155)


TIS Level


TIS# Qty Failed
-----+-----------------------
01 20 ( 5 + 15)
02 155 (65 + 40 + 50)

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

I'm confused about how many summaries you need. Do you just need them at the product and TIS level? What are your groups?

-LB

 

[AnthonyMJ]

I only need the summary on both Product and TIS levels.

However, I would need to consider the lot ID and split lot ID in order to summarize. Currently, I only have 2 groups - Product and TIS. I created a variable @ConcatKey that concatenates Product, TIS, Lot ID and Split Lot ID and used this to calculate the {@accum}. I wonder if I need to create groupings for Lot ID and Split Lot ID.

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar summax;

if {table.timestamp} = maximum({table.timestamp},{@ConcatKey}) then
summax := summax + {table.qtyfailed};

All the other variables created (@reset and @display) remain same.


XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

The summary below ONLY takes a group condition for the second argument:

maximum({table.timestamp},{@ConcatKey})

...so you would have to have a group on {@ConcatKey}, and this would be your lowest level group.

-LB

 

[AnthonyMJ]

Okay, I grouped on {@ConcatKey}and summarized based on this. However, it looks like the {@accum} doesn't reset.
I have put the {@reset} on the Product group header. Do I need to put it some place else ?

Expecting the @accum to reset per TIS#, so TIS# 02 would have values as 0, 65, 65, 105, and 155 per row.

Here is what the result looks like

              Lot   Split   Qty            
Prod   TIS#   ID    Lot ID  Failed  @Accum   Timestamp
------+------+-----+-------+-------+------+-------------------
ABCD    01      0        0      10      0    02/01/2008 11:00:00 AM
ABCD    01      0        0       5      5    02/01/2008 11:30:00 AM  <-- TIS#01  Lot 0  SplitLot 0
ABCD    01      0        1      20      5    02/01/2008 11:25:00 AM
ABCD    01      0        1      15     20    02/01/2008 11:30:00 AM  <-- TIS#01  Lot 0  SplitLot 1 


ABCD    02      0        0      25     20    02/01/2008 08:00:00 AM
ABCD    02      0        0      65     85    02/01/2008 09:00:00 AM  <-- TIS#02  Lot 0  SplitLot 0
ABCD    02      0        1      35     85    02/01/2008 08:30:00 AM
ABCD    02      0        1      40    125    02/01/2008 08:35:00 AM  <-- TIS#02  Lot 0  SplitLot 1
ABCD    02      1        0      50    175    02/01/2008 11:00:00 AM  <-- TIS#02  Lot 1  SplitLot 0

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

You need two separate sets of variables (three formulas each),one for each summary level. The reset formula belongs in the corresponding group header (product, for the product summary, TIS for the TIS summary), the accumulation formula in the detail, and the display formula in the corresponding group footer.

-LB