Flattening A Dataset - Crosstab/pivot

Hello,

I have a dataset that looks like this:

event_id answer_id answer 10001 20500 Red 10001 20501 Yes 10001 20502 No

I want to be able to turn this into a single row, so that my report output looks as follows:

event_id answer_20500 answer_20501 answer_20502 10001 Red Yes No

10002 Blue Maybe No

Can SSRS do this kind of transformation? For reasons not worth going into here, I'd rather not do this in SQL (although I CAN if I had to).

Thanks,

Michael

Hello Michael,

Yes, SSRS can do this for you. You'll need to use a matrix in your report with the error_id as your Row, answer_id as your Column, and answer as your Details.

With the sample data you gave, your report will look like this:

20500 20501 20502 10001 Red Yes No 10002 Blue Maybe No

Hope this helps.

Jarret

|||

That is most helpful! Thanks. I'll try that out and see how it goes. I'm still learning SSRS, and have a long way to go.

Thanks again,

Michael

|||

Jarret,

For each details column, I assume I will have to add a filter to bring back only the answer for the row with the specific question id? Or is there another way to accomplish that?

Michael

|||

Michael,

You won't have to do anything extra with the matrix report, it will handle that for you. The answer that is listed will be the answer that is associated with that answer_id and event_id.

Jarret

|||Hi Micheal and Jarrent,

Sorry to jump on your bandwagoon Micheal but I'm trying to do the same thing but in sql as I want to use it on a Gridwiew in ASP.Net web form.

Previously in MS Access, this is called a Crosstab query which I could do very easily as there is a wizard for it. The sql statement behind a completed crosstub query looks like this:

TRANSFORM Sum(ProfileData.Amount) AS SumOfAmount
SELECT ProfileData.[Project Number], Sum(ProfileData.Amount) AS [Total Of Amount]
FROM ProfileData
GROUP BY ProfileData.[Project Number]
PIVOT Format([ProfileDate],"mmm" & "-" & "yyyy");

Without having to list each date value in ProfileDate, this code dynamically puts every ProfileDate value as a colum, therefore I don't need to adjust the query every time a new ProfileDate value is inserted. Basically I'm having problems doing the same thing in sql server 2005. I know I'm new to it but I'm thinking there must be an easy way to do this.

Can you help please?

thank you

Aku

Flattening A Dataset

Hello,

I have a dataset that looks like this:

event_id answer_id answer 10001 20500 Red 10001 20501 Yes 10001 20502 No

I want to be able to turn this into a single row, so that my report output looks as follows:

event_id answer_20500 answer_20501 answer_20502 10001 Red Yes No

10002 Blue Maybe No

Can SSRS do this kind of transformation? For reasons not worth going into here, I'd rather not do this in SQL (although I CAN if I had to).

Thanks,

Michael

Hello Michael,

Yes, SSRS can do this for you. You'll need to use a matrix in your report with the error_id as your Row, answer_id as your Column, and answer as your Details.

With the sample data you gave, your report will look like this:

20500 20501 20502 10001 Red Yes No 10002 Blue Maybe No

Hope this helps.

Jarret

|||

That is most helpful! Thanks. I'll try that out and see how it goes. I'm still learning SSRS, and have a long way to go.

Thanks again,

Michael

|||

Jarret,

For each details column, I assume I will have to add a filter to bring back only the answer for the row with the specific question id? Or is there another way to accomplish that?

Michael

|||

Michael,

You won't have to do anything extra with the matrix report, it will handle that for you. The answer that is listed will be the answer that is associated with that answer_id and event_id.

Jarret

|||Hi Micheal and Jarrent,

Sorry to jump on your bandwagoon Micheal but I'm trying to do the same thing but in sql as I want to use it on a Gridwiew in ASP.Net web form.

Previously in MS Access, this is called a Crosstab query which I could do very easily as there is a wizard for it. The sql statement behind a completed crosstub query looks like this:

TRANSFORM Sum(ProfileData.Amount) AS SumOfAmount
SELECT ProfileData.[Project Number], Sum(ProfileData.Amount) AS [Total Of Amount]
FROM ProfileData
GROUP BY ProfileData.[Project Number]
PIVOT Format([ProfileDate],"mmm" & "-" & "yyyy");

Without having to list each date value in ProfileDate, this code dynamically puts every ProfileDate value as a colum, therefore I don't need to adjust the query every time a new ProfileDate value is inserted. Basically I'm having problems doing the same thing in sql server 2005. I know I'm new to it but I'm thinking there must be an easy way to do this.

Can you help please?

thank you

Aku

Flat File Destination

I have a red link from my OLE DB Destination to my Flat File Destination. I'm not sure why it's read but the OLE DB shoves some data into a table. I want to then take that data and move it into a flat file using the flat File Destination step but when I run my project, it shows the Flat File Destination Step green but my flat file shows nothing at the end.

I'm not sure what I'm doing wrong but the line linking the two is still red. Check out my picture here:

http://www.photopizzaz.biz/junk/1.jpg

and then the properties of my flat file destination module:

http://www.photopizzaz.biz/junk/2.jpg

http://www.photopizzaz.biz/junk/3.jpg

and my flat file connection manager editor properties:

http://www.photopizzaz.biz/junk/4.jpg

http://www.photopizzaz.biz/junk/5.jpg

Actually you redirected only the ErroroOutput of the OLE DB Destination.

Once the rows got into OLEDB Destination they don't move further. You need to add a Multicast transform in front of OLE DB destination to multiply the rows. And you can send one output to OLE DB Dest and one output to Falt File Destination

HTH,
Ovidiu

|||thanks a lot. But then what hooks up to the Multicast transform prior to the multicast transform? After my OLEDB Destination, then what, I just hook that up to the Multicast Transformation or hook something else to it first? How do I continue the flow after the OLE DB Destination is done I guess is what I'm asking...going from the OLEDB to the Multicast ? What's in between those 2?|||what did you mean by "Actually you redirected only the ErroroOutput of the OLE DB Destination."|||

Ok, got it to work, I didn't need what you said. I just used an OLE DB Source like I tried oringally but just deleted and reset it up shown here:

With that, how do I specify order of the workflow between my left side and right side. In other words, how do I ensure that after the OLE DB Destination is run, that it runs my OLE DB Source next? Since I can't hook an arrow between the 2, what is the proper way to ensure workflow and order here between the left set of tasks and right set of tasks show here:

http://www.photopizzaz.biz/junk/6.jpg

|||

You cannot force parallel graphs in the same Data Flow to execute sequentially. By setting them in the same Data Flow task you are actually setting a preference for them to be executed concurrently.

To assure your data flows are executed sequentially use multiple Data Flow tasks and connect them to define a wished order.

Thanks.

|||thanks, I actually figured out the data flow as you suggested.