SSRS Dynamic Ad-Hoc Reports/Report Builder/Report Models

Posted on 2007-04-09
Last Modified: 2009-01-25
SQL Server Reporting Services
Report Builder/Report Models/Ad-Hoc Reports
We are using Forms authentication- we have our own security system that we tied into SSRS that we use for our login.

In SQL Server Reporting Services, I need the Report Model to display client specific data based on the user that logged in.

For example, VendorA logs in, opens up Report Model X, and the report model displays VendorA's information.
VendorB logs in, opens up Report Model X, and the report model displays VendorB's information.
VendorC logs in, opens up Report Model X, and the report model displays VendorC's information.

The reason I need this, is because we have upwards of 200 vendors.  Each Vendor would like to use our Reporting Solution.

As it stands right now, I have to create a distinct DataSource View and Report Model per Vendor.

This does not scale well at all, maintenance is an absolute nightmare and nearly impossible when users want changes or we make upgrades to our system.  

Has anybody had any type of success in this realm?  This should be basic functionality/design for any system.
Question by:robotcat
  • 6
  • 4
LVL 14

Expert Comment

by:Megan Brooks
ID: 18887043
I haven't worked with custom security extensions, and I have done very little work with report models, but I see that there is a function, GETUSERID(), that can be referenced in expressions. If you are using a custom security extension I would expect this function (as well as the User!UserID global) to obtain its information from the IAuthenticationExtension.GetUserInfo method. It would then be up to the custom extension to provide the appropriate information about the current user.

Author Comment

ID: 18890841
Yea - we are aware of those functions- the question is, how do we use that to hook into the Report Model Functionality?  How do we use that userid function, to make a single report model use a specific dataset or "filter" the data set?
LVL 14

Expert Comment

by:Megan Brooks
ID: 18891781
You can define filters within the model. I will try to come up with an example later today. I had planned on using filters to limit access when we eventually deploy report models, but I've never actually tried it and this will be a good excuse to make sure filters can do what we need them to do.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 14

Expert Comment

by:Megan Brooks
ID: 18894709
Row level security can be added using security filters. I never got this feature to work today because I kept receiving "internal error" messages. There is a tutorial, however, that describes the process of adding row level security at

Author Comment

ID: 18901023
Interesting- I am wondering if row-level security is what I have been looking for.  I will definitely try it out though!

In the interim before I try it out, I came up with the following solution:
I am creating a report model for each entitty (group) that needs a separate model.  

We have about 200 entities and growing, and not wanting to have to manually code each model individually, I created an application that will automatically generate my distinct models using find and replace.  Each model also references the same set of database views.  I simply query the view and filter out the data using the data source view (dsv) (using named queries) for each entity.  My .dsv files are also automatically generated via my app.  This way, I can create a base report model and a base data source view, and then I can just generate multiple copies of my base objects and deploy.  All my objects remain in synch and making modifications to my large number of models is easy- just modify my base objects, re-generate and deploy.

I am giving each report model security so that only the entity logging in can see their specific model. (this has to be set manually, but that is ok because it only has to be configured once- if I re-deploy, the security settings are retained)

If anybody has any questions on my solution, let me know.  Let me know if this is a viable solution for your business as well.

This is as close as I can get to having a "dynamic" ad-hoc reporting solution.

Author Comment

ID: 18901057
Also, if the row-level security is what I was looking for, then I will make sure to give out the proper points!  Thanks big-time in advance!

Author Comment

ID: 18901310
Ok - after reviewing the row-level security article, it looks like that is not what I am looking for.  Row-level security basically appears to show/hide the available fields in the report models.  While this is a great feature, it is not the feature I need.  

I am looking for "data-level" security.  All my groups are allowed to see the fields that are available to them, but I need to restrict them from seeing other groups' information.  As such, I do not believe row-level security is a viable solution for my needs.
LVL 14

Expert Comment

by:Megan Brooks
ID: 18915208
The MS article describes two kinds of security, model item security and row-level security. Row-level security is supposed to make it possible to do things such as allowing users to only see their own data. The idea is to define one or more boolean security filter attributes and add them to the security filters collection. They then determine which rows are visible, according to the filter criteria. Since I haven't actually been able to make it work yet, I'm not certain whether it can do what you want to do or not, but I will continue to experiment with it as I have time.

Author Comment

ID: 18926216
Agreed- we both know what we are looking for, but as of yet have been unable to accomplish the goal.  I have moved on to another problem with SSRS (guhhh), but if I ever come up with a solution to this, I will post here as well.

Author Comment

ID: 18977688
I FINALLY got it to work exactly the way I want it to!  I am totally stoked- allow me to describe as best as possible how I was able to acheive the functionality I was looking for:

Step 1: Implement UFAIRS (Using Forms Authentication in Reporting Services) seen here:
Step 2: Our Forms Authentication has an identifier that lets us know what "group" the user logging in is as.  So for example, if I login as "user@group1", then I can parse out "group1" from the login name- this comes into play for my dynamic filtering.
Step 3: Create a Data Source to connect to the database.
Step 4: Create a Data Source View, with whatever tables/views you need.
Step 5: Make sure the data source view contains a table/view with a field that matches the "group" that you want to filter by (i.e., group1 from above)
Step 6: Create a report model.

Step 7: Create an entity (EntityA) in the report model and bind it to a table/view that contains the field we want to filter by.
Step 8: Add whatever source fields you want in (EntityA).
Step 9: Add an additional source field (FieldB) in (EntityA) that is bound to the field that contains our filter value = group1.
Step 10: I like to make (FieldB) hidden because this is only being used as my filter criteria.
Step 11: Add a new filter (FilterC) to (EntityA).
Step 12: Highlight (FilterC) and go to Properties->Filter.
Step 13: Click the edit (ellipses) button on the "Filter" Property to edit the [Expression].
Step 14: Drag in (FieldB) from the Fields List to use as a filter, into the center window.
Step 15: Right click on (FieldB) in the center window and select "Edit Formula"
Step 16: Make the formula say something like- "FieldB = SUBSTRING(GETUSERID(), FIND(GETUSERID(), "@")+1,LENGTH(GETUSERID()))"
      What I am doing here is parsing out the word "group1" from the username, and setting it equal to (FieldB).
      Let me know if this does not make sense.
Step 17: Hit ok to close the "Define Formula" window, then hit ok again to close the "Filter Data" window.
Step 18: I also like to make (FilterC) hidden so that users cannot see or use this field.
Step 19: Highlight (EntityA), and go to Properties->DefaultSecurityFilter.
Step 20: Click the ellipses to bring up the "Default Security Filter Attribute" window.
Step 21: Highlight (FilterC) and click ok to close the Default Security Filter Attribute window.
Step 22: Build the project, and deploy!  

After that, go give it a whirl and see if it worked! It totally works for me!  Let me know what you think!

Accepted Solution

Computer101 earned 0 total points
ID: 22527511
PAQed with points refunded (500)

EE Admin

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question