ForEachLoop and Object-Variable

Hi there!

I want to use a ForEachLoop. I've an object variable what i fill before going into the ForEachLoop. It contains 4 columns and in my testscenario it has two rows. In the ForEachLoop i want to set the current row values to 4 package variables (within package scope).

So i set the Enumerater as "Foreach-ADO-Enumerator", the Ado-source-variable is my objectvariable (what contains the recordset), and the enumerator-configuration i set to "rows in all tables" ("rows in the first table" works with equal result).

The variable-mapping looks like that:

Mypackvar1 - Index 0

Mypackvar2 - Index 1

Mypackvar3 - Index 2

Mypackvar4 - Index 3

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Am i on the right way? Is it great bullshit what i am doing?

Thanks for any suggestion,

Torsten

Sounds like you're on the right track. What I do is create an ExecuteSQL task with the result set set to "Full result set". In the Result Set page I click Add, put 0 for the result name and pick an Object variable to put the result in.

In the For Each loop I make the collection a "Foreach ADO.Net Schema rowset enumerator".
In variable mappings I pick a variable with the same type as the column and put in the appropriate offset ( 0 through fieldcount-1).

It sounds like you're doing that or something very close. I'd double check the variable you're assigning to is the same type as the resultset column.

|||

Torsten_Katthoefer wrote:

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Have you stepped through with the debugger to make sure you're getting back the values you expect? Are you calling a stored procedure, or just executing SQL? How are you populating the recordset?

|||

Hmm, it works - a little bit...

One problem has been the datatype - in the db, the column is bigint, and the conversion to DTI8 makes some trouble, so i decided to use a an object as datatype (package scope), and first in the for-each-loop i started a script task like that (CRQ_ID is the variable with type DTI8, and CRQ_OBJ is the result from my query to set the enumerations):

Dim Message As String

Dts.Variables("v_CRQ_ID").Value = CType(Dts.Variables("v_CRQ_OBJ").Value, Int64)

Message = CStr(Dts.Variables("v_CRQ_OBJ").Value) + "-" + CStr(Dts.Variables("v_CRQ_ID").Value) + "-" + CStr(Dts.Variables("v_LGE").Value) + "-" + CStr(Dts.Variables("v_DDS").Value) + "-" + CStr(Dts.Variables("v_FIS_PERIODE").Value)

MsgBox(Message)

I get the message boxes a view times, and everytime CRQ_OBJ is equal CRQ_ID. Seems it works fine.

BUT: The next task is a sql task witch updates a few rows with the CRQ_ID as input parameter. But it doesn't matter on the new values. Could it be, that i've to use another way to set the package variable?

Foreach Loop read table data and write to file

Hi,

I want to do the following with a ssis package:

INPUT:

A table contains 2 columns with data i need. column A=Filename and column B=FileContent

PROCESS:

I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.

I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.

Is there any example similiar to that so i could learn how to start on that?

Thanks

Danny

(Further you can use Import Column transform; in example from here this transform was called File Inserter (in beta release).) - I thought you need insert a file. To export a file you need Export Column transform

|||

The Sample you mention is not exactly what i need. That sample loops through a list of files and writes the names of the files back to a table. Then it has a standard Data Flow Task reading the table with the filenames inserted before and do something with it.

What i need is loops through a table, and for each row i need 2 values from the table to work with in the Data Flow Task. One of the values is the filename to be written and the other value is the content to be written in the file.

|||

You can do in following way :

1. Let's say you want to put the files in c:\YourFolder, add a data flow task and connection to your table

2. Add a derived column transformation; make a derived column name NewFilePath and in expressions :

"C:\\YourFolder\\+(DT_WSTR,50)ColumnA"

3. Add an Export Column transformation; in Export Column transformation editor set

Extract Column= ColumnB

File Path Column=NewFilePath

so SSIS will get the file from columnB and put in the folder using NewFilePath

Forcing Specific Distinct Columns....

Hi Guys,

I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...

