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

Fit dataset on one page

Hey guys,
Let's assume I have dataset with two columns (A,B) and it has 100 records.
I'd like to split this dataset on the same page with 25 records in every column. Side by side.

Example:

ColA ColB ColA ColB ColA ColB ColA ColB 25 rec 25 rec 25 rec 25 rec

What should I use and what properties I have to play with?
Thanks.anyone? :))|||Just a quick drop answer:

- I asume regular column format does not work for you because you want balanced columns and RS gives you unbalanced ones.
- I asume you have a static format= always 100 records, always 25 records/column, or alike.

In those circumstances:
- I'll put 4 table regions side by side on the report design area
- Create a sort of paging expression. Based on Chris Hays blog entry (http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx)
- But instead of grouping items, I'll use the expression for filtering contents on each table. So, table data region 1 will filter for those records with "group" value of 1, table 2 for group 2, and so on...

Hope this helps,

Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning|||A related query to you...

how do i control how many records are displayed on the page?. currently I do not see any property and RS just gives me some defaullt number of records on a page.

The problem is, in one case I get 3 records on a page and 2900 pages which I do not want.

Thanks.|||I'm guessing again, because I've not tried this in code, but I think you can follow the same dynamic grouping solution that Chris Hays suggest in their bloeg (see previous link) to have another grouping every 100 records and then adding a page break based on that grouping.

Let me sumarize the solution:

- One list region with grouping each 100 records (expression1) and page break after each group
- Inside the list: 4 table regions, each one holding 25 records filtered according to the block expression2 (now you may want take "page number" in account).

Hope this helps,

Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning|||Thanks Jordi,
However your assumption wasn't right enough. :)) Sorry.
In my situation recordset can be between 0 and 100 records.
I will try to manipulate with "region" tables.|||Hey,

I was still not able to control how many records are shown on a single page? Any ideas if there is a property I can configure to control this?

Thanks.|||Hi cvajre (whatever this means ;-)

No, there is no property to control how many reports are shown on a single page.
Keep in mind that RS is designed to support and handle rich free-form reports in an open number of presentation formats. That way, even the "page" concept is a tricky one.

You can set page breaks on groups (only?), therefore the trick mentioned above for creating groups that fit your expected recordnumber size.

Hope this helps,

Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning