Forcing a set number of result rows in a query

I'm trying to select 5 rows of data from a query. Sometimes there is less than 5 rows of data in the result set.

Is there a way to FORCE a return of 5 rows - even if they don't exist? For example, returning some text such as "No Data" or NULL in the result set?

What I'm doing to return 5 rows of data:

Select top 5 *

From MyTable

I need help modifying this query to make sure I always get 5 rows of data.

Thanks!

There is no pre-defined settings available but you do something below,

Code Snippet

Create table #Data(

Id int,

Name varchar(100)

)

Insert Into #Data Values(1,100)

Insert Into #Data Values(2,100)

Insert Into #Data Values(3,100)

Select Top 5 * From

(

Select Id, Name from #Data

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

)

as Data

Order By Case When Id is NULL Then 1 Else 0 End , ID

|||

Code Snippet

CREATE TABLE #temp (test int)

INSERT INTO #temp SELECT 1

INSERT INTO #temp SELECT 2

INSERT INTO #temp SELECT 3

DECLARE @.counter as int

set @.counter = (SELECT COUNT(*) from #temp)

SELECT * FROM #temp

WHILE @.counter < 5

BEGIN

INSERT INTO #temp SELECT NULL

SET @.counter = @.counter + 1

END

SELECT * FROM #temp

DROP TABLE #temp

Adamus

|||Thanks for the prompt replies - both of these replies were helpful and answered my question!

FOR XML return as a Scalar

When returning a result as XML using FOR XML SQL Server 2005 is returning
serveral rows when the result is greater than 2036 in length. Each row is
breaking on this length. I would like FOR XML to return all the xml in a
singe row single column so that I can use a select scalar for the results.
Currently I have to use a data reader and a string builder. I'm hoping that
there is a way to control the size if the output for FOR XML so that I can d
o
a scalar read of any result.
Thanks,
TylerHi Tyler,
Please post your statement for us to take a look at.
Thanks,
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tyler Carver" <Tyler Carver@.discussions.microsoft.com> wrote in message
news:DE8A3709-2E31-49F6-B30C-C93B4B1195E0@.microsoft.com...
> When returning a result as XML using FOR XML SQL Server 2005 is returning
> serveral rows when the result is greater than 2036 in length. Each row is
> breaking on this length. I would like FOR XML to return all the xml in a
> singe row single column so that I can use a select scalar for the results.
> Currently I have to use a data reader and a string builder. I'm hoping
> that
> there is a way to control the size if the output for FOR XML so that I can
> do
> a scalar read of any result.
> Thanks,
> Tyler|||In SQL 2005 you can use the TYPE directive to get a scalar XML value like:
SELECT * FROM tbl FOR XML AUTO, TYPE
In SQL 2000, you cannot do this directly in Query Analyzer. FOR XML returns
an XML stream which can be retreived as single string only if you use an API
which supports a stream interface. Since Query Analyer uses ODBC, the values
will be munged to 2032 characters per row.
One alternative is to use XML EXPLICIT with the edge table ( need to know
the resultset upfront ). Another is to extract the data externally ( to an
app or flat file ) and stitch them back together to form single XML
document.
Anith|||"Tony Rogerson" wrote:
> Please post your statement for us to take a look at.
Here is a SQL statement that returns a large XML result:
SELECT *
FROM Categories
FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE
This returns serveral rows from the database that need to be concatenated.
I want to change the statement so that only one row one column is returned.
Tyler|||For test...
select name as [text()]
from sys.objects
for xml path( '' ), root( 'sysobjects' ), type
So yours would be...
SELECT *
FROM Categories
FOR XML PATH(''), ROOT('ArrayOfCategory'), TYPE
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tyler Carver" <TylerCarver@.discussions.microsoft.com> wrote in message
news:E46BBA07-4B47-4C49-B5B6-BC4D69F66E65@.microsoft.com...
> "Tony Rogerson" wrote:
> Here is a SQL statement that returns a large XML result:
> SELECT *
> FROM Categories
> FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE
> This returns serveral rows from the database that need to be concatenated.
> I want to change the statement so that only one row one column is
> returned.
> Tyler|||Are you using SSMS? Can you post the results of:
DECLARE @.xml AS XML
SET @.xml = ( SELECT *
FROM Categories
FOR XML PATH('Category'), ROOT('ArrayOfCategory'), TYPE )
SELECT DATALENGTH( @.xml )
Are you getting an error?
Anith|||Sorry, you'll need a delimiter as well...
select name + ',' as [text()]
from sys.objects
for xml path( '' ), root( 'sysobjects' ), type
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:u3ACF6b%23FHA.2040@.TK2MSFTNGP14.phx.gbl...
> For test...
> select name as [text()]
> from sys.objects
> for xml path( '' ), root( 'sysobjects' ), type
> So yours would be...
> SELECT *
> FROM Categories
> FOR XML PATH(''), ROOT('ArrayOfCategory'), TYPE
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Tyler Carver" <TylerCarver@.discussions.microsoft.com> wrote in message
> news:E46BBA07-4B47-4C49-B5B6-BC4D69F66E65@.microsoft.com...
>|||"Anith Sen" wrote:
> In SQL 2005 you can use the TYPE directive to get a scalar XML value like:
Thanks Anith, this is exactly what I was looking for. We had several sprocs
without the Type directive and the example I had happened to have it and was
therefore unknown to us, working.
Thanks, again.
Tyler

FOR XML performance question

I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:F314E284-6E34-403C-94C1-B307682B1418@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.|||I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.
----
In addition to the above I have done some further investigation. On a query
which returns the top row from a table the FOR XML method performed 83.5%
faster than the recordset version. However, when I run a where query which
I
know returns a single row the FOR XML method performance plunges and is
actually 6% slower than the recordset version.
Is SQLXML just one of those things which seems like a great idea but has no
real practical use in an enterprise environment? I find it very frustrating
that its performance is superb in some situations but is so awful in others.
Is it a work in progress?
That said, are there any resources which discuss various ways to pull data
from SQL server 2000 as(and convert to) XML format? Surely there is a bette
r
way than using a ADO recordset as described above?
Thanks.|||Hi Lee
This is hard to answer without having more specifics.
How does your FOR XML query look like? How does it compare to the previous
query, what indices do yo have on it?
Etc.
Best regards
Michael
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.
> ----
> In addition to the above I have done some further investigation. On a
> query
> which returns the top row from a table the FOR XML method performed 83.5%
> faster than the recordset version. However, when I run a where query
> which I
> know returns a single row the FOR XML method performance plunges and is
> actually 6% slower than the recordset version.
> Is SQLXML just one of those things which seems like a great idea but has
> no
> real practical use in an enterprise environment? I find it very
> frustrating
> that its performance is superb in some situations but is so awful in
> others.
> Is it a work in progress?
> That said, are there any resources which discuss various ways to pull data
> from SQL server 2000 as(and convert to) XML format? Surely there is a
> better
> way than using a ADO recordset as described above?
> Thanks.|||The query is a simple :-
SELECT stuff
FROM table
WHERE condition (optional)
FOR XML RAW
There is a single index on the primary key of the table and that was the
field I did my where clause on as described in my above posts.
"Michael Rys [MSFT]" wrote:

> Hi Lee
> This is hard to answer without having more specifics.
> How does your FOR XML query look like? How does it compare to the previous
> query, what indices do yo have on it?
> Etc.
> Best regards
> Michael
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>
>

For Xml Path, functions and UDF's

I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this
.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UD
F
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work'
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')Ok, just found the issue with the UDF in BOL so I understand why I can't pas
s
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:

> I have created a select statement to return using for xml path which work
s
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing th
is.
> If i include the complete sub query directly in the sql it works without a
ny
> issues, but if I take the sub select and put into into a table returning U
DF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a
UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work'
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
> Ok, just found the issue with the UDF in BOL so I understand why I can't p
ass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see
if
> thay works.
> "Nathan" wrote:
>

For Xml Path, functions and UDF's

I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UDF
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work?
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
Ok, just found the issue with the UDF in BOL so I understand why I can't pass
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:

> I have created a select statement to return using for xml path which works
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing this.
> If i include the complete sub query directly in the sql it works without any
> issues, but if I take the sub select and put into into a table returning UDF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work?
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
[vbcol=seagreen]
> Ok, just found the issue with the UDF in BOL so I understand why I can't pass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see if
> thay works.
> "Nathan" wrote:

FOR XML PATH NULL Element

Hi there,
I'm using sp with FOR XML PATH('Employee'), ELEMENTS to return XML Data
from SQL Server 2005.
If row return null value return xml does not return element.
Can it be returned xml element even it contains null?
Ex
i'm getting this
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
</employee>
i want this:)
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
<image/>
</employee>
*** Sent via Developersdex http://www.examnotes.net ***Hello Zoka,
You could do something like this:
SELECT
..
e.image AS "image/node()"
,'' AS "image/node()" -- Same as above :-), now "image/node()" is never
NULL :-)
..
FROM ... AS e
FOR XML PATH('employee')
HTH
/ Tobias|||
Hi there,
I tried this functionality but does not solve the problem.
*** Sent via Developersdex http://www.examnotes.net ***|||
Sorry Tobias,
This solves my problem, thanks:))
I haven't drink coffe when i first try the script:)
Regards,
Zoka
*** Sent via Developersdex http://www.examnotes.net ***

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