Is their a way to force 1 column to always be unique?

Heres the query:

SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC

The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.

Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:

ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage

Which is kinda annoying... is there anyway i can prevent this?

And also apart from the Where Joins... is there a more efficient way of writing this?

thx for reading :-)

--PhilkillsDoes no one know any way around this?|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.

on the contrary, i left out the data on purpose as it was irrelevent... i was only trying to point out an example of whats wrong... in the "EXAMPLE"
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
The parts in bold should only appear once.... and the data it should select should be Mathew smith who purchased 30 of that item... 36 is the total amount sold, what i need the query to do... is to only display the person who bought the highest amount of the item that is part of the top 5 best selling items.

another "EXAMPLE":

ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................30......... ......................Jim
2.........20.................20......... ......................Jam
3.........10.................10......... ......................Flim
4.........5..................5......... .......................Flam
5.........2..................2......... .......................Stam

In this case... only 1 person purchased the items in the top 5...

However, if 5 people purchased the same item... the query would return:

ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................5.......... .....................Jim
1.........30.................5.......... .....................Jam
1.........30.................5.......... .....................Flim
1.........30.................5.......... .....................Flam
1.........30.................10......... .....................Stam

Notice how it returned the TOP 1 Item instead of the TOP 5 items?

The data is correct... the query is wrong... which is why i asked if i could put a constraint on the data returned saying that ItemID MUST be unique... unless ofcourse there is a better way to do it ;p

I hope this more accurately describes the problem i am having. :-)

--Philkills|||You are making this more confusing than it should be.

Lets break it down into parts.

Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||You are making this more confusing than it should be.

Lets break it down into parts.

Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

yep that code gives the top 5 items ^^|||...and now you want the top customer for each of those items?|||...and now you want the top customer for each of those items?

correct...|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

Theres no table called CUSTOMER_ITEM... However, i have edited it to return the top customerID correctly.

SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ITEM
INNER JOIN ORDER_ITEMOrdItem2 ON ITEM.ItemID = OrdItem2.ItemID
INNER JOIN ORDER_T ON OrdItem2.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC,
ORDER.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM_0510096.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||ok, so i can display the customer ID... now how do i display the rest of the customer info ... ^_^|||Am i assuming that no one knows the answer? :confused:

lol, kinda funny a problem that even professionals can't figure out :p|||lol, kinda funny a problem that even professionals can't figure out :poh stop

yes, there are a lot of professionals here

what makes you think they want to be your personal free development center?

ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

good luck, phil

:)|||oh stop

yes, there are a lot of professionals here

what makes you think they want to be your personal free development center?

ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

good luck, phil

:)

Eh?, i asked 1 question in this thread... and simply gave more info... in my other posts...

If your refering to my other questions that i've asked around the forum... well, they are pretty much unrelated to this question. I have done a lot of research on SQL queries and the only questions i've asked are related to areas that i have found very difficult to find an answer to.

So as a last resort i turned to this support forum, now i realise your doing this out of the kindness of your heart (i think) and for that im grateful. I too help people, but my area of expertise is with C++/Other languages, i on the other hand will help people regardless of the complexity of the problem, infact the more complex the better :p keeps things interesting.

In regards to my original point about no one being able to solve the problem... well, that was based on the posts inside this thread:

OK. I lied. This is complicated, but we are almost there...

He admitted himself that this was complicated, still being new to SQL i do not yet know all of its limits... but when a proffessional admits that something is complicated, i would say its a pretty good indication that it actually is complicated...

When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.

So perhaps next time, before jumping in and making assumptions... you should consider all the facts and why i might have said such words.|||When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.|||I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.

I.... was not intending to be rude
YOU.... Misinterpreted my point.. obviously thinking i was taking a dig at you or your skill when i was not...
I.... Appreciate all your help so far
YOU.... Probably won't even read this :p

It's up to you whether you help me or not i suppose... but just for the record... the only reason i may have seemed slightly impatient was due to the ever approaching deadline :confused:|||as the old saying goes, "failure to plan on your part does not constitute an emergency on our part"

i'm sorry about your homework assignment coming due real soon, but that's life

if you would like to start a new thread, we can close this one and let it fade into the archives...|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.

And the fact that now it turns out that this was some sort of homework assignment irks me even more.|||Not to bump a thread that seems to have everyone up in arms, but...

I like to try to solve these "puzzles", just to see if I can.

I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.

Using my own data, I came up with this:

SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC

This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.

And the fact that now it turns out that this was some sort of homework assignment irks me even more.

again suprise suprise you jump to conclusions that you think are right... i was NOT implying anything... if something can't be done... it can't be done... thats why i said what i did...

Not to bump a thread that seems to have everyone up in arms, but...

I like to try to solve these "puzzles", just to see if I can.

I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.

Using my own data, I came up with this:

Code:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549

thank you very much mate for putting in the effort to solve this conundrum
:-). I appreciate it i really do ^^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p
i on the other hand will help people regardless of the complexity of the problem,
When he never replied after that post... i just assumed that he gave up and admitted defeat...
YOU.... Probably won't even read this
You were acting like a complete twit. The fact that you seem oblivious to this I will chalk up to immaturity. Hopefully, you will soon grow up enough to be respectful to people who are volunteering their time and expertise to help you out.|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.

I intended the top 5 in the outer query to pull the top 5 customers, while
the top 5 in the nested query is pulling just the top 5 products.sql

Force columns to appear in matrix.

I have created the report below in SSRS to show the total by month for the calendar year.

As of my January running it runs great as there is data for all 12 months of the year and the matrix is created with the required 12 columns.

01

02

03

03

4

05

06

07

08

09

10

11

12

Total

AA

17.5

22

17.5

75

30

162

BB

15

15

15

15

15

15

15

15

15

15

15

15

15

195

CC

15

15

15

15

15

337

15

233.3

15

325.5

117

15

15

1147.8

DD

315

290

1231

1231

1231

269

953

384.8

706.8

1798.8

290

602

419

9720.25

Total

345

320

1261

1261

1261

638

983

633.1

758.8

2156.8

497

662

449

11225.1

As I go to project this report for January 2007 I am having trouble.

I want the report to appear as below with the 12 requisite columns.

01

02

03

03

4

05

06

07

08

09

10

11

12

Total

AA

0

BB

15

15

CC

15

15

DD

315

315

Total

345

345

But as only data for the first column is returned from my query I only get one column to appear.

01

Total

AA

0

BB

15

15

CC

15

15

DD

315

315

Total

345

345

How do I force the other 12 columns / months to appear in my crosstab/matrix and to populate with data as it becomes available?

Hi,

One option I can think of is adding some dummy records to the query, using the UNION statement. One for each month of the year, with a value 0.

Regards, Jeroen

|||

At the risk of sounding like a total noob. Can you modify the query below or give an example?

SELECT

bo.AS400Billingid,

OrgName,

ih.date,

ih.rev

FROM billingorg bo

INNER JOIN

(SELECT

AS400BillingID,

substring(InvoiceDate,4,2) date,

sum(SalesAmount) rev

FROM

InvoiceHeader

WHERE

left(InvoiceDate,3)='106'

GROUP BY

AS400BillingID,substring(InvoiceDate,4,2)) ih

ON bo.AS400BillingID=ih.AS400BillingID

|||

I found a solution.

SELECT

id,

OrgName,

AddedDate,

SalesTerritory,

IndustryCode,

AccountManager,

[01] AS 'JAN',

[02] AS 'FEB',

[03] AS 'MAR',

[04] AS 'APR',

[05] AS 'MAY',

[06] AS 'JUN',

[07] AS 'JUL',

[08] AS 'AUG',

[09] AS 'SEP',

[10] AS 'OCT',

[11] AS 'NOV',

[12] AS 'DEC'

FROM

