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!
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
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.
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.
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.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,817
Most Valuable Professional
nmaenpaa
101,156