Axis Object - Auto Interval Error

Hello,
I am trying to plot a graph using the following data expression:
=(sum(Fields!Closed.Value)*7.5)/sum(Fields!FTE.Value)
and I get an error whenever I try to publish the graph:
An error has occured while rendering chart chart1. Details: Axis Object - Auto Interval Error Axis Object - Auto Interval Error
Anyone know what causes this and how it can be fixed?
Thanks
Wow... found the answer. So an error in my data caused this expression to be infinity. The Auto Interval Error can not handle a data point that is infinite so to fix this simply perform a check. In this case:
=iif(sum(Fields!FTE.Value)=0,"100",((sum(Fields!Closed.Value)*7.5)/sum(Fields!FTE.Value)))
I used the value 100 here becasue it is beyond my scall so a value of 100 will be very obvious.

Avoid Drill trough for zero value

Hi,
I am using Drillthrough in table Cell in one of my report.
Table cell displays sum which is sometimes Zero. And when sum is zero, I
don't want Drillthrough featur at that point, so that I can avoid unnecessary
click.
I tried using expression =IIF( sum =0, "", ReportName)
but this then take me report server and displays all the avaialble report on
RS, which is big security hole.
Please let me know the solution.
Many Thanks,
MaheshHi,
I am using Drillthrough in table Cell in one of my report.
Table cell displays sum which is sometimes Zero. And when sum is zero, I
don't want Drillthrough featur at that point, so that I can avoid unnecessary
click.
I tried using expression =IIF( sum =0, "", ReportName)
but this then take me report server and displays all the avaialble report on
RS, which is big security hole.
Please let me know the solution.
Many Thanks,
Mahesh|||Instead of "", use the keyword Nothing: =iif( Sum(...) = 0, Nothing,
ReportName)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mahesh Gaware" <MaheshGaware@.discussions.microsoft.com> wrote in message
news:B92CEC44-8A06-47A2-930D-E0463834DB32@.microsoft.com...
> Hi,
> I am using Drillthrough in table Cell in one of my report.
> Table cell displays sum which is sometimes Zero. And when sum is zero, I
> don't want Drillthrough featur at that point, so that I can avoid
> unnecessary
> click.
> I tried using expression =IIF( sum =0, "", ReportName)
> but this then take me report server and displays all the avaialble report
> on
> RS, which is big security hole.
> Please let me know the solution.
> Many Thanks,
> Mahesh
>

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

AVG by columns not by rows

Hello is there a way to calculate Average values for columns not for
rows.
E.g.
col1 col2 avg
--
2 2 2.0
3 2 2.5
I found something that requires manual sum of every column and than
dividing with number of nonzero an nonnull columns. I ended using tons
of case when... statements.
Is there any simple solution?Assuming you have a PK on your table
SELECT PK, AVG(AvgCol)
FROM
(SELECT PK, C1 AS AvgCol
FROM YourTable
UNION
SELECT PK, C2
FROM YourTable) T
GROUP BY PK
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<milan.letic@.gmail.com> wrote in message
news:1151930480.771154.61470@.v61g2000cwv.googlegroups.com...
> Hello is there a way to calculate Average values for columns not for
> rows.
> E.g.
> col1 col2 avg
> --
> 2 2 2.0
> 3 2 2.5
> I found something that requires manual sum of every column and than
> dividing with number of nonzero an nonnull columns. I ended using tons
> of case when... statements.
> Is there any simple solution?
>|||If you need the entire original row, as I suspect you will, you will
need to join this back to the orignal table:
SELECT T.KeyCol,
AvgX = AVG(CASE WHEN N.X = 1 AND T.col1 <> 0 THEN T.col1
WHEN N.X = 2 AND T.col2 <> 0 THEN T.col2
WHEN N.X = 3 AND T.col3 <> 0 THEN T.col3
WHEN N.X = 4 AND T.col4 <> 0 THEN T.col4
WHEN N.X = 5 AND T.col5 <> 0 THEN T.col5
END)
FROM TheTable as T,
(select 1 as X UNION ALL
select 2 UNION ALL
select 3 UNION ALL
select 4 UNION ALL
select 5) as N
GROUP BY T.KeyCol
Also, your sample data appears to start with integer values but the
average is to one decimal place, so it appears some conversion before
the AVG is applied will be required.
Roy Harvey
Beacon Falls, CT
On 3 Jul 2006 05:41:20 -0700, milan.letic@.gmail.com wrote:

>Hello is there a way to calculate Average values for columns not for
>rows.
>E.g.
>col1 col2 avg
>--
>2 2 2.0
>3 2 2.5
>I found something that requires manual sum of every column and than
>dividing with number of nonzero an nonnull columns. I ended using tons
>of case when... statements.
>Is there any simple solution?|||If your table has a primary key, you can unpivot the result and group by the
primary key and then join the result to the original table.
create table dbo.t1 (
pk int not null identity primary key,
c1 int,
c2 int
)
go
insert into dbo.t1(c1, c2) values(2, 2)
insert into dbo.t1(c1, c2) values(3, 2)
go
select
t1.pk,
t1.c1,
t1.c2,
t2.col_avg
from
dbo.t1
inner join
(
select
a.pk,
avg(cast(
case b.c1
when 1 then a.c1
when 2 then a.c2
end as numeric(5, 2))) as col_avg
from
dbo.t1 as a
cross join
(select 1 as c1 union all select 2) as b
group by
a.pk
) as t2
on t1.pk = t2.pk
go
drop table dbo.t1
go
AMB
"milan.letic@.gmail.com" wrote:

> Hello is there a way to calculate Average values for columns not for
> rows.
> E.g.
> col1 col2 avg
> --
> 2 2 2.0
> 3 2 2.5
> I found something that requires manual sum of every column and than
> dividing with number of nonzero an nonnull columns. I ended using tons
> of case when... statements.
> Is there any simple solution?
>|||Guys, thank you. But, I don't understand your code. I don't know what
it does, which is more efficient.. I'm afraid I'll have to use my old
version.
Alejandro Mesa wrote:
> If your table has a primary key, you can unpivot the result and group by t
he
> primary key and then join the result to the original table.
> create table dbo.t1 (
> pk int not null identity primary key,
> c1 int,
> c2 int
> )
> go
> insert into dbo.t1(c1, c2) values(2, 2)
> insert into dbo.t1(c1, c2) values(3, 2)
> go
> select
> t1.pk,
> t1.c1,
> t1.c2,
> t2.col_avg
> from
> dbo.t1
> inner join
> (
> select
> a.pk,
> avg(cast(
> case b.c1
> when 1 then a.c1
> when 2 then a.c2
> end as numeric(5, 2))) as col_avg
> from
> dbo.t1 as a
> cross join
> (select 1 as c1 union all select 2) as b
> group by
> a.pk
> ) as t2
> on t1.pk = t2.pk
> go
> drop table dbo.t1
> go
>
> AMB
> "milan.letic@.gmail.com" wrote:
>|||Hi There,
Roji has already given you a solution , I had changed it minorly by
using UNION ALL instead of UNION and added where clause although it is
not required as much.
Here R is a value that uniquely identifies a row (i.e Primary key )
Create view tmpData3
as
Select 1 R,null a,2 b, 3 c
Union
Select 2 R,1 a,null b, 3 c
Union
Select 3 R,1 a,2 b, null c
Union
Select 4 R,1 a,null b, null c
Go
Select R,avg(col) From
(
Select R,cast(a as numeric) col from tmpData3 where a>0
Union All
Select R,b from tmpData3 where b>0
Union All
Select R,c from tmpData3 where c>0
)X group by R
drop view tmpData3
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
milan.letic@.gmail.com wrote:
> Guys, thank you. But, I don't understand your code. I don't know what
> it does, which is more efficient.. I'm afraid I'll have to use my old
> version.
>
> Alejandro Mesa wrote:

Avegage from 4 columns

I have 4 columns and i need average from this columns.
I can sum values from this columns and divide by 4, but this is inproper in case of nulls.

Is there any easy way to do that?

Right now I'am using:

---

((SELECT CASE WHEN Column1 IS NOT null
OR Column2 IS NOT NULL
OR Column3 IS NOT NULL
OR Column4 IS NOT NULL THEN
(SELECT
(CASE WHEN Column1 IS NOT null THEN Column1 ELSE 0 END +
CASE WHEN Column2 IS NOT null THEN Column2 ELSE 0 END +
CASE WHEN Column3 IS NOT null THEN Column3 ELSE 0 END +
CASE WHEN Column4 IS NOT null THEN Column4 ELSE 0 END)
/ (0.0 +
CASE WHEN Column1 IS NOT null THEN 1 ELSE 0 END +
CASE WHEN Column2 IS NOT null THEN 1 ELSE 0 END +
CASE WHEN Column3 IS NOT null THEN 1 ELSE 0 END +
CASE WHEN Column4 IS NOT null THEN 1 ELSE 0 END))
ELSE
null
END

---

Can you post a small sample with some numbers demonstrating your problem, along with the desired output and also an explanation with rules of exactly how you want your averages calculated?

/Kenneth

|||

This is probably the best way to do it. It is straightforward and easy to debug, if a pain to write.

It is never going to be easy when working with a vector of values like this in SQL. SQL is optimized solely for working with sets of data, so if you had another table with this value in it and a key to group on, you could have a million values and it would work. The first normal form deals with this situation of having a variable number of values in a row for this very reason.

This kind of thing isn't always wrong of course, I have a place where I have to compare date values from a join to see when any values in the row were last updated. It is a big red flag though.

|||

You can use some other syntax to shorten the notation, but you will have to do essentially the same logic. If you have the choice to reorganize you tables, you might want to put the four columns in a separate table. That would allow you to use the Aggregate functions, which already have the NULL handling.

Here is an example that shows how to use other functions to shorten the notation. In order to prevent a divide by zero in the case where all 4 columns are null. I chose to return 0, but you could also return null.

drop table #colAvg

create table #colAvg(

pkid int not null Identity(1,1),

colA float null,

colB float null,

colC float null,

colD float null

)

insert #colAvg values( 1.0, null, 3.0, 4.0 )

insert #colAvg values( null, null, 3.0, 4.0 )

insert #colAvg values( 1.0, null, null, 4.0 )

insert #colAvg values( null, null, null, 4.0 )

insert #colAvg values( null, null, null, null )

Selectpkid,

numerator = (IsNull( colA, 0 ) + IsNull( colB, 0 ) + IsNull( colC, 0 ) + IsNull( colD, 0 )),

denominator = IsNull( NullIf( IsNull( Sign( colA ), 0 ) + IsNull( Sign( colB), 0 ) + IsNull( Sign( colC ), 0 ) + IsNull( Sign( colD ), 0 ), 0 ), 1 ),

average = (IsNull( colA, 0 ) + IsNull( colB, 0 ) + IsNull( colC, 0 ) + IsNull( colD, 0 )) /

IsNull( NullIf( IsNull( Sign( colA ), 0 ) + IsNull( Sign( colB), 0 ) + IsNull( Sign( colC ), 0 ) + IsNull( Sign( colD ), 0 ), 0 ), 1 )

From#colAvg

pkid numerator denominator average

-- -- -

1 8.0 3.0 2.6666666666666665

2 7.0 2.0 3.5

3 5.0 2.0 2.5

4 4.0 1.0 4.0

5 0.0 1.0 0.0|||table (Column1, Column2, Column3, Column4).
Table always have only 1 row.

Example1:
values (1,2,3,4) - average 2.5

Example2:
values(1,2,3,null) - average 2

Example3:
values(1,null,null,9) - average 5|||

Wow. Totally suggest you create 1-4 rows instead of 1-4 scalar values like this. I don't know what the statement used to load the data, but I will be changing it to insert into a table would be far easier in the long run (especially when the annoying user realizes that actually they need 10+ values)

For one row, you could do this (for > 1 row you would need a bit more in the where clause or a group on the outside)

select sum(columnValue)
from (select column1 as columnValue
from table
where column1 is not null
UNION ALL
select column2
from table
where column2 is not null
UNION ALL
select column3
from table
where column3 is not null
UNION ALL
select column4
from table
where column4 is not null) as values

|||

Another way to write the query is to do below. The cross join might be faster depending on the data.

select avg(case c.n

when 1 then t.column1

when 2 then t.column2

when 3 then t.column3

when 4 then t.column4

end)

from table as t

cross join (select 1 union all select 2 union all select 3 union all select 4) as c(n)

|||

Nice solution to a unnice problem :) I am always impressed by the many ways one can write SQL to get past improper design. It just goes to show you that a few minutes upfront can often save you hours later. If the data was in rows:

select sum(value)
from table

Oh well, certainly not as many creative solutions required :)

|||

select (isnull(column1,0)+isnull(column1,0)+isnull(column1,0)+isnull(column1,0))/4

FROM TABLEX

|||

I use the same temp table from anomolous :

For a unpivot solution for Sql Server 2005:

drop table #colAvg

create table #colAvg(

pkid int not null Identity(1,1),

colA float null,

colB float null,

colC float null,

colD float null

)

insert #colAvg values( 1.0, null, 3.0, 4.0 )

insert #colAvg values( null, null, 3.0, 4.0 )

insert #colAvg values( 1.0, null, null, 4.0 )

insert #colAvg values( null, null, null, 4.0 )

insert #colAvg values( null, null, null, null )

SELECT pkid, AVG(newValues) as avgFrom4Columns FROM

(SELECT pkid, colA, colB, colC, colD

FROM #colAvg) p

UNPIVOT

(newValues FOR cols4 IN (colA, colB, colC, colD)

) as unpvt

GROUP BY pkid

By the way, the Isnull(colnameX,0) solution does not provide the right answer .

|||Good use of the new unpivot operator. Btw, it does the same as the CROSS JOIN technique although easier to express in terms of syntax.|||Data are in columns not in rows.|||I can't divide by 4. I must count not null columns. I must divide by 4 or 3 or 2 or 1 (depends on values).|||

---

select sum(columnValue)
from (select column1 as columnValue
from table
where column1 is not null
UNION ALL
select column2
from table
where column2 is not null
UNION ALL
select column3
from table
where column3 is not null
UNION ALL
select column4
from table
where column4 is not null) as values

---

This is a second way I want to do this, but instead of "sum" you must use "avg" and you can remove "where" statement (avg function automaticaly removes null values).

Here is my another question. Witch way will be faster? Yours with "union" or my with "case"?|||Case approach is faster since the query using CASE expression does less I/O than UNION ALL approach or CROSS JOIN or UNPIVOT. You can perform the calculations in one pass on each row.