How to sum a formula

[deeya]

Hi again,s.

CR11 SQL Server

Not sure how to do this..so here goes.

Data Output in Crystal

Interface Month % Average Ex of Result (% Change)

1111111111111
12/2010 71.14%
01/2011 65.02%
02/2011 45.23%
-25.91%
2222222222222
12/2010 12.01%
01/2011 22.23%
02/2011 45.23%
33.22%

@Average = Maximum ({@Max}, {InterfaceTraffic.DateTime}, "monthly")%{@Link Speed}

What we need is to work out the % change column which is 02/2011-12/2010 which equals the percentage difference.

So 45.23% - 71.14% = -25.91%

Please let me know if more information is required.

 

[deeya]

Sorry should have posted the nested formulas....

{@Link Speed} = {Interfaces.OutBandwidth}/1000000

{@Max} = If {@MaxBPS} > {@MinBPS} then {@MaxBPS} else {@MinBPS}

{@MaxBPS} = If {InterfaceTraffic.In_Maxbps} > {InterfaceTraffic.Out_Maxbps} then
({InterfaceTraffic.In_Maxbps}/1000000) else
({InterfaceTraffic.Out_Maxbps}/1000000)

{@MinBPS} = If {InterfaceTraffic.In_Minbps} > {InterfaceTraffic.In_Maxbps} then
({InterfaceTraffic.In_Maxbps}/1000000) else
({InterfaceTraffic.In_Minbps}/1000000)

 

[deeya]

bump

 

[lbass]

Well, what problem are you running into? Also, please be patient--bumping because you didn't get an immediate response is a little over the top.

-LB

 

[deeya]

Sorry for the urgency, my apologies.

The problem is that I don't know how to subtract the most recent months '% average' from the latest '% average' for each interface and then sort the report on this value

e.g interface 111111 the formula would need to result

45.23% - 71.14% = -25.91% << sort the report from this value

Thanks.

 

[lbass]

Getting the difference is easy enough. The problem is the group sort. To activate the group sort feature, you have to be able to right click and insert a summary on the field at the group level, and then sort on that summary. But your values are already summaries. The usual solution is to create the summary as a SQL expression--on which you can in turn insert a summary. But even this would be difficult in this case, because you have nested summaries. It might be necessary to handle this in a view, instead of Crystal, if the group sort is essential.

-LB

 

[deeya]

OK leave the sort part out for now. How would i find the difference?

 

[lbass]

Recreate your formulas like this:

//{@Maxbps}:
maximum([{InterfaceTraffic.In_Maxbps}/1000000,{InterfaceTraffic.Out_Maxbps}/1000000])

//{@Minbps}:
minimum([{InterfaceTraffic.In_Maxbps}/1000000, {InterfaceTraffic.In_Minbps}/1000000])

//{@Max}:
maximum([{@Maxbps},{@Minbps}])

Then create a SQL expression {%mindt} like this:

(
select min("datetime")
from "InterfaceTraffic" A
where A."Interface" = "InterfaceTraffic"."Interface"
)

Create another SQL expression {%maxdt}:
(
select max("datetime")
from "InterfaceTraffic" A
where A."Interface" = "InterfaceTraffic"."Interface"
)

Note that you might have to add limiting criteria depending upon your report selection criteria. SQL expressions don't honor the report selection criteria, since they directly access the database.

Then create formulas like these:

//{@first}:
if {InterfaceTraffic.Datetime} = {%mindt} then
{@Max}

//{@last}:
if {InterfaceTraffic.Datetime} = {%maxdt} then
{@Max}

//{@Difference}:
maximum({@last},{InterfaceTraffic.Interface})-maximum({@first},{InterfaceTraffic.Interface})

There are other ways to do this, but I was initially trying to see if I could get the results to a point where I could do a group sort, but this last formula doesn't work for that.

-LB

 

[deeya]

Thanks LB, I will try this out when im back in the office. Will let you know the outcome.