Solved

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

Posted on 2007-04-09
12
2,600 Views
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.
etc...
etc...
etc...

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.
0
Comment
Question by:robotcat
  • 6
  • 4
12 Comments
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
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.
0
 

Author Comment

by:robotcat
Comment Utility
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?
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
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.
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
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 http://msdn2.microsoft.com/en-us/library/ms365305.aspx.
0
 

Author Comment

by:robotcat
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:robotcat
Comment Utility
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!
0
 

Author Comment

by:robotcat
Comment Utility
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.
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
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.
0
 

Author Comment

by:robotcat
Comment Utility
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.
0
 

Author Comment

by:robotcat
Comment Utility
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:
      http://msdn2.microsoft.com/en-us/library/aa902691(SQL.80).aspx
      
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!
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now