none
Using reporting services for ad hoc reporting on a multi-client database

    Question

  • I am researching for an ad hoc reporting solution.  Current reporting with Crystal Reports using stored procedures for getting the dataset.  Database environment is SQL Server.  Database structure is multi-client database identified by a unique key per record.

    The Crystal Reports are used for standard reporting but the capability for clients to create their own report would be ideal.  This would let them work on their schedule instead of providing an RFP, getting a quote, and signing off on proceeding with development.

    I was unaware of SQLServer Reporting Service until recently.  It seems like a good fit to our current environment.  Since the Crystal Report uses a stored procedure, it takes a report id in that is unique to the user.  This will be used to determine and limit what records are returned to the user.  

    There are three parts for security.  1) The client identifier (limiting the records for the client level), 2) Topmost place in tree A, 3) Topmost place in tree B.  These three pieces of security are contained in a user table

    On the web services side, the layering limits what is returned to the user. In the stored procedures, the code limits what is returned to the user.  

    Can SQLServer Reporting Services also limit what is returned to the user by utilizing these three security points as mentioned?

    Saturday, January 25, 2014 3:33 PM

Answers

  • Hi,

    If I understand you correctly, the answer is YES and it is very simple to implement. I have done this. First off there is a 'system' based parameter  "User!UserID" which you will be using.  The trick is how to flag your records as to whom may access what and not knowing exactly how your system functions I am at a loss.

    Here is how I manage it. First off I carved my users into groups. 'See Every thing' , 'See most' and 'Just Right' (as examples). Each employee is allocated to a GROUP table and each table to a number such as 1 , 2 or 3.

    In short!!! Your security table

    "There are three parts for security.  1) The client identifier (limiting the records for the client level), 2) Topmost place in tree A, 3) Topmost place in tree B.  These three pieces of security are contained in a user table. I work off a similar model and here is how I achieve the same as you do."

    TABLE 1

    User ID                  Group #

    ATRION\ssimon       2

    ATRION\ssimon       3

    ATRION\ssimon       1

    ATRION\bsmith       2

    ATRION\bsmith       3

    1) Your user enters the report from Reporting Services and his user ID is automatically known by reporting services.

    2) You create parameter say @User which passes  "User!UserID" on with your other filter parameters to the Stored Procedure which will extract the data.

    3) The trick is to extract the data VIA AN INNER JOIN  and here is how it happens

    select all the data from my data sensitive table st

    inner join to my security table sc

    on sc.userID = st.userID

    and security code in (1, 2,3)

    The result set should be similar to the data shown below AND IT IS THIS WHICH IS passed back to the Reporting Services matrix.

    Username from table 1      Code from table 1                         Production Record Grouping Code       Production $   Production Record Date Due

    ATRION\ssimon                  2                                                2                                                      USD12,000      12/3/2013  

    ATRION\ssimon                  3                                                3                                                      USD14,000      12/5/2013  

    ATRION\ssimon                  1                                                1                                                      USD16,000      12/3/2013  

                                                                     

    In short

    1)        you are using an inner join to do your filtering which is more efficient and effective. Remember that a 'Where useriD in ('ssimon', 'bsmith') etc will only allow you to user 1000 'names' . This is a limit in most RDMS. Using the inner join by-passes this challenge.

    I hope that this helps. From what I can gather, you have the necessary infrastructure there already( in the form of your security table).     

    Should you wish to discuss this further, please feel free to contact me.

    njm870t@hotmail.com

    sincerest regards

    Steve Simon SQL Server MVP

     

     



    Sunday, January 26, 2014 12:00 PM

All replies

  • You may contact Crystal Migration Services (support@crystalmigration.com) for ssrs ad hoc web reporting.

    It provides the easiest way to create ssrs reports on the fly, and you may request a WebEx live demo to see what it can do. Almost anyone can create a well formatted SSRS reports from anywhere including IPAD, and your smart phone.

    Sunday, January 26, 2014 5:55 AM
  • Hi,

    If I understand you correctly, the answer is YES and it is very simple to implement. I have done this. First off there is a 'system' based parameter  "User!UserID" which you will be using.  The trick is how to flag your records as to whom may access what and not knowing exactly how your system functions I am at a loss.

    Here is how I manage it. First off I carved my users into groups. 'See Every thing' , 'See most' and 'Just Right' (as examples). Each employee is allocated to a GROUP table and each table to a number such as 1 , 2 or 3.

    In short!!! Your security table

    "There are three parts for security.  1) The client identifier (limiting the records for the client level), 2) Topmost place in tree A, 3) Topmost place in tree B.  These three pieces of security are contained in a user table. I work off a similar model and here is how I achieve the same as you do."

    TABLE 1

    User ID                  Group #

    ATRION\ssimon       2

    ATRION\ssimon       3

    ATRION\ssimon       1

    ATRION\bsmith       2

    ATRION\bsmith       3

    1) Your user enters the report from Reporting Services and his user ID is automatically known by reporting services.

    2) You create parameter say @User which passes  "User!UserID" on with your other filter parameters to the Stored Procedure which will extract the data.

    3) The trick is to extract the data VIA AN INNER JOIN  and here is how it happens

    select all the data from my data sensitive table st

    inner join to my security table sc

    on sc.userID = st.userID

    and security code in (1, 2,3)

    The result set should be similar to the data shown below AND IT IS THIS WHICH IS passed back to the Reporting Services matrix.

    Username from table 1      Code from table 1                         Production Record Grouping Code       Production $   Production Record Date Due

    ATRION\ssimon                  2                                                2                                                      USD12,000      12/3/2013  

    ATRION\ssimon                  3                                                3                                                      USD14,000      12/5/2013  

    ATRION\ssimon                  1                                                1                                                      USD16,000      12/3/2013  

                                                                     

    In short

    1)        you are using an inner join to do your filtering which is more efficient and effective. Remember that a 'Where useriD in ('ssimon', 'bsmith') etc will only allow you to user 1000 'names' . This is a limit in most RDMS. Using the inner join by-passes this challenge.

    I hope that this helps. From what I can gather, you have the necessary infrastructure there already( in the form of your security table).     

    Should you wish to discuss this further, please feel free to contact me.

    njm870t@hotmail.com

    sincerest regards

    Steve Simon SQL Server MVP

     

     



    Sunday, January 26, 2014 12:00 PM
  • Thank you for the responses.  I will check it out more once I am back in the office and post any questions that may come up.

    Cheers,

    Jim

    Sunday, January 26, 2014 1:42 PM
  • Now you can create SSRS reports from your mobile device such as IPAD and Android smartphone.

    Here is the site.

    http://adhocssrs.crystalmigration.com

    User: guest

    Password: password

    Here is a report that can be easily created.

    http://adhocssrs.crystalmigration.com//ShowReport.aspx?id=McXz8XASkPNDpJ2NHheCQSjXHtDYMGZce3GMeSqKWEaeBVA6XrUiwQ%3d%3d

    Sunday, February 16, 2014 6:37 PM