Math operators parameters

Question

I wanted to know if we can use math operators in the parameter of the report.

I have a parameter called "EMP_Attendance".

I want to have the parameter to display and Run the report with values

Parameter:

EMP_Attendance

           = 100.00%
           <100.00% and >=90.00%
           <90.00% and >=80.00%
           <80.00%

the Report should run according to this value

Could you eleborate a bit more? Your question is sorta hard to understand. — Mark W, Apr 24 '13 at 21:13
I want to show the list of employees in the report depending on their attendance % for the year. The attendance is calculated from the query itself. So i will have to display for example: <100.00% and >=90.00% : It has to show me all the employees having less than 100.% attendance but greater than 90.00% — snp.it, Apr 24 '13 at 21:21
Do you have a parameter the user is to select, one of the three above or is your report needed go be grouped by a field? — glh, Apr 25 '13 at 02:09

score 1 · Answer 1 · answered Apr 25 '13 at 02:19

1

This is not natively possible but there is a slight work around.

Create a visible parameter with your four options above.
Create two internal parameters that are populated by two dummy sqls, one lower and one upper.
Make the queries produce the appropriate upper and lower limits based on the users selection, e.g. If 100% is selected lower = 100 and upper = 101.
Filter the dataset by these two limits.

answered Apr 25 '13 at 02:19

glh

4,900
3
23
40

Can you please elaborate...and how is it gonna effect the main query – snp.it Apr 25 '13 at 13:56
It will filter the main query to what the user selects for your original parameter. – glh Apr 25 '13 at 21:31
The other option is to put this filter in the query itself similarly to Stevens answer – glh Apr 25 '13 at 22:25

score 0 · Accepted Answer · answered Apr 25 '13 at 16:12

First you would add 4 available values to your parameter with a label of "100%" etc. and values of 1,2,3,4.

Then you have 2 options to filter.

You can add something like this to your query:

SELECT case when EMP_Attendance = 100 then 1 
when EMP_Attendance <100 and EMP_Attendance >=90.00% then 2
when EMP_Attendance <90 and EMP_Attendance >=80 then 3
else 4 end as AttendanceGroup
WHERE AttendanceGroup = @AttendanceParameter

The other option is to keep the query as is so that it returns all the data to the report. Right-click on the table and go to its properties. Go to Filters. Add filters here to compare your parameter with the EMP_Attendance value.

Math operators parameters

2 Answers2