January 20, 2012 at 10:08 am
I have a website with that is currently running a large custom reports section. Some clients have been asking to have the ability to write their own reports. I was told the plan is to use Sql Server Reporting Services to accomplish this and I have to figure out how to accomplish this. The problem is I don't think it can provide me with what I need and I'm looking for some guidance.
The reason I don't think that SSRS with work for me is that the database holds data for all my clients. Within the current reporting we have everything filtered by what client(s) you have access to and controlled by the font & back end based on the report filters you've selected. I don't know if and how this can be accomplished with SSRS without getting crazy with client specific stored procs and a lot of manually configuration on permission.
Any thoughts are welcome.
January 20, 2012 at 10:55 am
meaneyj (1/20/2012)
I have a website with that is currently running a large custom reports section. Some clients have been asking to have the ability to write their own reports. I was told the plan is to use Sql Server Reporting Services to accomplish this and I have to figure out how to accomplish this. The problem is I don't think it can provide me with what I need and I'm looking for some guidance.The reason I don't think that SSRS with work for me is that the database holds data for all my clients. Within the current reporting we have everything filtered by what client(s) you have access to and controlled by the font & back end based on the report filters you've selected. I don't know if and how this can be accomplished with SSRS without getting crazy with client specific stored procs and a lot of manually configuration on permission.
Any thoughts are welcome.
I think SSRS can definitely be the solution. There will be some work involved though, depending on how things are currently done.
You will be able to pass the logged in user's credentials from SSRS to your stored procedures, and use some kind of a mapping table to filter records. So the only additional requirement in your stored procs would be the join to the mapping table, if something like that isn't already being done.
If you are planning to build cubes as the source of your report data, it will be possible to create roles and limit the data visibility based on those roles.
How is the data currently being filtered by your custom application? Hopefully the filtering isn't done in the application layer...
January 20, 2012 at 12:18 pm
Martin Schoombee (1/20/2012)
I think SSRS can definitely be the solution. There will be some work involved though, depending on how things are currently done.You will be able to pass the logged in user's credentials from SSRS to your stored procedures, and use some kind of a mapping table to filter records. So the only additional requirement in your stored procs would be the join to the mapping table, if something like that isn't already being done.
If you are planning to build cubes as the source of your report data, it will be possible to create roles and limit the data visibility based on those roles.
How is the data currently being filtered by your custom application? Hopefully the filtering isn't done in the application layer...
Thanks Martin,
OK I'm not ruling out SSRS just yet! I will start reading up on SSRS users and configuration.
We use a mapping table but since a user can run reports for 1 or many client codes at the same time this is mapped right before the current report/sp is run VIA the font end app. I'm thinking this may be a problem.
There is currently no plan for cubes.
January 20, 2012 at 12:35 pm
Maybe you could limit the clients which the users can select as a parameter, by means of some mapping table. The procs will then just use the received parameters...
Either way, there is some groundwork to be done before you will know for sure if SSRS is a viable solution.
Good luck.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply