Permission of Parameter Dropdown List

Question

 I have a report that shows employee performance. When an employee  opens the report by choosing his/her name from the dropdown parameter list then it show that emloyees performance. This parameter dropdown lists the all employee names. He/she chooses own name by choosing from that list. However, most of the employees must see only their names in that dropdown except the managers. When an normal employee opens the report then he/she should be able to see only his name in that drowdown. However, the department heads must see the all employee list in that dropdown. How can I make a solution for this?

Answer 1

Hi KDrozgr ,

  I guess you should be having a table storing all the employee details etc.

  From that probably you can try something like this in your dataset

  SELECT UserName
  UNION
  SELECT Ename
     from Employeetable
  Where Supervisor = UserName

  Hope that helps. If not please post your table structure for further assistance.

 

---

Best Regards Sorna

Answer 2

Use the built in field "User!UserID" to check the user.

Say I have 3 department heads with  UserIDs,  ABC, DEF, XYZ, you can then modify the query of the dataset that you are using to populate the drop down parameter to be something like this:

="select username from employeetable" & IIF(Instr("ABC,DEF,XYZ",User!UserID)>0,""," where userid='" & User!UserID & "'")

This checks if the user id of the person logged in is found in the string "ABC, DEF, XYZ", note that the User!UserID will return a format Domain\UserId, so you will have to include the Domain in the search format. So if it's found, there's not where clause, it selects all the username, if the user is not in the list of department heads, the where clause is added returning only one record which is the user's name. 

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 3

For example, one employee needs to see all list then how can I manage this? However, he is still not a manager. Sometimes, some employees need to see all dropdown list then I must give them full right. That's why, I don't know how to solve this?  I can see the all employees , but another employees must see only their name in that list. In addition, there is no a attribute which indicates the emloyee's level like manager,officer,etc..

Answer 4

Use the built in field "User!UserID" to check the user.

Say I have 3 department heads with  UserIDs,  ABC, DEF, XYZ, you can then modify the query of the dataset that you are using to populate the drop down parameter to be something like this:

="select username from employeetable" & IIF(Instr("ABC,DEF,XYZ",User!UserID)>0,""," where userid='" & User!UserID & "'")

This checks if the user id of the person logged in is found in the string "ABC, DEF, XYZ", note that the User!UserID will return a format Domain\UserId, so you will have to include the Domain in the search format. So if it's found, there's not where clause, it selects all the username, if the user is not in the list of department heads, the where clause is added returning only one record which is the user's name. 

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

I have an attribute which shows the employee name shorcuts. For example, My name is Kadir Ozgur. Then my shortcut is "OZK". This attribute's name is NT_id. 

Your answer is exactly my solution :) However, I tried to apply your query into my dataset but it gives me syntax and other errors. Can you adapt that query according to my shorcut(OZK) and Nt_id (attribute) please.

Thank you in advance

Answer 5

Hi Kadir,

The key here is using the built in field - User!UserID. This is what the report uses to  determine who is running the report. If you are using Windows Authentication, this will is the domain account of the user.

Create a simple report with a textbox and enter the expression   =User!UserID, run the report and that is the one you need to use. If your shortcut name OZK is part of that UserID, and this logic applies to the rest of the employees then you are in luck. We can use your NT_id attribute here. If not, you have two options:

1. Hardcode the domain\userid in your select statement expression separated by commas like "domain\USERID1,domain\USERID2..." or create a small table with two columns, USERID (domain\userid) and NT_ID (shortcut). You only add in this table the person you want full view of the employee list. 

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 6

Hi Kadir,

The key here is using the built in field - User!UserID. This is what the report uses to  determine who is running the report. If you are using Windows Authentication, this will is the domain account of the user.

Create a simple report with a textbox and enter the expression   =User!UserID, run the report and that is the one you need to use. If your shortcut name OZK is part of that UserID, and this logic applies to the rest of the employees then you are in luck. We can use your NT_id attribute here. If not, you have two options:

1. Hardcode the domain\userid in your select statement expression separated by commas like "domain\USERID1,domain\USERID2..." or create a small table with two columns, USERID (domain\userid) and NT_ID (shortcut). You only add in this table the person you want full view of the employee list. 

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Yes I got the User!UserID. It determines who is running it. But, my problem is about the query which you have written me above. I got the logic of it. That is what I am trying to find out. But when I try to adjust it into the my dataset query then I got several syntax error. I want that can u help me to adjust it into my query? 

Thank you in advance..

Answer 7

can you post the query you are using? 

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 8

can you post the query you are using? 

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

SELECT DISTINCT mitarbeiterName, mitarbeiter, NT_id
FROM            vw_LeistungsStand 
&IIF(Instr("MZSGCH\ozk,MZSGCH\ozk-admin",User!UserID)>0,"")
where NT_id'"&User!UserID &"'")
ORDER BY mitarbeiterName

Answer 9

Kadir,

I was expecting the query that you are using for your pull down parameter. I think this is for your dataset.

Nevertheless, it should be something like this

="SELECT mitarbeiterName FROM vw_LeistungsStand " & IIF(Instr("MZSGCH\ozk,MZSGCH\ozk-admin",User!UserID)>0,""," where nt_id='" & Left(Split(User!UserID,"\")(1),3) & "' ORDER BY mitarbeiterName")

Create a dataset and use the query above exactly as it is and use this as the available values for your parameter. If nt_id is all caps (OKR), then change the part with the split to  UCASE(Left(Split(User!UserID,"\")(1),3))

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 10

Is it an expression or query? 

Answer 11

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 12

As I see your "Query Designer" button has also been disabled. It executes as an expression. When I delete the quotes  before the select then it executes as an query. But, It gives still syntax errors which I can not correct..

Answer 13

just paste the text there and hit ok. you can't use query designer if it's an expression. Dont remove the quotes. 

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 14

It doesn't make sense :( There are still syntax errors and cant open the report.. Can we make it with the filtering?? If these Users have loginned then full list otherwise just own name? How can I adapt into the filtering? 

Edit: I cannot post any image. It says you haven't been verified yet. That's why I cannot show you the error..

Answer 15

Hi Kdrozgr,

As per my understanding, you should have a table in the database to store the information about the employees, so you can get the informoation for the database base on the User.ID build-in function, and then judge the employee is a normal employee or a manager. And the query that Krootz provided is a expression, you can click the "fx" butoon and then paste the query.

Hope this helps.

Regards,

---

Charlie Liao
TechNet Community Support