Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Pre-filtering SSRS reports based on CRM login Id's RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all, 


    I have few SSRS reports built, i would like to integrate them with CRM 2011 Dashboards but i need the reports to pre filter based on the Sales person who is logged into CRM (We are using an Outlook based CRM)

    Is it possible to pass the login ID or some sort of parameter to filter the reports based on the user that's logged in.

    Thanks for your help. 

    Thursday, March 21, 2013 9:57 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi

    Below query will return you the current logged in user GUID. 

    SELECT  dbo.fn_FindUserGuid()

    Now when you want to pull the records from customer table in different database, definitely you will have some reference of the CRM user to identify which records belongs to which user. So user below query to get the records from custom table for current user using below query. 

    Assumed MODEL is your customer DB and guest is the table where you have a column as USERID to store CRM user GUID referance. 

    SELECT * FROM MODEL.guest 
WHERE USERID = CONTOSO_MSCRM.dbo.fn_FindUserGuid()
    Join the above result with the CRM filtered view and show the result in report, the result will be only for the current logged in user.

    Chandan - http://mscrm-chandan.blogspot.in/ I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful !!!

    • Marked as answer by Goztepe2002 Friday, March 22, 2013 6:28 PM
    Friday, March 22, 2013 5:56 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Just use filteredviews (filteredaccount, filteredcontact, filteredincident, filteredopportunity, etc.) in your report while retrieving the data in your query. Its CRM default feature that CRM will return you only those records where the current logged user has read permission based on your Query. so in your dashboard report you will be getting only those records which owned by or shated to the current logged in Sales person.

    BTW internally you can pass the current logged in user GUID in your report query. Below function will return you the current logged in user GUID (this function is already defined in CRM DB). 

    dbo.fn_FindUserGuid()

     

    Chandan - http://mscrm-chandan.blogspot.in/ I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful !!!

    • Proposed as answer by VidhiyaM Friday, March 22, 2013 6:19 AM
    Friday, March 22, 2013 5:13 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Chandan, thank you very much for your answer.

    How about when using non native CRM data. Let's say that my SSRS is pointed to a standalone Database with Some Sales information (Sales budgets per sales person, backlogs ect.) and i would like to retrieve those numbers based on the CRM user logged in.

    Can you somehow pass "dbo.fn_FindUserGuid()" to SSRS prefilter ?


    Friday, March 22, 2013 3:42 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi

    Below query will return you the current logged in user GUID. 

    SELECT  dbo.fn_FindUserGuid()

    Now when you want to pull the records from customer table in different database, definitely you will have some reference of the CRM user to identify which records belongs to which user. So user below query to get the records from custom table for current user using below query. 

    Assumed MODEL is your customer DB and guest is the table where you have a column as USERID to store CRM user GUID referance. 

    SELECT * FROM MODEL.guest 
WHERE USERID = CONTOSO_MSCRM.dbo.fn_FindUserGuid()
    Join the above result with the CRM filtered view and show the result in report, the result will be only for the current logged in user.

    Chandan - http://mscrm-chandan.blogspot.in/ I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful !!!

    • Marked as answer by Goztepe2002 Friday, March 22, 2013 6:28 PM
    Friday, March 22, 2013 5:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    great! thank you!

    Friday, March 22, 2013 6:28 PM