locked
Report Question - CRM Report ID vs ReportServer ReportID? RRS feed

  • Question

  • Hi, I have a general question on CRM Reports. I was trying to find the Reports LastUsed information by using the following Query.

    SELECT  Distinct    UserName, TimeStart, TimeEnd, Source, Status
    FROM         [ReportServer].[dbo].[ExecutionLog] ReportServerLog INNER JOIN
    [MYCRMDB].[dbo].FilteredReport CRMReports ON CRMReports.reportid = ReportServerLog.ReportID

    The query always returned 0 results.

    After doing some research, I found that the ReportID logged into the ExecutionLogStaorage table in ReportServer is different from the ReportID from the CRM Reports table. But I notice that, running the same report logs the same ID. 

    What the the mapping between CRM ReportID to the ReportServer ReportID? Can someone please advise.

    Thanks.

    Saturday, January 18, 2014 2:55 AM

Answers

  • http://technologyasadventure.blogspot.in/2014/01/crm-report-usage-statistics.html

    When trying to map the CRM Report table with Catalog table of the Report server, make sure that you are able to connect to both databases. Please keep in mind that both are different databases. Also these databases can be on different physical server in case of multi-server installations. Please check if you have a multi-server installation.


    Ajay Chawla

    • Marked as answer by CRMAdmirer Tuesday, January 21, 2014 3:14 PM
    Tuesday, January 21, 2014 8:16 AM
  • Hi Ajay, Thanks for information. It got me into the right track in resolving the issue. One think I noticed that, the ReportNameonSRS column is blank for user reports in CRM Report table.

    Instead of using the ReportNameonSRS as a mapping column to join the Catalog, I used the ReportID of Reports to map to the Name (with {} removed) column of the Catalog table. That provides a good match.

    SELECT  UPPER(REPLACE(REPLACE(cat.name, '{',''), '}','')), UserName, TimeStart, TimeEnd, Source, Status
    FROM [ExecutionLog] AS ex
    INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID
    WHERE REPLACE(REPLACE(cat.name, '{',''), '}','') IN 
    ('E6CC01BE-BFF0-DF11-A85A-A4BADB2677D7','7B632217-C0F0-DF11-A85A-A4BADB2677D7')    <- CRM ReportIDs hardcoded


    SELECT  UPPER(REPLACE(REPLACE(cat.name, '{',''), '}','')), UserName, TimeStart, TimeEnd, Source, Status
    FROM [ExecutionLog] AS ex
    INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID
    INNER JOIN Report AS crm ON crm.ReportID = REPLACE(REPLACE(cat.name, '{',''), '}','')   <- IF YOU HAVE A LINKSERVER exists between CRM and SSRS servers

    • Marked as answer by CRMAdmirer Tuesday, January 21, 2014 3:14 PM
    Tuesday, January 21, 2014 3:14 PM

All replies

  • Hi,

    Query to CRM database
    select ReportId, name, ReportNameOnSRS from ReportBase where Name like '%Account%'

    Query to Reporting Database
    select ItemID, Name, Description from Catalog where Description like '%Account%'

    When you observe the results of above queries, you will notice that ReportNameOnSRS can be used as mapping key with Name column of the Catalog table of ReportServer database.


    Please mark this as an answer if my reply was helpful.

    Ajay Chawla


    • Edited by Ajay Chawla Saturday, January 18, 2014 8:09 AM
    Saturday, January 18, 2014 8:09 AM
  • Thank you... :-)
    Saturday, January 18, 2014 3:06 PM
  • Sorry I should have checked this, I notice that some of the resultset have NULL value in the ReportNameonSRS. How do you map these records to SSRS?

    Basically all of the reports we have built are showing NULL in the ReportNameOnSRS column. Out-of-box CRM Reports e.g. USer Summary have values.

    Any suggestions?

    Monday, January 20, 2014 6:25 AM
  • http://technologyasadventure.blogspot.in/2014/01/crm-report-usage-statistics.html

    When trying to map the CRM Report table with Catalog table of the Report server, make sure that you are able to connect to both databases. Please keep in mind that both are different databases. Also these databases can be on different physical server in case of multi-server installations. Please check if you have a multi-server installation.


    Ajay Chawla

    • Marked as answer by CRMAdmirer Tuesday, January 21, 2014 3:14 PM
    Tuesday, January 21, 2014 8:16 AM
  • Hi Ajay, Thanks for information. It got me into the right track in resolving the issue. One think I noticed that, the ReportNameonSRS column is blank for user reports in CRM Report table.

    Instead of using the ReportNameonSRS as a mapping column to join the Catalog, I used the ReportID of Reports to map to the Name (with {} removed) column of the Catalog table. That provides a good match.

    SELECT  UPPER(REPLACE(REPLACE(cat.name, '{',''), '}','')), UserName, TimeStart, TimeEnd, Source, Status
    FROM [ExecutionLog] AS ex
    INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID
    WHERE REPLACE(REPLACE(cat.name, '{',''), '}','') IN 
    ('E6CC01BE-BFF0-DF11-A85A-A4BADB2677D7','7B632217-C0F0-DF11-A85A-A4BADB2677D7')    <- CRM ReportIDs hardcoded


    SELECT  UPPER(REPLACE(REPLACE(cat.name, '{',''), '}','')), UserName, TimeStart, TimeEnd, Source, Status
    FROM [ExecutionLog] AS ex
    INNER JOIN Catalog AS cat ON ex.ReportID = cat.ItemID
    INNER JOIN Report AS crm ON crm.ReportID = REPLACE(REPLACE(cat.name, '{',''), '}','')   <- IF YOU HAVE A LINKSERVER exists between CRM and SSRS servers

    • Marked as answer by CRMAdmirer Tuesday, January 21, 2014 3:14 PM
    Tuesday, January 21, 2014 3:14 PM