Default Filter on report

pvarpvar Member Posts: 157
edited 2014-03-18 in Navision Attain
I have a report and when I run it, it needs to display a default value in one of the fields on the first data item and the user may/may not change that. Does any one know how to program this? I tried using setfilter, setrange in Oninitreport, onprereport, onpredataitem etc but none works. Thanks

Comments

  • SavatageSavatage Member Posts: 7,142
    it might help to let us in on what's the dataitem and what do you want to filter.

    is there a reason you haven't used the dataitemtableview property of the dataitem to set the filter?

    or perhaps use currreport.skip - if it's not a value that you want

    what do you mean by display the value? are you looking for something like when you print an invoice and the inv# already appears in the report, but still allows you to use the other filters?
  • pvarpvar Member Posts: 157
    Well, this is a multi-company report. We have a custom table that has all the company names and this table is the first data item in the report. So when a user at a particular company runs this report the company needs to default to his/her company. I tried setting the dataitemtableview for company=COMPANYNAME but didn't work.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    MyCompanyTable.Setrange("Company Name",COMpANYNAME);
    David Singleton
  • pvarpvar Member Posts: 157
    Where do you code this? As I said I tried every where. My data items are like this and I am trying to achieve this on our custom table, Plant group - Company name field

    Plant Group
    Company
    Item
  • pvarpvar Member Posts: 157
    Sorry, the data items are indented like this.

    Plant Group
    --Company
    ----Item
  • David_SingletonDavid_Singleton Member Posts: 5,479
    company on before get record
    David Singleton
  • pvarpvar Member Posts: 157
    Ok, there is no such thing like on before get record in a report, do you mean onpredataitem? Also my filter fields are on Plant group table not on Company table.

    Based on my understanding the request form is presented to the user after the oninitreport and before the onprereport. So something needs to be done, if any, on the onintreport to achieve what I am looking for.
  • SavatageSavatage Member Posts: 7,142
    have you tried it on OnPreDataItem()?

    Since it's not on the company dataitem perhaps you need a company.get; :-k
  • pvarpvar Member Posts: 157
    pvar wrote:
    I tried using setfilter, setrange in Oninitreport, onprereport, onpredataitem etc but none works
  • Mike_HWGMike_HWG Member Posts: 104
    edited 2014-03-18
    I know this is an old topic, but I'd like to respond for two reasons:

    1. This thread happens to be in the top Google search for this topic
    2. This topic has not been sufficiently answered in any of the top search hits.


    Issue:

    User wants to run a report directly. Upon load, a field that pertains to a DataItem needs to be preset with a filter value. This filter value needs to display on the Request Form of the report.

    Answer:

    It is not possible to set filters on DataItems that will display in the Request Form:
    -Setting a filter in the OnInitReport trigger does not work because the DataItem cannot be initialized. OnInitReport is the only trigger that occurs before the Request Form is displayed.
    -Setting a filter in the DataItemTableView does not pass to the Request Form

    There are three possible workarounds:

    1. If appropriate, call the report from a form. Filter the appropriate field for the record, then pass the record to the report using RUNMODAL OR RUN()

    2. Use a CodeUnit as a wrapper. Filter the appropriate field for the record, then pass the record to the report using RUNMODAL OR RUN(). If this is the only purpose of the codeunit, consider a better alternative - you are about to cost your client two objects.

    3. Utilize the Options tab of the Request form.

    The last option can be achieved in many ways, but I'll try to detail a the most robust solution I know how in the following example:

    ////////////////////////////////////////////////////
    EXAMPLE

    Assume that your business sells food items.
    Assume your desired report displays item information, and you want to set a default filter for the Inventory Posting Group.

    Your Report Designer looks like this:
    DataItem     Name
    ------------------
    Item         <Item>
    

    Create the following global variables (note the length is up to what you think is appropriate):
    Name         DataType     Length
    -------------------------------
    IPGFilter     Code            64
    

    Open the Request Options Form Designer. Add a text box control for IPGFilter. In the code for the text box, add the following variables to the Report - OnLookup
    Name         DataType     Subtype
    -----------------------------------------------
    IPG           Record          Inventory Posting Group 
    

    Add the following code to the Report - OnLookup
    IF FORM.RUNMODAL(0,IPG) = ACTION::LookupOK THEN
      Text := IPG.Code;
    EXIT(Text <> '');
    

    In the Report - OnInitReport, add the following code:
    IPGFilter := 'CANDY|COFFEE';  // Or whatever your default filter is
    

    In Item - OnPreDataItem, add the following code:
    IF IPGFilter <> '' THEN
      Item.SETFILTER("Inventory Posting Group",IPGFilter)
    ELSE
      ERROR('Inventory Posting Group filter must be set!'); // Optional - this is if you want to force the user to set a filter value
    

    With a setup like this, it is advisable to hide the DataItem's tab from showing in the report's Request Form. Do this by setting the DataItemTableView. Alternatively, block for situations where the user might try setting both.
    In the Report - OnPreReport, add the following code. This may look odd, but remember that the IPGFilter has not been applied to the DataItem at this point in code
    IF Item.GETFILTER("Inventory Posting Group") <> '' THEN
      ERROR('Do not set a %1 filter on the %2 tab.  Use the %1 filter on the Options tab instead'
        ,Item.FIELDCAPTION("Inventory Posting Group")
        ,Item.TABLECAPTION);
    
    Michael Hollinger
    Systems Analyst
    NAV 2009 R2 (6.00.34463)
  • absolutelyfreewebabsolutelyfreeweb Member Posts: 104
    I'd like to add, if you go route 3, you can also use how Navision has done the dimension selections:

    In report suggest vendor payments, if you push summarize per vendor, you can choose dimensions by ticking the records you want, and it will create a filter for you. This is better than navisions normal behavior, so you won't feel bad bc you did a workaround :)
  • Mike_HWGMike_HWG Member Posts: 104
    I'd like to add, if you go route 3, you can also use how Navision has done the dimension selections:

    In report suggest vendor payments, if you push summarize per vendor, you can choose dimensions by ticking the records you want, and it will create a filter for you. This is better than navisions normal behavior, so you won't feel bad bc you did a workaround :)

    That's a pretty cool way of doing it, but keep in mind this solution involves the report, a table, a form, and two codeunits (which could technically be consolidated to one codeunit). It is an incredibly robust, elegant solution, but you just paid for 4-5 objects... does the solution warrant the cost? As always, you'll need to make the determination.
    Michael Hollinger
    Systems Analyst
    NAV 2009 R2 (6.00.34463)
  • absolutelyfreewebabsolutelyfreeweb Member Posts: 104
    I duplicate three functions in the dimension selection buffer table and use the existing code. (they may see dimension selection on the selecting window), but you can do it with any records, customers, vendors, etc and filter it as you like, without adding objects.
Sign In or Register to comment.