JetReports med filtrering
Nu kommer JetReports*) med løsningen på hvordan manlaver Filtrering baseret på
data fra en anden tabel. Læs meget mere om dette nedenfor.
*) JetReports er et rapporteringsværktøj til Navision, som bruger Excel som frontend.
Se mere om Jet Reports her: www.jetreports.com
Solution
Sometimes you will want to filter one table based on data from a related table.
In Great Plains and the Universal connector, Jet Reports provides NL("Filter")
for this circumstance.
In Northwind for example, the Order Subtotals table does not have the OrderDate
in it, but the Orders table does. The OrderID is common to both tables, so if
you wanted to list Order Subtotals based on an OrderDate, you would start out
with an NL formula like the following.
=NL("Rows","Order Subtotals","Subtotal","OrderID",<List of OrderIDs with OrderDates
7/1/96..7/31/96>)
In the formula above, you need an NL formula which will replace the English description
of the OrderID filter. You can use NL("Filter") to create the OrderID filter from
the Orders table as shown in the formula below.
NL("Filter","Orders","OrderID","OrderDate","7/1/96..7/31/96")
Finally, you need to replace the English description in the first formula with
the second formula as shown below.
=NL("Rows","Order Subtotals","Subtotal","OrderID",NL("Filter","Orders","OrderID","OrderDate","7/1/96..7/31/96"))
There is one NL function inside another. The inner NL function returns a list
of OrderIDs that Jet can use as a filter for the Order Subtotals table.
If you are using multiple cross table filters, you should be aware that Jet Reports
will use each of the elements in each list as a filter in combination with all
of the elements of the other list. This can result in very slow reports if you
are not careful.
In Navision, Jet Reports has two mechanisms to help you do this, "Filter" and
"Link=". While both mechanisms return the same result, which one you should use
depends on what tables you are using. "Filter" should be used in situations where
the primary table is larger than (or of equivalent size to) the secondary table
(i.e. the Sales Line table filtered by the Sales Header table). "Link=", on the
other hand, will greatly increase performance when the primary table is smaller
than the secondary table (i.e. the Dimension table filtered by the G/L Entry table).
When performing cross-table filtering, you may not be able to drilldown on the
cell. That is because Navision will not accept a list as a filter, so Jet Reports
attempts to create a set of filters that will uniquely select the exact same records.
This is often possible when there are only a few records selected. However, if
there are too many records, Jet Reports will report that drilldown is not possible.
Using NL("Filter")
As stated above, NL("Filter") should be used when you would like to filter one
table based on data from another table that is of smaller or equivalent size (if
this is not the case, please use "Link="). For example, the Sales Line table is
generally larger than the Sales Header table since each Document can have several
lines associated with it but only one header. The Sales Line does not have the
Posting Date in it, but the Sales Header does. The Document Number is common to
both tables, so if you wanted to list Sales Lines based on Posting Dates, you
would start out with an NL formula like the following:
=NL("Rows","Sales Line",,"Document No.",{List of Document No.s with Posting Dates
1/1/02..1/31/02})
You need to replace the English description of the list in the formula above
with an NL formula. The field in the Sales Header that holds the document number
is "No.", so the NL formula that generates a document number filter is below:
=NL("Filter", "Sales Header", "No.", "Posting Date", "1/1/02..1/31/02")
Finally, you need to replace the English description in the first formula with
the second formula as shown below:
=NL("Rows","Sales Line",,"Document No.",NL("Filter","Sales Header","No.","Posting
Date", "1/1/02..1/31/02"))
There is one NL function inside another. The inner NL function returns the Document
No. filter that is then used in the Sale Line table.
Using "Link="
"Link=" is another mechanism that can be used to filter data in one table based
on data in a related table. Specifically, "Link=" should be used when the primary
table is smaller than the secondary table. For example, lets say you would like
to create a list of invoice numbers that contain item sales. You can list the
invoice numbers from the Sales Invoice Header table, but need to use the Sales
Invoice Line table to ensure that each invoice contains an item sale. Since all
you would like to do is create a list of invoice numbers, you do not need a complete
list of Sales Lines for each invoice. Rather, all you want to know is whether
an entry containing an item sale exists. To do this, your formula would look something
like the following:
=NL("Rows","Sales Invoice Header","No.","Posting Date","7/1/05..7/31/05","Link=","Sales
Invoice Line","Document No.","=No.","Type","Item")
Note that the first argument after the "Link=" is the name of the secondary table.
The successive arguments thereafter represent FilterField/Filter pairs that will
be applied to this table (i.e. "Document No." and "Type" are fields in the Sales
Invoice Line table, not the Sales Invoice Header). Filter values with an equals
sign ("=") pre-pended to them represent the linking fields from the primary table.
In the above example, the NL function will examine the value of the No. field
for each record in the Sales Invoice Header table and will only include the record
if a corresponding record exists in the Sales Invoice Line table where the "Type"
field is "Item" and the "Document No." field is equivalent. Note that there can
be multiple linking fields.
Til toppen af siden
Du skal være logget ind før du kan se eller skrive kommentarer til de forskellige indlæg. Klik her for at logge ind, eller oprette en bruger.
