Grouping of column issue with reporting services

Question

Grouping of column issue with reporting services

jerseyeddiem

SSC Enthusiast

Points: 188
More actions
June 10, 2010 at 9:34 am

#222007

Hello all, thanks for the help in advance.
I have a query that pulls back simple information
select date,partnum from orders
1/1/2010|parta
1/2/2010|
1/3/2010|partb
1/4/2010|partc
1/5/2010|partd
1/5/2010|parte
in a normal matric view it comes up like this
Column = 1/1/2010|1/2/2010|1/3/2010|1/4/2010|1/5/2010
ROW1 = parta|-|-|-|-
ROW2 = |-|-|partb|-|-
ROW3 = |-|-|-|partc|-
ROW4 = |-|-|-|-|partd
ROW5 = |-|-|-|-|parte
What can i do to make the data appear like this in reporting service.
1/1/2010|1/2/2010|1/3/2010|1/4/2010|1/5/2010
parta|-|partb|partc|partd
|-|-|-|-|parte
Its like a pivot but use date return value as column heading.. is this possible?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jerseyeddiem SSC Enthusiast Points: 188 More actions · Answer 1

I see that the information didnt come over formated well, sorry,,

my question seems simple, i want to display information from select date,partname from table1. I want my columns to be the date returned, and i want directly below each column header the partnames....

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Can you try using matrix ?

1)Drag the first column to the first column of the matrix

2)Insert one row for the column data and drag the second row record.

3)Hide the unwanted rows/columns

Thanks & Regards,
MC

jerseyeddiem SSC Enthusiast Points: 188 More actions · Answer 3

When i do that it still creates a row for each part, as apposed to only as many rows as exist for that date. i am just trying to get DIRECTLY underneath all columns, just a list of records associated with that column value.. I still get

1/1------1/2-------1/3

part1

---------partb

-------------------partc

trying to get

1/1------1/2------1/3

parta----partb-----partc

Thanks 🙂

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

You mean to say date and just below that the other data... right? like how it will come in a table..?

Select the individual column and do proper alignment...

Thanks & Regards,
MC

jerseyeddiem SSC Enthusiast Points: 188 More actions · Answer 5

Yes, so if say date 1/3 had the most amount of records, say 3,, the most amount of rows i would have is 3.

How do i do that??

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

Sorry to tell you that I didn't understand what you are trying to convey. Is it possible to put the expected format of result in an excel and attach? So that I can try to help you.

Thanks & Regards,
MC

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 7

only4mithunc (6/10/2010)
Can you try using matrix ?
1)Drag the first column to the first column of the matrix
2)Insert one row for the column data and drag the second row record.
3)Hide the unwanted rows/columns

I think MC is right.

I can see the expected result in SSRS report. Attached is the screen-shot of both Design and Preview of matrix used.

Cheers,
Hari
Tips & Tricks for SQL BI Developers