(SELECT

bo.AS400Billingid id,

bo.OrgName,

bo.AddedDate,

bo.SalesTerritory,

bo.IndustryCode,

bo.AccountManager,

substring(ih.InvoiceDate,4,2) date,

ih.SalesAmount rev

FROM

billingorg bo INNER JOIN InvoiceHeader ih

ON bo.AS400BillingID=ih.AS400BillingID

WHERE

left(InvoiceDate,3)='106') as data

PIVOT

(

sum(rev)

FOR date IN([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])

) XTab

For XML: create elements using cell values

Hi All,
If have a sql table with 2 columns and 2 rows with values
[["col1row1","col2row1"],["col1row2","col2row2"]].

Using t-SQL with "for xml"
How can i create a xml where the cell values (not column names) appear
as elements?
eg:
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2
Thanks,

slyi-- It can be done, but remember that you will have to
-- escape all the XML yourself

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

select 1 as Tag,
null as Parent,
'<'+col1+'>'+col2+'</'+col1+'>' as [TestNode!1!!xml]
from #test

order by Tag,[TestNode!1!!xml]
for xml explicit

drop table #test|||Thanks thats exactly what i needed to know|||On closer examination this wont work it gives

<TestNode><col1row1>col2row1</col1row1></TestNode>
<TestNode><col1row2>col2row2</col1row2></TestNode>
while i need something like
<TestNode>
<col1row1>col2row1</col1row1>
<col1row2>col2row2</col1row2>
</TestNode|||Unless someone else knows better, you're out of luck. Perhaps
you could look at redesigning the XML you are generating
and then apply an XSL transformation at the client.|||Thanks Mark. Could i create a temp table, with the cell values as
columns and build a sql xml query or loop from there?
Although im not too sure if that would work, very efficiently?|||(adrianca@.gmail.com) writes:
> Thanks Mark. Could i create a temp table, with the cell values as
> columns and build a sql xml query or loop from there?
> Although im not too sure if that would work, very efficiently?

I can't see that you can do this in SQL 2000 at all. Well, you can
build an nvarchar string that has the XML, and forego FOR XML
altogether, but if you exceed 4000 characters you lose anyway.

I think you need to build this document client-side.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--something like below is what i was thinking but is it efficent?
--as the client side asp code works out very slow thats why i want to
do it on the sql server if possible

create table #test(
col1 varchar(8),
col2 varchar(8))

insert into #test(col1,col2)
values ('col1row1','col2row1')
insert into #test(col1,col2)
values ('col1row2','col2row2')

create table #xmltree( xmlblob text)
INSERT INTO #xmltree VALUES ('<table>')

Declare @.sqlq varchar(4000)
DECLARE @.textptr varbinary(16)
DECLARE @.bigtext varchar(8000)
DECLARE @.textlen int
DECLARE @.col1 varchar(32), @.col2 varchar(32)

SELECT @.textptr=TEXTPTR(xmlblob) FROM #xmltree

DECLARE tst_cursor CURSOR FOR select * from #test
OPEN tst_cursor
FETCH NEXT FROM tst_cursor into @.col1, @.col2
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.bigtext='<'+ @.col1 + '>' +@.col2+'</'+ @.col1 + '>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext
FETCH NEXT FROM tst_cursor into @.col1, @.col2
END
CLOSE tst_cursor
DEALLOCATE tst_cursor
SET @.bigtext='</table>'
set @.textlen =(SELECT DATALENGTH(xmlblob) FROM #xmltree )
UPDATETEXT #xmltree.xmlblob @.textptr @.textlen 0 @.bigtext

select xmlblob from #xmltree

drop table #test
drop table #xmltree|||(adrianca@.gmail.com) writes:
> --something like below is what i was thinking but is it efficent?

More to the point: does it work?

> create table #xmltree( xmlblob text)
> INSERT INTO #xmltree VALUES ('<table>')

There is not really any way go get the xml from FOR XML into the table.
Well, you can get it to the client, and then INSERT back. Please don't
that. You're wasting bandwidth.

> --as the client side asp code works out very slow thats why i want to
> do it on the sql server if possible

For this sort of task, I would expect VBscript to be faster than T-SQL,
since we are only doing string manipulation.

You could write a program in C or C# for the task, but then you would have
to pass the XML string to the C program in some way. If you go by file,
you probably lose on the swings what you gain on the roundabout.

I should add the disclaimer that I have no knowledge about ASP
programming.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||asp 6 and asp.net both took about 10 sec to create the xml client side
from a sql table for a 50k table to xml

Using this method it now takes about 2 sec's by just displaying
resultset.

<%@. Page Language="C#" %
<%@. Import Namespace="System.Data.SqlClient" %
<script runat="server">
SqlConnection sqlConnection1;
SqlCommand sqlCommand1;

void Page_Load(Object Sender, EventArgs e) {

sqlConnection1 = new System.Data.SqlClient.SqlConnection();
sqlCommand1 = new System.Data.SqlClient.SqlCommand();
sqlConnection1.ConnectionString = "some connection details";
sqlConnection1.Open();
sqlCommand1.Connection = this.sqlConnection1;
sqlCommand1.CommandText = "sp_getaxml_dataisland";
Response.ContentType = "text/xml";
Response.Write(sqlCommand1.ExecuteScalar().ToStrin g());

}
</script
For me thats a performance gain worth taking.|||(adrianca@.gmail.com) writes:
> asp 6 and asp.net both took about 10 sec to create the xml client side
> from a sql table for a 50k table to xml

Just to check: how did you get the data to the client? You did get
all data into a dataset didn't you?

> sqlCommand1.CommandText = "sp_getaxml_dataisland";

sp_ is a prefix that is reserved for system stored procedure, and
SQL Server first looks in master for these. You should not use it
for your own code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Just to check: how did you get the data to the client? You did get
>all data into a dataset didn't you?
Since the resultset is just one huge SQL Text datatype,
i just wrote it directly to the page, no need for the overhead of
creating a .net dataset object,
and then a javascript dataisland reads it directly.

eg: <xml id="my-dataisland" src="http://pics.10026.com/?src=getdataisland.aspx" /
>> sqlCommand1.CommandText = "sp_getaxml_dataisland";

>sp_ is a prefix that is reserved for system stored procedure, and
>SQL Server first looks in master for these. You should not use it
>for your own code.
Thanks for the tip i didnt know that.
Do you know if first checks the master table, will that slow down the
request correct / target sp?
I had thought you needed to put "master.dbo.sp_" to access a master sp?

Thanks for your help.|||(adrianca@.gmail.com) writes:
> Since the resultset is just one huge SQL Text datatype,
> i just wrote it directly to the page, no need for the overhead of
> creating a .net dataset object,
> and then a javascript dataisland reads it directly.

Javascript is maybe not the fastest. Can you save to a file, and run a
program in a non-interpreted langauge?

> Do you know if first checks the master table, will that slow down the
> request correct / target sp?
> I had thought you needed to put "master.dbo.sp_" to access a master sp?

In such case "sp_help" would not work. In fact when you say

somedatabase.dbo.sp_help tbl

what you get information about is somedatabase.dbo.tbl.

Exactly what happens is difficult describe, because it changes every
now and then. But if Microsoft would ship a system procedure called
sp_getaxml_dataisland, you would be in for a nasty surprise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegroups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>|||Thanks Michael for reply
I'm using SQL 2000
Vishy

for xml raw problem

Hi
I'm running a simple query with for xml raw on a table
one of columns in select clause here is of type text and stores xml
file itself.
The problem i'm facing is when I get this text back from this column I
get back unformatted xml
e.g
instead of getting <GPF> I get <GPF> etc
How do I get this column as formatted xml text ?
Thanks for help
Vishy
>
> I'm running a simple query with for xml raw on a table
> one of columns in select clause here is of type text and stores xml
> file itself.
> The problem i'm facing is when I get this text back from this column I
> get back unformatted xml
> e.g
> instead of getting <GPF> I get <GPF> etc
> How do I get this column as formatted xml text ?
>
Hello
signs '<' and '>' are very important in XML because they show begin and end
of xml element, so when an element or attribute value has such signs it must
be changed into < and >. But when you open XML which contains such
substitution - using DOM or OPENXML in SQL you will get values with signs
'<', '>'. So I think that it sould not be a problem - siply try to process
XML which was generated and you will see that it has valid values of
elements/attributes.
I hope it helps,
Alwik
|||Thanks Alwik for reply
I tried to create object on XmlDocument using LoadXml() method
it loads up the xml successfully but if I want to run any Xpath; it
fails
as <GPF> is not treated as <GPF> so I can't find GPF element
is there any work around?
Vishy
|||Are you using SQL Server 2000 or 2005?
In the case of SQL Server 2000, you will have to use the explicit mode and
its !xml directive, if you want to have string data be inlined into the XML
structure.
In the case of SQL Server 2005, you can stick with the FOR XML RAW query,
but you need to cast the string data to XML in the select clause.
Best regards
Michael
"Vishy" <vishal.halbe@.gmail.com> wrote in message
news:1128445808.595093.141180@.g49g2000cwa.googlegr oups.com...
> Thanks Alwik for reply
> I tried to create object on XmlDocument using LoadXml() method
> it loads up the xml successfully but if I want to run any Xpath; it
> fails
> as <GPF> is not treated as <GPF> so I can't find GPF element
> is there any work around?
> Vishy
>
|||Thanks Michael for reply
I'm using SQL 2000
Vishy

For XML Path problems

Can anyone tell me how to add multiple columns with the same name? Here is an example of the XML format I'm trying to create using For XML Path

<TABLETYPE TYPEABBRV=“IDEADISAB” TOTALINDICATOR=“N”>
<CATEGORY TYPE=“DISABCATIDEA” VALUE=“AUT”/>
<CATEGORY TYPE=“AGESA” VALUE=“6”/>
<CATEGORY TYPE=“EDENVIRIDEASA” VALUE=“RC80”/>
<AMOUNT>10</AMOUNT>
</TABLETYPE>

Here is the query I was trying to use

SELECT

'IDEADISAB' AS '@.TYPEABBRV',

'N' AS '@.TOTALINDICATOR',

'DISABCATIDEA' AS 'CATEGORY/@.TYPE',

IdeaCategory AS 'CATEGORY/@.VALUE',

'AGESA' AS 'CATEGORY/@.TYPE',

AGE AS 'CATEGORY/@.VALUE',

'EDENVIRIDESAS' AS 'CATEGORY/@.TYPE',

EECATEGORY AS 'CATEGORY/@.VALUE',

COUNT(*) AS 'AMOUNT'

FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory

FOR XML PATH('TABLETYPE'), TYPE)

And this is the error I'm getting

Msg 6810, Level 16, State 1, Line 1

Column name 'CATEGORY/@.TYPE' is repeated. The same attribute cannot be generated more than once on the same XML tag.

Any help would be much appreciated

Use subqueries

SELECT
'IDEADISAB' AS '@.TYPEABBRV',
'N' AS '@.TOTALINDICATOR',
(SELECT
'DISABCATIDEA' AS '@.TYPE',
IdeaCategory AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'AGESA' AS '@.TYPE',
AGE AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'EDENVIRIDESAS' AS '@.TYPE',
EECATEGORY AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
COUNT(*) AS 'AMOUNT'
FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory
FOR XML PATH('TABLETYPE'), TYPE

|||

Thanks Mark,

I had already tried using subqueries but could not quite get the syntax correct. I was using the 'from' statement after every select and that was throwing all of my records under one tag. Thanks again.

FOR XML EXPLICIT

Hello.
I'm trying to list two columns and count one from it.
Here's my code:
SELECT Caption0 as 'Operating System', CSDVersion0 , COUNT(CSDVersion0) as
'Number'
FROM v_GS_OPERATING_SYSTEM
GROUP BY Caption0, CSDVersion0
ORDER BY Caption0, CSDVersion0
FOR XML EXPLICIT
I have a message, that I FOR XML EXPLICIT requiers the first column to
hold positive integers that represent XML tags IDs.
I know what it means, but i'm nOOb. What i must do? I think I looked by
google everywhere...
Legrooch
You may want to look at the articles linked from
http://blogs.msdn.com/mrys/archive/2...09/151523.aspx
While they talk about the new PATH mode, they contain several FOR XML
EXPLICIT examples.
Feel free to come back if that is not enough information.
Best regards
Michael
"Legrooch" <leszek.gruszka@.kana.com.pl> wrote in message
news:opsavdy3l2732xkw@.k3-xp-lg.k3.win...
> Hello.
> I'm trying to list two columns and count one from it.
> Here's my code:
> SELECT Caption0 as 'Operating System', CSDVersion0 , COUNT(CSDVersion0) as
> 'Number'
> FROM v_GS_OPERATING_SYSTEM
> GROUP BY Caption0, CSDVersion0
> ORDER BY Caption0, CSDVersion0
> FOR XML EXPLICIT
> I have a message, that I FOR XML EXPLICIT requiers the first column to
> hold positive integers that represent XML tags IDs.
> I know what it means, but i'm nOOb. What i must do? I think I looked by
> google everywhere...
> --
> Legrooch

For NULL

Hello Everybody
When I do Insert query for table targeting selected columns
it has to store NULL value for rest of the columns,
without going into design and click NULL for each column ,
is there any other way to set once for all, that is to be
applicable to all tables?On Mar 22, 10:20 pm, spaulsa...@.yahoo.co.in wrote:
> Hello Everybody
> When I do Insert query for table targeting selected columns
> it has to store NULL value for rest of the columns,
> without going into design and click NULL for each column ,
> is there any other way to set once for all, that is to be
> applicable to all tables?
Select the table and columns from information_schema.columns and write
a query to ALTER TABLE ALTER COLUMN with NULL

For NULL

Hello Everybody
When I do Insert query for table targeting selected columns
it has to store NULL value for rest of the columns,
without going into design and click NULL for each column ,
is there any other way to set once for all, that is to be
applicable to all tables?
On Mar 22, 10:20 pm, spaulsa...@.yahoo.co.in wrote:
> Hello Everybody
> When I do Insert query for table targeting selected columns
> it has to store NULL value for rest of the columns,
> without going into design and click NULL for each column ,
> is there any other way to set once for all, that is to be
> applicable to all tables?
Select the table and columns from information_schema.columns and write
a query to ALTER TABLE ALTER COLUMN with NULL

For NULL

Hello Everybody
When I do Insert query for table targeting selected columns
it has to store NULL value for rest of the columns,
without going into design and click NULL for each column ,
is there any other way to set once for all, that is to be
applicable to all tables?On Mar 22, 10:20 pm, spaulsa...@.yahoo.co.in wrote:
> Hello Everybody
> When I do Insert query for table targeting selected columns
> it has to store NULL value for rest of the columns,
> without going into design and click NULL for each column ,
> is there any other way to set once for all, that is to be
> applicable to all tables?
Select the table and columns from information_schema.columns and write
a query to ALTER TABLE ALTER COLUMN with NULL

For index

Is there any way I can findout all the columns and it's table which used in
stored procedure in the where clause and don't have indexes on it ? If yes
then how can I finout out.
Thanks
Look at the Index Tuning Wizard. That would be a great tool for you to
learn how to use.
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:188538E6-A0AA-4750-98DA-AE4DE36ECB81@.microsoft.com...
> Is there any way I can findout all the columns and it's table which used
in
> stored procedure in the where clause and don't have indexes on it ? If yes
> then how can I finout out.
> Thanks

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)