4 ms·

I'd love to see this concept taken a step further and be able to write back changes (within reason, of course) to a SQL database. The piece I'd most love to se

I'd love to see this concept taken a step further and be able to write back changes (within reason, of course) to a SQL database. The piece I'd most love to see is some form of editable Pivot Table, where the changes get written back to the appropriate place in the database. For example: CREATE TABLE Balances(Account varchar, Scenario varchar, Amount numeric, primary key(Account,Scenario)); -- Pretend that this is pre-populated with all Account and Scenario combinations Using this schema, I would want to get a Pivot Table out of this where Account makes up the row labels, Scenario forms the column labels, and the Amount values are the data. Where the magic happens is that I want to be able to make changes to the amounts and have it write those back into the database with the appropriate UPDATE statements.

cyberferret 10y ago
Be prepared for a bill from your DBA for his/her blood pressure medication then... or the accounts team when they realise that a missing conversion of a string to a numeric caused you to zero out every customer's account balance... ;) (Based on real world experience with my clients over 3 decades) Even 20 years ago good old Microsoft with their MSQuery capability in Excel stopped you from doing this every way they could.
- Mister_Snuggles 10y ago
  Depending on how it's done it can be perfectly safe. I've actually built a thing, in Lazarus of all things, to do this very thing. Unfortunately it's very specific to my use-case, but it is "safe" in the sense that it doesn't screw things up too badly. My particular use case is actually very spreadsheet-like, but I also want some things that are a lot easier to do in SQL than in Excel. My use case is to have a tool to help me evaluate monthly financial statements for a smallish organization, enter my own adjustments, and come up with a final statement. It's spreadsheet-like in the sense that I enter everything into a grid of rows and columns. The rows are accounts (in the income statement sense, not customer accounts), columns are a "type" (e.g., actuals, my adjustments, adjustments made by the auditor, adjusted actuals, budget, budget vs actuals, etc). Some accounts and some types are calculated, thus read-only in the table. There's also a drop-down at the top to select a period. Basically, it's everything that a basic Excel Pivot Table does, but database-backed. My Lazarus program lets me fill in that table however I want, when I hit save it persists it all to the database and runs a stored procedure to recalculate all of the calculated values. The database makes sure that I don't try to put a string in a numeric field, and it (PostgreSQL) is smart enough not to convert strings to zeroes. The thing that makes this better than a spreadsheet is that I can just add a new account/scenario/whatever and run a stored procedure to insert the missing rows in the data table. When I did a similar thing with Excel, it was easy enough to insert rows. The hard part was inserting all of the rows everywhere they needed to go (I had a tab for budget, a tab for actuals, a tab for adjustments, etc) and updating all of the formulas.

cyberferret 10y ago

Be prepared for a bill from your DBA for his/her blood pressure medication then... or the accounts team when they realise that a missing conversion of a string to a numeric caused you to zero out every customer's account balance... ;) (Based on real world experience with my clients over 3 decades) Even 20 years ago good old Microsoft with their MSQuery capability in Excel stopped you from doing this every way they could.

Mister_Snuggles 10y ago
Depending on how it's done it can be perfectly safe. I've actually built a thing, in Lazarus of all things, to do this very thing. Unfortunately it's very specific to my use-case, but it is "safe" in the sense that it doesn't screw things up too badly. My particular use case is actually very spreadsheet-like, but I also want some things that are a lot easier to do in SQL than in Excel. My use case is to have a tool to help me evaluate monthly financial statements for a smallish organization, enter my own adjustments, and come up with a final statement. It's spreadsheet-like in the sense that I enter everything into a grid of rows and columns. The rows are accounts (in the income statement sense, not customer accounts), columns are a "type" (e.g., actuals, my adjustments, adjustments made by the auditor, adjusted actuals, budget, budget vs actuals, etc). Some accounts and some types are calculated, thus read-only in the table. There's also a drop-down at the top to select a period. Basically, it's everything that a basic Excel Pivot Table does, but database-backed. My Lazarus program lets me fill in that table however I want, when I hit save it persists it all to the database and runs a stored procedure to recalculate all of the calculated values. The database makes sure that I don't try to put a string in a numeric field, and it (PostgreSQL) is smart enough not to convert strings to zeroes. The thing that makes this better than a spreadsheet is that I can just add a new account/scenario/whatever and run a stored procedure to insert the missing rows in the data table. When I did a similar thing with Excel, it was easy enough to insert rows. The hard part was inserting all of the rows everywhere they needed to go (I had a tab for budget, a tab for actuals, a tab for adjustments, etc) and updating all of the formulas.