Skip to main content

Notifications

Microsoft Dynamics 365 | Integration, Dataverse...
Suggested answer

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

(0) ShareShare
ReportReport
Posted on by 877

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:

pastedimage1661438276442v1.png
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!

  • EBMRay Profile Picture
    EBMRay 877 on at
    RE: SSRS/FetchXML - Filter by Month/Year against full date data format

    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

  • Suggested answer
    Bipin D365 Profile Picture
    Bipin D365 28,959 Super User 2024 Season 1 on at
    RE: SSRS/FetchXML - Filter by Month/Year against full date data format

    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.

  • EBMRay Profile Picture
    EBMRay 877 on at
    RE: SSRS/FetchXML - Filter by Month/Year against full date data format

    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?
    4760.Picture1.png

    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.

  • Suggested answer
    Mark Carrington Profile Picture
    Mark Carrington 334 on at
    RE: SSRS/FetchXML - Filter by Month/Year against full date data format

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,817 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans