0

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.

0

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 to 10.

|improve this answer|||||
  • Thank you for your reply. You may need to help with the SQL I'm afraid. I'm using :- ~~~ Select FEES.TABLE_NAME, SUM(CASE WHEN FEES.FEE_NAME = '3rd year' THEN FEE_VALUE END) AS [3rd year], SUM(CASE WHEN FEES.FEE_NAME = '5th year' THEN FEE_VALUE END) AS [5th year] From FEES where FEES.TABLE_NAME = 'PDE' or FEES.TABLE_NAME = 'PGB' ~~~ If I leave in the TABLE_NAME then I get an error. If I remove it then each year is added rather than a separate row for the new TABLE_NAME.+ – UK1539 Dec 2 '19 at 17:02
  • P.S. I've been able to acheive it using a Matrix table, thank you. I just need to look at the year order now as it has placed 10 before 1. Thank you – UK1539 Dec 2 '19 at 17:07
  • It's being sorted as a string, which can cause odd behaviors like that with numbers. You'll want to sort on an expression. Something like Int(left(Fields!My_Field.Value, Instr(Fields!My_field.Value, "-") +1)) will cut the string to the left most integer value and then sort based on integer values. I forget the exact syntax, but that should work as a starting point. – Jacobm001 Dec 2 '19 at 18:03
  • Thank you, I'll give that a go. – UK1539 Dec 3 '19 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.