SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reportin services - AVG of avg


Reportin services - AVG of avg

Author
Message
lukas 54445
lukas 54445
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
Hello,
I have question, how to sum rows. I create query =avg(iif(filds!urgency.value=1 and filds!_time<=14,1,iif(filds!urgency.value=2 and filds!_time<=130,1,0)), everything is ok, but I must sum average of average. When I sum row I get 93.33, but correctly score is 97,50%.



Maybe someone knows how to do it. How to sum calculated
H4K
H4K
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 567
Hi,

I think 93.33% maybe the right answer because the average is being calculated in the scope of the whole year or whatever months (Jan-June) included.

While your expectation of the answer being 97.5 % is wrong because, its just the average of percentages of every month.

Just try to take the average of sales for each individual months (For Ex. sales/30) and then for whole year(sales/365) with some random values.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
lukas 54445
lukas 54445
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
But how to calculate the resulting field RS. As in the example above, after the calculation as a result I get indirect outcome which I want to sum them up.
Is this possible?
//Ravi
//Ravi
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 Visits: 451
wait, I don't understand. You want to sum the up percentages? If you are summing up the percentages, then it would be 487.5%. Or you are looking for (100%+100%+100%+100+87.5%)/5 = 97.5%?

------------
Smile
lukas 54445
lukas 54445
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
Yes, I'm looking for answers to this (100%+100%+100%+100+87.5%)/5 = 97.5%?
H4K
H4K
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 567
You can use the following expression to get the sum of percentages:

=REPORTITEMS!Textbox15.Value

where 'Textbox15' is the textbox in which you are showing the percentage.

you have to get the value of '5' from somewhere...

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1793
The simple school maths level answer

Never average averages.

The slightly better answer

Never average averages unless the sample sizes of all elements are identical

The reason

suppose you have a set of average speeds for traffic along a road
January 30mph
February 45mph
March 15 mph

What is the average speed for the quarter
You cannot tell as you do not have sufficient information - you might think its (30+45+15)/3 = 30
That is highly unlikely to be correct - for two reasons
1 you don't know the sample sizes
2 speeds are ratios

Now suppose you know that you have
January
Vehicle a 10 miles at 30 mph
Vehicle b 5 miles at 30 mph
vehicle c 25 miles at 30 mph

February
Vehicle d 10 miles at 50 mph
Vehicle e 10 miles at 40 mph


March
vehicle f 25 miles at 15 mph


Total distance travelled = 85 miles
Total time taken (10/30)+(5/30)+(25/30)+(10/50)+(10/40)+(25/15)=
3.45 hours

Average speed= 85/3.45

=~ 24.64 mph
lukas 54445
lukas 54445
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
crmitchell, I agree with you but my supervisor won't avg avg
SSC-Enthusiastic, Your solution"=REPORTITEMS!Textbox15.Value", gives last calculated value, but I want sum in this table.

When I "=sum(REPORTITEMS!Textbox15.Value) I get error messages.
H4K
H4K
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 567
You may need to convert the values into numbers before adding them up.

=SUM(CAST(ReportItems!Textbox15.Value AS INT) )

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
lukas 54445
lukas 54445
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
Hi,

When I used =SUM(CAST(ReportItems!Textbox13.Value AS INT) ) I get the message "The Value expression for the textrun 'sum.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers".
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search