February 2, 2012 at 11:38 am
I have the need for Adhoc Reporting. SQL2008 SR2 SSRS is the current anticipated solution. I have data for multiple clients within the same database. I am looking to avoid having to setup a whole separate set of logins within SSRS and maintaining things there. So, I have setup an ASP.NET 4.0 front end to run the reports sending the logged in user’s information so that the Stored Procedures will filter the appropriate data for the user to view. This part is working great.
I want to supply the users with a font end to offer templates, that I will create, for them to use with the connection in it or even better sample data using the same schema that will come from my Stored Procedures. Then allow them to edit the report VIA Report Builder 3.0. Then publish the report through my front end. At that time I would edit the connection to point to the proper place, store the report in SSRS and setup everything needed in my app to allow the user to run this new report.
Is this Possible? If so I need some assistance on getting started with things like:
1. Running the Report Builder 3.0 but being possibly disconnected from the SSRS
2. Setting Up my Templates with Sample Data
3. Editing the report when Published to change the Connection String/Stored Proc Called
4. Deploying the reports from my ASP.NET app to SSRS
If you know of a better approach I would love to hear about it!
Thank you for your help 😀
February 2, 2012 at 12:10 pm
Jon MM (2/2/2012)
If you know of a better approach I would love to hear about it!Thank you for your help 😀
I don't know of a better approach but I've thought of doing something similar, just never found the time to do the research or the work. If you could keep us updated with your findings as you attempt this, I would be very appreciative. Otherwise, I'll be over there lurking in the corner. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 2, 2012 at 12:48 pm
I think that some of your requirements can be met through SSRS Report Parts.
I haven't played around with that yet to be in a position to tell you everything, but as far as I know Report Parts in 2208R2 allows you to create a report component (chart, tablix, etc.) and make it available to end users for use in their own reports (through Report Builder).
All connection string information etc. is maintained in the Report Part, so the end user doesn't have to know much about how you've created it...they can just use it. On top of that, I think it also allows end users to make a copy and possibly change these Report Parts.
I know this doesn't meet all your requirements exactly, and I do think you have to run SSRS integrated with Sharepoint to be able to publish Report Parts (I may be wrong)...but it could be a step in the right direction.
Hope this helps.
Martin.
February 3, 2012 at 8:38 am
Martin Schoombee (2/2/2012)
I think that some of your requirements can be met through SSRS Report Parts.
Martin, I will look some more at Report Parts. I wasn’t finding what I needed previously but I will revisit. Part of what I want with altering the connection is keeping the users away from the live data when working with their reports. I fear with the Stored Procedure parameters that they could too easily access another client’s data. This is the reason I am running the reports through my front end where I set the Parameters for the user.
With the approach I’ve posted I have now found the following:
1.Running the Report Builder 3.0 disconnected from SSRS. This is possible. You can install it on the local machine and not even deal with the click once install. This will allow my users to edit report files.
Now I just need to get through #2,3 & 4. I’m thinking of setting up my stored procs with defaults that return test data Or possibly having the test data in an external file also downloaded, if even possible. Then I would just need to change the connection when the file is uploaded.
Any thoughts, ideas, comments or ways to accomplish this?
Thanks
Jon
February 3, 2012 at 8:53 am
Jon MM (2/3/2012)
Martin Schoombee (2/2/2012)
I think that some of your requirements can be met through SSRS Report Parts.Martin, I will look some more at Report Parts. I wasn’t finding what I needed previously but I will revisit. Part of what I want with altering the connection is keeping the users away from the live data when working with their reports. I fear with the Stored Procedure parameters that they could too easily access another client’s data. This is the reason I am running the reports through my front end where I set the Parameters for the user.
With the approach I’ve posted I have now found the following:
1.Running the Report Builder 3.0 disconnected from SSRS. This is possible. You can install it on the local machine and not even deal with the click once install. This will allow my users to edit report files.
Now I just need to get through #2,3 & 4. I’m thinking of setting up my stored procs with defaults that return test data Or possibly having the test data in an external file also downloaded, if even possible. Then I would just need to change the connection when the file is uploaded.
Any thoughts, ideas, comments or ways to accomplish this?
Thanks
Jon
Good to see that you are making progress.
I may be missing something, but do you know that SSRS does have the identity of the logged-in user available as a system variable. You could possibly pass this as a parameter to your procs, and filter the data in that fashion to prevent users from seeing data that they shouldn't. All you would need is a simple mapping table in your backend database, mapping logins (users) to clients (or whatever it is that you need to filter on).
You would also be able to achieve something similar with an assembly, and creating a function in SSRS through custom code to build a client list for filtering. Personally, I prefer the mapping table for ease of use and visibility.
Is this something you considered doing instead of the .NET front-end...or am I missing the point?
February 3, 2012 at 9:03 am
Martin Schoombee (2/3/2012)
Is this something you considered doing instead of the .NET front-end...or am I missing the point?
You may be missing my point. My users are all coming in through an external website. I was looking at all web traffic using a single login to SSRS(VIA my .net front end) and controlling the access myself with my current mapping tables VIA stored procedures. This is currently how I have other reports and exports setup so that I can easily implement them for my many users while ensuring that their data is secure. If I'm missing an easier way to implement something like this please share 🙂
February 3, 2012 at 9:23 am
Jon MM (2/3/2012)
Martin Schoombee (2/3/2012)
Is this something you considered doing instead of the .NET front-end...or am I missing the point?
You may be missing my point. My users are all coming in through an external website. I was looking at all web traffic using a single login to SSRS(VIA my .net front end) and controlling the access myself with my current mapping tables VIA stored procedures. This is currently how I have other reports and exports setup so that I can easily implement them for my many users while ensuring that their data is secure. If I'm missing an easier way to implement something like this please share 🙂
That makes sense 😛
And yes, understandably you don't want to have to create hundreds of logins in SSRS. Assuming that the other external website is not in the public zone (and uses windows authentication), an alternative I have found to work pretty well is by using AD groups instead of creating individual logins. That way you still maintain user-level authentication, but maintaining it in AD.
Is that something you could use?
And please let me know if I am missing the point again...it's been a long week and Murphy has been on my case way too much 😛
February 3, 2012 at 9:36 am
That makes sense 😛
And yes, understandably you don't want to have to create hundreds of logins in SSRS. Assuming that the other external website is not in the public zone (and uses windows authentication), an alternative I have found to work pretty well is by using AD groups instead of creating individual logins. That way you still maintain user-level authentication, but maintaining it in AD.
Is that something you could use?
And please let me know if I am missing the point again...it's been a long week and Murphy has been on my case way too much 😛
Sorry not using windows authentication, so AD Groups would be out too.
Now I think you can follow better why I want to do such a complicated implementation of SSRS. Just need to come up with a working solution!
February 3, 2012 at 9:37 am
The template issue is also an interesting challenge. A possibility that comes to mind, is to limit the available connections in Report Builder to a Dev/QA instance with less data. (I think that's possible, but haven't tried it).
Or hard-code the user in the connection string to one that only sees data for a "test" client.
This approach will take some flexibility away though, because users will not be able to see all the data...so you will have to deploy their reports once they are done and change the connection string.
I haven't tried any of these methods in a production environment, so please forgive me if you have already tried these options or if they turn out to be impossible. Just trying to give you some theoretical options that may be viable in your situation 😉
February 3, 2012 at 9:40 am
Sorry not using windows authentication, so AD Groups would be out too.
Now I think you can follow better why I want to do such a complicated implementation of SSRS. Just need to come up with a working solution!
Yeah...that certainly makes things more difficult. I'll think it over a bit more, and let you know if I have any other suggestions.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply