Post reply

SSRS 2005 change the value in data field based on column name

  • February 14, 2012 at 12:59 pm

    #252869

    I am generating my report in SSRS 2005.

    I have a row group "matrix_coursename" and a column group "matrix_grademark". The data field is "Counts". Report looks like the following:

    Courses A B C D F Total

    Mathema 8 10 2 1 0 21

    SocialStu 3 9 1 1 2 16

    Science 5 2 0 0 0 7

    Total 16 21 3 2 2 44

    Course names comes from "matrix_coursename" and grades come from "matrix_grademark". All the data in the data field come from "Counts" field in the output of my Stored Procedure

    Now, they want me to add an another column which is called D/F-- which essentially gives the total of D's and F's.

    So, i inserted an empty row for D/F in the stored procedure, so, i get the D/F column but, it has '0' value as I am not computing anything in the stored procedure.

    Now, is there a way using SSRS 2005, that I can accomplish D/F column data.

    I tried a couple of ways : In the data field I added "SUM(IIF((Fields!grademark.Value ="D" or Fields!grademark.Value ="F" ),Fields!Counts.Value,0))" which gives me the total of D's and F's....but how do I assign this value to the D/F column.

    If I do something like the following it gives me an error :

    (IIF(Fields!grademark.Value ="DF"),(SUM(IIF((Fields!grademark.Value ="D" or Fields!grademark.Value ="F"),Fields!Counts.Value,0).

    Is there in a way using SSRS 2005 to accomplish this? or Do i have to do this in query?Please help me out ....

  • February 15, 2012 at 5:27 am

    #1447278

    hope you have to do this in query

    Regards
    Durai Nagarajan

  • February 16, 2012 at 7:32 am

    #1447878

    I think you're having the same problem I had a little while ago. I'm assuming you're in the Education Industry. You might have to to fix the script where you get you marks.. Does your table have marks like D and F and numeric marks as well? If you do your going to get conversion errors until you filter them out.

    You're missing some parenthesiseses in your expression

    SUM(IIF((Fields!grademark.Value ="D" or Fields!grademark.Value ="F"),0,Fields!Counts.Value))

    I wrote assuming that if the mark is a D or F then the makr is zero or else sum the Fields!count.value. I'm not sure what the first IIF was supposed to do. if the grademark was DF then it never would have counted antything because the True part would then tried to evaluate if the grade mark was D or F.

  • February 17, 2012 at 12:47 pm

    #1448736

    Thanks Steven and Durgai for the replies. I finally ended up doing the calculations from the stored procedure and pulling the results from the stored procedure.

    I figured it was much easier to do that way instead of searching for the ways to do in SSRS 2005.

    Thanks a lot for your replies.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply