Users only able to see their 'own' data (HELP ME!!) 2

[LoveToSpod]

Hi there,

Can someone please put me out of my misery!!!!!!!!! - I am using Reporting Services to deliver business reports over our Intranet to the world. However, I simply need to restrict the report information to the user's country...:

E.G. If the user is from Australia, he only sees the Australia data.

The information in the database relates to approx 100 different countries, and the information must be restricted so that one country doesn't see another country's data.

HOW DO I do this???

Please help!!!

LoveToSpod

 

[xlbo]

Really depends how you run the reports but a paprameter on the country name would seem to be your best option...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[LoveToSpod]

Hi Geoff,

Yep, agreed - parameter on the CountryName is the way to go. (BTW, I have a fair grasp on using parameters within reports.)

Only, I am stuck on how I carry the users' country name to the query parameter invisibly, preventing the user being able to access another country's reports/data??

LoveToSpod

 

[xlbo]

Do you use Active Directory or anything like that ?? If so, you could query that in your database to obtain the country of the employee...

Sounds like it is less of an RS issue and more of a data linking issue....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[LoveToSpod]

Hi again Geoff,

I wouldn't be sure how to use the active directory (to be perferctly honest).

If there is no straight forward solution, howzabout if I create a data table of users and their countries and we could reference that. If we use this method, how do I refernce the country name with the query behind the report?

Cheers,

LoveToSpod

 

[xlbo]

The 2nd option would be easiest as a one off but going forwards, who will maintain this list ??

If you do get access to a list of countries, I would build that into the query that returns your results

you would then need to create a .NET function to return the country based on the user's ID and enter that into the parameter e.g.

Function UserCountry()
'Code to return country based on userID
End Function

In the parameters section, set one to the "Country" filed returned in your query and in the parameters section, just use

=UserCountry

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[LoveToSpod]

Geoff,

This looks great, please hang on in there with me so I can nail this SOB!!

To answer your Q, I have a policy in place for the user list to be updated as and when users come/go/change. I can create and install the list of countries by user, to the sql server. Lets suggest the table is called [UserCountry] and the field names are [User] and [CountryName].

Can you save my life by answering the following:

1. Where do I put the 'UserCountry()' function?
2. What is the exact code I need to return country based on userID
3. You talk about the 'Parameters Section' Do you mean the Report Parameters dialogue box within VS, or the Report Manager Parameters section - using the default value field.

We're almost there, I appreciate your help ;-)

LoveToSpod

 

[xlbo]

Ok - slight change of plan as it is easier to return just the userID in the function and let a query do the rest

Function goes in the CODE window of the report
(right click OUTSIDE of the report layout area and choose "report properties" then the "code" tab) - lets assume the function is called "UserCountry"

To get the userId of the person will depend on your operating system to some extent so I cannot give you exact dode - it should be easy enough for you to do a search on "return userID .NET" or similar - there will be loads of hits in Google for that.

Once you have the userID, you can create a RECORDSET object which will query the users/countries table and return the country for that user.

You can call this function via an expression in a cell or in a parameter (again, right click outside of the report layout area but choose "Parameters") by using:

=code.UserCountry()

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[LoveToSpod]

Hi Geoff,

Someone provided me with this code:
========================
create Function dbo.UserCountry()
returns varchar
as
return (select CountryName from UserCountry where UserID = suser_sid() )
End Function
========================

I have followed your suggestion as per above and created a new datatable [UserCountry] and I added my username and country into the table to test the solution.

In the query behind the report, I linked the Main data table with the new table [UserCountry] with the column [CountryName]. This particular field is found in both tables and shares the same datatype (NVarchar50). The other field in [UserCountry] is [UserID].

In the generic query designer I placed [UserCountry].[CountryName], with the criteria set to @UserCountry. This creates an entry in the Report Parameters called 'UserCountry', and in this parameter I placed the call: "=code.UserCountry()" in the Value field. I believe this follows your suggestion to the letter.

When I go to preview the report I get the following build error messages:

There is an error on line 0 of custom code: [BC30188] Declaration expected.
The value expression for the report parameter ‘UserCountry’ contains an error: [BC30456] 'UserCountry' is not a member of 'ReportExprHostImpl.CustomCodeProxy'.

Can you help me understand what is wrong, and what I need to do to fix it!!

Many thanks.

LoveToSpod

 

[xlbo]

might be having a fit because the function name is the same as the field name but other than that, I agree, you have followed the suggestion well.

I have implemented this (well, not user names but using a .NET function to return a result to a parameter) in 2 or 3 reports so far and it seems to work well

Try changing the name of the function slightly and let me know if that helps. Other than that, have you tested the function to see if it does return the user name and whethr it is compatible with the data in your table ?

Must admit I am pretty new to RS myself (using it for 3 months max - 2 sets of reports) so forgive me if I have missed something obvious

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[LoveToSpod]

Geoff,

Here it is, the NIRVANA answer!!! - You can use the "=User.UserID" function to return the User ID in Reporting Services.

1 - I created the [UserCountry] table. This table has two fields [UserID] and [CountryName]. I linked the table with the rest of my data in the query using the [CountryName] fields. (Use the country table as the primary table, for the 'one-to-many' speed reason).
2 - I added [UserCountry].[UserID] into the query, and then queried the field: [UserCountry].[UserID] by placing @CurrentUserID in the criteria. This creates a report parameter "CurrentUserID"
3 - In the report parameters, I used the non-queried value with the function "=User.UserID". I removed the prompt and also made the Default value to "=User.UserID".

Now when I run the reports, data only shows for the users' country!!! Woohooaaahhhh.

PLUS Too I can also run the same process against the Report Parameters so users only have their countries available for selection. RESULT!!!

Thanks for your help. hopefully others will use this thread to solve their problems too!!!

Cheers,

LoveToSpod

 

[xlbo]

Excellent - and a star for you for the User.UserID info

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question