Aggregete() function doesn't work for SQL query results! - Part 2

I am writing my own MDX query inside Reporting Services, bypassing the graphic MDX generator.

Both MDX query and SQL query return identical results and both have detail level values and high level values.

During the flattenning process, null value from MDX is still null, but null value from SQL is translated into blank.

Thus only the MDX result works for Aggregate() function, not the SQL result. Because Aggregate() function is triggered by null value, not blank.

Someone suggested to use MDX stored procedure to do custom calculations. Thus my report is calling a MDX query, not a SQL query. I have tried this approach already, but MDX is not as flexible as SQL. For example, in SQL, I can easily join, pivot, order by, group by, filter...None of these is easy in MDX.

If I know how to make reporting services treat null as null or to make Aggregate() function be triggered by a blank value, then my problem is solved.

Does Aggregate() funciton only works with MDX, not SQL?

Thanks,

Bo Dong

bo_dong@.yahoo.com

The Aggregate() function only works in combination with a data extension that implements the IDataReaderExtension interface (see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_9lit.asp).

The "Analysis Services" data extension available in RS 2005 implements this interface and internally detects aggregation rows based on nulls in hierarchies and exposes that information through the IDataReaderExtension interface.

Note: The "SQL Server" data extension available in RS 2005 does not implement this interface.

You can implement your own custom data extension (http://msdn2.microsoft.com/en-us/library/ms154655.aspx) and then implement the IDataReaderExtension interface - based on that you have full control in your custom data extension of how exactly aggregate rows from your relational query are marked.

-- Robert

Aggregete() function doesn't work for SQL query results!

Reporting Services translates null value into blank on SQL query results, but not on MDX query results. But Aggregate() functions is triggered only by null value.

So Aggregate() function only works for MDX query results, not for SQL query results.

MDX example:

select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube

SQL example:

SELECT * FROM OPENQUERY(Linked_Cube, '
select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube')

Now you build a report with a table, then add a grouping and use "=Aggregate(Fields!Sales.Value)" for the group level cell. If you bind MDX query to this table, then aggregates show up correctly. But if you bind SQL query to this table, there are no aggregates at all.

I need to use SQL query to drive my reports, because MDX query results need to be merged with results from other calculations.

How can I make Aggregate() function to work for SQL query results?

Thanks,

Bo Dong

bo_dong@.yahoo.com

How can I make it to work for SQL as well?

Please read my answer on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=668524&SiteID=1

-- Robert

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.

aggregation question

Aggregation SUM adds the numbers together. Is there a similar aggregation function that returns the product of all the numbers?

Thanks

No, there is no built-in PRODUCT aggregate. One thing you could try is taking the exponent of the sum of the logs of the individual rows; however, this will tend to cause arithmetic execution errors if any of the individual rows have zero for a value.

Here is a similar discussion:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=355075&SiteID=1

|||

Do you mean 'product' as in factorial?

|||

Check out the link below for a solution that uses the built-in aggregates to compute product:

http://www.umachandar.com/technical/SQL6x70Scripts/Main21.htm

You can also write a SQLCLR aggregate in SQL Server 2005 to do the same.

Aggregates

I have a simple table that stores
name score
-- --
jim 343
bob 322
jane 122
Lets say i have a user defined function that applys a formula to the score
and I want to select the minimum value of the function.
eg.
SELECT name, score, MIN(dbo.alterScore(score))
FROM tester
I want to return the smallest value of the when the function is applied
along with the name and score.
How can I do this?
Many thanks.One way is:
SELECT TOP 1 name, score, MIN(dbo.alterScore(score)) AS MinScore
FROM tester
ORDER BY MinScore
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Thomson" <@.@.@.> wrote in message
news:uZVc3NtjFHA.2852@.TK2MSFTNGP15.phx.gbl...
I have a simple table that stores
name score
-- --
jim 343
bob 322
jane 122
Lets say i have a user defined function that applys a formula to the score
and I want to select the minimum value of the function.
eg.
SELECT name, score, MIN(dbo.alterScore(score))
FROM tester
I want to return the smallest value of the when the function is applied
along with the name and score.
How can I do this?
Many thanks.|||Why did you post the same question twice to the same group within 30 minutes
of each other?!
Your question was already answered (by multiple people) in your previous
thread titled "Easy SQL Problem"
"Mark Thomson" <@.@.@.> wrote in message
news:uZVc3NtjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> I have a simple table that stores
> name score
> -- --
> jim 343
> bob 322
> jane 122
>
> Lets say i have a user defined function that applys a formula to the score
> and I want to select the minimum value of the function.
> eg.
> SELECT name, score, MIN(dbo.alterScore(score))
> FROM tester
>
> I want to return the smallest value of the when the function is applied
> along with the name and score.
> How can I do this?
> Many thanks.
>

Aggregate(field, scope) function against Analysis Services 2005

Hi folks,
I am trying to make use of a new feature that is supposed to be in
RS2005, AS2005 but Im not sure if it is really there.
The web page
<http://www.microsoft.com/sql/technologies/reporting/faq.mspx> mentions
that AS 2005 has "Report server support for server-calculated
aggregates".
Also the BOL documentation suggests that Aggregate supports aggregation
supplied by the data provider. I was led to hope that RS Aggregate
would somehow get the MDX aggregate of the scoped data.
When I tried I could not get it to work.
The following yields the correct number:
=Sum(Fields!YTDSales.Value, "table1_Group1")
The following parses OK but yields an empty cell:
=Aggregate(Fields!YTDSales.Value, "table1_Group1")
The reason I am doing this is to write generic reports that allow users
to select measures that may aggregate in different ways (e.g. Total
Cost and Margin%).Did you ever get a solution to this? I am having the same difficulty. I want
to use the power of Analysis Services to do the calculations and
aggregations. That way we can centralize the definition of all calculations
and the calculation script and just pull the results into whatever reports we
want.
"FatOaf" wrote:
> Hi folks,
> I am trying to make use of a new feature that is supposed to be in
> RS2005, AS2005 but Im not sure if it is really there.
> The web page
> <http://www.microsoft.com/sql/technologies/reporting/faq.mspx> mentions
> that AS 2005 has "Report server support for server-calculated
> aggregates".
> Also the BOL documentation suggests that Aggregate supports aggregation
> supplied by the data provider. I was led to hope that RS Aggregate
> would somehow get the MDX aggregate of the scoped data.
> When I tried I could not get it to work.
> The following yields the correct number:
> =Sum(Fields!YTDSales.Value, "table1_Group1")
> The following parses OK but yields an empty cell:
> =Aggregate(Fields!YTDSales.Value, "table1_Group1")
> The reason I am doing this is to write generic reports that allow users
> to select measures that may aggregate in different ways (e.g. Total
> Cost and Margin%).
>

Aggregate() function not working when a measure is not specified.

Hi,

I am new to Analysis Services, having used it for less than a month. I do apologise if this problem is the result of a stupid newbie mistake, but I could really use some help.

I am totally unable to get the Aggregate() function to work unless I specify the optional measure. I have build a cube from the Adventure Works DW database, based on the internet sales fact table and related tables. I used the wizard to design the hierarchy for the time dimension.

Both of the following queries fail with the same error message:

Query 1:

WITH MEMBER [Time Aggregate Test] AS 'Aggregate({[Ship Date].[Calendar Year - Calendar Semester - Calendar Quarter - English Month Name - Day Number Of Month].[Calendar Year].&[2002].&[2].&[3].&[7]:[Ship Date].[Calendar Year - Calendar Semester - Calendar Quarter - English Month Name - Day Number Of Month].[Calendar Year].&[2003].&[1].&[2].&[5]})'

SELECT [Time Aggregate Test] ON 0,{[Measures].[Sales Amount], [Measures].[Tax Amt]} ON 1

FROM [Adventure Works DW]

Query 2

WITH MEMBER [Aggregate Test]

AS 'Aggregate({[Dim Product].[English Product Name].&[Blade],[Dim Product].[English Product Name].&[Chain]})'

SELECT [Aggregate Test] ON 0,{[Measures].[Sales Amount], [Measures].[Tax Amt]} ON 1

FROM [Adventure Works DW]

The error message is: 'The Measures hierarchy already appears on the axis0 axis. It is very important for me to be able to show the aggregate members on columns and the measures on rows if at all possible. I have checked the AggregateFunction on both measures, and it is set to Sum, which is what I want. Again, I do apologise if this is a simple newbie mistake and I would be really grateful for any help.

Since in the WITH statement you didn't specify parent hierarchy, it assumed Measures. So you ended up with same hierarchy (Measures) being used both on columns and rows. To fix you can do this:

WITH MEMBER [Dim Product].[English Product Name].[Aggregate Test]

AS 'Aggregate({[Dim Product].[English Product Name].&[Blade],[Dim Product].[English Product Name].&[Chain]})'

SELECT [Aggregate Test] ON 0,{[Measures].[Sales Amount], [Measures].[Tax Amt]} ON 1

FROM [Adventure Works DW]

|||This works perfectly. Thank you very much for your help, Mosha. Your blog has made very useful reading, by the way.

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 String Concatenation function?

Hi,

I'm trying to do the following, but am getting errors because (obviously) SUM doesn't work with data types of nvarchar.

SELECT
SUM(CASE WHEN FieldName = 'SPECIFIC' THEN Tolerance ELSE '' END) AS 'Specific Tolerance'
FROM FIELD_TOLERANCE
GROUP BY Area

Tolerance holds values such as '100 +/- 25'. Obviously the first thought would be to seperate the two parts '100' and '25' into seperate fields and then have the program reconstruct it. Unfortunatly sometimes the value is odd, such as '100 +10 -25' (meaning a range of 75 - 110 with a target of 100).

Is there any way to put effectively sum up the Tolerance. Also, I know for a fact the FieldName 'SPECIFIC' will only be in the database once for each area.

Thanks,
RyanI searched Books Online for aggregate functions as well as just functions. I found nothing listed under either which would help.

Should I create a function for this task? How would I create a function to do this?

Thanks,
Ryan|||We no longer need to do what I was asking about. But is there a way? I'm curious.|||If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?|||Originally posted by MCrowley
If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?

well I have a table like so (dashes inserted for web formating purposes):

Area--Field--Tolerance
1----FieldA--100 +/- 12
1----FieldB--100 +/- 13
2----FieldA--97 +3 -7
2----FieldC--95 +/- 5

Area type = int
Field type = varchar
Tolerance type = varchar

I want the results I want are as follows (dashes inserted for web formating purposes):

Area--FieldATolerance--FieldBTolerance--FieldCTolerance
1----100 +/- 12---100 +/-13
2----97 +3 -7----------95 +/- 5

This way I can pass the values for field I want the tolerances for and get them all back in one record. This allows the Tolerance table to hold tolerances for different fields, yet make retrieving the tolerances easy.

-Ryan

ps. Thanks for the reply

Aggregate on an aggregate

I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",

"tPCT"."BatchKey"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "tPCT"."BatchKey"

It yields the following results

ItemID Short Description Freeze Qty Current Qty BatchKey 3002954 SET, WRENCH HEX METRIC -33 129 42221 3002954 SET, WRENCH HEX METRIC 51 129 42244 3002954 SET, WRENCH HEX METRIC -31 129 42250

I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:

3002954 SET, WRENCH HEX METRIC -13 129

Can this be done via a subquery? Any assistnance would be greatly appreciated?

Thanks,

DLee

I would think you could create a subquery using the following;

SELECT "ItemID","Short Description","Current Qty", SUM("Freeze Qty")

FROM ("vSS");

|||

Donna, try this query

SELECT ItemID

, MAX(SQ.ShortDesc) as [Short Description]

, SUM(SQ.FreezeQty) as [Freeze Qty]

, MAX(SQ.[Current Qty]) as [Current Qty]

FROM (

SELECT tI.ItemID

, MAX(vSS.ShortDesc)

, MAX(tPCT.FreezeQty)

, SUM(vSS.QtyOnHand)

, tPCT.BatchKey

FROM vSS INNER JOIN tI

ON vSS.ItemKey=tI.ItemKey

LEFT OUTER JOIN tPCT

ON vSS.ItemKey=tPCT.ItemKey

WHERE vSS.ItemID = '3002954'

GROUP BY tI.ItemID, tPCT.BatchKey

) SQ

GROUP BY SQ.ItemID

|||

Hello Donna,

Tweak your query a little and try this...

SELECT "tI"."ItemID", "vSS"."ShortDesc" "Short Description",

SUM("tPCT"."FreezeQty") "Freeze Qty", "vSS"."QtyOnHand" "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "vSS"."ShortDesc", "vSS"."QtyOnHand"

Hope this helps.

Regards.....

|||

If you do not need BatchKey, you can just remove it from the query and you should get the desired answer.

e.g.

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID"

|||

This did the trick!

Thanks Gopi!

Aggregate on an aggregate

I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",

"tPCT"."BatchKey"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "tPCT"."BatchKey"

It yields the following results

ItemID Short Description Freeze Qty Current Qty BatchKey 3002954 SET, WRENCH HEX METRIC -33 129 42221 3002954 SET, WRENCH HEX METRIC 51 129 42244 3002954 SET, WRENCH HEX METRIC -31 129 42250

I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:

3002954 SET, WRENCH HEX METRIC -13 129

Can this be done via a subquery? Any assistnance would be greatly appreciated?

Thanks,

DLee

I would think you could create a subquery using the following;

SELECT "ItemID","Short Description","Current Qty", SUM("Freeze Qty")

FROM ("vSS");

|||

Donna, try this query

SELECT ItemID

, MAX(SQ.ShortDesc) as [Short Description]

, SUM(SQ.FreezeQty) as [Freeze Qty]

, MAX(SQ.[Current Qty]) as [Current Qty]

FROM (

SELECT tI.ItemID

, MAX(vSS.ShortDesc)

, MAX(tPCT.FreezeQty)

, SUM(vSS.QtyOnHand)

, tPCT.BatchKey

FROM vSS INNER JOIN tI

ON vSS.ItemKey=tI.ItemKey

LEFT OUTER JOIN tPCT

ON vSS.ItemKey=tPCT.ItemKey

WHERE vSS.ItemID = '3002954'

GROUP BY tI.ItemID, tPCT.BatchKey

) SQ

GROUP BY SQ.ItemID

|||

Hello Donna,

Tweak your query a little and try this...

SELECT "tI"."ItemID", "vSS"."ShortDesc" "Short Description",

SUM("tPCT"."FreezeQty") "Freeze Qty", "vSS"."QtyOnHand" "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "vSS"."ShortDesc", "vSS"."QtyOnHand"

Hope this helps.

Regards.....

|||

If you do not need BatchKey, you can just remove it from the query and you should get the desired answer.

e.g.

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID"

|||

This did the trick!

Thanks Gopi!

Aggregate Functions on char fields?

hi,
i wondered whether there is any way to simulate a kind of aggegate function
that summerizes char/varchar fiealds.
to make myself clear, please look at the following table t1 which has 2 int
fields:
f1 | f2
--
1 | 10
1 | 20
2 | 30
select f1, sum(f2) s1 from t1 group by f1
the result would be:
f1 | s1
--
1 | 30
2 | 30
so far so good.
now, please look at the following table t2 which as 1 int field and 1 char
field:
f1 | f2
--
1 | A
1 | B
2 | C
select f1, sum(f2) s1 from t2 group by f1
i want the result to be:
f1 | s1
--
1 | A,B
2 | C
is there any way to do it through 1 query only?
thanks!edo
First of all it has nothing to do with aggregates. It is called a
contacenation
Second, I'd strongly recommend you doing such reports on the client side
create table w
(
id int,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"edo" <ewilde@.nana.co.il> wrote in message
news:uHe0RT1lFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi,
> i wondered whether there is any way to simulate a kind of aggegate
> function
> that summerizes char/varchar fiealds.
> to make myself clear, please look at the following table t1 which has 2
> int
> fields:
> f1 | f2
> --
> 1 | 10
> 1 | 20
> 2 | 30
> select f1, sum(f2) s1 from t1 group by f1
> the result would be:
> f1 | s1
> --
> 1 | 30
> 2 | 30
> so far so good.
> now, please look at the following table t2 which as 1 int field and 1 char
> field:
> f1 | f2
> --
> 1 | A
> 1 | B
> 2 | C
> select f1, sum(f2) s1 from t2 group by f1
> i want the result to be:
> f1 | s1
> --
> 1 | A,B
> 2 | C
>
> is there any way to do it through 1 query only?
>
> thanks!
>|||Thanks !!

Aggregate Function: AverageOfChildren

I am using the Aggregate Funtion: AverageOfChildren for a Measure.

I want to write a equivalent SQL query for the same. Any suggestions as to how can I ge the AverageOfChildren aggregation in a SQL query.

Thanks.

How about using the SQL Avg() aggregate function?

Aggregate function vs parent-child dimension (SSAS)

Hi!
I have to create a report using a cross-join of an attribute hierarchy and a
parent-child hierarchy. For the parent-child hierarchy, I use a nested detail
group in my report. (group by uniquename and parent group by parents
uniquename) The attribute hierarchy is shown as a group around the detail
level.
For regular crossjoins of non-jagged hierarchies, I can use Aggregate() to
get the value of the dataset line with (null) shown as the all level. In this
case though, the parent-child hierarchy's all member is shown as "All". If I
use aggregate in the attribute hierarchy group now, the value returned is
blank.
Is there a way to make the parent-child all memeber show as null, or any
other way to eliminate it so Aggregate() will work? Eventually, any other way
to implement this report?
--
Lars-ErikHello Lars,
I would like to get more detailed information to assist this issue.
What's the MDX query you use to get the data?
And how you join the parent-child hierarchy to others?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi!
Not sure what you mean by how I join. There's an intermediate dimension
between the measures and the parent-child dimension.
The query looks something like this:
select {} on 0, crossjoin([AttributeDim].[AttributeHierarchy].Members,
[ParentChildDim].[Hierarchy].Members) on 1 from [MyCube]
Result in SSRS will be
[Attribute], [ParentChildAtt]
(null), All
Att1, All
Att2, All
Att1, Level 2
Att1, Level 2.2
If the parent-child hierarchy was a regular hierarchy, the All member would
be (null) too.. I guess it's because the members are flattened to the same
column instead of separate ones as for a regular hierarchy.
As far as I remember, RS 2000 created a column for each level that existed
at design time. 2005 puts all in one column.
--
Lars-Erik
"Wei Lu [MSFT]" wrote:
> Hello Lars,
> I would like to get more detailed information to assist this issue.
> What's the MDX query you use to get the data?
> And how you join the parent-child hierarchy to others?
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Lars,
This issue may related to the MDX Query you use. I am consulting some
internal person.
I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Lars,
Here is an example of query created using Adventure Works by means of
dragging "Internet Order Count" measure, "Employees" parent-child hierarchy
and "Employee Title" attribute hierarchy.
SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS, NON
EMPTY { (DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS)
* [Employee].[Title].[Title].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON
ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
I would like to know whether you have resolved this issue or not. If you
have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi again!
Thanks for your effort. Sorry for being late. I've been on vacation for
three weeks.
In my query, the attribute hierarchy is the leftmost rowheader and the
parent-child hierarchy should be second. Not sure if that is relevant.
Anyway, we're not at a solution yet.
Have a look at this modified query:
SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS, NON EMPTY
{ ([Employee].[Title].ALLMEMBERS * {[Employee].[Employees].[All Employees],
DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) } ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE,
FONT_FLAGS
I need the all level from the title attribute, so I used the attribute
hierarchy instead of the attribute alone, yealding a (null) item with an
aggregate of all titles. I also added the All Employees member to get
aggregates for all employees per title, and all titles per employee (ie. boss
with all subordinates titles).
The problem is that the All Employees member is returned as "All Employees",
while the All Titles member is returned as (null). SSRS will use the
Aggregate function fine whith (null) representations, but the result of
Aggregate(field) is blank for the top level of the parent-child hierarchy.
(Due to the all emps. member not being null)
--
Lars-Erik
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to know whether you have resolved this issue or not. If you
> have any question, please feel free to let me know.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Lars,
With the MDX query on my side, I also see "All Employees" as "all" level of
Title attribute in the result if I run the MDX query in management studio.
However, in VS Dataset view, I did see the behavior you described.
It seems the SSRS dataset query has different process method for
parent-child dimension such as Employees.
I'm not quite sure about what you get to by using aggregation function. If
you don't want the All level to be included in the aggregation. You may
want to set IsAggregatable to false of the IsAggregatable property of the
attribute at the top-most level
In Microsoft SQL Server 2005 Analysis Services (SSAS), the (All) level is
an optional, system-generated level. It contains only one member whose
value is the aggregation of the values of all members in the immediately
subordinate level. This member is called the All member. It is a
system-generated member that is not contained in the dimension table.
Because the member in the (All) level is at the top of the hierarchy, the
member's value is the consolidated aggregation of the values of all members
in the hierarchy. The All member often serves as the default member of a
hierarchy.
The presence of an (All) level in an attribute hierarchy depends on the
IsAggregatable property setting for the attribute and the presence of an
(All) level in a multilevel hierarchy depends on the IsAggregatable
property of the attribute at the top-most level of multilevel hierarchy.
If the IsAggregatable property is set to True, an (All) level will exist. A
hierarchy has no (All) level if the sAggregatable property is set to False.
If this does not meet your requirement, please let's know more details
about the report/aggregation you'd like to get so that we may be able to
find other workarounds. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi again!
The Aggregate function is SSRS seems to look for the record with (null) in
all grouped fields and return the aggregate value from the dataset instead of
doing like Sum and the others. (Summing the values of the grouped records
itself instead of taking the SSAS one)
I might have been too eager on using it though. I've been fiddling a bit
with the last AW query we used, and the value Aggregate would've returned if
it worked with parent-child hierarchies is actually the same as First
returns. As long as the hierarchy isn't broken by ie. Order at least.
I think I can solve my problem that way for now.
To clear things up, I am after the All members value, but if you want the
value for (All titles, All employees) or (Sales Representative, All
employees) you won't get it in a group row with Aggregate, and the value will
be wrong if you use Sum, but it seems to be correct with First. :)
Here's an RDL showing exactly what I wanted. To see the erroneous behavior
of Aggregate, remove the Group1 filter and swap First for Aggregate. I tried
to hack the behavior with Iifs to make All null, but wouldn't do. ;)
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>176784d1-3d01-42a1-aab4-eb2e55edaa2f</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Reseller_Sales_Amount.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>EmployeeTitleSales</DataSetName>
<Top>2cm</Top>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>=Nothing</FilterValue>
</FilterValues>
<FilterExpression>=Fields!Title.Value</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!Title.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Title">
<rd:DefaultName>Title</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Iif(Fields!Employees.LevelNumber > 0, Nothing,
Fields!Title.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="TempEmployee">
<rd:DefaultName>TempEmployee</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>=CStr(Fields!Employees.LevelNumber*5) +
"pt"</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Employees.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Reseller_Sales_Amount">
<rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Reseller_Sales_Amount.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<Parent>=Fields!TempEmployeeParent.Value</Parent>
<GroupExpressions>
<GroupExpression>=Fields!TempEmployee.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>TempEmployee</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Title</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Employee</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Reseller Sales Amount</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>5.25cm</Width>
</TableColumn>
<TableColumn>
<Width>5.25cm</Width>
</TableColumn>
<TableColumn>
<Width>5.25cm</Width>
</TableColumn>
</TableColumns>
<Height>1.90476cm</Height>
</Table>
</ReportItems>
<Height>5.1746cm</Height>
</Body>
<rd:ReportID>ac5ed0ff-058a-4574-b854-d56d3898731d</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="EmployeeTitleSales">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<rd:DesignerState><QueryDefinition
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="<CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification
xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
xsi:type="Level"><DimensionName>Employee</DimensionName><HierarchyName>Title</HierarchyName><HierarchyUniqueName>[Employee].[Title]</HierarchyUniqueName><LevelName>Title</LevelName><UniqueName>[Employee].[Title].[Title]</UniqueName></ID><ItemCaption>Title</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID
xsi:type="Level"><DimensionName>Employee</DimensionName><HierarchyName>Employees</HierarchyName><HierarchyUniqueName>[Employee].[Employees]</HierarchyUniqueName><LevelName>Employee
Level 02</LevelName><UniqueName>[Employee].[Employees].[Employee Level
02]</UniqueName></ID><ItemCaption>Employees</ItemCaption><UniqueName>true</UniqueName><IsParentChild>true</IsParentChild></Item><Item><ID
xsi:type="Measure"><MeasureName>TempEmployee</MeasureName><UniqueName>[Measures].[TempEmployee]</UniqueName></ID><ItemCaption>TempEmployee</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID
xsi:type="Measure"><MeasureName>TempEmployeeParent</MeasureName><UniqueName>[Measures].[TempEmployeeParent]</UniqueName></ID><ItemCaption>TempEmployeeParent</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID
xsi:type="Measure"><MeasureName>Reseller Sales
Amount</MeasureName><UniqueName>[Measures].[Reseller Sales
Amount]</UniqueName></ID><ItemCaption>Reseller Sales
Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure
Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates
/><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER
[Measures].[TempEmployee] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 0 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.UniqueName)
MEMBER [Measures].[TempEmployeeParent] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 1 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.Parent.UniqueName)
SELECT NON EMPTY { [Measures].[TempEmployee],
[Measures].[TempEmployeeParent], [Measures].[Reseller Sales Amount] } ON
COLUMNS, NON EMPTY
{ ([Employee].[Title].ALLMEMBERS * {[Employee].[Employees].[All Employees],
DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) } ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE,
FONT_FLAGS
</Statement><ParameterDefinitions
/></Query></QueryDefinition></rd:DesignerState>
<CommandText>WITH MEMBER [Measures].[TempEmployee] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 0 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.UniqueName)
MEMBER [Measures].[TempEmployeeParent] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 1 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.Parent.UniqueName)
SELECT NON EMPTY { [Measures].[TempEmployee],
[Measures].[TempEmployeeParent], [Measures].[Reseller Sales Amount] } ON
COLUMNS, NON EMPTY
{ ([Employee].[Title].ALLMEMBERS * {[Employee].[Employees].[All Employees],
DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) } ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE,
FONT_FLAGS
</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level"
UniqueName="[Employee].[Title].[Title]" /></DataField>
</Field>
<Field Name="Employees">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level"
UniqueName="[Employee].[Employees]" /></DataField>
</Field>
<Field Name="TempEmployee">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[TempEmployee]" /></DataField>
</Field>
<Field Name="TempEmployeeParent">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[TempEmployeeParent]" /></DataField>
</Field>
<Field Name="Reseller_Sales_Amount">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[Reseller Sales Amount]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>15.75cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>
--
Lars-Erik
""Peter YangMSFT]"" wrote:
> Hello Lars,
> With the MDX query on my side, I also see "All Employees" as "all" level of
> Title attribute in the result if I run the MDX query in management studio.
> However, in VS Dataset view, I did see the behavior you described.
> It seems the SSRS dataset query has different process method for
> parent-child dimension such as Employees.
> I'm not quite sure about what you get to by using aggregation function. If
> you don't want the All level to be included in the aggregation. You may
> want to set IsAggregatable to false of the IsAggregatable property of the
> attribute at the top-most level
> In Microsoft SQL Server 2005 Analysis Services (SSAS), the (All) level is
> an optional, system-generated level. It contains only one member whose
> value is the aggregation of the values of all members in the immediately
> subordinate level. This member is called the All member. It is a
> system-generated member that is not contained in the dimension table.
> Because the member in the (All) level is at the top of the hierarchy, the
> member's value is the consolidated aggregation of the values of all members
> in the hierarchy. The All member often serves as the default member of a
> hierarchy.
> The presence of an (All) level in an attribute hierarchy depends on the
> IsAggregatable property setting for the attribute and the presence of an
> (All) level in a multilevel hierarchy depends on the IsAggregatable
> property of the attribute at the top-most level of multilevel hierarchy.
> If the IsAggregatable property is set to True, an (All) level will exist. A
> hierarchy has no (All) level if the sAggregatable property is set to False.
> If this does not meet your requirement, please let's know more details
> about the report/aggregation you'd like to get so that we may be able to
> find other workarounds. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>|||Hello Lars,
Great to see you have found a workaround on this issue. I managed to get
the report work on my side and I was able to reproduce the issue you
encountered. Currently I was not able to find ohter workaround for this
issue.
I tried to filter the [all] member in the Title and Employee attribute in
MDX query and Aggregate function still returns blank result.
WITH MEMBER
[Measures].[TempEmployee] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 0 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.UniqueName)
MEMBER [Measures].[TempEmployeeParent] AS
Iif([Employee].[Employees].CurrentMember.Level.Ordinal = 1 OR
[Measures].[Reseller Sales Amount] = 0, null,
[Employee].[Employees].CurrentMember.Parent.UniqueName)
SELECT NON EMPTY { [Measures].[TempEmployee],
[Measures].[TempEmployeeParent], [Measures].[Reseller Sales Amount] } ON
COLUMNS, NON EMPTY
{ ( Except({[Employee].[Title].ALLMEMBERS}, {Employee.[Title].[All]}) * {
Except({[Employee].[Employees].[All Employees]},
{[Employee].[Employees].[All]} ),
DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS)
} )}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE,
FONT_FLAGS
It seems the issue is caused by parent-child attribute itself other than
the All member. I have reported this issue to the product channel. If there
is any update, we will let you know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Aggregate Function to Concatenate Columns Data into a single Row

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :

