Small and medium business | Business Central, N...

Query based Report Filtering in AL Language?

(5) Share

Report

Posted on by CU05031656-0

I designed in AL a report based on a query dataitem. How do I add filters to this? I cannot even add filters at runtime when running the report in the auto requestpage. What gives?

Categories:

Microsoft Dynamics RMS

Microsoft Dynamics NAV

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Teddy Herryanto (Th... 14,284 Super User 2025 Season 2 on at

Like (1)

Report

You should be able to use filter on your query.

Check this two blog posts for reference.

https://businesscentralgeek.com/query-object-how-does-it-work-in-business-central

https://yzhums.com/58885/

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,331 Super User 2025 Season 2 on at

Like (1)

Report

Hi, hope the following helps.

Filtering in Query objects

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-filters?wt.mc_id=DX-MVP-5004336

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,698 Moderator on at

Like (1)

Report
Hi,

you can try something like this:

report 50100 "MyQueryReport" { UsageCategory = ReportsAndAnalysis; dataset { dataitem(MyQuery; "MyQuery") { column(No; No) { } column(Name; Name) { } column(BalanceLCY; BalanceLCY) { } } } requestpage { layout { area(content) { group(Filters) { field(MinBalance; MinBalanceFilter) { ApplicationArea = All; Caption = 'Minimum Balance'; trigger OnValidate() begin // Validate and store the filter value end; } } } } } var MinBalanceFilter: Decimal; trigger OnPreReport() begin // Apply the filter from the request page to the query if MinBalanceFilter <> 0 then MyQuery.SetFilter(BalanceLCY, '>=%1', MinBalanceFilter); end; }

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 20,275 Super User 2025 Season 2 on at

Like (1)

Report

Hope this help as well: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-filters

Was this reply helpful? Yes No
CU05031656-0 17 on at

Like (0)

Report

I am not looking to filter the query, I am looking for filtering the report at runtime.

This is where I do not have any filtering. If I click on +Filter I get this, regardless, of what I have.

No actual fields I need to filter on.

Sure, I could make a personalized RequestPage, but there are two issues with this:

1. I will miss the drop-down pick of actual values for the filtering fields, like below.

and 2.

In the requestpage definition in the report object, again there is no filters I could actually use.

Was this reply helpful? Yes No
CU05031656-0 17 on at

Like (0)

Report

@Nitin Verma

Not sure where this syntax comes from as AL definitely expects a table inthere not a query.

I cannot make a query work there, it only accepts a table.

Was this reply helpful? Yes No
Gerardo Rentería Ga... 25,169 Most Valuable Professional on at

Like (1)

Report

Hi, good day
I hope this can help you, and give you some hints.

How Do I: Create a Report based upon a Query? – think about IT

Best Regards
Gerardo

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,698 Moderator on at

Like (0)

Report
Hi,

I have created this whole report now, can you try now, replace your actual values here:

report 50100 "MyQueryReport" { UsageCategory = ReportsAndAnalysis; DefaultLayout = RDLC; RDLCLayout = './MyQueryReport.rdlc'; // Optional: Add your layout file dataset { dataitem(Integer; Integer) { DataItemTableView = where(Number = const(1..)); column(No; CustNo) { Caption = 'Customer No.'; } column(Name; CustName) { Caption = 'Customer Name'; } column(BalanceLCY; CustBalanceLCY) { Caption = 'Balance (LCY)'; } trigger OnPreDataItem() begin // Apply filters to the query before opening it if MinBalanceFilter <> 0 then MyQuery.SetFilter(BalanceLCY, '>=%1', MinBalanceFilter); MyQuery.Open(); // Open the query with any applied filters end; trigger OnAfterGetRecord() begin // Read the next row from the query if not MyQuery.Read() then CurrReport.Break(); // Exit when no more rows // Assign query values to report variables CustNo := MyQuery.No; CustName := MyQuery.Name; CustBalanceLCY := MyQuery.BalanceLCY; end; } } requestpage { layout { area(content) { group(Filters) { field(MinBalance; MinBalanceFilter) { ApplicationArea = All; Caption = 'Minimum Balance'; trigger OnValidate() begin // Validate the input if needed if MinBalanceFilter < 0 then Error('Minimum Balance cannot be negative.'); end; } } } } } var MyQuery: Query "MyQuery"; // Reference to your query object CustNo: Code[20]; // Variables to hold query column values CustName: Text[100]; CustBalanceLCY: Decimal; MinBalanceFilter: Decimal; // Variable for runtime filter from request page } query 50100 "MyQuery" { QueryType = Normal; elements { dataitem(Customer; Customer) { column(No; "No.") { } column(Name; Name) { } column(BalanceLCY; "Balance (LCY)") { } } } }

Was this reply helpful? Yes No
Suggested answer

Ramiz 597 on at

Like (0)

Report

Hi,

You should have the table dataitem in the report in order to use the default filtering like you mentioned.

If you add the table (Sales Invoice Header) as dataitem you will be able to apply the filter.

Was this reply helpful? Yes No
CU05031656-0 17 on at

Like (0)

Report

@Nitin Verma

These names are NOT known in the context.

Was this reply helpful? Yes No