Report works in SSRS, no data when imported into CRM

(0) Share

Report

Posted on by Community Member Microsoft Employee

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

All responses (16)

Answers (1)

Suggested answer

Anh Luu 215 on at

Like (0)

Report

RE: Report works in SSRS, no data when imported into CRM

I have exactly the same issue.

In short:

In my case, it does not work because my CRM DB name does not end with '_MSCRM', after I add the suffix '_MSCRM' to my CRM DB name in SQL server, it works fine!

In details:

I opened SQL Profiler to find the reason why it's so strange.

What I found is that when my CRM DB name has suffix '_MSCRM', it will set the SQL context to the current user (who is also a user in CRM), so the Filtered view will return data.

Otherwise (no suffix '_MSCRM'), no context is set, the query will be executed by the account running the reporting service (who is not a user in CRM), so no data is returned by the Filtered view.

I hope this helps!

Regards,

Kim Anh
a33ik 84,323 Most Valuable Professional on at

Like (1)

Report

RE: Report works in SSRS, no data when imported into CRM

To be honest - I have no idea what could be the reason for that weird behavior but I'm pretty sure that good SQL Admin can give an answer.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Report works in SSRS, no data when imported into CRM

Well at least I wasn't going insane. Do you have any idea what might cause this so that I can speak to the installer?

I want to thank you for all your help Andrii!
Verified answer

a33ik 84,323 Most Valuable Professional on at

Like (2)

Report

RE: Report works in SSRS, no data when imported into CRM

I'm sorry to day but this is not normal behavior for reports in CRM. I'm afraid that somebody did something with your DB to make it work that way. The only right way is to use Filtered views. When you're not sysadmin in CRM and you try to run report that uses non-filtered views usually ends up with errors.

Also there is no supported way to move "Name" column to usual views.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Report works in SSRS, no data when imported into CRM

By pure fluke, I've had something of a breakthrough. I simply changed the FROM filteredmembership to FROM membership and this worked - data is now appearing in the report!

Interestingly, none of the filtered datasets turn up in the report in CRM - some permissions issue I'm missing?

I have one other question if you able to assist - one of the columns on the filtered report was PaymentmethodName, this doesn't exist on the unfiltered view, is there someway to bring this accross or could I use SWITCH/IIF to change the text from an ID (such as 099080002) to "Credit/Debit Card"?
Community Member Microsoft Employee on at

Like (0)

Report

RE: Report works in SSRS, no data when imported into CRM

I'm not sure why they'd be different, the CRMLIVE_MSCRM db is the one I am querying in SSRS and ostensibly should be the same DB once the report is imported into CRM. I'll look into SQL Profiler, but thanks so far. I wonder if it's at all related to using Windows Integrated Authentication on the CRM DB?

Or am I forced to have another go with Fetchxml?
a33ik 84,323 Most Valuable Professional on at

Like (1)

Report

RE: Report works in SSRS, no data when imported into CRM

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?
Community Member Microsoft Employee on at

Like (0)

Report

RE: Report works in SSRS, no data when imported into CRM

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))
Suggested answer

a33ik 84,323 Most Valuable Professional on at

Like (2)

Report

RE: Report works in SSRS, no data when imported into CRM

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

with membershipstatus = 990810000
Community Member Microsoft Employee on at

Like (0)

Report

RE: Report works in SSRS, no data when imported into CRM

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