June 11, 2015 at 1:07 am
Hi, I'm developing some reports in SSRS (SQL2012) and the customer wants different users to see different data depending on which department/role they belong to. For example a manager of dep1 should only see data regarding his employees or customers that visited dep1 and not the other 25-30 departments.
As of now we only have a couple of public reports (non-sensitive data) which all users can access and some admin-reports (all data) which only 2 report admins can access and if managers wants more data they have to contact the report admins and order extra reports from them.
They now want specific access-controlled manager reports an I'm struggling with the design of this and was wondering if anyone here has some input to how I can design/develop this?
I'm using SQL Server 2012 R2 and the SSRS reports are both from a relational database and a SSAS 2012 Cube and report access is now controlled via SSRS Browser role tied to 2 Windows AD-groups ReportAdmin (all data reports) and ReportUsers (non-sensitive data reports).
I first tried creating a "manager_belongs_to_departments" table lookup table and then used the SYSTEM_USER variable in T-SQL to try to catch the managers departments and this work for the relational data sources but won't work for the Cube-reports because there's no SYSTEM_USER variable in MDX (only in T-SQL) and I couldnt get the T-SQL report parameter to work in the MDX report.
I'm now thinking of maybe making the manager reports entirely in T-SQL, because only the report admins can or will twist and turn the report data in a BI-Analytic manner and managers only wants to see overall totals and those kind of numbers but these numbers comes from the Cube/Cube reports 🙁
Any input on this would be much appreciated
June 11, 2015 at 5:47 am
Ok thanks 🙂 I'll try the username() MDX function to see if it works in my solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply