Subreport not returning values when executed in Dynamics CRM

(0) Share

Report

Posted on by andrewj

We have a report that has 6 subreports of which 5 return data when run in Microsoft Dynamics.

When run in Report Builder on the server that the CRM is hosted on, all 6 work.

The one that doesn't work when used as a subreport does work when uploaded to dynamics by itself.

When run as a subreport, it returns the format of the subreport (e.g. table, pie chart, etc.) however it does not seem to be getting the data being queried by the report.

We've tried recreating the subreport several times, we've looked at the query and cannot find anything wrong with it (it runs on Dynamics by itself and as a subreport when in Report Builder) and we are out of ideas to try.

We're running Dynamics CRM 2016 on premise hosted on a remote server and using Report Builder 3.0 for building the reports.

Please send help.

*This post is locked for comments

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

bac26 2,268 on at

Like (0)

Report
Copy link

Link copied!

Need to see the parameters of query. If you changed the query to include different fields, does it render correctly? Definitely working and not finding data because it shows the format. So it sounds like something is off with the query and we don't see the query in this thread..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

andrewj and I are working together on this report. Our sub-report query looks like this:

___

Select distinct
Account.Name as 'Organisation Name',
AC.[Activity Classification],
AC.[Subprogramme],
AC.[activityid],
AC.Subject

From Account

LEFT JOIN
(
select
Filteredhbs_activityclassification.hbs_name as [Activity Classification],
Filteredhbs_activityclassification.new_subprogrammename as [Subprogramme],
Connection.Record2Id as [activityid],
ActivityPointerBase.regardingobjectid,
ActivityPointerBase.subject

from Filteredhbs_activityclassification
inner join connection on Filteredhbs_activityclassification.hbs_activityclassificationid = connection.record1id
LEFT JOIN ActivityPointerBase ON Connection.record2id = ActivityPointerBase.Activityid
WHERE
dateadd(hour,12, ActivityPointerBase.actualstart) >= @StartDate AND
dateadd(hour,12, ActivityPointerBase.actualstart) <= @EndDate AND
ActivityPointerBase.statecode = 1 AND
(
ActivityPointerBase.ActivityTypeCode = 4201
OR ActivityPointerBase.ActivityTypeCode = 4202
OR ActivityPointerBase.ActivityTypeCode = 4210
)
) AC
on AC.regardingobjectid = Account.AccountId

WHERE
Account.hbs_organisationcategoryName = 'General Practice'
and Account.StateCode = 0 and Account.hbs_lga1Name <> 'Outside Area'
and Account.Name = (@AccountName)

order by ac.[activityid]

___

Our parameters on the sub -report are:

Our Main Report parameters are:

The ONLY sub-report not working in CRM is "New_AC_Percentv5". All sub-reports call the @AccountName in the where clause.

Any suggestions gratefully appreciated.

Was this reply helpful? Yes No