Calling extended stored procs from a function

I know you cannot call functions from a stored proc.. only extended stored
procs otherwise you get this message
"Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
Only functions and extended stored procedures can be executed from within a
function."
So, to get around it, I am using sp_Executesql which IS an extended stored
proc but I am still getting the error. PLease explain?
Thanks!!!!!
ALTER function nextval2
( @.sequence varchar(100)) returns int
AS
BEGIN
declare @.sequence_id int
set @.sequence_id = -1
exec sp_executesql N'update sequences set sequence_id = sequence_id + 1'
select @.sequence_id = max(sequence_id)
from sequences
RETURN @.sequence_id
ENDThere are 2 ways that I know of to maintain variable information between
calls to a function,
1) Use OPENQUERY to pass through an UPDATE or INSERT to a loopback linked
server.
2) Use sp_OA calls to access parameters on a previously declared object (e.g
VBScript.RegExp)
I suspect these are just about as dangerous as each other :)
Mr Tea
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:C92D80A6-1367-4BF5-9BE2-65BD8C54EC12@.microsoft.com...
>I know you cannot call functions from a stored proc.. only extended stored
> procs otherwise you get this message
> "Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
> Only functions and extended stored procedures can be executed from within
> a
> function."
> So, to get around it, I am using sp_Executesql which IS an extended stored
> proc but I am still getting the error. PLease explain?
> Thanks!!!!!
> ALTER function nextval2
> ( @.sequence varchar(100)) returns int
> AS
> BEGIN
> declare @.sequence_id int
> set @.sequence_id = -1
> exec sp_executesql N'update sequences set sequence_id = sequence_id + 1'
> select @.sequence_id = max(sequence_id)
> from sequences
> RETURN @.sequence_id
> END
>|||Thanks LEe.
"Lee Tudor" wrote:

> There are 2 ways that I know of to maintain variable information between
> calls to a function,
> 1) Use OPENQUERY to pass through an UPDATE or INSERT to a loopback linked
> server.
> 2) Use sp_OA calls to access parameters on a previously declared object (e
.g
> VBScript.RegExp)
> I suspect these are just about as dangerous as each other :)
> Mr Tea
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:C92D80A6-1367-4BF5-9BE2-65BD8C54EC12@.microsoft.com...
>
>

Call vb.Net developed dll in SQL Server 2005 with configuration level 80 then gets error "I

Hi,

I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.

Error Source: ODSOLE Extended Procedure

Description: Invalid class string

Code of stored procedure and vb.net class is given below:

VB.Net

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class PositivePay

Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)

' impersonate the calling user

Dim newContext As System.Security.Principal.WindowsImpersonationContext

newContext = SqlContext.WindowsIdentity.Impersonate()

Try

Dim w As StreamWriter = File.AppendText(LogName)

LogIt(newMessage, w)

w.Close()

Catch Ex As Exception

Finally

newContext.Undo()

End Try

End Sub

End Class

===============================================================

STORED PROCEDURE

Create PROCEDURE [dbo].[PPGenerateFile]

AS

BEGIN

Declare @.retVal INT

Declare @.comHandler INT

declare @.errorSource nvarchar(500)

declare @.errorDescription nvarchar(500)

declare @.retString nvarchar(100)

-- Intialize the COM component

EXEC @.retVal= sp_OACreate 'PositivePay.class', @.comHandler OUTPUT

IF(@.retVal <> 0)

BEGIN

--Trap errors if any

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

-- Call a method into the component

EXEC @.retVal = sp_OAMethod @.comHandler,'LogToTextFile',@.retString OUTPUT, @.LogName = 'D:\text.txt',@.newMessage='Hello'

IF (@.retVal <>0 )

BEGIN

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

select @.retString

END

sp_OACreate is used to invoke a OLE object. Assuming you are wanting to leverage SQLCLR since this is the forum you are posting the question in to do this you would create a class in .Net, reference it in a CLR procedure, and deploy both the class and the procedure.

If you are wanting to leverage the older (.80/2000) methods you can try creating a Service Component (assuming this is still supported in .Net 2.0, this exploses .Net assemblies via COM+) and then use sp_OACreate to invoke the type.

Derek

|||

Hi Derek,

Basicaly our application is using SQL Server 2005 with configuration level 80. So i have to use older method to call com component from SQL Server using stored procedure as shown in already posted code.
Please review my code and help me what should i do to perfom my task successfuly. I give you again some description about the task.
Basicaly a job will call the stored procedure at some settled time. That stored procedure will innvoke the component (whose developed in VB.Net using Framework 2.0). That component will get some record from database and will write them in simple .txt file.

So please tell me by revewing my above code that what should i do?
Best Regards,
Jawad Naeem

|||

Try this:

use the type library export, TlbExp.exe via the .Net Framework SDK command prompt, you can use the "/?" to show it's full syntax.

Then Register your new type via RegSvr32.exe

Then test invoking it from say a VBScript using Dim oTest oTest = CreateObject()

If the test succeeds, then use the sp_OACreate in a TSQL script to invoke the type successfuly.

The end goal here is to create a basic COM component from a managed assembly. TSQL's consumption of the object is a mute point. Any automation-aware environment could consume it.

HTH,

Derek

|||

Hi,

Please tell me the steps with more detail, like using code example

Thanks

|||I am assuming your new thread was the same issue as this one, thus please see your other thread for my response...and give me two answers if it solves your problem lol :)

Call vb.Net developed dll in SQL Server 2005 with configuration level 80 then gets error &qu

Hi,

I want to call a dll from Stored procedure developed in SQL Server 2005 at configuration level 80. but when I execute the stored procedure I get the following error.

Error Source: ODSOLE Extended Procedure

Description: Invalid class string

Code of stored procedure and vb.net class is given below:

VB.Net

Imports System

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports Microsoft.VisualBasic

Imports System.Diagnostics

Public Class PositivePay

Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)

' impersonate the calling user

Dim newContext As System.Security.Principal.WindowsImpersonationContext

newContext = SqlContext.WindowsIdentity.Impersonate()

Try

Dim w As StreamWriter = File.AppendText(LogName)

LogIt(newMessage, w)

w.Close()

Catch Ex As Exception

Finally

newContext.Undo()

End Try

End Sub

End Class

===============================================================

STORED PROCEDURE

Create PROCEDURE [dbo].[PPGenerateFile]

AS

BEGIN

Declare @.retVal INT

Declare @.comHandler INT

declare @.errorSource nvarchar(500)

declare @.errorDescription nvarchar(500)

declare @.retString nvarchar(100)

-- Intialize the COM component

EXEC @.retVal= sp_OACreate 'PositivePay.class', @.comHandler OUTPUT

IF(@.retVal <> 0)

BEGIN

--Trap errors if any

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

-- Call a method into the component

EXEC @.retVal = sp_OAMethod @.comHandler,'LogToTextFile',@.retString OUTPUT, @.LogName = 'D:\text.txt',@.newMessage='Hello'

IF (@.retVal <>0 )

BEGIN

EXEC sp_OAGetErrorInfo @.comHandler,@.errorSource OUTPUT, @.errorDescription OUTPUT

SELECT [error source] = @.errorsource, [Description] = @.errordescription

Return

END

select @.retString

END

sp_OACreate is used to invoke a OLE object. Assuming you are wanting to leverage SQLCLR since this is the forum you are posting the question in to do this you would create a class in .Net, reference it in a CLR procedure, and deploy both the class and the procedure.

If you are wanting to leverage the older (.80/2000) methods you can try creating a Service Component (assuming this is still supported in .Net 2.0, this exploses .Net assemblies via COM+) and then use sp_OACreate to invoke the type.

Derek

|||

Hi Derek,

Basicaly our application is using SQL Server 2005 with configuration level 80. So i have to use older method to call com component from SQL Server using stored procedure as shown in already posted code.
Please review my code and help me what should i do to perfom my task successfuly. I give you again some description about the task.
Basicaly a job will call the stored procedure at some settled time. That stored procedure will innvoke the component (whose developed in VB.Net using Framework 2.0). That component will get some record from database and will write them in simple .txt file.

So please tell me by revewing my above code that what should i do?
Best Regards,
Jawad Naeem

|||

Try this:

use the type library export, TlbExp.exe via the .Net Framework SDK command prompt, you can use the "/?" to show it's full syntax.

Then Register your new type via RegSvr32.exe

Then test invoking it from say a VBScript using Dim oTest oTest = CreateObject()

If the test succeeds, then use the sp_OACreate in a TSQL script to invoke the type successfuly.

The end goal here is to create a basic COM component from a managed assembly. TSQL's consumption of the object is a mute point. Any automation-aware environment could consume it.

HTH,

Derek

|||

Hi,

Please tell me the steps with more detail, like using code example

Thanks

|||I am assuming your new thread was the same issue as this one, thus please see your other thread for my response...and give me two answers if it solves your problem lol :)

Call Stack Level

I figure there is someway to pull this out but I can't find it anywhere. I
have a recursive stored procedure and I want to error out if it recurses
more than 10 times. How can I pull out how deep in the call stack I am? Is
this possible? I know I could pass a parameter to the stored procedure and
keep incrementing it but I was hoping there was a better way.
--
TIA
AltmanAltman (NotGiven@.SickOfSpam.com) writes:
> I figure there is someway to pull this out but I can't find it anywhere.
> I have a recursive stored procedure and I want to error out if it
> recurses more than 10 times. How can I pull out how deep in the call
> stack I am? Is this possible? I know I could pass a parameter to the
> stored procedure and keep incrementing it but I was hoping there was a
> better way.
There is a global variable (or function as Microsoft calls it these
days), @.@.nestlevel that holds this information. @.@.nestlevel is increased
by 1 for every scope you to descend to. For this reason, I would be hesitant
to use @.@.nestlevel, as the rules would change if your procedure was
wrapped into another procedure. Passing a parameter is probably better.
The max nesting level in SQL Server is 32, by the way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Calculations Script Order and Formatting

I have a parent-child dimension for Accounts with a calculated member drapped off of the top level that figures Net Earnings % using a measure called Amount. As described by the name of the member, I need this expressed as a percentage. I am using this calc in the same view as a (Shell/Utility) Time Calculations dim that figures Year-to-date, Prior Year-to-date, YTD % Change, etc.... Depending on the order of the calculations in the calculations script I get different results. If I put the formula for the Net Earnings % before the MDX for the Time Calculations the results comes out correct but the formating gets overidden by the formating applied when the Time Calulations are applied. So the Net Earnings % for [YTD] and [YTD PY] are expressed as decimals and [YTD % Change] is expressed as a percentage. If I flip the order in the calculations script the formating gets corrected because the formatting of the Net Earnings % get applied last but my results for Net Earnings % are incorrect for [YTD % Change] because they were applied after the Time Calculations and end up applying the formula using the reults (the percentages) of the [YTD % Change] calc instead of the numbers used to make up that percentage.

Is there a way to put the Net Earnings % MDX before the Time Calculations MDX so that the results are correct for [YTD % Change] and reapply the formating of percent to [YTD] and [YTD PY] after the formatting of the Time Calculations get applied?

Nevermind, I figured it out. I placed the Net Earnings % MDX before the Time Calculations MDX and placed the following MDX after the Time Calculations MDX:

Code Snippet

Scope({[Account].[Account Hierarchy].[Net Earnings%]});

FORMAT_STRING(THIS) = "Percent";

End Scope;

|||

Nevermind, I figured it out. I placed the Net Earnings % MDX before the Time Calculations MDX and placed the following MDX after the Time Calculations MDX:

Code Snippet

Scope({[Account].[Account Hierarchy].[Net Earnings%]});

FORMAT_STRING(THIS) = "Percent";

End Scope;

Calculating totals for second group level

I have a report that shows Customers, Categories, and Quantity, grouped
by Customers and Categories. (I'm using the Northwind db to try to
figure this out.) I am able to get a grand total in the header for All
Customers and All Categories, but not for each of the categories.
Does anybody know how to do this?
Current Report:
Customer Category Qty
All Customers All Categories 51,317
Alf. Futterk. 174
Beverages 36
Condiments 44
Dairy 35
...
Desired Report:
Customer Category Qty
All Customers All Categories 51,317
Beverages 9,532
Condiments 5,298
Dairy 9,149
Alf. Futterk. 174
Beverages 36
Condiments 44
Dairy 35
...
rdl for existing report:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini..."xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>3pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>16pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.3in</Height>
<CanGrow>true</CanGrow>
<Value>Northwind Subtotals</Value>
</Textbox>
<Table Name="table1">
<Style>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Customer"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Category"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<CanGrow>true</CanGrow>
<Value>="All Customers"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="All Categories"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<DataSetName>northwindsql</DataSetName>
<Top>0.3in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="shipname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>shipname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!shipname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>12pt</FontSize>
<Color>Green</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_shipname">
<GroupExpressions>
<GroupExpression>=Fields!shipname.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!shipname.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>11pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_CategoryName">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>shipname</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.125in</Width>
</TableColumn>
<TableColumn>
<Width>1.875in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.49in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="northwindsql">
<rd:DataSourceID>c2582881-4536-427b-abea-b0fb61d2f245</rd:DataSourceID>
<DataSourceReference>northwindsql</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="northwindsql">
<Fields>
<Field Name="shipname">
<DataField>shipname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>northwindsql</DataSourceName>
<CommandText>SELECT Orders.shipname, Categories.CategoryName,
[Order Details].UnitPrice, [Order Details].Quantity, Orders.OrderID
FROM Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>053a7cd6-e7c8-4554-b1a2-c2291033e518</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>Try this:
* in the second table header, add a list with a details grouping based on
categories
* put two textboxes in that list, one for the category name, the other for
the Sum(...)
I attached the updated report at the bottom of this posting.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jon" <jcrowell@.gmail.com> wrote in message
news:1128960913.001129.271160@.g49g2000cwa.googlegroups.com...
>I have a report that shows Customers, Categories, and Quantity, grouped
> by Customers and Categories. (I'm using the Northwind db to try to
> figure this out.) I am able to get a grand total in the header for All
> Customers and All Categories, but not for each of the categories.
> Does anybody know how to do this?
> Current Report:
> Customer Category Qty
> All Customers All Categories 51,317
> Alf. Futterk. 174
> Beverages 36
> Condiments 44
> Dairy 35
> ...
> Desired Report:
> Customer Category Qty
> All Customers All Categories 51,317
> Beverages 9,532
> Condiments 5,298
> Dairy 9,149
> Alf. Futterk. 174
> Beverages 36
> Condiments 44
> Dairy 35
> ...
=======================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>3pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>16pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.3in</Height>
<Value>Northwind Subtotals</Value>
<CanGrow>true</CanGrow>
</Textbox>
<Table Name="table1">
<Style>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Customer"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Category"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value>="All Customers"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="All Categories"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>2</ColSpan>
<ReportItems>
<List Name="list1">
<ZIndex>6</ZIndex>
<Style>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
</Style>
<Grouping Name="list1_Details_Group">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
<Left>1.875in</Left>
</Textbox>
<Textbox Name="CategoryName_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CategoryName_1</rd:DefaultName>
<Width>1.625in</Width>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
<Left>0.125in</Left>
</Textbox>
</ReportItems>
</List>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<DataSetName>northwindsql</DataSetName>
<Top>0.3in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="shipname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>shipname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!shipname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>12pt</FontSize>
<Color>Green</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_shipname">
<GroupExpressions>
<GroupExpression>=Fields!shipname.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!shipname.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>11pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_CategoryName">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>shipname</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.125in</Width>
</TableColumn>
<TableColumn>
<Width>1.875in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.49in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>62d84756-f9a0-4007-a8f5-1a466039dfe4</rd:DataSourceID>
<DataSourceReference>Northwind</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="northwindsql">
<Fields>
<Field Name="shipname">
<DataField>shipname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Orders.shipname, Categories.CategoryName,
[Order Details].UnitPrice, [Order Details].Quantity, Orders.OrderID
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>053a7cd6-e7c8-4554-b1a2-c2291033e518</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Calculating average by hierarchy level

Hi all,

I have a problem which needs to be sorted out immediate in Analysis service Cube. My requirement is as follows

The following data explains the average value of each employee in corresponding level.

Level1 - > E1 – (25hrs /25days) =1 hrs/day

Level2 - >E2 – (125hrs /25days) = 5 hrs/day

Level3 >E4 – (150hrs /25days) = 6hrs/day

Level4 > E6 – (100hrs /25days) = 4hrs/day

Level4 > E7 – (75hrs/25days) = 3hrs/day

Level4 > E8 – (175hrs/25days) = 7hrs/day

Level3 >E5 – (75hrs/25days) = 3hrs/day

Level2 - >E3 – (100hrs /25days) = 4hrs/day

Eg:

I have productivity records of each day and each employee. I need to calculate avg of each last level employee productivity by monthly. Again Last level employee productivity avg must be added up with their immediate head. But, when I define a Measure Item as avg in the cube, it sums all the values of lost level employees & headand divides with number of records (normal avg).

My requirement is calculating each head avg by sum of each last level employee avgs / no of employees. If head having value, he too will be added. Again Head’s Avg will be added up immediate head.

The following calculation gives average value at each level.

Average of Level 3(E4) = > (4+3+7+6)/4 = 5 hrs/day< = (E6+E7+E8+E4)/4

Average of Level 2(E2) = > (5+3+5)/3= 4.333< =avg(Level3(E4))+avg(Level3(E5)))/2

Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111

< = avg(Level3(E2))+avg(Level3(E3)))/2

Formula for average of level :

: (Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)

I want to calculate average of each employee as well as average of each level in cube (SQL Server Analysis Services).

Thanks in advance

Thiru

I'm not 100% clear on what exactly you are wanting to calculate, so I started with the bolded string towards the end of your post:

(Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)

Looking at this, I worked through some calculations on Adventure Works. Hopefully there is something in there that helps lead you to your answer.

Take a very close look at the formulas and the data returned by each. There is a very important concept illustrated in this example regarding Data Members. In a parent-child hierarchy, a member may have data of their own as well as data from its children. These are stored separately. Books Online has a good article on this, "Working with Attributes in Parent-Child Hierarchies ", which explains this in better detail than I can here.

Hope this leads you to a solution,
Bryan

Code Snippet

with member [Measures].[Num of Children Inc Self] as

COUNT(DESCENDANTS([Employee].[Employees].CurrentMember,,SELF_AND_AFTER))

member [Measures].[Self Quota] as

([Employee].[Employees].DataMember,[Measures].[Sales Amount Quota]),

format="Currency"

member [Measures].[Children Quota] as

SUM(

DESCENDANTS([Employee].[Employees].CurrentMember,,AFTER),

([Employee].[Employees].DataMember,[Measures].[Sales Amount Quota])

),

format="Currency"

member [Measures].[Self + Children Quota] as

[Measures].[Self Quota]+[Measures].[Children Quota]

member [Measures].[Avg Quota] as

[Measures].[Self + Children Quota]/[Measures].[Num of Children Inc Self]

select

{

[Measures].[Num Of Children Inc Self],

[Measures].[Sales Amount Quota],

[Measures].[Self Quota],

[Measures].[Children Quota],

[Measures].[Self + Children Quota],

[Measures].[Avg Quota]

} on 0,

DESCENDANTS([Employee].[Employees].[Brian S. Welcker],,SELF_AND_AFTER) on 1

from [Adventure Works]

;

Calculated Members and Aggregation

Hi,

I am still in AS 2000 and would like to know if it is possible to have calculations performed at lowest granular level of the cube before aggregation.Perhaps I am missing a basic understanding on this but what I am hoping to achieve is to hold parameters at the lowest level in a separate measure ‘Param’ (separate column in the Fact table) and then reference to those parameters in a calculated member with the resulting values held in the default measure of the cube before being rolled up in the aggregations to higher levels.

Currently in the higher levels input parameters are being aggregated before determination of the calculated member value.

Will much appreciate your suggestions on whether it is possible to achieve above, and if not is there another more efficient way to handle.

Barry

Hi Barry,

Could you describe a more concrete scenario - what is the specific data and business problem you're trying to solve, with an example? It is harder to suggest alternative approaches with such an abstract description.

|||

Thanks for getting back to me on this Deepak.

By way of simplified example:

I have a Sales cube with two dimensions ‘Product’ and ‘Account’ and two measures ‘Amount’ and ‘Param’ (‘Amount’ being the default).Lets say there are two rows in the fact table, as follows:

AccountProductAmountParam

SalesApples-2000.1

SalesOranges-2000.2

I have a calculated member for ‘commission expense’ in the ‘Account’ dimension:

-[Account].[Sales] * ([Account].[Sales], [Product].CurrentMember, [Measures].[Param])

After processing I get the following:

All ProdsApplesOranges

Sales-400-200-200

Commission1202040

Obviously, this 'logically incorrect' result at ‘All Prods’ level is due to aggregation occurring prior to commission calc.

In essence what I want to do is hold parameters (in this example ‘commission rate’, that varies by product), at lowest granular level (individual product), then reference to that parameter in calculated member, before aggregation taking place. Might be asked why not have this calc performed in underlying fact table input, but that does not suit my purpose.

I suppose question I am asking:Is it possible to override default order of processing in the cube ?

As already noted perhaps I am misunderstanding a very basic point here… will be pleased if you can put my thinking straight.

Many thanks in advance

Barry

Calculated Member using CurrentMember.Properties("XX") does not work for ALL Level on

