Set default value of parameter based on user

Question

Post reply

Set default value of parameter based on user

donbuz

SSC Eights!

Points: 961
More actions
September 27, 2011 at 5:35 am

#248473

Good Day Everyone,
I would like to know if it is possible to configure a Report so that one of its parameter could be set at run-time, upon opening, from an expression / variable (or anything else) based on the actual User displaying the Report.
Thank you for your time,
donbuz

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Yes it is.

In the reports's available variable you have user.userid or something like that.

Create a new dataset that fetches the default value from some table and use that value to filter for that user.

Please not that using the same technic you can save the default parameters for each user in a table and have them redisplay on <many other> reports.

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 2

Hi,

In SSRS, in the report parameter section, you can define the default value either by hardcoding the values or via T-SQL.

regards

Palash Gorai

donbuz SSC Eights! Points: 961 More actions · Answer 3

Thank you both for your inputs, this is much appreciated.

donbuz

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

You will find a parameters column in your executionLog2 view of your RSDB.

This will help you determine the parameter list. 🙂

Raunak J

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 5

If I understand correctly you want a parameter such as Area (divisions in company) to display depending on user running the report. So if finance user runs report it will display Finance under Area, whereas if Marketing user runs report it will display Marketing under Area.

I know that Microsoft Dynamics CRM works similar, you place users in different Business Units, only those users belonging to that particular Business Unit can view info / data related to that Business Unit. (It uses Windows Authentication, i.e. Active Directory group/s are added to SQL Server Security).

When the users selects Area parameter, only the (division) under Area related to his Business Units is displayed. If you are an administrator on CRM, all (divisions) under Area is displayed.

donbuz SSC Eights! Points: 961 More actions · Answer 6

Good Day Kevin_nikolai,

Ninja's_RGR'us provided with the actual right answer for the matter at stakes here. Using the built-in field User ID seems to the best way to achieve my objective (code in expression : User!UserID) .

MSDN says :

UserID (String) :

The ID of the user running the report. If you are using Windows Authentication, this value is the domain account of the current user. The value is determined by the Reporting Services security extension, which can use Windows Authentication or custom authentication.

Regards,

donbuz

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 7

You can add following to your SQL report Department dataset (to get department based on Userid):

SELECT DISTINCT Department

FROM mytable

WHERE User!Userid in (@Department)

---------------------------------------------------------------------------

Then simply go to SQL report @Department parameter, add Department to Label field,

make @Department parameter datatype = text.