Reportin services - AVG of avg

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Reportin services - AVG of avg

11 posts, Page 1 of 2

1 2 »»

Reportin services - AVG of avg

Rate Topic

Display Mode

Topic Options

Author

Message

lukas 54445

Posted Saturday, October 13, 2012 7:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
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

Post #1372417

H4K

Posted Sunday, October 14, 2012 7:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

Post #1372505

lukas 54445

Posted Tuesday, October 16, 2012 5:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
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?

Post #1373134

//Ravi

Posted Tuesday, October 16, 2012 10:06 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:54 AM
Points: 77, Visits: 259

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%?

------------
:)

Post #1373380

lukas 54445

Posted Tuesday, October 16, 2012 10:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
Points: 5, Visits: 20

Yes, I'm looking for answers to this (100%+100%+100%+100+87.5%)/5 = 97.5%?

Post #1373399

H4K

Posted Wednesday, October 17, 2012 12:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

Post #1373649

crmitchell

Posted Wednesday, October 17, 2012 8:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:32 AM
Points: 48, Visits: 201

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

Post #1373832

lukas 54445

Posted Monday, October 22, 2012 2:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
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.

Post #1375299

H4K

Posted Wednesday, October 24, 2012 6:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

Post #1376425

lukas 54445

Posted Friday, November 09, 2012 3:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 5:34 AM
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".

Post #1382914

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions