none
How to formulate TSQL column expression?

    Question

  • I have an SSRS tablix with a column groupings on one of the columns.  The field expression value for this column is:

        =iif(isnothing(Fields!rptcard_score.Value),0,Fields!rptcard_score.Value)

    But for this column group, it is grouped on [score_type] and sorted by the following expression:

        =iif(Fields!score_type.Value="1st","1",
        iif(Fields!score_type.Value="2nd","2",
        iif(Fields!score_type.Value="3rd","5",
        iif(Fields!score_type.Value="4th","6",
        iif(Fields!score_type.Value="Exam 1","3",
        iif(Fields!score_type.Value="Exam 2","7",
        iif(Fields!score_type.Value="Sem 1","4",
        iif(Fields!score_type.Value="Sem 2","8",
        iif(Fields!score_type.Value="Final","9",
        Fields!score_type.Value)))))))))

    Now what I am trying to do is to make a custom expression in either SSRS or TSQL.  I would prefer to do this in TSQL.  This sproc dataset looks like:

        **subject    score_type    rptcard_score**
        Language Arts    Quarter 3    95
        Mathematics            TCAP            78
        Art                    Quarter 1    88
        Health/Safety     Quarter 1    84
        Modified Program     Quarter 2    86
        Physical Education     Quarter 1    62

    And in SSRS, this row looks like this:


    So my question is how do I develop an expression in my TSQL sproc or SSRS according to the following logic:

    Semester 1 Grade = Q1+Q2/ 2
    Semester 2 Grade = (.425 * Q1) + (.425 * Q2) + (.15 * TCAP)
    Final Exam = (Semester 1 Grade + Semester 2 Grade) / 2

    I realize that not all of the score_type values referenced above are part of this dataset yet, but I can at least calculate the Semester 1 Grade.  How can I calculate this according to the above formula? 

    Ryan D

    Tuesday, June 25, 2013 9:43 PM

Answers

  • Hi ironryan77,

    Please refer to the following codes:

    declare @Score table
    (
    	[subject] varchar(20)
    	,score_type char(9)
    	,rptcard_score int
    )
    
    insert into @Score values ('Language Arts',    'Quarter 3',    95)
    ,('Mathematics','TCAP',78)
    ,('Art','Quarter 1',88)
    ,('Health/Safety','Quarter 1',84)
    ,('Modified Program','Quarter 2',86)
    ,('Physical Education','Quarter 1',62)
    
    select [subject], isnull([Quarter 1],0) as [1st],isnull([Quarter 2],0) as [2nd],isnull([Quarter 3],0) as [3rd],isnull([TCAP],0) as [TCAP]
    from 
    (
    	select subject,score_type,rptcard_score
    	from @Score
    ) as t
    pivot
    (
    	sum(rptcard_score) for score_type in ([Quarter 1],[Quarter 2],[Quarter 3],[TCAP])
    ) as pt

    If you have any feedback on our support, please click here.



    Allen Li
    TechNet Community Support

    Friday, June 28, 2013 1:34 AM