RunningID Date WOid

1234 1/23/2007 23

1236 1/24/2007 23

1239 1/2/2007 24

1222 1/4/2007 23

1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:

select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX

group by WOid

Results:

RunningID_str MaxDate WO

1234#1236#1222 1/24/2007 23

1239 1/2/2007 24

1321 2/4/2007 22

Any advise would be much appreciated.

If you use SQL server 2005,

Code Snippet

Create Table #data (

[RunningID] int ,

[Date] datetime ,

[WOid] int

);

Insert Into #data Values('1234','1/23/2007','23');

Insert Into #data Values('1236','1/24/2007','23');

Insert Into #data Values('1239','1/2/2007','24');

Insert Into #data Values('1222','1/4/2007','23');

Insert Into #data Values('1321','2/4/2007','22');

Select

[RunningIDs],

Max([Date]) [Date],

[WoId]

From

(

select

Substring((Select '#' + cast([RunningID] as varchar) as [text()] from #data sub

where sub.[Woid] = main.[Woid] for xml path('')

),2,8000) as [RunningIDs],

[Date],

[WoId]

from

#data main

) as data

Group By

[RunningIDs],[WoId]

Order By

[RunningIDs]

|||

Thanks for your code.

However, this must be done on the fly and there are many similar rows in a single selection and how do we encapsulate the above code into a function. If not, how do we insert the dynamic data into the temp table on the fly?

|||

Post your query.. I didn't understand the dynamic data / on the fly.. You can achive this without function.|||

Here is my query:

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,
W.id,W.running as WRunning,W.[date] as WO_Date
FROM WorkOrder W
RIGHT JOIN PurchaseOrder P ON (P.refWO=W.id)
where not W.id is null
UNION ALL

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,
W.id,W.running as WRunning, W.[date] as WO_Date
FROM WorkOrder W
RIGHT JOIN PurchaseOrder P ON (P.addWO like ('%#' + cast(W.id as varchar) + ':%'))
where not W.id is null
order by W.id, W.[date], W.running

The PRunning and P.date will have a few rows to one P.refWO. his might be occuring a few times over the result.

|||

May be something like this,

Code Snippet

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,

W.id,W.running as WRunning,W.[date] as WO_Date into #temp

FROM WorkOrder W

RIGHT JOIN PurchaseOrder P ON (P.refWO=W.id)

where not W.id is null

UNION ALL

SELECT P.running as PRunning, P.[date], P.refWO, P.addWO,

W.id,W.running as WRunning, W.[date] as WO_Date

FROM WorkOrder W

RIGHT JOIN PurchaseOrder P ON (P.addWO like ('%#' + cast(W.id as varchar) + ':%'))

where not W.id is null

order byW.id, W.[date], W.running

Select

PRunning

,Max([date])

,refWO

,addWO

,WRunning

,max(WO_Date)

From

(

Select

(Select '#' + PRunning as [text()] from #temp sub where sub.id = main.id For xml path('')) as PRunning

,[date]

,(Select '#' + refWO as [text()] from #temp sub where sub.id = main.id For xml path('')) as refWO

,(Select '#' + addWO as [text()] from #temp sub where sub.id = main.id For xml path('')) as addWO

,(Select '#' + WRunning as [text()] from #temp sub where sub.id = main.id For xml path('')) as WRunning

,WO_Date

from

#temp

) as Data

Group By

PRunning

,refWO

,addWO

,WRunning

|||

Hi I am using SQL 2000 and I suppose i need some minor tweating to the code. When i run the code, It reported invalid for "For XML Path('')'. So i took those out.

Another issue is where does the alias "main" referring to?

Aggregate Function task issue

We have a Data Flow Task.
Inside this, we have a OLE DB Data Source which calls and executes a stored procedure.
We use a Row Count Task to count the number of rows returned by the OLE DB Data Dource Task.
Then, we use an aggregate function task to get the sum of all the rows of one particular column that is returned from the OLE DB Data Source.
The issue here is that we want to assign the sum value returned by the Aggregate Function Task to a User Variable named User::Variable. We have tried to assign this by using @.User::Variable and User::Variable, but neither of those return the expected value.

If there is any other method to do the same then let us know.

We have checked the that the row count is greater than zero.
Any help would be very much appreciated.

Thanks in advance.Yep, you can't store that value in a variable.... Your best bet would be to use a script component to capture that result and store it.|||How can we access the output of the aggregate function in script task?
Or Is it possible to use script task after a Data source task (without using aggregate)?

Is there any other method to get the sum of a field of data source task in a variable? I think it can be done using Expressions but when i have treed i was not able to get expression in data flow task.|||Use a script component in transformation mode...

Notice how Jamie uses the row.[fieldname] convention. You'll have an output coming from your aggregate transformation, which you can use in the script component. Search this forum for many examples on working with variables.

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-The-script-component-and-regular-expressions.aspx

Aggregate Function task issue

We have a Data Flow Task.
Inside this, we have a OLE DB Data Source which calls and executes a stored procedure.
We use a Row Count Task to count the number of rows returned by the OLE DB Data Dource Task.
Then, we use an aggregate function task to get the sum of all the rows of one particular column that is returned from the OLE DB Data Source.
The issue here is that we want to assign the sum value returned by the Aggregate Function Task to a User Variable named User::Variable. We have tried to assign this by using @.User::Variable and User::Variable, but neither of those return the expected value.

If there is any other method to do the same then let us know.

We have checked the that the row count is greater than zero.
Any help would be very much appreciated.

Thanks in advance.Yep, you can't store that value in a variable.... Your best bet would be to use a script component to capture that result and store it.|||How can we access the output of the aggregate function in script task?
Or Is it possible to use script task after a Data source task (without using aggregate)?

Is there any other method to get the sum of a field of data source task in a variable? I think it can be done using Expressions but when i have treed i was not able to get expression in data flow task.|||Use a script component in transformation mode...

Notice how Jamie uses the row.[fieldname] convention. You'll have an output coming from your aggregate transformation, which you can use in the script component. Search this forum for many examples on working with variables.

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-The-script-component-and-regular-expressions.aspx

Aggregate Function on TextBoxes - a big "NO-NO"...

Ok...so we can't do an aggregate function on numerical values stored in
textboxes...but is there some other way to access the value(s) in the
textbox and use them in a calculation?
=Sum(ReportItems!textbox1.Value) is an invalid expression - but is there
another workaround?
-KB"Kevin B" <No-SPAM@.misnet.info> wrote in message news:<uoXYuR8gEHA.4092@.TK2MSFTNGP10.phx.gbl>...
> Ok...so we can't do an aggregate function on numerical values stored in
> textboxes...but is there some other way to access the value(s) in the
> textbox and use them in a calculation?
> =Sum(ReportItems!textbox1.Value) is an invalid expression - but is there
> another workaround?
> -KB
KB
I've been using Aggregate fuctions quite a bit in the last several
months. Just be sure to specify a scope in your function (i.e. a
dataset or group, etc.)
=Sum(ReportItems!textbox1.Value, "Group1")
Hope this helps,
MN

Aggregate Function On SubQuery

I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but

SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid

but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'

Is there an elegant solution for this?

Thanks,Hi Doug

Maybe I missed something but why does this not do it:

SELECT userid, COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions
GROUP BY userid

?

The Max isn't necessary as the sub-select should return one value per user. Remove that and the above is equivelent.

HTH

Aggregate function MAX ignores NULLs

I need to run a query that will pull the most recent revision, and it
must be based on the RevisionDate (if you need to know why it is
because the revision column can contain letters, numbers, hyphens, or
underscores - numbers, in this case have the highest value, but SQL
interprets letters as having higher values), and give me the result set
below. The problem is that using the aggregate function MAX ignores
the null values, so the revision information for drawingID 37000 is
left out of the result set in the current query, which is below.
Sample Data (sorry I couldn't line up these columns better, the google
text editor keeps moving them):
tblDrawings
ID DrawingNo
37000 R2-S01
36455 431001200201
tblDrawingRevisions
ID DrawingID Revision RevisionDate Status
281213 36455 _ NULL ApprovedAs Noted
281781 36455 1 2006-07-27 Approved
281703 37000 -- NULL Approved for Constr.
Current Result Set:
DrawingID Drawing No Revision Status
37000 R2-S01
36455 431001200201 1 Approved
Result Set Needed:
DrawingID Drawing No Revision Status
37000 R2-S01 -- Approved for Constr.
36455 431001200201 1 Approved
Current Query:
SELECT dr.ID as dwgID,
dr.DwgNo as dwgNo,
dRev.DrawingRev,
dRev.RevStatus as dwgRevStatus
FROM tblDrawings dr
LEFT JOIN (SELECT t1.ID, t1.DrawingID, t1.DrawingRev, t1.DwgRevDate,
t1.RevStatus
FROM (SELECT ID, DrawingID, DrawingRev, DwgRevDate, RevStatus
FROM tblDrawingRevisions)t1
INNER JOIN
(SELECT DrawingID, MAX(DwgRevDate) as MaxDate
FROM tblDrawingRevisions GROUP BY DrawingID)t2
ON t1.DrawingID = t2.DrawingID AND t1.DwgRevDate =
t2.MaxDate)dRev
ON dRev.DrawingID = dr.ID
WHERE dr.dwgno = 'r2-s01' or dr.dwgno = '431001200201'
Please help!Keep in mind NULL means "Unknown". So you can't say Unknown is equal
to anything. You can't even say Unknown is equal to Unknown. So if it
is NULL I would default the DwgRevDate to the create date (you must
have one?). Otherwise just use a very early date for the default
isnull(t1.DwgRevDate,'01/01/1900').
So your SQL becomes:
SELECT dr.ID as dwgID,
dr.DwgNo as dwgNo,
dRev.DrawingRev,
dRev.RevStatus as dwgRevStatus
FROM tblDrawings dr
LEFT JOIN (SELECT t1.ID,
t1.DrawingID,
t1.DrawingRev,
t1.DwgRevDate,
t1.RevStatus
FROM tblDrawingRevisions t1
INNER JOIN
(SELECT DrawingID,
isnull(MAX(DwgRevDate),'01/01/1900') as MaxDate
FROM tblDrawingRevisions
GROUP BY DrawingID)t2
ON t1.DrawingID = t2.DrawingID
AND isnull(t1.DwgRevDate,'01/01/1900') = t2.MaxDate)dRev
ON dRev.DrawingID = dr.ID
WHERE dr.dwgno = 'r2-s01' or dr.dwgno = '431001200201'
JJ