CODEWITHSUNDEEP
reporting-servicessqlreportingservice
Maher Khalil
Maher Khalil

Reputation: 539

Get value between from to dataset columns ssrs

I have a data set like that:

Data Set Contents

From  To Comment 
----+---+-------- 
0    50   Bad 

50   70   Good 

70   100  Excellent

If I have a value of 75, I need to get Excellent by searching the Dataset. I know about the lookup function but it is not what I want. How can I do that? The values should be in percentage.

Note : the value (75) is Average of a column (Calculated) it calculate student grade from max and student mark Version SQL Server 2016

Note 2 : the dataset is from database not static values

Thank You

Upvotes: 0

Views: 567

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21738

Assuming you only ever have a fixed number of 'grades' then this will work. However, I would strongly recommend doing this type of work on the server where possible.

Here we go...

I created two datasets dsGradeRange with the following sql to recreate your example (more or less)

DECLARE @t TABLE (low int, high int, comment varchar(20))

INSERT INTO @t VALUES
(0,49,'Bad'),
(50,69,'Good'),
(70,100, 'Excellent')

SELECT * FROM @t

dsRandomNumbers This just creates 30 random numbers between 0 and 100

SELECT * 
    FROM (SELECT top 30 ABS(CHECKSUM(NEWID()) % 100) as myNumber FROM sys.objects) x
    ORDER BY myNumber

I added a table to the report to show the grades (just for reference).

I then added a table to show the dsRandomNumbers

Finally I set the expression of the 2nd column to the following expression.

=SWITCH
    (
        Fields!myNumber.Value < LOOKUP("Bad", Fields!comment.Value, Fields!high.Value, "dsGradeRange"), "Bad",
        Fields!myNumber.Value < LOOKUP("Good", Fields!comment.Value, Fields!high.Value, "dsGradeRange"), "Good",
        True, "Excellent"
    )

This gives the following results

enter image description here

As you can see we only need to compare to the high value of each case, the first match will return the correct comment.

Upvotes: 1

Strawberryshrub
Strawberryshrub

Reputation: 3399

Right click on your dataset and add a calculated field. Go to Field Properties > Fields > Add and add the following expression, which descripes your scenario:

=IIF(Fields!Number.Value < 50, "Bad", "Good")

Upvotes: 0

Related Questions