I have a table that I'm trying to report out on in SSRS-2014.
A cut down version of the table:-
TABLE_NAME FEE_NAME FEE_VALUE
PDE 1st year 100.00
PDE 2nd year 120.00
PDE 3rd year 130.00
PDE 4th year 140.00
PDE 5th Year 150.00
PGB 1st year 500.00
PGB 2nd year 520.00
PGB 3rd year 530.00
PGB 4th year 440.00
PGB 5th Year 850.00
PUS 1st year 100.00
PUS 2nd year 120.00
PUS 3rd year 130.00
PUS 4th year 140.00
PUS 5th Year 150.00
PCA 1st year 100.00
PCA 2nd year 120.00
PCA 3rd year 130.00
PCA 4th year 140.00
PCA 5th Year 150.00
PJP 8th year 600.00
PJP 10th year 820.00
PJP 11th year 40.00
PJP 12h year 140.00
PJP 14th Year 750.00
How can I display the data so that it's :-
Table Name 1st year 2nd year 3rd year 4th year 5th year 6th year 7th year 8th year 9th year 10th year 11th year 12 year 13th year 14th year
PDE 100.00 120.00 130.00 140.00 150.00
PGB 500.00 520.00 530.00 440.00 850.00
PJP 600.00 820.00 40.00 140.00 750.00
And so on.
The year columns will range from 1 to 20 and there wont always be a value for every year.
I've tried using :-
=Lookup(ReportItems!Textbox3.Value, Fields!FEE_NAME.Value, Fields!FEE_DIS1.Value, "Fees")
to use the year in the header row of the SSRS table to lookup the FEE_NAME from the dataset, but I get a row for every entry in the dataset but each row shows the same (correct) value.
I've tried using CASE in the SQL :-
CASE WHEN FEES.FEE_NAME = '3rd year' THEN FEE_VALUE END AS [3rd year],
Again, I get the value I want and then empty rows for the rest of the TABLE_NAME rows.
I also looked at Pivot (SQL) and a Matrix (SSRS) table but I just want to return the corresponding fee value and display it in the relevant year column whereas from what I've read so far Pivot and SSRS need an aggregate.
Hopefully this makes sense.
Cheers.
Best Answer
You can do this in either the SQL or using a matrix table within SSRS. Typically, I prefer to do it in the SQL, but that's not often the most convenient for others. You are correct in that you need to use an aggregate function with any kind of pivot functionality. In your case, you should use a
sum()
aggregate.While this sounds slightly counter intuitive, it will work fine as there is only a single entry here per
fee_name
. What you end up with is a list of values, where the only thing in the list is the desired value. A list such as,[10]
will sum to10
.