Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
CRM 4.0 Report Failure using Explicit Pre-Filtering

Answered CRM 4.0 Report Failure using Explicit Pre-Filtering

  • Thursday, 2 August, 2012 12:18 PM
     
     
    Sign In to Vote
    0

    I reposted this and deleted the old one because I couldn't reply.  I think when I pasted some text it goofed some stuff up on it.

    CRM 4.0 On Premise Rollup 21.  SQL 2005

    I'm working on an report that will be used as a User Score Card.  We want to know how many different types of records each user has created in a date range.  So it will basically be like so:

    User          Accounts                Leads             Cases             Total

    User1         1                            1                    1                    3

    User2         1                            3                    2                    6

    I am building this in SSRS and using CRM Explicit Pre-Filtering on the CRM_FilteredSystemUser view.  The report works unless I try to filter the user (like Manager equals current user).  If I return all users it works fine.  There is also a Begin and End Date for the date range that is handled on the report side not the CRM default filter.  When it fails it says "An Error occured during report processing.  Query execution failed for dataset 'CRM'.

     

All Replies

  • Thursday, 2 August, 2012 12:46 PM
    Moderator
     
     
    Sign In to Vote
    1

    The error is most likely to be a problem with the SQL you generate. The Reporting Services log file may have more information - otherwise can you post your SQL ?

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk


     
  • Thursday, 2 August, 2012 8:26 PM
     
     
    Sign In to Vote
    0

    I found this in the log:

    w3wp!processing!1!8/1/2012-13:26:06:: e ERROR: An exception has occurred in data source 'CRM'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'CRM'. ---> System.Data.SqlClient.SqlException: Invalid column name 'createdbyn'.
    w3wp!processing!1!8/1/2012-13:26:06:: i INFO: Merge abort handler called for ID=-1. Aborting data sources ...
    w3wp!processing!1!8/1/2012-13:26:06:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
     Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'CRM'. ---> System.Data.SqlClient.SqlException: Invalid column name 'createdbyn'.

    I don't see anywhere that I am referencing createdbyn.  I'm wondering if the createdbyname is bring truncated for some reason.

    Here is my SQL

    DECLARE @SQL nVarchar(max)
    SET                @SQL = '
    select createdbyname, max(LeadCount) LeadCount, max(OppCount) OppCount, max(ContactCount) ContactCount, max(ApptCount) ApptCount, max(AccountCount) AccountCount, max(PhoneCount) PhoneCount
    FROM(
    SELECT COALESCE(fl.createdbyname, fo.createdbyname, fc.createdbyname, fa.createdbyname, fap.createdbyname, fp.createdbyname, fsu.createdbyname) AS createdbyname, LeadCount, OppCount, ContactCount, AccountCount, ApptCount, PhoneCount
    FROM
    (SELECT createdbyname, createdby, count(*) AS AccountCount FROM FilteredAccount where (cast(CONVERT(varchar(10), createdon, 101)as datetime) >= (' + quotename(cast(@DateBegin as nvarchar(25)),'''') + '))
    AND (cast(CONVERT(varchar(10), createdon, 101)as datetime) <= (' + quotename(cast(@DateEnd as nvarchar(25)),'''') + '))
    AND (createdby IN (SELECT systemuserid FROM (' + @CRM_FilteredSystemUser + ') AS createdby)) GROUP BY createdby, createdbyname) as fa

    FULL JOIN 
    (SELECT createdbyname, createdby, count(*) AS OppCount FROM FilteredOpportunity where (cast(CONVERT(varchar(10), createdon, 101)as datetime) >= (' + quotename(cast(@DateBegin as nvarchar(25)),'''') + '))
    AND (cast(CONVERT(varchar(10), createdon, 101)as datetime) <= (' + quotename(cast(@DateEnd as nvarchar(25)),'''') + '))
    AND createdby IN (SELECT systemuserid FROM (' + @CRM_FilteredSystemUser + ') AS createdby) GROUP BY createdby, createdbyname) as fo
    ON fa.createdby=fo.createdby

    FULL JOIN
    (SELECT createdbyname, createdby, count(*) AS ContactCount FROM FilteredContact where (cast(CONVERT(varchar(10), createdon, 101)as datetime) >= (' + quotename(cast(@DateBegin as nvarchar(25)),'''') + '))
    AND (cast(CONVERT(varchar(10), createdon, 101)as datetime) <= (' + quotename(cast(@DateEnd as nvarchar(25)),'''') + '))
    AND createdby IN (SELECT systemuserid FROM (' + @CRM_FilteredSystemUser + ') AS createdby) GROUP BY createdby, createdbyname) as fc
    ON fo.createdby=fc.createdby

    FULL JOIN
    (SELECT createdbyname, createdby, count(*) AS LeadCount  FROM FilteredLead where (cast(CONVERT(varchar(10), createdon, 101)as datetime) >= (' + quotename(cast(@DateBegin as nvarchar(25)),'''') + '))
    AND (cast(CONVERT(varchar(10), createdon, 101)as datetime) <= (' + quotename(cast(@DateEnd as nvarchar(25)),'''') + '))
    AND createdby IN (SELECT systemuserid FROM (' +@CRM_FilteredSystemUser + ') AS createdby) GROUP BY createdby, createdbyname) as fl
    ON fc.createdby=fl.createdby

    FULL JOIN
    (SELECT createdbyname, createdby, count(*) AS ApptCount FROM FilteredAppointment where (cast(CONVERT(varchar(10), createdon, 101)as datetime) >= (' + quotename(cast(@DateBegin as nvarchar(25)),'''') + '))
    AND (cast(CONVERT(varchar(10), createdon, 101)as datetime) <= (' + quotename(cast(@DateEnd as nvarchar(25)),'''') + '))
    AND createdby IN (SELECT systemuserid FROM (' + @CRM_FilteredSystemUser + ') AS createdby) GROUP BY createdby, createdbyname) as fap
    ON fl.createdby=fap.createdby

    FULL JOIN
    (SELECT createdbyname, createdby, count(*) AS PhoneCount FROM FilteredPhoneCall where (cast(CONVERT(varchar(10), createdon, 101)as datetime) >= (' + quotename(cast(@DateBegin as nvarchar(25)),'''') + '))
    AND (cast(CONVERT(varchar(10), createdon, 101)as datetime) <= (' + quotename(cast(@DateEnd as nvarchar(25)),'''') + '))
    AND createdby IN (SELECT systemuserid FROM (' + @CRM_FilteredSystemUser + ') AS createdby) GROUP BY createdby, createdbyname) as fp
    ON fap.createdby=fp.createdby

    FULL JOIN
    (SELECT fullname AS createdbyname, systemuserid AS createdby, count(*) AS UserCount FROM ('
                              + @CRM_FilteredSystemUser + ') AS SU GROUP BY systemuserid, fullname) as fsu
    ON fap.createdby=fsu.createdby
    ) as OuterTable
    GROUP BY createdbyname
    ORDER BY 1

    '
                              EXECUTE (@SQL)

     
  • Tuesday, 7 August, 2012 1:22 PM
     
     
    Sign In to Vote
    0
    Any ideas anyone?
     
  • Monday, 10 September, 2012 5:46 PM
     
     Answered
    Sign In to Vote
    0
    Ended up putting this query in a stored procedure and it worked fine.
    • Marked As Answer by NatHei Monday, 10 September, 2012 5:47 PM
    •