Help with rewriting code without cursor

Hello,
Just wondering if anyone can tell me the best way to rewrite the below code
without a cursor.
It's just passing each Id to a stored procedure.
Let me know if you need any more info.
Thanks & go easy on me, I know cursors tend to rile everyone up.
Declare cur_DeleteStuff Cursor Scroll For
Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID)
Open cur_DeleteStuff
Fetch First FROM cur_DeleteStiff into @.ChildTableID
While (@.@.Fetch_Status <> -1)
Begin
exec sp_SMART_ANADeleteFrom @.ChildTableID, 1, 0
If @.@.Error <> 0
BEGIN
ROLLBACK Transaction CDTTransfer
RAISERROR('Something Bad Happened, Updates ROLLED BACK!',1,1)
RETURN
END
Fetch Next FROM cur_Deletestuff into @.ChildTableID
END
Close cur_DeleteStuff
Deallocate cur_DeleteStuff"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> Hello,
> Just wondering if anyone can tell me the best way to rewrite the below
> code
> without a cursor.
> It's just passing each Id to a stored procedure.
> Let me know if you need any more info.
> Thanks & go easy on me, I know cursors tend to rile everyone up.
>
What is the code for the stored procedure: sp_SMART_ANADeleteFrom
If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
based on the ChildTableID
then you should be able to modify the delete to do something like the
following:
DELETE TableName
WHERE ChildTableID IN
(Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID))
One a side note: You should probably not be naming your user defined stored
procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
generally use for SQL Server system stored procedure which are found in the
master database and are available globally throughout the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for your help Rick,
Though the naming convention implies it's only deleting a child - it's
actually doing something completely different.
I still need to call the stored procedure for each table ID found.
Thanks for the sp_ info.
Lesley
"Rick Sawtell" wrote:

> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> What is the code for the stored procedure: sp_SMART_ANADeleteFrom
> If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
> based on the ChildTableID
> then you should be able to modify the delete to do something like the
> following:
> DELETE TableName
> WHERE ChildTableID IN
> (Select distinct TableID
> from tbl_DTM
> Where APID IN
> (Select TableID from tbl_DTM where
> supplierID = @.v_FromSupplierID))
>
> One a side note: You should probably not be naming your user defined stor
ed
> procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
> generally use for SQL Server system stored procedure which are found in th
e
> master database and are available globally throughout the system.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Whatever it does, we can't help you find a set-baset solution without you
posting the procedure.
ML|||Sorry, I was thinking I could just use the sp as is. I pasted it below. It
was written a while ago by someone else & is in production now.
Basically it's deleting rows from a table, then deleting the defining row
from another table based on the tableID
I'd welcome any input on how to change this to set based.
That may also address rollback issues I predict I will have.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_SMART_ANADeleteFrom
@.FromTableID INT = 0,
@.OKtoDeleteORIG BIT = 0,
@.DebugMode INT = 0
AS
DECLARE @.TableType CHAR(3)
DECLARE @.AnalyticParentID INT
DECLARE @.FromPhysicalTableName varchar(255)
DECLARE @.strSQL nvarchar(2000)
if @.FromTableID is null
begin
raiserror ('Invalid From Table ID.',1,1)
return
end
--
SELECT @.TableType = MyType,
@.AnalyticParentID = AnalyticParentID,
@.FromPhysicalTableName = PhysicalDataTableName
from
tbl_DataTableMaster where Tableid = @.FromTableID
BEGIN TRANSACTION DELETEfromAnalytics
-- Delete the rows from the Quarterly ANA table
SET @.StrSQL = N'DELETE FROM My_Users.' + @.FromPhysicalTableName +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error deleting rows. Table Deletion did NOT occur!!',1,1)
RETURN
end
--delete row from tbl_DataTableMaster
SET @.strSQL = N'DELETE FROM tbl_DataTableMaster ' +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE data table master Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error Deleting in Data Table Master. Table Deletion did NOT
occur!!',1,1)
RETURN
end
--
COMMIT TRANSACTION DELETEfromAnalytics
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id
"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:

>I have the following nonindexable query due to the "<>" operater. Column "i
d"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index see
k
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:

> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestat
s)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is stil
l
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:[vbcol=seagreen]
>If you have a clustered index on the "id" column, the index will get used i
n
>the search, but it will be an index scan rather than a index seek(which wil
l
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index se
ek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you hav
e
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause
.
>HTH
>RA
>
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via droptable.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could
be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatest
ats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause
:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is st
ill
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would
be
> appreciated.
> Rocky A wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>I have the following nonindexable query due to the "<>" operater. Column "id"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index seek
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:
> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
> >I have the following nonindexable query due to the "<>" operater. Column "id"
> >in the following scenario is a clustered index.
> >
> >DECLARE @.tid
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >
> >
> >Is there a way to rewrite such a search condition so as to make it an
> >indexexable search condition?
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestats)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is still
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:
>If you have a clustered index on the "id" column, the index will get used in
>the search, but it will be an index scan rather than a index seek(which will
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index seek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you have
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause.
>HTH
>RA
>> How large is the table? How much of the table is equal to @.tid? Is
>> t.id unique?
>[quoted text clipped - 22 lines]
>> >Is there a way to rewrite such a search condition so as to make it an
>> >indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatestats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is still
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would be
> appreciated.
> Rocky A wrote:
> >If you have a clustered index on the "id" column, the index will get used in
> >the search, but it will be an index scan rather than a index seek(which will
> >be in case of =), for the simple reason that you are not trying to find one
> >value, you are trying to eleminate a value, so it has to compare it against
> >every value in the index. So I am assuming you are looking for an index seek
> >rather than a scan,
> >
> >You can try doing this
> >
> >DECLARE @.tid int
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >and t.id>0
> >
> >I am not sure if you can make this assumption that "id" will always be > 0,
> >this actually runs an index seek on the table. The plan improves if you have
> >this inside a stored proc, as the query plan gets cached.
> >
> >As Roy pointed out this can be a very expensive query without anything else
> >in your where clause depending on the size of the data. It almost took 31
> >seconds for me to run this on 11 Million records. If I was you I would look
> >at changing the query and including some more filtering in the where clause.
> >
> >HTH
> >RA
> >
> >> How large is the table? How much of the table is equal to @.tid? Is
> >> t.id unique?
> >[quoted text clipped - 22 lines]
> >> >Is there a way to rewrite such a search condition so as to make it an
> >> >indexexable search condition?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with Multiple inserts

HI,
How do I rewrite the first query using the datafrom query 2 so that I
can do multipe inserts for all the units given this input:
@.EnrolmentID,
@.dteEnroled,
@.Outcome,
@.CourseID
-- 1. This creates one unit enrolment using parameters
INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
QualUnitID) VALUES (@.EnrolmentID,@.dteEnroled,@.Outcome, @.QualUnitID)
-- 2. This gets a list of Unit ID's for a given Course
SELECT QualUnitID FROM QualUnits WHERE QualID=(SELECT QualID FROM
COURSES WHERE CourseID=@.intCourse)Hi
I'm not sure understand your question
INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
QualUnitID) SELECT @.EnrolmentID,@.dteEnroled,@.Outcome, QualUnitID FROM
QualUnits WHERE QualID=(SELECT QualID FROM
COURSES WHERE CourseID=@.intCourse)
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1130322728.990452.313140@.g43g2000cwa.googlegroups.com...
> HI,
> How do I rewrite the first query using the datafrom query 2 so that I
> can do multipe inserts for all the units given this input:
> @.EnrolmentID,
> @.dteEnroled,
> @.Outcome,
> @.CourseID
>
> -- 1. This creates one unit enrolment using parameters
> INSERT INTO tblUnitEnrolment (EnrolmentID,EnrolDate,Outcome,
> QualUnitID) VALUES (@.EnrolmentID,@.dteEnroled,@.Outcome, @.QualUnitID)
> -- 2. This gets a list of Unit ID's for a given Course
> SELECT QualUnitID FROM QualUnits WHERE QualID=(SELECT QualID FROM
> COURSES WHERE CourseID=@.intCourse)
>|||Thanks - that does the trick

Help with measure aggregation functions

Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.childr en,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael Prendergast
Averages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.childr en,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast
|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegr oups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>

Help with measure aggregation functions

Hi, I will rewrite my question.
I'm having trouble to show averages of a measure in a cube, where the normal
aggregation function for a measure is SUM.
I see no AVG aggregation function for measures (I see Min, Max, Count,
Distinct Count and SUM).
If I hide the measure (cost), and create a calculated member based on that
measure, ie Avg(cost), I have te problem of how to average it, since the
cube has two dimensions in the row axis, like:
Time
---
Product |
Customer | avg of cost
If I use avg(nonemptycrossjoin(product.currentmember.children,
customer.currentmember.children), measures.cost) I get the same average for
every cell in the cube, what's not corrrect...
Sorry to bother you all, but this thing is becoming a nightmare.
Hope you can help
Michael PrendergastAverages are usually handled by summing and counting...and then
dividing the sub by the count in a calculated member.
MPS wrote:
> Hi, I will rewrite my question.
> I'm having trouble to show averages of a measure in a cube, where the
normal
> aggregation function for a measure is SUM.
> I see no AVG aggregation function for measures (I see Min, Max,
Count,
> Distinct Count and SUM).
> If I hide the measure (cost), and create a calculated member based on
that
> measure, ie Avg(cost), I have te problem of how to average it, since
the
> cube has two dimensions in the row axis, like:
> Time
> ---
> Product |
> Customer | avg of cost
> If I use avg(nonemptycrossjoin(product.currentmember.children,
> customer.currentmember.children), measures.cost) I get the same
average for
> every cell in the cube, what's not corrrect...
> Sorry to bother you all, but this thing is becoming a nightmare.
> Hope you can help
> Michael Prendergast|||Sometimes, getting back to basics gets the job done
Thank yo very much, problem solved
Michael
"OLAPMonkey" <jjanke@.spss.com> escribi en el mensaje
news:1112202692.231562.128020@.g14g2000cwa.googlegroups.com...
> Averages are usually handled by summing and counting...and then
> dividing the sub by the count in a calculated member.
> MPS wrote:
> normal
> Count,
> that
> the
> average for
>