Limiting the RequestFilterFields to a subset of records

(4) Share

Report

Posted on by ME-31032107-0

683

I am building an SSRS report in BC that lists open Sales Orders and Line Items for a customer range. The Customer range is based on new field in the Customer page called "Customer Location", which has its own table and list. The report should only show customers with a Location starting with 'ABC'.

In the Customer Data item, I am restricting the customers returned for this report like this:

dataitem(CU; Customer)
{
DataItemTableView = where("Location No." = FILTER('ABC*'));
RequestFilterFields = "Location No.";
PrintOnlyIfDetail = True;

column(Customer_Nbr; "No.")
{

}
column(Location_No; "Location No.")
{

}

The Location No. field is a Filter on the report, but it returns the entire list of Customer Locations as below:

Is there a way to get the Report Filter to only show the Locations starting with 'ABC' to match the list of Customers included in the report?

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

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

Like (2)

Report

This cannot be done using RequestFilterFields, you need to add a new field on the request page and then do some filtering.

Or you can use a security filter to completely restrict user access to these values.

More details: https://yzhums.com/10133/

Hope this can give you some hints.

Thanks.

ZHU

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

Like (3)

Report

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

Dynamics 365 Business Central: Can we change default options/filters on the request page of Report??? | Dynamics 365 Lab

Best Regards
Gerardo

Was this reply helpful? Yes No
Suggested answer

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

Like (2)

Report

In Business Central SSRS reports, the RequestFilterFields property only controls what fields users can filter on but does not restrict the values available in the filter selection dropdown.

Was this reply helpful? Yes No
Khushbu Rajvi. 20,439 Super User 2025 Season 2 on at

Like (3)

Report

Apply below code and check:

requestpage

{

layout

{

area(Content)

{

group(GroupName)

{

field("Filtered Location"; LocationFilter)

{

ApplicationArea = All;

TableRelation = Location.Code WHERE(Code = FILTER('ABC*'));

}

}

}

}

}

var

LocationFilter: Code[20];

Location: Record Location;

trigger OnPreReport()

begin

IF LocationFilter <> '' THEN

Location.SETFILTER(Code, LocationFilter)

ELSE

Location.SETFILTER(Code, 'ABC*');

end;

Was this reply helpful? Yes No
Suggested answer

ME-31032107-0 683 on at

Like (3)

Report

Khushbu Rajvi, your post worked perfectly for what I needed, thanks!!

Was this reply helpful? Yes No
ME-31032107-0 683 on at

Like (2)

Report

Khushbu Rajvi, actually I am now realizing that while your code does provide the filtered location list, if I choose a single location in the filter, all the Locations still appear in the output. The report needs to only show the customer data for the selected Location.

Was this reply helpful? Yes No
Suggested answer

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

Like (3)

Report

Hi, ME-31032107-0 please try below code

report 50710 Customerr

{

UsageCategory = ReportsAndAnalysis;

ApplicationArea = All;

Caption = 'Customer Report';

DefaultRenderingLayout = LayoutName;

dataset

{

dataitem(Customer; Customer)

{

column(ColumnName; "No.")

{

}

column(Name; Name)

{

}

column(Address; Address)

{

}

column(Location_Code; "Location Code")

{

}

trigger OnPreDataItem()

begin

if LocationFilter <> '' then

SETRANGE("Location Code", LocationFilter)

else

CLEAR("Location Code");

end;

}

}

requestpage

{

AboutTitle = 'Teaching tip title';

AboutText = 'Teaching tip content';

layout

{

area(Content)

{

group(GroupName)

{

field(Filter; LocationFilter)

{

ApplicationArea = All;

TableRelation = Location.Code WHERE(Code = FILTER('ABC*'));

}

}

}

}

}

rendering

{

layout(LayoutName)

{

Type = RDLC;

LayoutFile = 'mySpreadsheet.rdl';

}

}

var

LocationFilter: text;

}

Was this reply helpful? Yes No
Verified answer

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

Like (3)

Report

Here is the OutPut. I have checked it.

With fILTER

Without Filter:

Was this reply helpful? Yes No
ME-31032107-0 683 on at

Like (4)

Report

Khushbu Rajvi, thank you, that works much better.

Was this reply helpful? Yes No