Microsoft Dynamics 365 | Integration, Dataverse...

SSRS/FetchXML - Filter by Month/Year against full date data format

(0) Share

Report

Posted on by EBMRay

905

Hello,

I am working on an SSRS Report where the user selects Month (Jan-Dec) and Year(2022,2021..) as custom parameters. After the user selection, the report should return all the applied leave requests that fall under this range based on the start/end date of the request.

The table resides in dataverse and below is a sample data in an excel sheet:

As you see the start/end dates are formatted in a full-day format and the problem is the below:

I am struggling in building the fetch XML query which should filter the leave requests based on the "Month" and the "Year" selected from the parameters against the start/end date columns since they are in mm-dd-yyyy. In our requirements, the user is not allowed to select from/to dates only month and year.

Example: If the user selects the current month "August" and the Year "2022" the fetchXML should return all the leave requests requested during this period.

What will be the workaround to pass "Year" and "Month" to the fetchXML and return the needed result knowing that the date format is mm-dd-yyyy in the table? Could you please provide an example?

In addition, Is it possible to deploy an SSRS report to PowerApps by using SQL query instead of FetchXML? Because in SQL it can be done very easily.

Mark Carrington would highly appreciate your support and any help is greatly appreciated.

Thank you!

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Mark Carrington 334 on at

Like (1)

Report

It's been a while since I built an SSRS report, but I'd look at calculating the start and end dates based on the year and month parameters within the report, and then passing those calculated dates to the FetchXML query. I don't believe there is a way of using the year and month parameters directly in the FetchXML.

You can't use SQL-based reports in PowerApps, the preferred method would be to use Power BI instead.

Was this reply helpful? Yes No
EBMRay 905 on at

Like (0)

Report

Hello Mark Carrington ,

Thank you for your reply.

Using Power BI, can we run a report for a selected record context, similar to the below functionality?

I know that we can embed a Power BI report into model-driven but we need to run a report based on a record selection which will generate the data needed and allow the user to save it as PDF.

How does it work if we use power bi?

Awaiting your response.

Thank you.

Was this reply helpful? Yes No
Suggested answer

Bipin D365 28,983 Moderator on at

Like (1)

Report

Hi,

Try creating two whole number custom fields to store Month and Year part from the full date field using plugin.

And use these fields in your report.

Was this reply helpful? Yes No
EBMRay 905 on at

Like (0)

Report

Hello Bipin Kumar ,

Thank you for your reply.

The two whole number custom fields won't work and below is the reason:

Assume that you want to apply for a leave request starting 8/31/2022 - 9/02/2022. The difference between those two dates is 3 days.

The start date month is "August" and the End Date month "Is September".

On which date are we going to rely upon in that case? In addition from reporting perspective, It should show that in August I took 1 day off, and in Sept 2 days off which accumulate the above total.

What will be the alternative in that case?

Awaiting your response.

Best regards,

EBMRay

Was this reply helpful? Yes No