Solved

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

Posted on 2007-04-09
12
2,604 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 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.
0
 

Author Comment

by:robotcat
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?
0
 
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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Author Comment

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

Author Comment

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

Author Comment

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

Author Comment

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

Author Comment

by:robotcat
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:
      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
ID: 22527511
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL: launch actions one before the other 10 22
create an aggregate function 9 31
insert wont work in SQL 14 18
Need help constructing a conditional update query 16 37
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

813 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

13 Experts available now in Live!

Get 1:1 Help Now