Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2007-04-09
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
LVL 16

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 16

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.
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

LVL 16

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 16

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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

670 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