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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
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.
robotcatAuthor Commented:
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?
Megan BrooksSQL Server ConsultantCommented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Megan BrooksSQL Server ConsultantCommented:
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
robotcatAuthor Commented:
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.
robotcatAuthor Commented:
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!
robotcatAuthor Commented:
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.
Megan BrooksSQL Server ConsultantCommented:
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.
robotcatAuthor Commented:
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.
robotcatAuthor Commented:
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!
PAQed with points refunded (500)

EE Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.