I want to create a sales report per country per sales type.
So far so good, but for every combination I want a sheet.
Report options give me the ability to select one or more countries and one or more sales types.
Both are from a different table in NAV.
On running the report after I selected the options I end up with e.g. NL,DE,GB as countries and B2B,B2C as sales types.
Now I want 6 sheets: NL-B2B, NL-B2C, DE-B2B, DE-B2C, GB-B2B, GB-B2C.
What Jet function can I use to accomplish this?
I'm kind of stuck on ideas :)
rmw
Date
Votes
7 comments
-
Jet Reports Historic Posts Greetings -
If you are using a "Universal" data source, this can be done with the "SQL=" feature.
Example
=NL("Sheets","SQL=SELECT CONCAT(C.Field,'-',V.Field) as R FROM Table1 as C, Table2 as V","R")
If you are using any other type of data source (e.g., NAV, GP), I do not know of any way to accomplish this. -
Jet Reports Historic Posts Thx for the reply.
It is a NAV datasource, so it is probably a no go….
I tried to use a worksheet range as source of NL("Sheets") but that only seem to work for a fixed range, not a generated one.
NL("Sheets",'Data!$C$5:$C$6) works ok, but if you use other Jet functions to make it grow,by inserting rows, it won't work.
It seems the replicator functions of NL are all loaded at the start and not evaluated (again) when it is their turn to be executed.
Can anyone confirm that?
rmw -
Jet Reports Historic Posts I spent ages trying to get this to work, I like an excel challenge. It does say in the support docs that NL("Sheets") is evaluated first, before any other replicators.
I managed to get you the correct number of sheets, (i.e. if your filters returned 3 countries and 4 sales codes it would create you 12 tabs, but unfortunately they were numbered 1 to 12 rather than with combinations. -
Jet Reports Historic Posts I have not tried this yet - but perhaps it will get things going in the right direction for you. I know I have used this logic for column replicators, so it SHOULD be possible.
but I think you have to have this as a report that runs on the scheduler so you can eliminate the hidden rows - so that may cause some issue.
Put a new worksheet at the beginning of your workbook (it must be before the report worksheet with the "SHEETS" command)
On the new sheet, you need to build a list (using a ROWS command) that will create the names for your sheets.
Something like this (please keep in mind, I'm simplifying here, you'll need to substitute your logic)
in cell C3 =NL("ROWS=2",table with country data,"COUNTRY",your filter logic…)
in cell C4 =C3
in cell D4 =C3&"-"&NL("ROWS",table with sales type,"TYPE",your filter logic…)
put a HIDE on A3 (so row three won't show), and put a HIDE on A5 (so row five won't show)
for your SHEETS command, reference D4:D5 on your logic sheet as the table to pull the replicators from.
then you will need to pull the options apart for your report logic on that page (because NL-B2B won't mean anything to the table that NL comes from)
this can be done using the "LEFT" and "RIGHT" commands in Excel
assuming your Sheets command is on Cell B4,
C4 = LEFT(B4,FIND("-",B4)-1) this will give you characters BEFORE the dash
D4 = RIGHT(B4,FIND("-",B4)) this will give you the characters AFTER the dash
convoluted, but it may work for you -
Jet Reports Historic Posts Thx Heather for the suggestion.
I already tried that approach. See the attached report.
But it seems, as Teresa states in her post, that NL("Sheets") is evaluated before anything else.
I guess it is impossible for NAV connections
rmw -
Jet Reports Historic Posts Dirty hack time. This depends on comfort level with using the admin side of NAV, and your license. More detailed instructions available upon request.
First create a table in Nav called CountrySalesperson or similar, with three fields in it, one for country, one for salesperson and one for the combination (probably of type text 30).
If your license allows you to do it, add something to the code attached to both the country table and salesperson table that adds new combinations to your table when records are added or changed. I wouldn't worry about deleting or anything as having redundant records won't hurt.
Alternatively, write a report that goes through all the possible combinations and adds them if they aren't present. Downside is you have to remember to run the report occasionally. To aid this, you could write a check that flags up if a combination you asked for in your options doesn't appear as a sheet.
Once you've done that, you can build your report as before but use your new table to generate your NL Sheets.
Possibly too much work for what you want to do, and there may be a nice way to streamline some of it depending on your version. -
Jet Reports Historic Posts or - and yes, this isn't the most elegant method - have a Jet report build your list to generate sheets from, then past that final data as a table into the report that creates your different worksheets.
not elegant, but it does get the job done if this is how you have to do it.
Please sign in to leave a comment.