Reporting Services 2005 Caculating the percentage on columns in a single group

0
votes

After a week of googling I am no closer to a solution to my issue and am hoping someone out there can help. I apologize in advance if this has been answered although I searched quite hard.

I am creating a report of what we are calling "Aged Debtors". Part of this report requires that we have Last years total, this years total and then a percentage of the two as columns. Another requirement was to break "This years" (i.e. dates from today back to 1 year ago) into accounts that are 30 days overdue, 60 days overdue and over 90 days overdue. I have returned all the data in one dataset with the following columns

(#) Accounts, Country, AccountType, OverdueAmount, OverdueTime (30, 60, 90 and last year) and YearGroup (TY for this year, LY for Last Year)

I have created 2 row groups (Country and Account Type (which is a sub group of Country) and 2 column groups (Total Overdue and Overdue time - No relation to one another...Overdue time splits the amounts in to 30, 60 90 days etc while Total overdue gives me the last year and this year figures)

Total Overdue is split between 2 columns in the group using the following

=format(iif(Fields!YearGroup.Value = "LY",Sum(Fields!OverdueAmount.Value), iif((Fields!YearGroup.Value = "TY"), Sum(Fields!OverdueAmount.Value), 0)), "C")

THIS works fine! My problem is the client then wants a column next to that that basically does a percentage for him - sum(this year-last year)/last year. So basically I want this layout

LAST YEAR, THIS YEAR, PERCENTAGE

I tried creating another column in the group however this just inserted a new column next to each of the previous columns so I ended up with

Last Year, Column, This Year, Column

I then tried to create a column next to the group and use the scope of the "AccountType" row and an iif statement to differentiate between last year and this year

 (iif(Fields!YearGroup.Value = "TY", sum(overdueamount.value), 0) -iif(fields!YearGroup.Value = "LY", sum(overdueamount.value), 0)

to try and get a basic difference and it seems to want to add everything up - An example is 

Last year = $200
This Year = $100

Difference should be $100 - RS tells me it is $300!

(I have tried numerous iterations including nesting the iif statements as above)

It seems to be summing everything no matter what the value of YearGroup is - I have a feeling this is how RS renders groups - If one of the year values is 0 (Say last year there were $200 of an account type and this year there are $0) then it "seems" to do what I want it too.

I have contemplated doing this in SQL but I think it would be too complex to return in one data set (I am already doing some mad unions to get it all into one DS now).

Is there any way to work out a % value based on the difference between 2 dynamically created columns in a group? Happy to try and give more detail if needed...

3
sql-serverreporting-servicesreportingservices-2005

3 Answers

1
votes

I think you need to move your iif logic inside the Sum function, something like this:

= sum( iif(Fields!YearGroup.Value = "TY", overdueamount.value 
, iif(fields!YearGroup.Value = "LY", 0 - overdueamount.value , 0) ) )

BTW SSRS 2008+ resolves this challenge elegantly through the ReportItems collection...

0
votes

Firstly thanks Mike for getting me on the right path! I realised that I needed to do a conversion on the second part of the division and now it is working perfectly!

Cheers and I hope this question and these answers help someone else - After 2 weeks of fighting with this I might finally be able to put it to bed!

0
votes

Doing this is actually fairly simple in SQL, using PARTITION:

SELECT DISTINCT
    CASE
        WHEN DatePart(Year, INV.DueByDate) = DatePart(Year, GetDate()) - 1 THEN 'Last Year'
        ELSE 'This Year'
    END YearGroup,
    SUM(INV.AmountDue) OVER(PARTITION BY    CASE
                                                    WHEN DatePart(Year, INV.DueByDate) = DatePart(Year, GetDate()) - 1 THEN 'Last Year'
                                                    ELSE 'This Year'
                                                END) TotalAmountOverDue,
    SUM(INV.AmountDue) OVER(PARTITION BY    CASE
                                                    WHEN DatePart(Year, INV.DueByDate) = DatePart(Year, GetDate()) - 1 THEN 'Last Year'
                                                    ELSE 'This Year'
                                                END) / Sum(INV.AmountDue) OVER() * 100 Percentage

FROM Invoice INV

WHERE INV.DueByDate >= '1/1/2012'