Report works in SSRS, no data when imported into CRM

(0) Share

Report

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)

All responses (16)

Answers (1)

a33ik 84,331 Most Valuable Professional on at

Like (2)

Report

Hello,

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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)

Was this reply helpful? Yes No
Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (2)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (2)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi again,

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

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 :)

Was this reply helpful? Yes No
Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (2)

Report

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

with membershipstatus = 990810000

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi 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))

Was this reply helpful? Yes No
a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

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?

Was this reply helpful? Yes No