SSRS Fetch-based Report in CRM

(0) Share

Report

Posted on by FrankF-WorldStrides

We are implementing a conversion to CRM Online 2016 from another SQL Server based CRM system and I'm beginning to try and migrate the reports. I understand that to use SSRS, we need to use FetchXML to get the data out of the system. I've been able to implement a couple of very simple reports this way, importing the rdl back into CRM to put on the reports menu, but am having an issue with a more complicated one.

The idea for this one (at least this part of it) is to look at phone call records, comparing the count of inbound to the count of outboud and getting a percentage (In/Out) by user. There is much more to the whole report, but this is the piece I've started with.

The FetchXML is straightfoward for each side: (this is outbound, and is slightly modified to have the parameter)

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true"> 
  <entity name="phonecall">
    <attribute name="ws_legacyownerid" alias="User" groupby="true" />
	<attribute name="actualend" alias="CallDate" aggregate="max" />
	<attribute name="subject" alias="PhoneCallCount" aggregate="countcolumn" />
    <filter type="and">
      <condition attribute="directioncode" operator="eq" value="1" />
      <condition attribute="statecode" operator="eq" value="1" />
      <condition attribute="actualstart" operator="not-null" />
      <condition attribute="actualend" operator="not-null" />
      <condition attribute="actualend" operator="ge" value="@sDate" />
      <condition attribute="ws_legacyownerid" operator="not-null" />
    </filter>
  </entity>
</fetch>

Inbound is the same query, but directioncode = 0

I then bring these into SSRS as shared datasets. I have the outbound side as the main query and do a Lookup to the inbound record (if it exists). @sDate is a report parameter that is also a query parameter to get the starting date to being with.

The report works fine in Preview, but when I deploy and try to import the rdl into CRM, I get this error:

Is this because of the lookup and/or date parameter in the report?

Thanks in advance.

-Frank

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Community Member on at

Like (0)

Report

How do you import your rdl file?

Recenlty i got the same problem. For me, the problem is the way i import the rdl file. There is import button within report entity form. Actually, you don't import your rdl file from that button. But create a new report ( + ). On the optionset field. Choose existing file.

Was this reply helpful? Yes No
FrankF-WorldStrides 80 on at

Like (0)

Report

Hi Fikri,

Yes, that is how I am trying to bring it in. Sales/Reports/New then change Report Type to "existing file"

Was this reply helpful? Yes No
FrankF-WorldStrides 80 on at

Like (0)

Report

Another curious thing: I can still run it in the Preview tab with no issues. Can choose different dates and it runs just fine. But when I try it in debug more (clicking the green "run debug" arrow), it gives me this error:

Both datasets use the same data source, and it is using a valid connection string as it works in preview.

Was this reply helpful? Yes No
ScottDurow 21 on at

Like (0)

Report

Hi Frank,

AFAIK you can't use Shared datasets on SSRS wtih CRM Online - you also can't use custom code:

technet.microsoft.com/.../dn531138.aspx

You might be better of using Power BI to do these more sophisticated reports.

Was this reply helpful? Yes No
FrankF-WorldStrides 80 on at

Like (0)

Report

Thanks for the link Scott.

We started down the path of using powerBI, but found that you can only use the tiles in Personal Dashboards and not public dashboards.

The other issue, as far as I could tell, was that PowerBI does not share the security scheme with CRM, so we would need to maintain two security role systems to make sure everyone can see and can't see what they're supposed to.

We're finding the CRM Online environment very restrictive. I understand why it is that way, but it really makes producing the user experience we are trying to achieve very difficult.

Was this reply helpful? Yes No
ScottDurow 21 on at

Like (0)

Report

Hi Frank,

Yes - you are right about the restrictions and that power BI doesn't share the user's security context. I use power BI for reports at the exec level where the user has access to everything.

One way to make reports and dashboards work better is to use rollup/goals to perform aggregation calculations in CRM and then use SSRS to display.

Was this reply helpful? Yes No