CODEWITHSUNDEEP
sqlreporting-servicesreplacecaseiif
sethb
sethb

Reputation: 1

Using Case Statement to replace value in one field based on value in a seperate field

I'm creating a report in SSRS and I can't use the IIF statement within the report to get the following done. I'm getting aggregate errors when I try to sum within the SSRS report. 

`IIF(Fields!Period=0,0,IIF(Period=13,0,Balance/12))`

Works fine up until the moment I try to Sum.. get a silly aggregate error "Aggregate functions other than First, Last, Previous, Count, and Count Distinct can only aggregate data of a single data type"... These are all integers.

Basically I have a value in Master.Balance that I need to divide by 12 only when Secondary.Period equals 0 or 13. If Secondary.Period equals 0 or 13 then the value should be 0. I know my problem has to do with including the relationship between the tables, but I just don't know how to write that in.

Here is what I'm trying to use:

`CASE 
     WHEN Secondary.Period=0 OR Secondary.Period=13 
         THEN 0 
         ELSE Master.Balance/12 
End As BudByPer`

Here is how the two tables are related to each other:

`FROM Master LEFT OUTER JOIN Secondary 

ON Master.Project = Secondary.Project 
AND Master.object = Secondary.object 
AND Master.org = Secondary.org

`

How do I get the above into this:

    SELECT DISTINCT Master.Project, Master.Object, Master.Fund, Master.Segment, Master.Balance, Secondary.project, Secondary.object, Secondary.org, Secondary.Period, Secondary.object, Secondary.Project.

    FROM Master LEFT OUTER JOIN Secondary 
        ON Master.Project = Secondary.Project 
        AND Master.object = Secondary.object 
        AND Master.org = Secondary.org

WHERE (Master.object>=600000)
ORDER BY [Master.Fund]

Upvotes: 0

Views: 3056

Answers (1)

Metaphor
Metaphor

Reputation: 6405

You just need a select, it looks fine to me...

SELECT 
    Master.account, 
    Master.segment,  
    Secondary.desc, 
    Secondary.bud, 
    Segment.Num, 
    Segment.office,
    CASE 
        WHEN Secondary.Period=0 OR Secondary.Period=13 THEN 0 
        ELSE Master.Balance/12 
    End As BudByPer
FROM Master 
LEFT JOIN Secondary 
    ON Master.Project = Secondary.Project 
    AND Master.object = Secondary.object 
    AND Master.org = Secondary.org

Upvotes: 0

Related Questions