Help with counts.

[priyanthan]

i have the following sample data and trying get a count for each network between ADMIN network transactions.

DATE TIME NETWORK
05/01/2011 06:01 VISA
05/01/2011 06:05 ADMIN
05/02/2011 06:22 MS
05/02/2011 08:15 MS
05/02/2011 08:46 MS
05/02/2011 08:47 VISA
05/03/2011 06:11 VISA
05/03/2011 06:26 MS
05/03/2011 08:10 MS
05/03/2011 08:36 MS
05/03/2011 08:40 VISA
05/04/2011 01:05 ADMIN

I need to count the transaction between each admin networks.Can any one please help me with this.

The results should be,
if take the time period "05/01/2011 06:05" and "05/04/2011 01:05"
VISA = 3
MS = 6

if take the time period "05/01/2011 06:01" and "05/01/2011 06:01"
VISA = 3

working with CR8.5 and DB2

Thanks,

 

[MCuthill]

priyanthan,

To get a count "after" (on) each record, and reset on each instance of ADMIN, please use the following:
1) In the report header, please place the following:
{@VariableReset_All}

[blue]WhilePrintingRecords[/blue];
[blue]NumberVar[/blue] NetworkVISA:=0;
[blue]NumberVar[/blue] NetworkMS:=0;

(repeating these lines once for each Network needed)

2) On the details row, create a formula such as:
{@VariableAccumulation}

[blue]WhilePrintingRecords[/blue];
[blue]NumberVar[/blue] NetworkVISA;
[blue]NumberVar[/blue] NetworkMS;

[blue]IF[/blue] {Table.Network}="ADMIN" [blue]THEN[/blue]
(
   NetworkVISA:=0;
   NetworkMS=:0;
) [blue]ELSE
IF[/blue] {Table.Network}="VISA" [blue]THEN[/blue]
(
   NetworkVISA:=NetworkVISA+1;
) [blue]ELSE
IF[/blue] {Table.NETWORK} = "MS" [blue]THEN[/blue]
(
   NetworkMS:=NetworkMS+1;
)

(again, create 1 if statement per Network/Variable used)

3) To display the variables, there are a few options. The most basic is to create a formula for each variable, such as:
{@VariableDisplay_CounterVISA}

[blue]WhilePrintingRecords[/blue];
[blue]NumberVar[/blue] NetworkVISA;
NetworkVISA;

If you will be able to pare your report to just the ADMIN to ADMIN flags and just need simple counts, the following should work:
{@counter_VISA}

[blue]IF[/blue] {Table.NETWORK} = "VISA" [blue]THEN[/blue] 1 [blue]ELSE[/blue] 0

the same could be used for your other networks and simply sum the formula for the report. Of course, if you cannot narrow your time-stamps ahead of time, this approach will not work.

Cheers! I hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[priyanthan]

Thanks for your replay. but this just give me the total counts of each network. Could you please explain how i can get the count between the 2 ADMIN networks.

Thanks,

 

[MCuthill]

priyanthan,

Sorry for misunderstanding your intent. Your original posting split the networks (VISA=3 and MS=6), I assumed this to be the final presentation sought.

If ever you may need to split the above into "per each network", you could simply create a formula which sums all the individual accumulations to provide a total. Such like:
{@Variables_GrandTotal}

WhilePrintingRecords;
NumberVar NetworkVISA;
NumberVar NetworkMS;
...
NumberVar NetworkTotal;

NetworkTotal:=NetworkVISA+NetworkMS;

If you wish to engineer the whole report to only count between two admins, without tracking which network, simply create one variable and use it throughout my previous posting. You can replace the "NetworkVISA" variable with "TransCount" or something more meaningful. The accumulation formula would then reset if NETWORK="ADMIN" or Accumulate this variable when it is not "ADMIN".

I would rather not repost the entire solution; but if you would like me to do so (the prior pargraph not being enough information), please advise and I can retype all 3 formulas.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[priyanthan]

Thanks again for taking time for the reply MCuthill,
I would really appreciate if you could the repost the entire solution.

Also how can i display the counts on the ADMIN row. (Grouping the the ADMIN with date& time)

DATE TIME NETWORK
05/01/2011 06:01 VISA
05/01/2011 06:05 ADMIN VISA = 1
05/02/2011 06:22 MS
05/02/2011 08:15 MS
05/02/2011 08:46 MS
05/02/2011 08:47 VISA
05/03/2011 06:11 VISA
05/03/2011 06:26 MS
05/03/2011 08:10 MS
05/03/2011 08:36 MS
05/03/2011 08:40 VISA
05/04/2011 01:05 ADMIN VISA = 3 MS = 6

Please let me know if i can do it like this.

Thanks a lot.

 

[MCuthill]

priyanthan,

To display the results, as you have posted most recently, my original solution will work.

I do not beleive you can group on the ADMIN DateTime only, but you can present the information in the matter you have shown here (you could suppress non-ADMIN rows should you need to see a summary).

For the solution you have shown (and referencing my formula names from my first posting), please setup your report as follows:

[tt]Report Header: {@VariableReset_All}
- Group Header 1 ({Table.Date}, on change of month):
- - Details: {Table.DATE} {Table.Time} {Table.Network} {@VariableAccumulation} {@VariableDisplay_NetworkVISA} {@VariableDisplay_NetworkMS}
- Group Footer 1
Report Footer[/tt]

Conditionally Suppress the "VariableDisplay" (using the "x+2" formula box on the format window), by entering:

{Table.NETWORK}<>"ADMIN"

Using your data example, the results should show like:
[tt]DATE TIME NETWORK
05/01/2011 06:01 VISA
05/01/2011 06:05 ADMIN 1
05/02/2011 06:22 MS
05/02/2011 08:15 MS
05/02/2011 08:46 MS
05/02/2011 08:47 VISA
05/03/2011 06:11 VISA
05/03/2011 06:26 MS
05/03/2011 08:10 MS
05/03/2011 08:36 MS
05/03/2011 08:40 VISA
05/04/2011 01:05 ADMIN 3 6[/tt]

You can then add text boxes or headings to identify which number on the ADMIN rows is which.

The theory is, accumulate a conditional Running Total, and display the results / display as per "ADMIN" network flag.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[priyanthan]

Thank you so much MCuthill. i'll give it a try.