February 21, 2008 at 4:35 am
Hi Folks
I'm an SSRS Newbie, so please be gentle with me.
I have a List of Departments, with a Status and Value. I would like to convert the List into Columns.
Hopefully the illustration below helps explain this.
List looks like this:-
APP 100 DEPT 1
OFF 200 DEPT 1
APP 300 DEPT 2
OFF 400 DEPT 2
Would like to achieve this:-
APP OFF DEPT
100 200 1
300 400 2
I can handle the Grouping, but it's the Columns I'm struggling with.
I was hoping to have an expression that looks somthing like:-
=Fields!valuation.Value
iif (Fields!status.Value = 'APP')
and then the next column would have an expression:-
=Fields!valuation.Value
iif (Fields!status.Value = 'OFF')
I've obviously missed something as this doesn't work.
Any help would be appreciated.
Shodman
February 21, 2008 at 10:14 am
I will assume you are presenting the data in a table, but the same principles would apply if presenting in a series of text boxes or a list box.
Column1: expression should be something like =MID(Fields!Valuation.Value,1,3)
This should give you the first 3 characters of that field/column to use as a column in your table.
Column 2: expression should be something like =MID(Fields!Valuation.Value,5,3)
The above assumes there is a space after the first 3 characters and before the next, you probably get the general gist of this now (I hope).
Good luck
Nigel West
UK
April 9, 2008 at 3:49 am
I've found two ways to transpose a list of values into a column. The first is by far the easiest, but what you use will be dependent on your individual situation:
1. In SSRS, run the Report Wizard :blush:, and set the column containing your desired column headers as columns.
OR
2. In your TSQL use the PIVOT function. The online documentation has more details on this and sample code.
Good luck.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply