Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

Refresh-able Excel Spreadsheets

  • 1.  Refresh-able Excel Spreadsheets

    Posted 04-11-2018 11:50 AM

    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
    ------------------------------


  • 2.  RE: Refresh-able Excel Spreadsheets

    TOP CONTRIBUTOR
    Posted 04-11-2018 11:57 AM
    ​Hi @Chris Powers

    Using the Excel Refreshables in no way impacts licensing - it's coming to the point that only users who need to enter transactions (write) need licenses.  To query ​the GP databases without license fees is now easy through Excel, Power BI, SSRS,  Excel Integrated reporting, etc.


    *Caveat* the user's Active Directory account will be used in SQL to query the db.  So, if you have limited SQL licensing it could become a factor*

    The only "downside" you may run into is security.  You will need to use the 'rpt' security roles in SQL to limit access to some reports - you will also probably want to work in AD security on the file structure to limit or grant access.

    Also, be aware that if you have a ton of data and quite a number of users trying to kick of huge queries at once it could put a strain on SQL.


    Those are all I can think of right this second.

    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Senior Financial Systems Consultant
    Advanced Credentialed Professional-Dynamics GP
    Heartland Business Systems, LLC
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 3.  RE: Refresh-able Excel Spreadsheets

    Posted 04-11-2018 01:13 PM

    Hi @Jo deRuiter

    I appreciate your response.

    ​​​The way that I decided to create these is using excel's data connection tool directly to SQL Server. I will keep in mind the licensing but I believe we have enough that it shouldn't be an issue as long as everyone isn't running one at the same time while they are also logged into GP.

    Is it really an issue for security? As I use the data connection tool to link to SQL Server these queries are not saved directly in SQL Server at all to my knowledge, excel links, runs the query, and returns data. My thought process behind this was having the excel files saved locally with the queries embedded into the excel docs so only the user who has the document could run the query. These excel docs could also be password protected if they were saved on any kind of shared drive. If I'm incorrect in my assumption and these are being saved in SQL Server in some way my other thought was as long as you had read access but, no physical access to SQL Server, you could still use the sheets as GP is connected and you have no need to interface with SQL Server directly. In our organization most people don't have access to SQL Server management studio.

    On the flip side, in excel you must do a bit of digging to actually get to the SQL of which ensures that unless a user is trying really hard to mess something up, they really can't mess up the SQL in any way.

    As for performance on the server we're not running anything quite large enough to bog down the system but, I could see how this would be an issue with simultaneous use but in reality, I don't believe that to be a likely issue.

    I would appreciate your thoughts on my logic above.

    Thank you!

    P.S: Sorry for the messaging, I realized my inbox wasn't updating correctly due to an internet connection issue. 



    ------------------------------
    Chris Powers
    Senior Accountant
    Grand Rapids MI
    ------------------------------



  • 4.  RE: Refresh-able Excel Spreadsheets

    TOP CONTRIBUTOR
    Posted 04-11-2018 01:19 PM
    ​Hi @Chris Powers

    Your logic is excellent!  The queries are stored in Excel and run from Excel.  However, Excel will still expect a user ID security for read permissions on the data.  Some people hard-code a user ID in so that any user can run it (NOT what I would recommend) and some people leave it as Active Directory based - which means you have to add the users AD logins to SQL Server with read access.

    ​I would definitely password protest any sheets with confidential data, and I would also keep a backup folder of anything you create in Excel (or make sure you have an easy-to-get-to backup for restore purposes).  Users can and do occasionally delete columns or change things that can't be undone.

    I, personally, LOVE Ref Exes.

    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Senior Financial Systems Consultant
    Advanced Credentialed Professional-Dynamics GP
    Heartland Business Systems, LLC
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 5.  RE: Refresh-able Excel Spreadsheets

    Posted 04-11-2018 02:12 PM

    Hi @Jo deRuiter,

    Thank you for your help! I did not code a hard user ID and the permissions are based on the active directory so they do have read permissions in SQL Server. In the event of the worst case scenario I figured it simply wouldn't work for someone that doesn't have those permissions.

    We don't have much if any confidential data as management made the decision some time ago to remove payroll from GP, I would assume for this reason. ​​​

    As for the changes in excel, I've never actually seen something that couldn't be undone. I'm using Excel 2016 and if I delete any row, column, data, and etc if I click refresh it treats it as a fresh run of the query and anything I deleted comes back (I've tested on multiple spreadsheets). While backups will be done on a day to day level I'm not seeing it as much of an issue as you can always hit back or just refresh the spreadsheet.

    I appreciate your help as I'm an Accountant so the back end leaves me with questions on the system back end at times. 

    Thank you!



    ------------------------------
    Chris Powers
    Senior Accountant
    Grand Rapids MI
    ------------------------------



  • 6.  RE: Refresh-able Excel Spreadsheets

    TOP CONTRIBUTOR
    Posted 04-11-2018 06:00 PM
    ​Hi @Chris Powers

    Since you are an accountant and now writing queries, you may be interested in a webinar that I am giving in May here on GPUG.



    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Senior Financial Systems Consultant
    Advanced Credentialed Professional-Dynamics GP
    Heartland Business Systems, LLC
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 7.  RE: Refresh-able Excel Spreadsheets

    GPUG ALL STAR
    Posted 04-11-2018 05:47 PM
    @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-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 8.  RE: Refresh-able Excel Spreadsheets

    TOP CONTRIBUTOR
    Posted 04-11-2018 06:04 PM
    @Steve Erbach


    I stand by both my spelling AND pronunciation  Ref Exes!!!  Forever!!!


    Image result for stubborn child

    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Senior Financial Systems Consultant
    Advanced Credentialed Professional-Dynamics GP
    Heartland Business Systems, LLC
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 9.  RE: Refresh-able Excel Spreadsheets

    GPUG ALL STAR
    Posted 04-12-2018 07:38 AM
    @Steve Erbach & @Jo deRuiter

    You're both wrong !   it's  SSERF and nothing else.. basta !


    PS: SSERF stands for Self-Service Excel Refreshable Files... ​​

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14
    ------------------------------



  • 10.  RE: Refresh-able Excel Spreadsheets

    GPUG ALL STAR
    Posted 04-12-2018 10:24 AM
    @Beat Bucher & @Jo deRuiter,

    So! It's to be a war of words, is it? Bring it!



    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-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 11.  RE: Refresh-able Excel Spreadsheets

    Posted 04-12-2018 06:29 PM
    Steve,
    I can't remember if we talked about this before but if you can teach your users a little bit of syntax you can can provide them with multiple parameters they can update in excel to alter the where clause of the query.

    I like to default in wild cards and set the query to not run until the user exits the field. If the field is a specific data type (especially dates) I pre-enter a likely parameter in the correct formatting so the user can easily see the required syntax as an example before updating it to fit their needs.

    ------------------------------
    Kirk Livermont
    Assistant Operations Manager
    J. Rousek Toy Company Inc.
    Bishop CA
    ------------------------------



  • 12.  RE: Refresh-able Excel Spreadsheets

    GPUG ALL STAR
    Posted 04-16-2018 10:39 AM
    @Kirk Livermont,

    >> I can't remember if we talked about this before but if you can teach your users a little bit of syntax you can can provide them with multiple parameters they can update in excel to alter the where clause of the query. <<

    I can't remember either... but, like, I'm 65 and the memory engrams ain't what they used to be!

    I'm trying to visualize what you might mean by "multiple parameters to alter the where clause". If one uses a stored procedure then the "query itself" could be a dynamic query with where clauses that could be built up from pieces. Not simple to set up, however... and certainly not something an Accounts Receivable clerk should have the ability to do.

    Can you give me an example of what you're talking about?

    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-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 13.  RE: Refresh-able Excel Spreadsheets

    GPUG ALL STAR
    Posted 04-16-2018 10:52 AM
    @Jo deRuiter,

    I think you should use that grumpy little girl​ as your profile pic!

    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-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 14.  RE: Refresh-able Excel Spreadsheets

    TOP CONTRIBUTOR
    Posted 04-16-2018 10:53 AM
    @Steve Erbach

    With my mood today, she would be perfect!!  LOL.

    But, seriously, I know she's cuter than me, but dddaaaannnggg!  You gotta call that out?!

    LOL​

    ------------------------------
    Kindest Regards,

    Jo deRuiter
    "That GP Red Head"
    Senior Financial Systems Consultant
    Advanced Credentialed Professional-Dynamics GP
    Chairman, GPUG Partner Advisory Board
    Heartland Business Systems, LLC
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------