@Chris Powers,
@Jo deRuiter has given you some good perspective
(though she persists in spelling "refexes" as two words!).
One nice thing about do-it-yourself refexes is that you may create them to make use of parameters in the embedded table and view queries... or you may use stored procedures with parameters. For an extreme example, I created a refex that duplicates the output of the Receivables Management Historical Aged Trial Balance (RMHATB), based on a stored procedure with
32 parameters.
Then, of course, as you've pointed out, you may add onto the refex with formulae, pivot tables, slicers, and charts. Lots of good reasons to use refexes over SmartLists.
In my opinion, the only advantage -- and it's a
good one -- to using SmartLists is that the Search capability allows you to alter the number, order, and type of filter conditions very nicely. Can't do that in a refex.
One bit of advice: for any refexes you create, use the Microsoft Query capability instead of any wizards. You may then change the Connection string in the Excel workbook's Connection Properties to use a DSN-less connection. I use a standard connection string like this:
DRIVER=SQL Server;
SERVER=<my server>;
Trusted_Connection=Yes;
DATABASE=<my database>
That way the workbook can be moved to a different workstation or server without having to ensure that the DSN is set up properly on the new machine.
Anyway, my two cents...
Regards,
------------------------------
"Sparkly" Steve Erbach - Business Analyst
GLC Minerals, LLC - Green Bay, WI
Co-Chair, GPUG WI (Green Bay) Chapter
GP 2016 R2 (16.00.0579) / MR 2012 CU16
Blog:
https://www.gpug.com/blogs/steve-erbachTwitter: twitter.com/serbach
------------------------------
Welcome to Dynamics!
You do not have permission.
Call Steve.
(hat tip: Lou Spevack)
------------------------------
Original Message:
Sent: 04-11-2018 11:49 AM
From: Chris Powers
Subject: Refresh-able Excel Spreadsheets
Hello all,
I've been a lurker for quite some time but, wanted to discuss anyone thoughts on refresh-able excel spreadsheets. I'm a new-ish (about a year) GP user but familiar with several different kinds of accounting software.
From an IT point of view, are there any disadvantages to using refresh-able spreadsheets? When I say "refresh-able" I don't mean a smartlist, I mean an excel connection straight to the SQL Server Database with a predefined query that you set it up with.
Personal examples that I use these for are transactions for every company (limited by GL, year, etc based on what spreadsheet it is in), refresh-able trial balances (Query for the current active chart of accounts use these hard coded results to SUMIF against a month by month TB), query for unposted batches, intercompany transactions, and etc.
I'm a huge believer in keying in numbers is an Accountant's number one waste of time and switching between companies are GP user's number one waste of time (especially if your server is slow) but, am not familiar enough with GP to ensure that if we were to roll these out company wide that it doesn't effect anything on the back end (user licenses, etc).
Thank you!
------------------------------
Chris Powers
Senior Accountant
Grand Rapids MI
------------------------------