locked
Account Specific SRS reports RRS feed

  • Question

  • We’re planning on setting up the SQL Report Manager to serve up an outside facing report that certain CRM accounts can run to pull their account specific data from CRM. Is there a function in SRS that allows us to  capture the AD user account that they are logged in as and translate that to an entity in CRM like an account. So when the user runs the report it looks at the AD account they are logged in as determines that it  = customer 1234 in CRM and shows information in the report for customer 1234 only.

    Donny Kensmoe - Dynamics GP Consultant - Wipfli
    Wednesday, January 4, 2012 10:17 PM

Answers

  • Hi you can create a DataSet with the following query

    SELECT SYSTEM_USER

    This will return the domain and username of the current user running the report.

    If you need to find the current user  id in CRM you can create another dataset with the following query

    select SystemUserId from dbo.SystemUser where domainName = SYSTEM_USER

    To get all accounts of the current user you can use

    select * from account where owninguser = (select SystemUserId from dbo.SystemUser where domainName = SYSTEM_USER)

     


    Regards,
    Damian Sinay
    • Marked as answer by DKensmoe Friday, January 6, 2012 8:29 PM
    Wednesday, January 4, 2012 11:03 PM

All replies

  • Hi you can create a DataSet with the following query

    SELECT SYSTEM_USER

    This will return the domain and username of the current user running the report.

    If you need to find the current user  id in CRM you can create another dataset with the following query

    select SystemUserId from dbo.SystemUser where domainName = SYSTEM_USER

    To get all accounts of the current user you can use

    select * from account where owninguser = (select SystemUserId from dbo.SystemUser where domainName = SYSTEM_USER)

     


    Regards,
    Damian Sinay
    • Marked as answer by DKensmoe Friday, January 6, 2012 8:29 PM
    Wednesday, January 4, 2012 11:03 PM
  • How are you setting this up for your customer.

    I think it is doable with csharp and it is not a big effort.

    1. Create a ssrs report with the hidden parameter (it can be name or guid).   
    2. create a customfield on account to store their ad account.
    3. Create a custom aspx page(solution). add the reference to CRM webservices
    4.   On form load-- windows.Identity give the logged in user

                get the customer where the custom field is same as loggedin user and pass the customername/guid to the ssrs report.

    I did something like that for a customer few years ago.

    I hope this helps. 

     

     

     


    If you find this post helpful then please "Vote as Helpful" and "Mark As Answer". Amreek Singh Senior CRM Consultant CDC Praxa Sydney, Australia http://mscrmshop.blogspot.com http://crm2011usersettings.codeplex.com
    • Proposed as answer by Amreek Singh Wednesday, January 4, 2012 11:03 PM
    Wednesday, January 4, 2012 11:03 PM