Do you want custom Report without Customization?

Hi Folks,

I hope you guys are fantastic and enjoying each moments of life.

Last week I was checking community and found one requirement for report which is not available in Dynamics 365 Finance as OOB. At the same time I had an conversation with one the my friend regarding requirement to see data with custom filter.

So in this blog I am providing you one standard solution to build custom report without customization.

Scenario : 1 Free Text Invoice Report

As we know we don’t have any standard OOB report for all Free Text Invoices. So let’s start and build our own Report.

Let’s Open OOB Invoices Journal Inquiry form and begin the journey

  1. Go to Account Receivable > Inquiry and Reports > Invoices > Invoice Journal
  2. Add Column as “Order Type”
  3. Add Filter on:
    • Order type as Exactly = Journal OR
    • Voucher as “Begins with = FTV (prefix you use on Free Text Invoice Voucher) OR
    • Invoice as “Begins with = FTI (prefix you use on Free Text Invoice Number)
  4. By adding this filter only Free text Invoice will be left in standard inquiry form.

You can do this filter directly on Column or Advance Filter pane or using Advance Filter and sort (Options)

Advance Filter

Output after applying the filter:

Output in Current report.

Now we have the report. Now we want to save this filter on this report so whenever we want to see this details we don’t have to do all filter once again.

For this let’s do one simple trick.

Go to Options > Add to Workspace > Select the Workspace > As per your choice > Select Presentation > “List”

Workspace Selection

Now click on “Configuration” and select the required column

Column Selection

Click “Ok” and Refresh the browser and Open the workspace.

Final Report

Final Report as requested:

Please share you feedback.

Scenario : 2 Vendor Payment Due in Next 7 Days

We don’t have any report which can provide how much we need to pay in next 7 or 30 days except Aging Report or BI Report.

Let’s assume we need Details of Vendor Invoice which will due in Next 7 Days.

Condition:

  1. We don’t need past due Invoice
  2. We only need Open Invoice having due date within 7 Days
  3. Need Report in Workspace

Let’s Open OOB Open Vendor Invoices Inquiry form and begin the journey

Go to > Accounts Payable > Invoices > Open Vendor Invoices

Apply the filter on Due Date as “Matches = (DayRange(0,7))

After applying this filter you will see the records which will be due within 7 days from today (Session Date).

Advance Filter

Now we have the report. Now we want to save this filter on this report so whenever we want to see this details we don’t have to do all filter once again.

For this let’s do one simple trick again.

Go to Options > Add to Workspace > Select the Workspace > As per your choice > Select Presentation > “List”

Workspace Selection

Now click on “Configuration” and select the required column

Column selection for report

Click “Ok” and Refresh the browser and Open the workspace.

Final Report as requested:

Final Report

Tips for Advance Date Filter

There is one old blog on “bits here and there…” for explanation for Dynamics AX 2012.

This tricks still works in Dynamics 365 Finance & Operation.

Extracts from original blog: Use with matches in Date column of any report (Inquiry or SSRS)

Query valueDescription
(day(relativeDays))
Relative days to today’s date. Default to 0 when the ‘relativeDays’ optional parameter is ignored. Can be offset + or -.

Eg.
(day(-1)) will gives you 29/06/2011
(day(0)) will gives you 30/06/2011
(day(1)) will gives you 01/07/2011
(dayRange(relativeDaysFrom, relativeDaysTo))
Relative day range from and to given today’s date.
To get past 1 week + next 1 week date, you can use (-7, 7).

Eg.
(dayRange(0,0)) gives you 30/06/2011 to 30/06/2011
(dayRange(-2,2)) gives you 28/06/2011 to 02/07/2011
(dayRange(0,2)) gives you 30/06/2011 to 02/07/2011
(dayRange(-2,0)) gives you 28/06/2011 to 30/06/2011
(greaterThanDate())
Similar to (day()), but give the date of after the offset.

Eg.
(greaterThanDate(0)) gives you “> 30/06/2011”
(greaterThanDate(2)) gives you “> 02/07/2011”
(greaterThanDate(-2)) gives you “> 28/06/2011”
(lessThanDate())
Similar to (greaterThanDate()), but the other way round.

Eg.
(lessThanDate(0)) gives you “< 30/06/2011”
(lessThanDate(2)) gives you “< 02/07/2011”
(lessThanDate(-2)) gives you “< 28/06/2011”
(monthRange(relativeMonthsFrom, relativeMonthsTo))Similar to (dayRange()), instead of days, it gives months.
(yearRange(relativeYearsFrom, relativeYearsTo))Similar to (dayRange()), instead of days, it gives years.

These methods is coming from the class “SysQueryRangeUtil”.

*NOTE: It needs the bracket around the method name.
Eg.
– Correct: (day(0))
– Incorrect: day(0)

So, This is it for today.

Please let me know in comment section if you like the content of blog.

Thank you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: