FOR XML EXPLICIT formatting issue

I am trying to use FOR XML EXPLICIT to group records that are related.
I want to achieve something like
<Header Id="1" ... >
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
</Header>
<Header Id="2" ... >
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
</Header>
<Header Id="3" ... >
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
</Header>
I keep getting
<Header Id="1" ... >
<Header Id="2" ... />
<Header Id="3" ... />
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
The query looks something like
SELECT
1 AS Tag
, NULL AS Parent
, header AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, ChildId AS 'Child1!2!Child1Id'
, Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, ChildId AS 'Child2!3!Child1Id'
, Name AS 'Child2!3!Name'
FROM
blah, blah, blah
Any help with either of these is gratefully appreciated.
Let me know if I am on the wrong path also, as I wouldn't be surprised
Thanks
Steve
You're missing an ORDER BY. Try this
create table #Header(HeaderId int)
insert into #Header(HeaderId) values(1)
insert into #Header(HeaderId) values(2)
insert into #Header(HeaderId) values(3)
create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
SELECT 1 AS Tag
, NULL AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
UNION ALL
SELECT 2 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, c.Child1Id AS 'Child1!2!Child1Id'
, c.Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
UNION ALL
SELECT 3 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, c.Child2Id AS 'Child2!3!Child1Id'
, c.Name AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
ORDER BY h.HeaderId,Tag
FOR XML EXPLICIT
drop table #Child2
drop table #Child1
drop table #Header
|||"markc600@.hotmail.com" wrote:

> You're missing an ORDER BY. Try this
>
> create table #Header(HeaderId int)
> insert into #Header(HeaderId) values(1)
> insert into #Header(HeaderId) values(2)
> insert into #Header(HeaderId) values(3)
> create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
> create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
> insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
> SELECT 1 AS Tag
> , NULL AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> UNION ALL
> SELECT 2 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , c.Child1Id AS 'Child1!2!Child1Id'
> , c.Name AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
> UNION ALL
> SELECT 3 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , c.Child2Id AS 'Child2!3!Child1Id'
> , c.Name AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
> ORDER BY h.HeaderId,Tag
> FOR XML EXPLICIT
>
> drop table #Child2
> drop table #Child1
> drop table #Header
>
Excellent, so close yet so far.
Also, in my actual code I had not propogated the Header Id into the other
parts of the union, it din't actually know what the relationships were.
And now BizTalk seems to like it.
Thanks

FOR XML EXPLICIT formatting issue

I am trying to use FOR XML EXPLICIT to group records that are related.
I want to achieve something like
<Header Id="1" ... >
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
</Header>
<Header Id="2" ... >
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
</Header>
<Header Id="3" ... >
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
</Header>
I keep getting
<Header Id="1" ... >
<Header Id="2" ... />
<Header Id="3" ... />
<Child1 Child1Id="1" Name="Fred" .../>
<Child1 Child1Id="2" Name="Tom" .../>
<Child1 Child1Id="5" Name="Fred" .../>
<Child1 Child1Id="6" Name="Tom" .../>
<Child1 Child1Id="9" Name="Fred" .../>
<Child1 Child1Id="10" Name="Tom" .../>
<Child2 Child2Id="3" Name="Dick" .../>
<Child2 Child2Id="4" Name="Harry" .../>
<Child2 Child2Id="7" Name="Dick" .../>
<Child2 Child2Id="8" Name="Harry" .../>
<Child2 Child2Id="11" Name="Dick" .../>
<Child2 Child2Id="12" Name="Harry" .../>
The query looks something like
SELECT
1 AS Tag
, NULL AS Parent
, header AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, ChildId AS 'Child1!2!Child1Id'
, Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM
blah, blah, blah
UNION ALL
SELECT
1 AS Tag
, NULL AS Parent
, NULL AS 'Header!1!Id'
::
::
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, ChildId AS 'Child2!3!Child1Id'
, Name AS 'Child2!3!Name'
FROM
blah, blah, blah
Any help with either of these is gratefully appreciated.
Let me know if I am on the wrong path also, as I wouldn't be surprised
Thanks
SteveYou're missing an ORDER BY. Try this
create table #Header(HeaderId int)
insert into #Header(HeaderId) values(1)
insert into #Header(HeaderId) values(2)
insert into #Header(HeaderId) values(3)
create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
SELECT 1 AS Tag
, NULL AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
UNION ALL
SELECT 2 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, c.Child1Id AS 'Child1!2!Child1Id'
, c.Name AS 'Child1!2!Name'
, NULL AS 'Child2!3!Child1Id'
, NULL AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
UNION ALL
SELECT 3 AS Tag
, 1 AS Parent
, h.HeaderId AS 'Header!1!Id'
, NULL AS 'Child1!2!Child1Id'
, NULL AS 'Child1!2!Name'
, c.Child2Id AS 'Child2!3!Child1Id'
, c.Name AS 'Child2!3!Name'
FROM #Header h
INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
ORDER BY h.HeaderId,Tag
FOR XML EXPLICIT
drop table #Child2
drop table #Child1
drop table #Header|||
"markc600@.hotmail.com" wrote:

> You're missing an ORDER BY. Try this
>
> create table #Header(HeaderId int)
> insert into #Header(HeaderId) values(1)
> insert into #Header(HeaderId) values(2)
> insert into #Header(HeaderId) values(3)
> create table #Child1(HeaderId int,Child1Id int,Name varchar(5))
> create table #Child2(HeaderId int,Child2Id int,Name varchar(5))
> insert into #Child1(HeaderId,Child1Id,Name) values(1,1,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(1,2,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,3,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(1,4,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,5,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(2,6,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,7,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(2,8,'Harry')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,9,'Fred')
> insert into #Child1(HeaderId,Child1Id,Name) values(3,10,'Tom')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,11,'Dick')
> insert into #Child2(HeaderId,Child2Id,Name) values(3,12,'Harry')
> SELECT 1 AS Tag
> , NULL AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> UNION ALL
> SELECT 2 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , c.Child1Id AS 'Child1!2!Child1Id'
> , c.Name AS 'Child1!2!Name'
> , NULL AS 'Child2!3!Child1Id'
> , NULL AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child1 c ON c.HeaderId=h.HeaderId
> UNION ALL
> SELECT 3 AS Tag
> , 1 AS Parent
> , h.HeaderId AS 'Header!1!Id'
> , NULL AS 'Child1!2!Child1Id'
> , NULL AS 'Child1!2!Name'
> , c.Child2Id AS 'Child2!3!Child1Id'
> , c.Name AS 'Child2!3!Name'
> FROM #Header h
> INNER JOIN #Child2 c ON c.HeaderId=h.HeaderId
> ORDER BY h.HeaderId,Tag
> FOR XML EXPLICIT
>
> drop table #Child2
> drop table #Child1
> drop table #Header
>
Excellent, so close yet so far.
Also, in my actual code I had not propogated the Header Id into the other
parts of the union, it din't actually know what the relationships were.
And now BizTalk seems to like it.
Thanks

FOR XML AUTO broken in 2005

Re: http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx

I'm having exactly the same problem, although I'm writing queries that need to run on both SQL 2000 and 2005. I cannot believe this isn't a bug. Although I understand the logic behind the results I cannot accept the results in 2005 are correct. If each part a union produces a parent/child structure why is it considered correct that UNIONing the two produces a flat no-child relationship? It makes no sense, I don't want to see a compatible mode in a service pack for 2005 I want to see the bug fixed!

I understand your problem is that you want to write an XML publishing query with the specific UNION that works both in SQL Server 2000 and in SQL Server 2005. Please consider using FOR XML EXPLICIT - it should solve your problem.

Also note that SQL Server 2005 Service Pack 1 should contain a fix for the compatibility issue - FOR XML AUTO query with the UNION from the link you provided will work the same way between SQL Server 2000 and SQL Server 2005 SP1 for a database with 80 compatibility level, thus not breaking your application after upgrade to SQL Server 2005 SP1. FOR XML AUTO with the UNION on a database with 90 compatibility level will work the same in SQL Server 2005 RTM and SP1.

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you for the reply, and yes I am using XML Explicit to work-around the problem...and it looks horrible.

I also understand that the service pack will have this special compatible mode, my point is that it shouldn't have. I don't understand the justification of letting 2005 produce results the way it does. I'm stating that this is a bug and should be fixed. I'd be very interested in learning why Microsoft feel this is a compatibility issue and not a bug. Given that if Select X...For XML AUTO produces a parent/child result surely unioning two lots of Select X would still produce a parent/child result?

|||

In addition to my explanations in http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx I'd say that while column naming derived from the first leg of UNION [ALL] is documented in BOL column-to-table association (which FOR XML AUTO uses) on top of UNION [ALL] was never documented; SQL Server 2000 behavior there is incorrect.

I generally discourage you from using AUTO mode of FOR XML on top of set operations (like UNION [ALL]/EXCEPT/INTERSECT) since it will prevent from using some performance optimizations we can do in AUTO mode. This is in SQL Server 2005. In SQL Server 2000 we would apply the optimizations but because of the buggy column-to-table associations we can get wrong results. I provided a repro for the wrong results below.

If you need UNION ALL and not UNION you may consider supplying two separate FOR XML AUTO and concatenating the results on the client side. This is given that you need to use a syntax that works on both SQL Server 200 and 2005. In SQL Server 2005 this can be achieved in a more explicit and cleaner way.

Here’s the SQL Server 2000 repro that produces wrong results. Notice different PK constraints on different tables and duplicate col1 values for t2 and t4.

create table t1(col1 int not null primary key, col2 varchar(256) not null)

insert t1 select 1,'t1col2row1'

insert t1 select 2,'t1col2row2'

go

create table t3(col1 int not null primary key, col2 varchar(256) not null)

insert t3 select 1,'t3col2row1'

insert t3 select 2,'t3col2row2'

go

create table t2(col1 int not null, col2 varchar(256) not null primary key)

insert t2 select 1,'t2col2row1'

insert t2 select 1,'t2col2row2'

go

create table t4(col1 int not null, col2 varchar(256) not null primary key)

insert t4 select 1,'t4col2row1'

insert t4 select 1,'t4col2row2'

go

select t1.col1,t1.col2 col12,t3.col2 from t3

inner join t1 on t3.col1 = t1.col1

union

select t2.col1,t2.col2 col12,t4.col2 from t4

inner join t2 on t4.col1 = t2.col1

for xml auto

go

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Again, thank you for the reply, interesting to hear that it doesn't perform very well. As I've mentioned I do understand the algorithm that is producing the results, what I'm saying is the algorithm is fundamentally flawed when used with UNIONs. To the user, SQL 2000 produces logical results whereas 2005 does not, for me that tells me that this is a bug. If you want to tell me that UNIONs and FOR XML AUTO are not longer supported but we'll provide a compat' mode, then I can swallow that. What I don't understand is the view that it's working fine in 2005 when clearly it doesn't.

