SSRS - Scaling reporting to hundreds of locations RRS feed

  • Question

  • I was curious if anyone had any experience with creating SSRS reports for hundreds of different locations for a business at a large scale. 

    For example, the client that I'm working for has over 600 store locations. I've created standardized SSRS dashboards that intake a series of parameters, but store number being the most important one so that stores can only see what they need to see. 

    How I've managed to do this without the store having access to location is by hiding the location parameter in SSRS, and dynamically passing them in. However, this is where it's a little tricky. I've created an site, extracted the locations IP address in order to get the store number, and then I pass that store number variable into a SSRS Report Viewer object. I've also considered just passing a store number the URL, but this poses risk for a manager to be smart enough to swap location if they knew how SSRS URLs work. I really want to ditch this method because I don't want to maintain an site that is simply a shell for an SSRS report. 

    Does anyone know of a better way to do something like this at a large scale? What have your experiences been like? Store locations currently all have generic logins at their locations. 

    For higher management positions reporting, it has been relatively easy for me because they are all currently in their active directory system and I can just leverage SSRS' built in user ID. 

    Hope this made sense! 

    matthew nguyen

    Tuesday, June 5, 2018 4:53 PM

All replies

  • Hi Matthew,

    How are the reports being accessed from store location? Is it something that you have exposed the SSRS report over the internet or are they are still part of the intranet?

    When you say generic logins at their locations means all locations have the same login or different?

    One thing i can think of is that we can have specific logins for individual stores and then create a mapping table in the backed between store and login. In this way we can by default filter the reports to the store based on the login rather than going via the location details.

    If the login is also part of the domain then you could use the xp_LoginInfo stored proc to identify the user and by default populate the storeId parameter in the reports. But you still need to have a mapping table in the backend to identify which login belongs to which store. This stored proc returns the used ID who has logged in and you can make this parameter hidden in SSRS report.



    Please vote as helpful or mark as answer, if it helps

    Tuesday, June 5, 2018 7:51 PM