Open Forum

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!

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!

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. 

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!

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. 

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!

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!

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. 

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!

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!

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!

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!

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!

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!

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!

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!

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!