As for the workarounds (and doesn't this also imply a bug) those are ok (not a great fan of using the client to do that) and I'm using the EXPLICIT XML alternative.

I don't want to appear argumentative but I would just like it to be recognised as a failing in 2005 and it should be documented as a breaking change rather than have a test fail or ,worse, have a customer report that upgrading to 2005 has broken their crucial application and have had to downgrade back to 2000! All of which could be avoided by clearly stating that there is a breaking change.

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

Footer problems

Hi all,
I've got a couple of problems with some reports that I'm developing:
1) I'm trying to print a Group Footer at the bottom of the page in a fixed
position, regardless of how many detail records there are. Is there a way
to do this?
2) I have another report that is supposed to give page totals on each page,
as well as a grand total at the end of the report. Since I can't put any
fields in the Page Footer, and I can't use the Global Page variables in the
Body, how can I get these totals to appear correctly?
Thank you very much in advance,
JimJim wrote:
> Hi all,
> I've got a couple of problems with some reports that I'm developing:
> 1) I'm trying to print a Group Footer at the bottom of the page in a
> fixed position, regardless of how many detail records there are. Is
> there a way to do this?
> 2) I have another report that is supposed to give page totals on each
> page, as well as a grand total at the end of the report. Since I
> can't put any fields in the Page Footer, and I can't use the Global
> Page variables in the Body, how can I get these totals to appear
> correctly?
> Thank you very much in advance,
> Jim
I'll tackle number 2 first;
In a table footer you can use the 'RunningValue' function instead of
'Sum' and make sure the footer is set to 'Repeat on each page'.
Number 1 is not currently acheivable.
Rectangles are the key to semi-absolute positioning, but unless your
report is alway a one page report (i.e. an invoice) you won't be able
to do it.
Merry Christmas
Chris|||HI
I am also trying to do the same. But I cannot achieve it. Which scope do I
have to give' Can you please give me an example?
"Chris McGuigan" wrote:
> Jim wrote:
> > Hi all,
> >
> > I've got a couple of problems with some reports that I'm developing:
> >
> > 1) I'm trying to print a Group Footer at the bottom of the page in a
> > fixed position, regardless of how many detail records there are. Is
> > there a way to do this?
> >
> > 2) I have another report that is supposed to give page totals on each
> > page, as well as a grand total at the end of the report. Since I
> > can't put any fields in the Page Footer, and I can't use the Global
> > Page variables in the Body, how can I get these totals to appear
> > correctly?
> >
> > Thank you very much in advance,
> >
> > Jim
> I'll tackle number 2 first;
> In a table footer you can use the 'RunningValue' function instead of
> 'Sum' and make sure the footer is set to 'Repeat on each page'.
> Number 1 is not currently acheivable.
> Rectangles are the key to semi-absolute positioning, but unless your
> report is alway a one page report (i.e. an invoice) you won't be able
> to do it.
> Merry Christmas
> Chris
>|||Hey Soan,
I actually have found out how to do this stuff, although it was pretty
nasty. MS needs to come up with something better than it has thus far.
First off, the RunningValue function did not work for me in the table
footer. What I ended up doing was adding another Detail row, and added an
expression to the Hidden property of that row that hides it unless it goes
at the bottom of the page. I calculated the number of detail rows that fit
on each page, then used the Mod function of the RowNumber as such:
RowNumber(Nothing) Mod x = 0, where x is the number of rows that fit in a
page. I then added a TextBox to that new Detail row and included the
RunningValue function in it; that worked!
As for positioning on the page, well, that is similar. I have two stored
procedures, one of which calculates the total number of detail rows that
will show up on the report, then subtracts that from the (number of pages *
max rows per page). I then created a dummy table with no controls in the
detail row (no header or footer row either), and set the row source to that
extra row calculation sproc. This worked, but I hate it.
FYI, I tried Chris' solution of using rectangles, but I couldn't get the
Height property to accept an expression (according to BOL, it shouldn't
anyway), so I have no idea how he meant I could use rectangles.
Jim
"Soan" <Soan@.discussions.microsoft.com> wrote in message
news:01963902-278F-4A66-BE25-276F8115F8B1@.microsoft.com...
> HI
> I am also trying to do the same. But I cannot achieve it. Which scope do I
> have to give' Can you please give me an example?
>
> "Chris McGuigan" wrote:
>> Jim wrote:
>> > Hi all,
>> >
>> > I've got a couple of problems with some reports that I'm developing:
>> >
>> > 1) I'm trying to print a Group Footer at the bottom of the page in a
>> > fixed position, regardless of how many detail records there are. Is
>> > there a way to do this?
>> >
>> > 2) I have another report that is supposed to give page totals on each
>> > page, as well as a grand total at the end of the report. Since I
>> > can't put any fields in the Page Footer, and I can't use the Global
>> > Page variables in the Body, how can I get these totals to appear
>> > correctly?
>> >
>> > Thank you very much in advance,
>> >
>> > Jim
>> I'll tackle number 2 first;
>> In a table footer you can use the 'RunningValue' function instead of
>> 'Sum' and make sure the footer is set to 'Repeat on each page'.
>> Number 1 is not currently acheivable.
>> Rectangles are the key to semi-absolute positioning, but unless your
>> report is alway a one page report (i.e. an invoice) you won't be able
>> to do it.
>> Merry Christmas
>> Chris|||Hi Jim/Soan,
Soan, you asked about scope, generally I use 'Nothing' which instructs
RS to work it out for you! For basic reports you could use the dataset
name (in double quotes!).
You can set a group footer to repeat on each page (see 'Edit Group
Properties' and check 'Repeat Footer on each page')
Use the RunningValue function of the form
RunningValue(Fields!Amount.Value, Sum, Nothing), this should do it.
Note! This is not a page total but a running total!
Jim, your method is fine as long as you don't start grouping or have
conditionally hidden lines, then tracking where you are on the page
becomes a nightmare.
The rectangle solution only really works if can get prints on one page.
You place a rectangle exactly where you want it on the page and how big
you want, then place a data region inside it and make the data region
display what you want, i.e. the totals.
As Jim said, Microsoft haven't really provided an adequate way of
achieving this.
Regards
Chris
Jim wrote:
> Hey Soan,
> I actually have found out how to do this stuff, although it was
> pretty nasty. MS needs to come up with something better than it has
> thus far.
> First off, the RunningValue function did not work for me in the table
> footer. What I ended up doing was adding another Detail row, and
> added an expression to the Hidden property of that row that hides it
> unless it goes at the bottom of the page. I calculated the number of
> detail rows that fit on each page, then used the Mod function of the
> RowNumber as such: RowNumber(Nothing) Mod x = 0, where x is the
> number of rows that fit in a page. I then added a TextBox to that
> new Detail row and included the RunningValue function in it; that
> worked!
> As for positioning on the page, well, that is similar. I have two
> stored procedures, one of which calculates the total number of detail
> rows that will show up on the report, then subtracts that from the
> (number of pages * max rows per page). I then created a dummy table
> with no controls in the detail row (no header or footer row either),
> and set the row source to that extra row calculation sproc. This
> worked, but I hate it.
> FYI, I tried Chris' solution of using rectangles, but I couldn't get
> the Height property to accept an expression (according to BOL, it
> shouldn't anyway), so I have no idea how he meant I could use
> rectangles.
> Jim
> "Soan" <Soan@.discussions.microsoft.com> wrote in message
> news:01963902-278F-4A66-BE25-276F8115F8B1@.microsoft.com...
> > HI
> >
> > I am also trying to do the same. But I cannot achieve it. Which
> > scope do I have to give' Can you please give me an example?
> >
> >
> > "Chris McGuigan" wrote:
> >
> >> Jim wrote:
> > >
> >> > Hi all,
> >> >
> >> > I've got a couple of problems with some reports that I'm
> developing: >> >
> >> > 1) I'm trying to print a Group Footer at the bottom of the page
> in a >> > fixed position, regardless of how many detail records there
> are. Is >> > there a way to do this?
> >> >
> >> > 2) I have another report that is supposed to give page totals on
> each >> > page, as well as a grand total at the end of the report.
> Since I >> > can't put any fields in the Page Footer, and I can't use
> the Global >> > Page variables in the Body, how can I get these
> totals to appear >> > correctly?
> >> >
> >> > Thank you very much in advance,
> >> >
> >> > Jim
> > >
> >> I'll tackle number 2 first;
> >> In a table footer you can use the 'RunningValue' function instead
> of >> 'Sum' and make sure the footer is set to 'Repeat on each page'.
> > >
> >> Number 1 is not currently acheivable.
> >> Rectangles are the key to semi-absolute positioning, but unless
> your >> report is alway a one page report (i.e. an invoice) you won't
> be able >> to do it.
> > >
> >> Merry Christmas
> >> Chris
> >>