Hello all,
Maybe you have the same requests from customers so I’d like to share this trick.
For the JetReports Professional exam I posted a report which can replicate unique values from a matrix which is also built from replicated values from Navision.
Sometimes a customer wants a report from Navision which is based on their own values.
For example a customer places regularly orders with his own customer reference.
The users store this reference in field Customer reference.
For example: reference 210013-bicycle
The first 3 digits are representing the department of the customer.
After a few months the customer wants a report of sales per department.
Of course you can add an extra field Department in Navision but if you can trust the user data input you can also use Jet and Excel to extract the department codes.
For example with the =LEFT() formula.
You can create a list of all saleslines with the customer reference.
But how to make a total overview based on unique values of the department code in the salesline-list?
I’ve enclosed an example how to replicate unique values from a by Jet replicated list.
The trick is to use the NP- intersect formula and compare the range with its own values.
=NL(“Rows”;NP(“Intersect”;Range;Range)).
In the report I use the Zip Codes from the contact table to extract the State codes from US-Contacts and generate an overview in the second sheet from these values.
In Cronus International the Zip Code is as followed: US-NY 11010.
In my example I use in Column P the formula =MID($M8;SEARCH("-";$M8)+1;2) to extract the State Code. (NY)
In the second sheet I replicate the unique values from this column with the NL-NP combination.
Because of the text-values this report works as expected but if the values are numbers you can get double values.
For example the first three digits from “210013-bicycle” result in number 210. =LEFT(“210013-bicycle”;3)
A replicated list with numbers and the NL-NP-intersect combo results sometimes in double values.
I’m not completely sure but it has something to do with the format of the cell.
You can solve this problem by using the =VALUE() formula: =VALUE(LEFT(“210013-bicycle”;3)).
Another tip: make sure if you use multiple worksheets that the listsheet is in front of the overview sheet, so Jet "first" replicates the list before it replicates the overview.
Take a look at the example(s) maybe you find it useful.
I made a viewer-example version for people who don’t have the Cronus International Dbase.
If you have a more simple technique to replicate Unique values from an Excel range please respond on this topic!
Greetings
Hans Dütting
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Hans,
Nice to see your example here on the community! Glad that you're sharing it with us.
Don't know if this is exactly the same, but here's a trick I use to do a Unique on the first characters of a field: =NL("Rows","Tablename","=LEFT(NF(,""Fieldname""),NoOfChar)")
Yes, you may use an Excel function here. E.g. I've done a YEAR function on a posting date to retrieve the fiscal years that were posted into. And I'm sure there are more nice features to use :geek: -
Jet Reports Historic Posts Unique filtered lists are usually difficult with Excel, this just made my life so much easier. Used it with the "Columns" function in order to get a filtered list of dates, which I then used to filter date from that date.
Thanks,
Tim
Please sign in to leave a comment.