For XML Path

im using the ROOT directive in association with FOR XML PATH to return results. When the query returns no records then I get no root node either (which makes the XML invalid). Is there an attribute that specifies that the root node should always be returned (even when empty)?

i.e. <ROOT />

thx

Not that I know of. Since no rows were returned, not data would be returned at all. You could do something like this:

select cast(
'<root>' +

coalesce((select *
from sys.objects
where 1=2 --change to 1=1 to get rows
for xml path),'')

+ '</root>' as xml)

This does seem to work, though not 100% sure if there will be much of a performance hit.

|||

thanks for the tip :)

im am confused becuase if i say that i want a resultset typed as xml, then i would expect (for the xml to be valid) that it has a root node regardless..... what concept am i missing if this is not the case?

|||I think the fact is, it isn't invalid XML, it is nothing. So if you return no data, then no XML is created.|||

i agree

BUT :)

that does mean that anything that uses the resultset requires a condition to check if it is Null and either 1)do nothing, or 2) subsitute it for what would be valid xml i.e. an empty parent node eg <Root />

i think a lot of applications would require number 2, and therefore think the XML functionality of sql2005 should have this built in.

|||

And I don't disagree with you, though you can use the cast and concatenation thing I posted earlier as a workaround.

If noone posts that I was wrong, consider posting your suggestion here: https://connect.microsoft.com/SQLServer/Feedback and then post in this thread that you have, and I will vote for it.

|||

how about this:-

CREATE PROCEDURE [dbo].[up_DoStuff]
(
-- params
)
AS
SET NOCOUNT ON;

DECLARE @.pXML XML
SET @.pXML = (
SELECT
...
FROM
...
WHERE
...
FOR
XML Path('Test'),
ELEMENTS,
ROOT('Tests'),
TYPE
)

SELECT ISNULL(@.pXML, '<Tests/>')

FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return

FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?
It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>
|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]
|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]

for xml explicit

Hello,
I have been using for xml Explicit for a little while but this one has got
me stumped. I am return several tables that will each end up on a different
excel worksheet. A portion of the query is:
SELECT
1 as Tag,--metadata
Null as Parent,
isnull(@.project,'MISC') as [ReportData!1!Project],
Recid as [ReportData!1!Recid],
tnum as [ReportData!1!tnum],
null as [Metadata!2!WorksheetName!Element], --optional
Null AS [Metadata!2!Title!Element],
Null as [Metadata!2!FirstSubTitle!Element], --optional
Null as [Metadata!2!SecondSubTitle!Element], --optional
Null as [Metadata!2!Asofdate!Element],
Null as [Metadata!2!Rundate!Element]
from @.tblrecid as ReportData
union all
SELECT
2 as tag, --metadata
1 as parent, -- subset of Reportdata
Null, --Project
Reportdata.Recid as [ReportData!1!Recid],
Reportdata.tnum as [ReportData!1!tnum],
isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_'+left(rtrim(l.type2),10)),
l.Title,
lu.Title2 as FirstSubTitle,
lu.Subtitle1 as SecondSubTitle,
convert(char(10),l.Asofdate,121) as Asofdate,
convert(char(10),l.rundatetime,121) as Rundate
FROM tblReportLog l join tblReportLU lu
on l.tnum = lu.tnum join @.tblRecid Reportdata
on l.recid = Reportdata.recid
for xml explicit
The recid is the identifyer for each table.
what i get is:
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
<Metadata>
<WorksheetName>R_Freq</WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-11-01</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22"/>
What I want is (the root is added later):
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate></Asofdate>
<Rundate></Rundate>
</Metadata>
</ReportData>
Any ideas?Are you missing the order by that will group the children rows to its parent
row? Your excerpt does not show one...
Adding something like
order by [ReportData!1!Recid]
should help.
Best regards
Michael
PS: Another good case where using FOR XML PATH in SQL Server 2005 will make
writing such queries so much easier...
"michanne" <michanne@.discussions.microsoft.com> wrote in message
news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
> Hello,
> I have been using for xml Explicit for a little while but this one has got
> me stumped. I am return several tables that will each end up on a
> different
> excel worksheet. A portion of the query is:
> SELECT
> 1 as Tag,--metadata
> Null as Parent,
> isnull(@.project,'MISC') as [ReportData!1!Project],
> Recid as [ReportData!1!Recid],
> tnum as [ReportData!1!tnum],
> null as [Metadata!2!WorksheetName!Element], --optional
> Null AS [Metadata!2!Title!Element],
> Null as [Metadata!2!FirstSubTitle!Element], --optional
> Null as [Metadata!2!SecondSubTitle!Element], --optional
> Null as [Metadata!2!Asofdate!Element],
> Null as [Metadata!2!Rundate!Element]
> from @.tblrecid as ReportData
> union all
> SELECT
> 2 as tag, --metadata
> 1 as parent, -- subset of Reportdata
> Null, --Project
> Reportdata.Recid as [ReportData!1!Recid],
> Reportdata.tnum as [ReportData!1!tnum],
> isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_'+left(rtrim(l.type2),10)
),
> l.Title,
> lu.Title2 as FirstSubTitle,
> lu.Subtitle1 as SecondSubTitle,
> convert(char(10),l.Asofdate,121) as Asofdate,
> convert(char(10),l.rundatetime,121) as Rundate
> FROM tblReportLog l join tblReportLU lu
> on l.tnum = lu.tnum join @.tblRecid Reportdata
> on l.recid = Reportdata.recid
> for xml explicit
> The recid is the identifyer for each table.
> what i get is:
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> <Metadata>
> <WorksheetName>R_Freq</WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-11-01</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22"/>
> What I want is (the root is added later):
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate></Asofdate>
> <Rundate></Rundate>
> </Metadata>
> </ReportData>
> Any ideas?
>|||I had an order clause just like that but i took it out in one of the
iterations. I just tested it again to be sure but the result was the same. :
-(
As much as i'd prefer 2005, it isn't going to be available to me for a long
time.
"Michael Rys [MSFT]" wrote:

> Are you missing the order by that will group the children rows to its pare
nt
> row? Your excerpt does not show one...
> Adding something like
> order by [ReportData!1!Recid]
> should help.
> Best regards
> Michael
> PS: Another good case where using FOR XML PATH in SQL Server 2005 will mak
e
> writing such queries so much easier...
> "michanne" <michanne@.discussions.microsoft.com> wrote in message
> news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
>
>|||Ok - i needed to also order by one of the fields in tag 2.
Thanks!
"michanne" wrote:
> I had an order clause just like that but i took it out in one of the
> iterations. I just tested it again to be sure but the result was the same.
:-(
> As much as i'd prefer 2005, it isn't going to be available to me for a lon
g
> time.
> "Michael Rys [MSFT]" wrote:
>

for xml explicit

Hello,
I have been using for xml Explicit for a little while but this one has got
me stumped. I am return several tables that will each end up on a different
excel worksheet. A portion of the query is:
SELECT
1 as Tag,--metadata
Null as Parent,
isnull(@.project,'MISC') as [ReportData!1!Project],
Recid as [ReportData!1!Recid],
tnum as [ReportData!1!tnum],
null as [Metadata!2!WorksheetName!Element], --optional
Null AS [Metadata!2!Title!Element],
Null as [Metadata!2!FirstSubTitle!Element], --optional
Null as [Metadata!2!SecondSubTitle!Element], --optional
Null as [Metadata!2!Asofdate!Element],
Null as [Metadata!2!Rundate!Element]
from @.tblrecid as ReportData
union all
SELECT
2 as tag, --metadata
1 as parent, -- subset of Reportdata
Null, --Project
Reportdata.Recid as [ReportData!1!Recid],
Reportdata.tnum as [ReportData!1!tnum],
isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_' +left(rtrim(l.type2),10)),
l.Title,
lu.Title2 as FirstSubTitle,
lu.Subtitle1 as SecondSubTitle,
convert(char(10),l.Asofdate,121) as Asofdate,
convert(char(10),l.rundatetime,121) as Rundate
FROM tblReportLog l join tblReportLU lu
on l.tnum = lu.tnum join @.tblRecid Reportdata
on l.recid = Reportdata.recid
for xml explicit
The recid is the identifyer for each table.
what i get is:
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
<Metadata>
<WorksheetName>R_Freq</WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-11-01</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22"/>
What I want is (the root is added later):
<ReportData Project="MISC" Recid="1111" tnum="11">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate>2004-09-30</Asofdate>
<Rundate>2004-10-14</Rundate>
</Metadata>
</ReportData>
<ReportData Project="MISC" Recid="2222" tnum="22">
<Metadata>
<WorksheetName></WorksheetName>
<Title></Title>
<FirstSubTitle></FirstSubTitle>
<SecondSubTitle></SecondSubTitle>
<Asofdate></Asofdate>
<Rundate></Rundate>
</Metadata>
</ReportData>
Any ideas?
Are you missing the order by that will group the children rows to its parent
row? Your excerpt does not show one...
Adding something like
order by [ReportData!1!Recid]
should help.
Best regards
Michael
PS: Another good case where using FOR XML PATH in SQL Server 2005 will make
writing such queries so much easier...
"michanne" <michanne@.discussions.microsoft.com> wrote in message
news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
> Hello,
> I have been using for xml Explicit for a little while but this one has got
> me stumped. I am return several tables that will each end up on a
> different
> excel worksheet. A portion of the query is:
> SELECT
> 1 as Tag,--metadata
> Null as Parent,
> isnull(@.project,'MISC') as [ReportData!1!Project],
> Recid as [ReportData!1!Recid],
> tnum as [ReportData!1!tnum],
> null as [Metadata!2!WorksheetName!Element], --optional
> Null AS [Metadata!2!Title!Element],
> Null as [Metadata!2!FirstSubTitle!Element], --optional
> Null as [Metadata!2!SecondSubTitle!Element], --optional
> Null as [Metadata!2!Asofdate!Element],
> Null as [Metadata!2!Rundate!Element]
> from @.tblrecid as ReportData
> union all
> SELECT
> 2 as tag, --metadata
> 1 as parent, -- subset of Reportdata
> Null, --Project
> Reportdata.Recid as [ReportData!1!Recid],
> Reportdata.tnum as [ReportData!1!tnum],
> isnull(lu.worksheetname,left(rtrim(l.type1),8)+'_' +left(rtrim(l.type2),10)),
> l.Title,
> lu.Title2 as FirstSubTitle,
> lu.Subtitle1 as SecondSubTitle,
> convert(char(10),l.Asofdate,121) as Asofdate,
> convert(char(10),l.rundatetime,121) as Rundate
> FROM tblReportLog l join tblReportLU lu
> on l.tnum = lu.tnum join @.tblRecid Reportdata
> on l.recid = Reportdata.recid
> for xml explicit
> The recid is the identifyer for each table.
> what i get is:
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> <Metadata>
> <WorksheetName>R_Freq</WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-11-01</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22"/>
> What I want is (the root is added later):
> <ReportData Project="MISC" Recid="1111" tnum="11">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate>2004-09-30</Asofdate>
> <Rundate>2004-10-14</Rundate>
> </Metadata>
> </ReportData>
> <ReportData Project="MISC" Recid="2222" tnum="22">
> <Metadata>
> <WorksheetName></WorksheetName>
> <Title></Title>
> <FirstSubTitle></FirstSubTitle>
> <SecondSubTitle></SecondSubTitle>
> <Asofdate></Asofdate>
> <Rundate></Rundate>
> </Metadata>
> </ReportData>
> Any ideas?
>
|||I had an order clause just like that but i took it out in one of the
iterations. I just tested it again to be sure but the result was the same. :-(
As much as i'd prefer 2005, it isn't going to be available to me for a long
time.
"Michael Rys [MSFT]" wrote:

> Are you missing the order by that will group the children rows to its parent
> row? Your excerpt does not show one...
> Adding something like
> order by [ReportData!1!Recid]
> should help.
> Best regards
> Michael
> PS: Another good case where using FOR XML PATH in SQL Server 2005 will make
> writing such queries so much easier...
> "michanne" <michanne@.discussions.microsoft.com> wrote in message
> news:6D0DDE55-012A-4287-96C6-56C483880857@.microsoft.com...
>
>
|||Ok - i needed to also order by one of the fields in tag 2.
Thanks!
"michanne" wrote:
[vbcol=seagreen]
> I had an order clause just like that but i took it out in one of the
> iterations. I just tested it again to be sure but the result was the same. :-(
> As much as i'd prefer 2005, it isn't going to be available to me for a long
> time.
> "Michael Rys [MSFT]" wrote:

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)