Sage 100

I have a client that runs three Sage reports and then exports them as CSV files, then copies the files to a folder on their network once a month. She does the GL Trial Balance and two custom Sales Order Reports I created in Crystal. She is exporting first to Excel because Crystal can't produce a correct CSV.

She does all this manually. Their parent company, the one that ultimately uses the CSV files for consolidations, is now asking that the process be fully automated and all 3 reports are to be run twice a day, once during business hours and again after hours.

First problem is how to enter the report parameters.
For the GL Trial Balance, I know I can save the report settings, but the start and ending periods needs to be set. It could be calculated from today's date and where today falls in their GL Fiscal Year (4,4,5 week ending dates). Not sure how to do that.
For the two custom reports seems like I could eliminate the date range parameter and replace it with code, will need some help with that.

Second problem is how to automate the reports to run twice daily.
I have never actually had a reason to use the Sage Scheduler, so I am a complete idiot on how that works. I have heard about Logicity and Visual Cut but have never used them and not sure of their capabilities.

Third problem is how to get Crystal to output a proper CSV file.
Maybe I am just missing the correct settings, but the only way I have been able to do this in the past was to make one big all encompassing concatenation formula to "build" the comma separated values record.

Any and all advice welcome, TIA

------------------------------
Greg Stiles
S & W Microsystems
Torrance CA
310.787.1010
------------------------------

I think Visual Cut is the preferred choice of many 90 mindsers, but we set many of these up with either
Jeff-Net Report Runner Batch or Sage Alerts
I know Report Runner gives you alot of control over parameters, but for automation sake,you may end up using formulas based off of 'today.'  It also lets the output be txt, csv, whatever.  Far as I know, Crystal does OK with csv files.  I typically make my fields butt up against one another if I'm planning to export.  It can be tricky if you have a sub report, though.
We use scheduled tasks to run the report runner jobs.


------------------------------
==================
Rhonda McNamara
Customer Success Manager
Stewart Technologies, Inc.
rsm@stewarttechnologies.com
------------------------------

I had a similar issue except I was exporting sales orders created/modifed for the day. I used Visual Cut which also lets you specify parameters. The customer saves this data to a folder on their server and then it is transmitted via FTP.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

We do exports to CSV all the time with Visual Cut.  Dynamic date parameters are easy with VC.  With really tricky formatting requirements, consolidating everything into one long concatenated string (building in the quotes and commas manually) then exporting using a text file format can solve that... but direct CSV exports usually work.
I've also done button script exports, and external vbs to query and write data to a file.
The big problem is your work table based report... I have no tips for that one.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 09-26-2019 14:41
From: Wayne Schulz
Subject: Automating Crystal Reports that are CSV Exports

I had a similar issue except I was exporting sales orders created/modifed for the day. I used Visual Cut which also lets you specify parameters. The customer saves this data to a folder on their server and then it is transmitted via FTP.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

Thanks Everyone,

Watched the videos of Visual Cut and requested the eval copy.
Seems like I will have to re-write the GL Trial Balance report.
Need to look again at the client's reports and see why they were exporting to Excel first.

Thanks again.

------------------------------
Greg Stiles
S & W Microsystems
Torrance CA
310.787.1010
------------------------------

Original Message:
Sent: 09-26-2019 14:51
From: Kevin Moyes
Subject: Automating Crystal Reports that are CSV Exports

We do exports to CSV all the time with Visual Cut.  Dynamic date parameters are easy with VC.  With really tricky formatting requirements, consolidating everything into one long concatenated string (building in the quotes and commas manually) then exporting using a text file format can solve that... but direct CSV exports usually work.
I've also done button script exports, and external vbs to query and write data to a file.
The big problem is your work table based report... I have no tips for that one.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 09-26-2019 14:41
From: Wayne Schulz
Subject: Automating Crystal Reports that are CSV Exports

I had a similar issue except I was exporting sales orders created/modifed for the day. I used Visual Cut which also lets you specify parameters. The customer saves this data to a folder on their server and then it is transmitted via FTP.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990

Yes! Ask "Why?" five times. Good advice.

------------------------------
==================
Rhonda McNamara
Customer Success Manager
Stewart Technologies, Inc.
rsm@stewarttechnologies.com
------------------------------

Original Message:
Sent: 09-27-2019 12:50
From: Greg Stiles
Subject: Automating Crystal Reports that are CSV Exports

Thanks Everyone,

Watched the videos of Visual Cut and requested the eval copy.
Seems like I will have to re-write the GL Trial Balance report.
Need to look again at the client's reports and see why they were exporting to Excel first.

Thanks again.

------------------------------
Greg Stiles
S & W Microsystems
Torrance CA
310.787.1010
------------------------------


Original Message:
Sent: 09-26-2019 14:51
From: Kevin Moyes
Subject: Automating Crystal Reports that are CSV Exports

We do exports to CSV all the time with Visual Cut.  Dynamic date parameters are easy with VC.  With really tricky formatting requirements, consolidating everything into one long concatenated string (building in the quotes and commas manually) then exporting using a text file format can solve that... but direct CSV exports usually work.
I've also done button script exports, and external vbs to query and write data to a file.
The big problem is your work table based report... I have no tips for that one.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-26-2019 14:41
From: Wayne Schulz
Subject: Automating Crystal Reports that are CSV Exports

I had a similar issue except I was exporting sales orders created/modifed for the day. I used Visual Cut which also lets you specify parameters. The customer saves this data to a folder on their server and then it is transmitted via FTP.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990

Answer might be "that's the way we have always done it!" LOL
If I remember correctly there were sub reports. I don't have full time access to their server so can't check until they let me in.

------------------------------
Greg Stiles
S & W Microsystems
Torrance CA
310.787.1010
------------------------------

Original Message:
Sent: 09-27-2019 15:58
From: Rhonda McNamara
Subject: Automating Crystal Reports that are CSV Exports

Yes! Ask "Why?" five times. Good advice.

------------------------------
==================
Rhonda McNamara
Customer Success Manager
Stewart Technologies, Inc.
rsm@stewarttechnologies.com
------------------------------


Original Message:
Sent: 09-27-2019 12:50
From: Greg Stiles
Subject: Automating Crystal Reports that are CSV Exports

Thanks Everyone,

Watched the videos of Visual Cut and requested the eval copy.
Seems like I will have to re-write the GL Trial Balance report.
Need to look again at the client's reports and see why they were exporting to Excel first.

Thanks again.

------------------------------
Greg Stiles
S & W Microsystems
Torrance CA
310.787.1010


Original Message:
Sent: 09-26-2019 14:51
From: Kevin Moyes
Subject: Automating Crystal Reports that are CSV Exports

We do exports to CSV all the time with Visual Cut.  Dynamic date parameters are easy with VC.  With really tricky formatting requirements, consolidating everything into one long concatenated string (building in the quotes and commas manually) then exporting using a text file format can solve that... but direct CSV exports usually work.
I've also done button script exports, and external vbs to query and write data to a file.
The big problem is your work table based report... I have no tips for that one.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-26-2019 14:41
From: Wayne Schulz
Subject: Automating Crystal Reports that are CSV Exports

I had a similar issue except I was exporting sales orders created/modifed for the day. I used Visual Cut which also lets you specify parameters. The customer saves this data to a folder on their server and then it is transmitted via FTP.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990