Skip to main content
Post a question

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge 🌸
News and Announcements icon
Community site session details

Community site session details

Session Id : oiuz/QRye2khnPDbSGfWza
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Limiting the RequestFi...
Small and medium business | Business Central, N...
Answered

Limiting the RequestFilterFields to a subset of records

Like (4) ShareShare
ReportReport
Posted on 2 Apr 2025 20:47:57 by ME-31032107-0 101
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
  • ME-31032107-0 Profile Picture
    ME-31032107-0 101 on 04 Apr 2025 at 21:31:26
    Limiting the RequestFilterFields to a subset of records
    Khushbu Rajvi, thank you, that works much better.
  • Verified answer
    Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 13,339 Super User 2025 Season 1 on 04 Apr 2025 at 13:59:58
    Limiting the RequestFilterFields to a subset of records
    Here is the OutPut. I have checked it. 
     
     
    With fILTER
     
    Without Filter: 
     
     
  • Suggested answer
    Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 13,339 Super User 2025 Season 1 on 04 Apr 2025 at 13:57:51
    Limiting the RequestFilterFields to a subset of records
    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;
    }
  • ME-31032107-0 Profile Picture
    ME-31032107-0 101 on 03 Apr 2025 at 19:00:41
    Limiting the RequestFilterFields to a subset of records
    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.
  • Suggested answer
    ME-31032107-0 Profile Picture
    ME-31032107-0 101 on 03 Apr 2025 at 18:37:09
    Limiting the RequestFilterFields to a subset of records
    Khushbu Rajvi, your post worked perfectly for what I needed, thanks!!
  • Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 13,339 Super User 2025 Season 1 on 03 Apr 2025 at 12:43:16
    Limiting the RequestFilterFields to a subset of records
    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;
     
     
     
     
     
     
  • Suggested answer
    Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 13,339 Super User 2025 Season 1 on 03 Apr 2025 at 12:27:37
    Limiting the RequestFilterFields to a subset of records
    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.
  • gdrenteria Profile Picture
    gdrenteria 17,146 Most Valuable Professional on 03 Apr 2025 at 11:40:29
    Limiting the RequestFilterFields to a subset of records

    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

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 80,711 Super User 2025 Season 1 on 02 Apr 2025 at 23:13:09
    Limiting the RequestFilterFields to a subset of records
    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.
     
    Hope this can give you some hints.
    Thanks.
    ZHU

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

News and Announcements

🌸 Community Spring Festival 2025 Challenge 🌸

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,716 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,683 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans