I produce an accounts receivable aging reports weekly for each of our account managers, with each manager only receiving information that is intended for his team (Global Dimension 2 in NAV). My current process is to run the report with an NL(Sheets) function and each team report would be on a separate tab. I would then copy the tabs for each manager to a new workbook and then send accordingly. (Some managers have multiple 'teams' that they are responsible for).
I was able to create a batch template based off of the demo video and successfully send the report via email to each manager based on the filter of Team. However, since some managers are responsible for multiple teams, they will receive multiple emails. I would like to consolidate this function somehow but I am unsure how to code this in the batch template. For example, for our BMW client, we have three teams based on our specific products offerings: BMW, BMWDBI, and BMWLDS. But no matter how I try and put this on the Batch Template file Batch List, it isn't producing the report properly.
Any suggestions?
Thanks!
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Jlhudy,
Does your batch template have a line per Team? Then my suggestion would be to create a line per Team manager, and put all his/her Teams in the /P options in one single filter. It could be something like Autopilot …. /P "TeamFilterInReport" "BMW|BMWDBI|BMWLDS"
Then your report would be created with 3 sheets, one for each team and sent as a single email.
Does this work for you? -
Jet Reports Historic Posts Hi Jlhudy,
Does your batch template have a line per Team? Then my suggestion would be to create a line per Team manager, and put all his/her Teams in the /P options in one single filter. It could be something like Autopilot …. /P "TeamFilterInReport" "BMW|BMWDBI|BMWLDS"
Then your report would be created with 3 sheets, one for each team and sent as a single email.
Does this work for you?
Hi Hans!
I do have it set up that way - one line per team (I just used an NL Rows function to pull a list of all the teams, but I suppose this can be done manually as well). So under "Batch List", I would want to list each Manager name, then my Range1 can remain as "TEAM" since this is what I want to filter the report by, then hardcode "Value1" as the teams included for that manager? -
Jet Reports Historic Posts Hi again,
Well, as I have no idea which tables you are using I have different suggestions:
- of course you could hard code your batch file and enter a line for each team manager;
- you could also hard code a list of team manager and the team filte in Excel, like
PERSONA TEAM1|TEAM5
PERSONB TEAM2|TEAM3|TEAM4
with your NL(Rows) you read the team managers from your database. next you need a VLOOKUP to your Excel list to get the filters from.
- use the NL(Rows) to retrieve the team managers and then do another NL to retrieve the teams from your database to generate a filter. Now, I don't know how your data are structured but it the retrieval and filter generation could look like
=NP("Join", NL("Filter","TableWithTeams","FieldWithTeamCode","FieldWithTeamManagerCode",CellRefToTeamManager), "|")
Some ideas for you to try out to see what suits best for you. If it does not work, please upload your Excel file so that we can actually seeing what you are doing, plus some info about the tables and fields that contain Team manager and Team data. Thanks and have a good day :)
Please sign in to leave a comment.