A client wants 10 salespeople to be able to view one-day-old receivables information, each salesperson looking at their specific customers only. The report could be a PDF or an Excel spreadsheet. They have created a folder for each salesperson on their server, and that's were they would like each report sent.

They want the reports updated "automatically" every evening. They are comfortable using Task Scheduler.

I know I can use GridView to generate the report, and build 10 filters, one for each salesperson. But how can I have it "automatically" create 10 Excel spreadsheets, each in a different folder depending on the filter.

I might add that it takes a few minutes to recalculate the GridView, so the fewer recalcs needed, the better.

Not as critical, but they have two sister companies, with shared salespeople. It would be ideal if each salesperson's report contained two customer lists - one for each company. In Excel this would mean two separate sheets. If this is not feasible, they are OK with getting two separate reports.

Thanks for any suggestions you might have, especially if they involve GridView functions I am not familiar with.

Steve

Hi Steve,

Here is the complete text from the Help file regarding using Command Line switches for the scheduled task and the results for the automated Excel files created. Note that the separation by salesperson would be accomplished by the UserID and the XDP parameter, rather than a user-specified salesperson folder location assigned in the Filter.

XDView Parameters

XDView parameters respect the Allow export option on the Edit Definition window. For workspaces containing multiple views, only those views with the Allow export option enabled will be sent to Excel.

/XDV:<workspace> or /xdv:<workspace> - Sets the location from which to both load the view and to save the resultant Excel workbook. Enter the full path to the workspace. The Excel workbook receives the GridView workspace name and the individual Excel sheets inherit the GridView views' names.

/XDW:<workspace> - Launches GridView and sends the named workspace (or View) to Excel without operator intervention. Enter the full data path to the workspace or View. The Excel workbook is placed in the subfolder x:\data\XDViews (where 'x:\data' is the path to your data) with the same name as the workspace (or View). The View is refreshed prior to the ExcelDirect function being performed. A log of the automated export is created in the file x:\data\XDViews\GVLog.log.

/XDF:<sheetname:filtername> - Selects the named filter "filtername" for sheet "sheetname" in a workspace. The filtername is separated from the sheetname by a colon. Only enough characters of the filtername to uniquely identify it need to be specified. You may use multiple filternames on the command line. If the filter cannot be found for that sheet, an error is reported in the log and the View or Workspace will not be sent to Excel. (Must be used with /XDW or /XDP).

NOTE: When spaces exist in a "sheetname" and/or "filtername", make sure that quotation marks surround the entire "sheetname:filtername" on the Command Line.

/XDP:<workspace> - Launches GridView and sends the named workspace (or View) to Excel without operator intervention. Enter the full data path to the workspace or View. The Excel workbook is placed in the subfolder x:\data\XDViews\UserID with the same name as the workspace or View (where 'x:\data' is the path to your data and 'UserID' is the ID of the user logged in to GridView).

/XDD - Creates a subfolder with today's date to hold the Excel workbook under x:\data\XDViews or x:\data\XDViews\UserID if XDP is specified (where 'x:\data' is the path to your data and 'UserID' is the ID of the user logged in to GridView). Folder names take the form yyyy-mm-dd and can be used to separate the ExcelDirect runs from different dates. (Must be used with /XDW or /XDP.)

Hi Softrak

I don't know how using the /XDP parameter will accomplish what the client wants. The accounting clerk is logging in with her ID, and wants to generate 10 separate spreadsheets/reports, one for each salesperson, each in a separate folder. The idea of her having to log out and in 10 times with a different ID each time is not very appealing, and certainly does not fall under the category of "automatic".

So I guess I'm still soliciting ideas.

One idea that comes to mind is to write an Excel macro (using Virtual Basic?) that takes an unfiltered spreadsheet that GridView can write to the XDVIEWS folder, and divvy it up into 10 spreadsheets/reports (maybe using the Data Filter function). But I don't know Excel well enough to write the macro. Does anyone out there think this can work?

Thanks

Steve

Who said anything about logging in 10 times?

This is going to run overnight as a scheduled task. The batch file will contain 10 separate invocations of GridView.EXE with different filters specified on each invocation. The resulting spreadsheet will be placed in a known Folder. The next line in the batch file copies the workbook to its final resting place for the salespeople. Then the workbook is deleted and the next GridView.EXE with a /XDW parameter is invoked.

Think about creating a multi line "batch file" with all the correct /XD parameters on the individual lines and then scheduling the running of that file.

G:\Softrak\GGVWin\GVWin.exe /..........
G:\Softrak\GGVWin\GVWin.exe /..........
....
....
G:\Softrak\GGVWin\GVWin.exe /..........

It will be able to get you exactly what you want.

Hi Michael

That's good, I will think about it. I forgot about the batch file concept, which is odd because I do that all the time for other solutions.

Thanks

Steve

PS - Is there an example of the use of the XDF parameter anywhere? I know I will need that parameter, but my brain can't wrap itself around the Help instructions.

D:\RD\Softrak\GVWin\GVWin.exe /c /usys /psys
/d:D:\RD\Softrak\APWIN\SAMDATA
/e:SAM
/xdw:"D:\RD\Softrak\APWIN\SAMDATA\Views\VendorBalances.gvs"
/XDF:"GVVendorBalance:NotZero"
/F:"D:\RD\Softrak\APWIN\SAMDATA\Views\User"

The above will automatically load the WorkSpace

D:\RD\Softrak\APWIN\SAMDATA\Views\VendorBalances.gvs

Apply the filter "NotZero" to the View "GVVendorBalance"

Send the result to Excel

Save the XLS(X) in

D:\RD\Softrak\APWIN\SAMDATA\Views\User\XDViews

Create a log file called GVLog.log

Time to start experimenting....

I tested this and /xdf didn't work for me. If I can't get it to work after some more fiddling, I'll send my work to tech support to troubleshoot.

Steve

Workspace with a single view may not apply the filter, the work around is to Add a 2nd View to the Workspace and and set the recalc Order..

File | Set Workspace Recalc Order

Any View you want to go out to Excel must have Allow Export

If you don't want the 2nd View to be exported untick Allow Export

If you must send your work use

File | Gather files for support

and zip collection and upload to data repair attention Michael