Sage 100

Many clients ask about sending reports to excel. I show how to do this but also show how to query data from excel using the SOTAMAS90 DSN. Of course Premium (SQL) does not have this DSN. Anyone have a best practice for using excel to access their data? Manually setup a DSN on each workstation or is there a better more direct option that I am not thinking of.

You could set up a DSN, though Excel can go direct to the SQL Server and keep the connection details inside the Excel workbook. We also use an Excel add-in (Vivid Reports Flex) to allow the users to drill-down inside Excel, like AR Aging, drill on customer shows invoices, drill on invoices, shows the line items, all on the same Excel worksheet.

We are setting up an SQL ODBC on each computer manually. I don't like it but that is all that I know to do. Cooper

@SteveIwanowski I think I have looked at the direct SQL and it is one table at a time correct?

If the report is 'simple' which is often the case, I teach them all of the features on custom lookups since these are a quick export to excel.

@MoiraGoggin ALEs?

It doesn't have to be. The wizard makes it easy to do a single table, view or stored procedure, but you can always go back into the Connection Properties and edit the SQL to incorporate more tables/views/etc.

@LarryBradford Yes.

We'll also use VBA/Macros to allow parameters to be assigned from Excel. Usually for date ranges.

@SteveIwanowski , I know all of these individual tools but would love to see how to put them together efficiently.

@MoiraGoggin , I do them same for the simple ones.

Do you have something specific in mind (hopefully simple)? I could try to whip something up.

Ah, I do have one handy. Attached is an Excel file that uses a stored procedure (that looks at Production History Details) as a data source. The user modifies the date ranges on the Pivot tab, and then Excel updates and executes the stored procedure, which refreshes the records in the Data tab. The user can then refresh the pivot table to look at their production performance per bill per component (the users adds conditional formatting after the fact, though that could be in the macro, too). So this has an Excel connection direct to SQL, a dynamic stored procedure, VBA macros, and a pivot table. Everything an analyst needs :)

Sweet. Thank you. I will take a look. This particular task was triggered by me sending a little 3 page instruction sheet for querying using Sage's ODBC. That triggered one of my 4 SQL clients to ask how this works for them. I was looking for the most efficient and simplest connection for the client and to setup a new set of instructions for SQL.

Sage Intelligence is more than just financials and all reports output to Excel. You may want to consider it. I think the unlimited everything, viewer, manager, designer, connector (for 1-3 company codes) is stlll $99 per month. With connector you can connect to nearly any data set so not just Sage.

I really like the way SI serves up the reports and keeps them in a central repository as well. We were recently certified and during that process reevaluated the value of the data mining options of the tool and not just using for financials. Still like my Crystal Reports though.

Crystal still has lots to offer but when the customer wants Excel output, SI shines.

@LarryBradford - I have done all the above - not a fan of SI - too much work and limited on licenses and also for people who might not have sage.(if using PVX you have to have sotamas90 drive installed to access those files) But another option that I have used is a ""file"" DSN. Put this one file on the sage directory and just reference it in excel. So when anyone opens this same excel file it has the credentials to access the SQL or pvx data.