Microsoft Dynamics CRM (Archived)

Fetch XML SSRS report

(0) Share

Report

Posted on by Jay2014

Hi All,

I would to build a Fetch XML query to bring a count of created cases by Quarter :

16/17

Qtr 1

17/18

Qtr 2

17/18

Qtr 3

17/18

Qtr 4

17/18

Any ideas how i go about reporting on the above ?

Thanks,

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Radu Chiribelea 6,667 on at

Like (0)

Report
Copy link

Link copied!

hi M. Ncube

One way to do this is to create the report filtering criteria using

Created On - After Quarter start Date

and

Created On - Before Quarter end date

run the report 4 times (for each quarter)

Or you can simply run the report for an entire FY and then do the filtering in Excel.

Hope this helps,

Radu

Was this reply helpful? Yes No
Suggested answer

Preeti Sharma 2,678 on at

Like (1)

Report
Copy link

Link copied!

Hi,

You can apply iif conditions in textboxes of tables as below:

Sum(iif(createdon>quarter1startdate and createdon < quarter1enddate,1,0))

and same for other quarters in other textboxes.

See below links as well:

social.msdn.microsoft.com/.../count-detail-records-based-on-a-condition-in-ssrs

stackoverflow.com/.../reporting-services-count-column-values-if-equals-a

Hope it helps:)

Was this reply helpful? Yes No
Suggested answer

ashlega 34,477 on at

Like (0)

Report
Copy link

Link copied!

You can't create a fetch query for that - all you can do in fetch is group by, so, potentially, you might group by quarter if you had quarter field on those records.

You might try creating a calculated field on the entity then use FetchXml aggregation:

msdn.microsoft.com/.../gg309565.aspx

Or use Radu's suggesion.. maybe, build a subreport and add it to the main report 5 times,

Was this reply helpful? Yes No
Jay2014 on at

Like (0)

Report
Copy link

Link copied!

Hi Preeta, in your case that means i have to manually specify the Quarter dates ? That means having to update the report every year .. ?

Was this reply helpful? Yes No
Jay2014 on at

Like (0)

Report
Copy link

Link copied!

Thanks Radu and Alex, I have not built a subreport before. If i am to go with that route, will i need to have the Quarter fields ? I have not built Sub Reports before

The calculated field, how would i go about that ?

Was this reply helpful? Yes No
Jay2014 on at

Like (0)

Report
Copy link

Link copied!

I guess I could have 4 fields on each year to hold the Quarter values :r

Previous Year Q1 to Q4

Current Year Q1 to Q4

Next Year Q1 to Q4

These would have to be manually updated perhaps by a workflow on start of the year or something .. Question is how do i go about implementing the logic of finding the Quarters ...

Would this be a feasible design approach ?

Was this reply helpful? Yes No
Suggested answer

Preeti Sharma 2,678 on at

Like (0)

Report
Copy link

Link copied!

Hi Ncube,

You don't need to update fields every year. You can calculate current or previous year by using expressions in ssrs. Dates of quarters can be fixed.

Say quarter 1 starts from 1 april and ends on 1 july then you can get it as below:

Sum(iif(createdon>cdate("1/4"& Str(year(today)))and createdon < cdate("1/6"& Str(year(today))) ,1,0))

Similarly you can get values for previous years as well.

Sum(iif(createdon>cdate("1/4"& Str(year(today.AddYears(-1))))and createdon < cdate("1/6"& Str(yeartoday.AddYears(-1)))),1,0))

Hope this helps:)

Was this reply helpful? Yes No