Sage 100

Opinion: There are like 10 ways to export data from MAS90. The process needs to occur on 3 separate occasions during the life of the project. My opinion is to use the Business Insights Reporter wizard. Create a very wide Crystal report and then export to excel from that report. This engine seems very fast with large data sets, very user friendly UI and allows you to pull in pre-linked tables easily. Anybody have a strong opinion on an alternate way?

Go with what you (and the person doing the export) are comfortable with............... Depends on the # of tables and the amount of manipulation. I prefer Excel Queries and Pivot tables. Works great with minimum # of tables to link. When you want to pull the data the 2nd and third times, just refresh the query. Dan Burleson would do the same thing in Access and Beth B would write a crystal report. Many ways to approach. Pick your favorite.

I'm with @JeffSchwenk I love Excel Quieries and Pivot Tables. They are so easy to use and to refresh but you are limited to # of tables on one sheet. I do add additional excel sheet with other tables(Quieries) and then use vlookup to add the additional info/fields to the original sheet before I do the pivot table...

I've used BIR on several occasions for the reasons you describe. I think one limitation is that you need to be in MAS 90 to initiate the report, so you may have issues with unattended reports. Using Crystal, along with a scheduler, should overcome that problem. On occasion, I've also had issues converting BIR reports... which required re-creating them.

Oooooh, someone else uses PT's????????

Starting to use them more than crystal for quick reports and clients that can't make up their minds on how they want to run the report..... I'm sure you are much more advanced at it then I am!!!

I just did a decent sized export job and used MS Access. Like Jeff said though - multiple tools will get you there depending on what is available. I've not had super great success with Crystal for exports if there is a requirement for fixed width.

As Diane mentioned, Excel queries for quick and dirty ad hoc reports that are usually one offs. Crystal once a client figures out what they want on a repetitive basis. If the report pulls from a large table (e.g. AR History) the Crystal is written to pull through the Access PTQ. If it requires hard core data manipulation, the Access with a menu driven user interface.

If there is a possiblity of using a Pivot Table - Jeff Schwenk will be all over it! My two cents - if you ultimately want the data in Excel - then set up a silent connection, use Excel to write a query for what you want - save that query to the desktop. Clicking the icon on the desktop will execute the query as you need it, and if there are selection criteria - open the query file with notepad and change the criteria, (for date ranges and the like)

My only problem with excel, is the odbc setup screen is very old and very small, you can barely see the table names and read the full field names with that screen format in excel, it must be 15 to 20 years old

Yes, the selection screens can be a royal PITA. Randy, regarding selection criteria, these can be set up in Excel as a user interface. Very easy to do (if I can remember how).

With all of the excel investment that is gone into the user interface and many dumb features features, it's amazing the odbc screens still date back to like excel 2000 or something

You can edit the query in MS Query and continue to run in Excel. Being an old cut paste and add I used to use Excel and autofilter as my main method, but alas I have reached a level of incompetency and rarely am allowed to touch data.

Isn't another issue with BIR that it uses separate work tables. That makes the reporting very fast however as soon as someone asks you to add a field that's not available (or linked, etc) you're possibly going to be working the weekend to re-develop BIR ... I'd love to be wrong on this but I think I remember that from when they initially introduced BIR.

@WayneSchulz One of the benefits is that you can modify the report with Crystal afterwards... so linking tables is easy cause you're linking to the temp table. You'd only have to recreate if your linked tables should be part of the temp file.

I must be doing it a different way or not understanding what you are saying . My selection screens for the tables are fine to read. In excel 2007 and 2010.

I think he is referring to Excel 2000/2003.

@DianeRuth As you can see, that window is fixed size and on my 24"" monitor is tiny compared to my available real estate. MAS has hundreds of tables and scrolling and selecting tons of fields on a screen smaller than my smartphone is a PITA. In this day and age, why isn't this dialogue resizable??

MC, did you mean to attach a picture?

I'm referring to Diane's picture From: do-not-reply@socialcast.com [mailto:do-not-reply@socialcast.com] On Behalf Of Jeff Schwenk Sent: Friday, July 06, 2012 9:45 AM To: Mark Chinsky Subject: Re: [90 Minds Consulting Group] Opinion: There are like 10 ways to export data from MAS90. The process needs to occur on 3 separate occasions during the life of the proj...

Oh gotcha Mark, but since I have everything in Large Font LOL. I didn't even realize it couldn't be resized.... The eye's are not what they use to be :)

Is this a MAS window or a Excel issue? VI in MAS has the same issue which I blame on Sage's TLTF attitude.

I think Mark is saying it is the MAS ODBC - Sage TLTF issue.....

No its an excel issue From: do-not-reply@socialcast.com [mailto:do-not-reply@socialcast.com] On Behalf Of Jeff Schwenk Sent: Friday, July 06, 2012 10:08 AM To: Mark Chinsky Subject: Re: [90 Minds Consulting Group] Opinion: There are like 10 ways to export data from MAS90. The process needs to occur on 3 separate occasions during the life of the proj...

I would use Access, easier and faster. With coding you can do just about anything. I once worked on a project with a VAR in which they had to extract data from MAS each night and post it to a FTP site so the headquarters in Sweden could pull the data. Worked like a charm and did not take long at all.

That dialog box is part of MS Query which hasn't been updated since before Excel 4. One good aspect of it is its consistency and reliability. I disagree with Jeff and Diane, MS Query can be used for complex reports when the customer demands easy to use Excel based output. Crystal exported Excel sucks for presentation. Check out the attached scheduling workbook that I created using 9 MS Queries. It ensures organic chocolate is always available at my local grocery store! :-)

BTW, I think, believe it or not, I'm going to go with VI Export Jobs. They seem to be the easiest to create, easiest to rerun and re-edit and produce the most useful output. That being said, as @DanBurleson mentioned, the default using Crystal to Export Excel sucks because you are limited to the amount of columns you can fit across the printed page in your export. If you wanted to export every column of the AR History Detail, it ain't ever gonna fit on a Crystal report in one row... However, I figured out a nice workaround. Use something like PDF995 (free), and set a custom print size of 200"" (its max) and you have lots of real estate. That being said, AR History detail equaled something like 500"" of paper so I found an option in the PDF995 printer setup called 'scaling' which shrinks everything printed. I set it to about 40% and got the equivelent of a 500"" wide piece of paper, more than enough to get every field, including UDF's to fit in a row and thus export nicely with column names.

That's darn impressive Dan.

I use the HP DesignJet drivers because they are pre-installed on Windows and they have a Custom Size option for setting your desired width. I also wrote an Excel enhancement that implements a macro language for formatting Crystal based Excel exports. I call it 'menu-eze' because the langauge is that of the Excel 2003 menus and commands. (i.e. Format, Column, Width, ColumnWidth=14; Edit, Copy) Then MS got rid of menus - F#&K! Giant step backwards.

@DanBurleson what I was saying is that you really don't want to link more than 2 files together for one Query sheet. I can have more than one Query sheet per report and the Pivot Table gives the customer more flexibility on how they want to run the report. With that said, very impressive report !!!! @MarkChinsky thanks for the clarification !

One more voice. We use many methods as well; Crystal (sucks with fixed widths and needs fix for comma seperated files over a certain column width), MS Access (regular queries and PTQ's), Linked Libraries in MS SQL, and MS Excel. Each one works better in certain situations. We only use Crystal for reports that will be run repetativly from the MAS menu. Otherwise, too much overhead, even with BIR. The client is never happy with what comes out as default. We use MS SQL when the extracted data will be used in a SQL environment. Linked Libraries allow you to bypass multiple steps. (BizInsights uses these so look at thier instructions on how to create) Our prefered is Access or Excel. If we need to do a lot of manipulation to transform the data or pull from multiple companies, we use Access macros to lay out the transformation process and manipulate the ODBC connection string. If a lot of manipulating is NOT required, we default to Excel. Someone mentioned having to go into MS Query to change parameters. The below link will show you how to create a parameter query in MS Query and pass them from an Excel spreadsheet, making your Excel report dynamic. If you haven't tried this, you should. You will be a hero to your clients. We use these to extract account balances and statistical data for use in dynamic allocations which in turn become import data for a VI Job (which gets triggered from a command button on the worksheet). http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP005199548.aspx Happy computing!