web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Report works in SSRS, ...
Microsoft Dynamics CRM (Archived)

Report works in SSRS, no data when imported into CRM

(0) ShareShare
ReportReport
Posted on by Community Member

Hello,

I'm relatively new to SSRS and have been relying on FetchXML to connect to my Dynamics 2015 On Prem IFD. I built a new report using SQL Server as my embedded Data Source and linked to my on premises SQL database (on the same box that I am running SSRS), using windows authentication. 

Pulling queries for datasets and the resulting report works fine in my Visual Studio 2012 setup, however, once I export the report and import into CRM, it comes up blank - it does not state that there is any authentication issues. 

It should be noted that the company who built our CRM do not allow SQL authentication and it is Windows authentication for accessing the SQL database. I've tried logging in as my domain admin account in CRM but the report is still blank.

This is killing me as I spent ages building this report and it works a charm in Visual Studio, any help would be greatly appreciated!

*This post is locked for comments

I have the same question (0)
  • a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    Hello,

    Can you please provide SQL Queries that you use in your report?

  • Community Member Profile Picture
    Community Member on at

    There are 4 datasets at play:

    First and most important (Membership):

    SELECT        membershipuniqueid, membername, membershipstartdate, membershipenddate, name, prevmembershipid, membershiptype,membershiptypename

    FROM            Filteredmembership

    WHERE        (membershipstartdate BETWEEN @startdate AND @enddate) AND (membershipstatusname = 'Current') AND (prevmembershipid IS NULL)

    and (membershiptypename in ('Adult Member', 'Adult Member DD', 'Member Plus', 'Member Plus DD', 'Student Member', 'Student Member DD', 'Member Plus Direct Debit') or (membershiptypename is null))

    Contact:

    select emailaddress1, fullname

    from filteredcontact

    Payment:

    select paymentmethodname, totalamount, memberidname, membershipidname

    from filteredpayment

    Renewal (same as First dataset, with an change on PrevmembershipID IS NOT NULL)

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    First question I have - where did you get those membership and payment entities from? They don't have prefix so they looks like standard but I don't see same entities in instances I have an access to. Can you please explain that?

    It seems that the reason why you don't see results is filtering by optionset names. Try to use following inside report:

    SELECT        membershipuniqueid, membername, membershipstartdate, membershipenddate, name, prevmembershipid, membershiptype,membershiptypename

    FROM            Filteredmembership

    WHERE        (membershipstartdate BETWEEN @startdate AND @enddate) AND (membershipstatus = 1) AND (prevmembershipid IS NULL)

    and (membershiptypename in (1, 2, 3, 4, 5, 6, 7) or (membershiptypename is null))

    in other words replace optionset labels that you use with correspond int values. In all queries.

  • Community Member Profile Picture
    Community Member on at

    I can indeed - we are using custom entities and the prefix for all is identfiying each field as created by the company we consulted to create our CRM , I didn't feel that was necessary here so removed. Many thanks for your help, I will amend the values now and report back

  • Community Member Profile Picture
    Community Member on at

    Hi Andrii, 

    Thanks for your help so far. I dug a little and the membership type is it's own entity and the field on the membership record is a lookup.  I've tried replacing the names that were under the column membershiptypename with the membershiptype value:

    SELECT        membershipuniqueid, membername, membershipstartdate, membershipenddate, name, prevmembershipid, membershiptype,membershiptypename

    FROM            Filteredmembership

    WHERE        (membershipstartdate BETWEEN @startdate AND @enddate) AND (membershipstatusname = 'Current') AND (prevmembershipid IS NULL)

    and (membershiptype in ('9A23C575-D8A4-E611-80CF-00155D019F2C', '9C23C575-D8A4-E611-80CF-00155D019F2C', '9623C575-D8A4-E611-80CF-00155D019F2C', '9823C575-D8A4-E611-80CF-00155D019F2C', 'DB440597-82FF-E711-80F9-00155D019677', '9E23C575-D8A4-E611-80CF-00155D019F2C', 'A023C575-D8A4-E611-80CF-00155D019F2C') or (membershiptype is null))

    The same issue, works a charm in SSRS but not in CRM. Any further assistance thankfully recieved!

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    You forgot to change this condition - membershipstatusname = 'Current'

  • Community Member Profile Picture
    Community Member on at

    Hi again,

    I have updated the conditions as requested and now it just loads entirely blank:

    Screen-Shot-2018_2D00_01_2D00_22-at-18.08.56.png

    Previously I had the column headings but no data.

    SQL:

    SELECT membershipuniqueid, membername, membershipstartdate, membershipenddate, name, prevmembershipid, membershiptype,membershiptypename
    FROM Filteredmembership
    WHERE (membershipstartdate BETWEEN @startdate AND @enddate) AND (membershipstatus = '990,810,000') AND (prevmembershipid IS NULL) 
    and (membershiptype in ('9A23C575-D8A4-E611-80CF-00155D019F2C', '9C23C575-D8A4-E611-80CF-00155D019F2C', '9623C575-D8A4-E611-80CF-00155D019F2C', '9823C575-D8A4-E611-80CF-00155D019F2C', 'DB440597-82FF-E711-80F9-00155D019677', '9E23C575-D8A4-E611-80CF-00155D019F2C', 'A023C575-D8A4-E611-80CF-00155D019F2C') or (membershiptype is null))

    Thanks :)

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    Looks like it fails on the background. Replace membershipstatus = '990,810,000'

    with membershipstatus = 990810000

  • Community Member Profile Picture
    Community Member on at

    Screen-Shot-2018_2D00_01_2D00_23-at-10.53.05.pngHi Andrew,

    Thanks for that, great spot! I've amended that condition and the report is now displaying but again reporting 0 data in CRM. Any other suggestions?

    Current SQL

     

    SELECT        membershipuniqueid, membername, membershipstartdate, membershipenddate, name, prevmembershipid, membershiptype,membershiptypename

    FROM            Filteredmembership

    WHERE        (membershipstartdate BETWEEN @startdate AND @enddate) AND (membershipstatus = 990810000) AND (prevmembershipid IS NULL)

    and (membershiptype in ('9A23C575-D8A4-E611-80CF-00155D019F2C', '9C23C575-D8A4-E611-80CF-00155D019F2C', '9623C575-D8A4-E611-80CF-00155D019F2C', '9823C575-D8A4-E611-80CF-00155D019F2C', 'DB440597-82FF-E711-80F9-00155D019677', '9E23C575-D8A4-E611-80CF-00155D019F2C', 'A023C575-D8A4-E611-80CF-00155D019F2C') or (membershiptype is null))

     

  • a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    I would suggest to run SQL Profiler and track query that is sent to SQL Server. That should give an answer why there is no data.

    Are you sure that you execute queries against the same DBs?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
ScottDurow Profile Picture

ScottDurow 2

#2
GJones Profile Picture

GJones 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans