aggregation using lastchild

Hi there

I'm using the following MDX function and it works perfect.

iif([Ledger Date].CurrentMember.Level.Name = "Year", ", [Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

But now I want to add another dimension namely Accout Type to the scenario containing Asset, Liabilities, Income and Expence. The lastchild must only work for "Year", "Liabilities" and "Assets"

How do I achive this?

Thank you in advance.

Try :

iif( ([Ledger Date].CurrentMember.Level.Name = "Year" and [Account Type].CurrentMember is [Account Type].[Liabilities]) or ([Ledger Date].CurrentMember.Level.Name = "Year" and [Account Type].CurrentMember is [Account Type].[Assets]) , ", [Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

|||

i've adjusted your suggestion to the following:

iif(([Ledger Date].CurrentMember.Level.Name = "Year" and [Ledger Entries].CurrentMember is [Ledger Entries].[Account Type].&[Asset]) ,[Ledger Date].CURRENTMEMBER.LASTCHILD, ([Ledger Date].CURRENTMEMBER, [Measures].[Value]))

but then I get the following error:

Infinite recursion detected during execution of calculated member.

any idea why?

thanks again.

Aggregate with different function in different level

I have an area dimension with three levels: Area, County, and School
I want to aggregate the number of students with different function in
different level, like that in the school level, I want to aggregate student
number with max, in the County and Area level, and I want to aggregate the
student number with sum.
How can I do that?
you can create 2 measures, 1 with the max aggregation, the second with the
sum aggregation.
Create a calculated measure which use the max result if the user is at the
school level, and the sum at the other levels.
like this:
iif(Schools.Currentmember.level is Schools.School, MAXMEASURE, SUMMEASURE)
but this works if your sum is calculated from the fact table directly and
NOT the sum of the max of each school.
"ad" <ad@.wfes.tcc.edu.tw> a crit dans le message de news:
%233WnPoUrEHA.2724@.TK2MSFTNGP14.phx.gbl...
>I have an area dimension with three levels: Area, County, and School
> I want to aggregate the number of students with different function in
> different level, like that in the school level, I want to aggregate
> student
> number with max, in the County and Area level, and I want to aggregate the
> student number with sum.
> How can I do that?
>

Aggregate may not appear in an UPDATE statement

I am trying to update a field in a table with the most recent event_date. However, I am receiving the following error:

Msg 157, Level 15, State 1, Line 1

An aggregate may not appear in the set list of an UPDATE statement.

My code appears below:

-

update mraOI

set mraOI.event_history_key = eh.event_history_key,

mraOI.event_date = MAX(eh.event_date),

mraOI.event_time = eh.event_time,

mraOI.event_type_key = eh.event_type_key,

mraOI.item_key = eh.item_key,

mraOI.associated_item_ke = eh.associated_item_ke

FROM reports.MedRecActionOI mraOI INNER JOIN

srm.EVENT_HISTORY eh ON mraOI.CC_episode_key = eh.item_key

--

Can someone suggest a better method or a work around?

You could try this:

Code Snippet

update mraOI

set mraOI.event_history_key = eh.event_history_key,

mraOI.event_date = eh.event_date,

mraOI.event_time = eh.event_time,

mraOI.event_type_key = eh.event_type_key,

mraOI.item_key = eh.item_key,

mraOI.associated_item_ke = eh.associated_item_ke

FROM reports.MedRecActionOI mraOI INNER JOIN

srm.EVENT_HISTORY eh ON mraOI.CC_episode_key = eh.item_key

where eh.event_date = (select max(event_date) from srm.EVENT_HISTORY where item_key = mraOI.CC_episode_key)

I haven't tested it as I don't have your DB structure, but hopefully that'll help you.

One potential issue to be aware of. If there are more than one event with the same event_date you could get more than one row. If you have the time as well as the date it's very unlikely to happen, as times are accurate to 3ms. However if you're using smalldatetime they're only accurate to 1 minute, so the risk of getting more than one with the same value is greater.

Sean

|||This worked great, Sean! Thanks.

aggregate calculations on xml ?

i've been breaking my head presenting aggregate summary calculations on
higher level nodes (. using sql server 2005 for xml path method. i'd
need output something like this:
<row>
<customer id="1" ocnt="2" icnt="7">
<order id = "o1" icnt="3">
<item id="i1" cnt=1/>
<item id="i2" cnt=2/>
</order>
<order id = "o1" icnt="4">
<item id="i1" cnt=1/>
<item id="i2" cnt=1/>
<item id="i3" cnt=2/>
</order>
<customer>
</row>
how to calculate values icnt and ocnt?
i know one way would be using translations. but i want to do it
directly from single query direct into xml. how to do it? is there a
way to reference xml elements from for xml path query and aggregate
that way? there must be an easy way. thanks in advance, Ed.Hello ubator@.gmail.com,

> how to calculate values icnt and ocnt?
Hows this?
declare @.x xml
set @.x = '<row>
<customer id="1" ocnt="2" icnt="7">
<order id = "o1" icnt="3">
<item id="i1" cnt="1"/>
<item id="i2" cnt="2"/>
</order>
<order id = "o1" icnt="4">
<item id="i1" cnt="1"/>
<item id="i2" cnt="1"/>
<item id="i3" cnt="2"/>
</order>
</customer>
</row>'
select @.x.value('count(/row/customer/order)','int') as ocnt
select @.x.value('sum(/row/customer/order/item/@.cnt)','int') as icnt
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Ubator,
You could try something like this :-)
USE Northwind;
WITH x AS (
SELECT
c.CustomerID
,(SELECT o.OrderID AS "@.id" FROM dbo.Orders AS o
WHERE o.CustomerID = c.CustomerID
FOR XML PATH('order'), TYPE
) AS orders
FROM dbo.Customers AS c
)
SELECT
x.CustomerID AS "@.id"
,x.orders.value('count(/order)', 'INT') AS ordercount
,x.orders AS "node()"
FROM x
FOR XML PATH('customer')
GO
HTH
/ Tobias|||Thanks, Tobias, this would be it. I thought there is a chance to submit
it in a single query with no need of procedure but i guess it's not
possible.
I've found also this link that could be useful:
http://www.codecomments.com/archive...6-1-746830.html|||> Thanks, Tobias, this would be it. I thought there is a chance to submit
> it in a single query with no need of procedure but i guess it's not
> possible.
How do you mean?
This is a single query...
/ Tobias

Agg op question

hi,

i have a table 'Details' with columns [id int; level varchar(20)]. i want to get two things from one single query:
1) the total number of items with Details.id=xxx
2) all the associated [level] text

i tried a few times doing things like:
SELECT [Level], COUNT(*) FROM Details WHERE id=xxx GROUP BY id

but it either gives me error "Column [Level] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

i am using MS SQL Server as the back engine and T-SQL in Access...

Help~

thanks in advancePerhaps you can do this:

select level, (select count(*) from details where id=xxx) as cnt
from details where id=xxx;

Works in Oracle, I don't know about your DBMS.|||Beautifully done~ thanks!

After attaching Sql Server 7 database can't make a diagram

I have successfully attached an sql server 7 database, changed the owner to
sa and the compatibility level to 2000 but when I try to create a diagram, I
get the following message. I go to the MS site and it is no help:
TITLE: Microsoft SQL Server Management Studio
--
Invalid column name 'uvalue'.
Invalid column name 'uvalue'.
Could not find stored procedure 'dbo.sp_upgraddiagrams'.
Object is invalid. Extended properties are not permitted on
'dbo.sysdiagrams', or the object does not exist.
Object is invalid. Extended properties are not permitted on
'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
Server, Error: 207)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
--
BUTTONS:
OKHi Ellie
The link talks about case sensitive collations. What collation are you using?
It also mentions Management Studio, but you said SQL 2000!
John
"Ellie" wrote:
> I have successfully attached an sql server 7 database, changed the owner to
> sa and the compatibility level to 2000 but when I try to create a diagram, I
> get the following message. I go to the MS site and it is no help:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Invalid column name 'uvalue'.
> Invalid column name 'uvalue'.
> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
> Object is invalid. Extended properties are not permitted on
> 'dbo.sysdiagrams', or the object does not exist.
> Object is invalid. Extended properties are not permitted on
> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
> Server, Error: 207)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
> --
> BUTTONS:
> OK
>
>|||Hi John,
The collation is:
SQL_Latin1_General_CP1_CI_AS
It was the default. I am using Sql Server 2005 but made the database
compatible with 2000. I'm really not that familiar with the workings of the
server. I have another database that I am able to use the diagrams and it
looks like I have the same settings as this one so I don't know what's
wrong. Thanks Ellie
-- Original Message --
From: "John Bell" <jbellnewsposts@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 23, 2008 3:33 AM
Subject: RE: After attaching Sql Server 7 database can't make a diagram
> Hi Ellie
> The link talks about case sensitive collations. What collation are you
> using?
> It also mentions Management Studio, but you said SQL 2000!
> John
> "Ellie" wrote:
>> I have successfully attached an sql server 7 database, changed the owner
>> to
>> sa and the compatibility level to 2000 but when I try to create a
>> diagram, I
>> get the following message. I go to the MS site and it is no help:
>>
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Invalid column name 'uvalue'.
>> Invalid column name 'uvalue'.
>> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sysdiagrams', or the object does not exist.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
>> Server, Error: 207)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
>> --
>> BUTTONS:
>> OK
>>
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3EF7E80C-B854-4EF9-B1CF-D0B2F345E544@.microsoft.com...
> Hi Ellie
> The link talks about case sensitive collations. What collation are you
> using?
> It also mentions Management Studio, but you said SQL 2000!
> John
> "Ellie" wrote:
>> I have successfully attached an sql server 7 database, changed the owner
>> to
>> sa and the compatibility level to 2000 but when I try to create a
>> diagram, I
>> get the following message. I go to the MS site and it is no help:
>>
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Invalid column name 'uvalue'.
>> Invalid column name 'uvalue'.
>> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sysdiagrams', or the object does not exist.
>> Object is invalid. Extended properties are not permitted on
>> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
>> Server, Error: 207)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
>> --
>> BUTTONS:
>> OK
>>|||Hi Ellie
I can't say I use diagrams much! The easiest option may be to drop them
before detaching and re-creating them when they on the destination system.
Which @.@.version of SQL 2005 are you using?
John
"Ellie" wrote:
> Hi John,
> The collation is:
> SQL_Latin1_General_CP1_CI_AS
> It was the default. I am using Sql Server 2005 but made the database
> compatible with 2000. I'm really not that familiar with the workings of the
> server. I have another database that I am able to use the diagrams and it
> looks like I have the same settings as this one so I don't know what's
> wrong. Thanks Ellie
> -- Original Message --
> From: "John Bell" <jbellnewsposts@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 23, 2008 3:33 AM
> Subject: RE: After attaching Sql Server 7 database can't make a diagram
>
> > Hi Ellie
> >
> > The link talks about case sensitive collations. What collation are you
> > using?
> >
> > It also mentions Management Studio, but you said SQL 2000!
> >
> > John
> >
> > "Ellie" wrote:
> >
> >>
> >> I have successfully attached an sql server 7 database, changed the owner
> >> to
> >> sa and the compatibility level to 2000 but when I try to create a
> >> diagram, I
> >> get the following message. I go to the MS site and it is no help:
> >>
> >>
> >> TITLE: Microsoft SQL Server Management Studio
> >> --
> >>
> >> Invalid column name 'uvalue'.
> >> Invalid column name 'uvalue'.
> >> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sysdiagrams', or the object does not exist.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
> >> Server, Error: 207)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
> >>
> >> --
> >> BUTTONS:
> >>
> >> OK
> >>
> >>
> >>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3EF7E80C-B854-4EF9-B1CF-D0B2F345E544@.microsoft.com...
> > Hi Ellie
> >
> > The link talks about case sensitive collations. What collation are you
> > using?
> >
> > It also mentions Management Studio, but you said SQL 2000!
> >
> > John
> >
> > "Ellie" wrote:
> >
> >>
> >> I have successfully attached an sql server 7 database, changed the owner
> >> to
> >> sa and the compatibility level to 2000 but when I try to create a
> >> diagram, I
> >> get the following message. I go to the MS site and it is no help:
> >>
> >>
> >> TITLE: Microsoft SQL Server Management Studio
> >> --
> >>
> >> Invalid column name 'uvalue'.
> >> Invalid column name 'uvalue'.
> >> Could not find stored procedure 'dbo.sp_upgraddiagrams'.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sysdiagrams', or the object does not exist.
> >> Object is invalid. Extended properties are not permitted on
> >> 'dbo.sp_upgraddiagrams', or the object does not exist. (Microsoft SQL
> >> Server, Error: 207)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
> >>
> >> --
> >> BUTTONS:
> >>
> >> OK
> >>
> >>
> >>
>
>