IIF (

[HAP5All_Board].CurrentMember.Properties("Dept") = "PH",

(

[BoardIncAcct2].&[61510000] -- General Services

+

[BoardIncAcct2].&[61520000] -- Accounting & Financem Services

+

[BoardIncAcct2].&[61530000] -- Information Technology Services

+

[BoardIncAcct2].&[61540000] -- Purchaseing Services

+

[BoardIncAcct2].&[61550000] -- Executive Services

+

[BoardIncAcct2].&[61560000] -- Human Resources Services

+

[BoardIncAcct2].&[61570000] -- Public Affairs Services

+

[BoardIncAcct2].&[61580000] -- Training Services

)

,

0

) -- IIF Section to filter in only PH

This is in the definition of a calculated member. It works fine for all members except for the "ALL level" on the dimension (Parent-Child dimension). Because the "All Level" is not actually a member/record in the table but defined on the dimension, it does not have Properties for that particular "member", so AS errors out when trying to focus on top ALL Level. What should we do to use such definition on ALL Level?

Maybe you could check for the [All] member first, like:

IIF (

[HAP5All_Board].CurrentMember is [HAP5All_Board].[All],

0,

IIF (

[HAP5All_Board].CurrentMember.Properties("Dept") = "PH",

(

[BoardIncAcct2].&[61510000] -- General Services

+

[BoardIncAcct2].&[61520000] -- Accounting & Financem Services

+

[BoardIncAcct2].&[61530000] -- Information Technology Services

+

[BoardIncAcct2].&[61540000] -- Purchaseing Services

+

[BoardIncAcct2].&[61550000] -- Executive Services

+

[BoardIncAcct2].&[61560000] -- Human Resources Services

+

[BoardIncAcct2].&[61570000] -- Public Affairs Services

+

[BoardIncAcct2].&[61580000] -- Training Services

)

,

0

) -- IIF Section to filter in only PH

)

|||

Thanks, Deepak!

That works syntactically. OLAP took it. However, I am wondering that ALL level is taking the aggregate sumation of all children, would that (IF ALL THEN 0) rules out children that actually is PH in Properties. Or in other words, I need to have PH and all others roll up into ALL with PH children having those accounts incluced. Would (IFF ALL THEN 0) prevent PH children to be correctly rolled up? Am I making sense?

Thanks again!

Julius

|||

Hi Julius,

Could you illustrate your exact scenario with some examples - I have only a vague idea of what you want, since 2 different dimensions seem to be involved?

Calculated Member Problem in RS

Hi,
There are two problems during I tested the Cube in RS:
1. If I choice a Member is the Top Level of the Dimension, as following, the
"All" this Member.
The All Member is the name of "All Caption" of the AS Advanced Property
and it's the sum of the Department Dimension.
When I use this All Member in RS, I can't see the Member name in Dataset
Fields, Why?
Sales Department Dimension
|--All
|--dept A
|--dept B
|--dept C
2. When the source data is Cube and use Matrix to present, is any way to
calculate dimension member on Column and Row?
It's mean that I want to use the member in Column and Row to calculate a
Calculated Member and show its value on "data" cell.
Or we should make the Calculated Member in AS first and select it on RS?
Have any idea about this and how to do?
Thanks for your time!
AngiLook for a whitepaper on Analysis Services and Reporting Services on the
Microsoft website. I know it is there. I read it and concluded that RS is
absolutely not ready for AS.
The disappearing "All" member is a known issue. The whitepaper shows ways
around the most common problems you'll encounter.
Hth,
Tom
"angi" wrote:
> Hi,
> There are two problems during I tested the Cube in RS:
> 1. If I choice a Member is the Top Level of the Dimension, as following, the
> "All" this Member.
> The All Member is the name of "All Caption" of the AS Advanced Property
> and it's the sum of the Department Dimension.
> When I use this All Member in RS, I can't see the Member name in Dataset
> Fields, Why?
> Sales Department Dimension
> |--All
> |--dept A
> |--dept B
> |--dept C
> 2. When the source data is Cube and use Matrix to present, is any way to
> calculate dimension member on Column and Row?
> It's mean that I want to use the member in Column and Row to calculate a
> Calculated Member and show its value on "data" cell.
> Or we should make the Calculated Member in AS first and select it on RS?
> Have any idea about this and how to do?
> Thanks for your time!
> Angi
>
>