How to display mulitple columns (header and value) horizontally stacked in one row cell?

Question

Lets say I have a report dataset like...

Name, AgeYears, WeightPounds, HeightInches
Anna, 84, 90, 60 
Sarah, 38, 120, 67
Matt, 36, 160, 68
Preston, 6, 40, 40

I would like the report to display those values in 4 rows, but only TWO cells per row (one cell for numeric values and the other for the Name) like...

What is the simplest way to accomplish stacking column data horizontally in a cell for one row of data?

Answer 1

Simplest way without grouping would be as follow:

1. Add three columns and three rows in table.

• In 1st Row 1st Column add Text "Age"
• In 2nd Row 1st Column add Text "Weight"
• In 3rd Row 1st Column add Text "Height"
  Set Right Border to "None" for above mentioned 3 cells.

• In 1st Row 2nd Column put age field. = Fields!Age.Value
• In 2nd Row 2nd Column put weight field. = Fields!Weight.Value
• In 3rd Row 2nd Column put height field. = Fields!Height.Value
  Set Left Border to "None" for above mentioned 3 cells.
  

• In 1st Row 3rd Column put Name field. = Fields!Name.Value
  Select All 3 rows of third column and Merge Rows

                   

Answer 2

Hi ,

Try this simple technique , but you wont have lines separated b/w Age,Weight & Height .

Add two columns :

For Stats -  = Fields!Age.Value + VBCRLF + Fields!Weight.Value +  VBCRLF + Fields!Height.Value

For Name -  = Fields!Name.Value

Reference link - http://social.technet.microsoft.com/wiki/contents/articles/18750.ssrs-multiple-ways-to-split-a-string-into-multiple-lines.aspx

---

sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

Answer 3

Is there a "Merge Rows" option?  That would be ideal!

Answer 4

Yes there is Merge option to merge rows or columns.

Please see this link :http://technet.microsoft.com/en-us/library/dd207131.aspx

Regards,

Sami

Answer 5

Hi ,

If you dont need lines in between Age,Weight & Height then follow like I suggested in my previous post .

Else here is another solution :

Consider below query is my dataset :

DECLARE  @sample_data table
(
Name varchar(50),
AgeYears INT,
WeightPounds INT,
HeightInches INT
)
insert @sample_data SELECT  'Anna',84,90,60
insert @sample_data SELECT  'sarah',38,120,67
SELECT * FROM @sample_data

Add a matrix , Under Rows add Name field

and then Insert Row - > Inside Group -  Below  as shown in below image: (similar way add two more rows)

Below is the expressions for three inserted rows :

="Age"+Space(3)+CStr(Fields!AgeYears.Value)
="Weight"+Space(3)+Cstr(Fields!WeightPounds.Value)
="Height"+Space(3)+Cstr(Fields!HeightInches.Value)

---

sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

Answer 6

Hi Sathya,

Great Post!!

There is a more simple way of achieving this .

Below are the steps for doing that:

1.Take a list and assign it to the desired data set.

2.Add a additional column  to the right of the tablix.

3.Insert a  Row Above outside the group above..and name the headers in that row.

4.Now drag the "Name " field in the first column. and in the second column Add a rectangle to it.

5.After adding the rectangle in the second column drag all the three fields(i.e Age,Height,Weight) as three individual textboxes.

6.Go the details  row group and add grouping by name.

7.Go for a preview and enjoy the output !! :)

This is a better approach as you don't have to write  any custom code for calculating any of the fields.

If you are facing any trouble in achieving this i can mail you the sample RDL file.

Thanks &  Regards,

Milan.

Sharing knowledge is Fun!!

Mark as answered if my post solved your problem and Vote as helpful if my post was useful.