Budget Example for Microsoft Excel and SQL Server

Example Goals

  1. To show a concept of SaveToDB usage for application development.
  2. To show a concept of working with normalized database using Microsoft Excel.
  3. To show a concept of complex screen input forms.
  4. To show a concept of user INSERT, UPDATE and DELETE procedures generation and usage.
  5. To show a method of system integration.

The example is fully functional with free SaveToDB Community Edition except BudgetForm and PlanPayments sheets that require SaveToDB Enterprise Edition or trial version.
Free SQL Server 2008/R2 Express Edition can be used also.

This example is included to SaveToDB standalone installer. Example's data stored in SQL Azure.
So, you can try this example right after install.

Also you can download SaveToDB SDK to deploy this example to internal SQL Server or SQL Azure.

Business Requirements

There is a database of Payments and Cash Flow reports.
See Payments example.

Requirements:

  1. To add a feature of plan payments.
  2. To create a Plan Cash Flow report.
  3. To create a Plan-Fact Cash Flow report.
  4. Multi-user work is required.

Development Approach

Considerations:

  1. Budget system is required...
    Our company is not alone that needed the budget system. Definitely there are many ready budget applications.
  2. Otherwise the required system is very simple and cleared.
    We can use current applications as Microsoft Excel with SaveToDB Add-In and SQL Server.
  3. It is required to design a database, to create some procedures and to implement screen budget form.
  4. The database will be normalized.
    The database will include tables: Item, Company, Account, Manager, Payments and Budget.
  5. As source payments table is not normalized we will create a new database and will import data with normalization.
  6. For editing of database tables we will use Microsoft Excel with default SaveToDB edit mode.
  7. For report control we will use Microsoft Excel with SaveToDB.
  8. For convenient budget form we will create a special screen form and will create procedures for data changes.
  9. For access control we will create three schemas: dbo - for developers, xls - for users, etl - for data import/transform operations.

Tasks:

  1. Create database, schemas, tables, keys and constraints.
  2. Create Payments import procedure and import payments.
  3. Create new Cash Flow reports in new database.
  4. Create edit procedures for plan payments table as it uses foreign keys.
  5. Create a workbook and add sheet for universal select and edit operations of any table.
  6. Insert plan payments as fact payments for testing.
  7. Create required Plan and Plan-Fact Cash Flow reports.
  8. Design screen Budget Form and create SQL Server procedures for data changes.
    Insert plan payments for future period.
  9. Create a query for the SaveToDB Query List with user database objects.
  10. Create a result workbook with sheets for all reports, write short help.
  11. Train users.
  12. Accept a technology.
  13. Setup user rights in SQL Server.
  14. Setup SaveToDB Enterprise Edition for users who will input budget data.
    During trial period users can use trial version of SaveToDB.
  15. Setup SaveToDB Community Edition for other users.
  16. Publish or email the new workbook.

Implementations Remarks

Creation of Database, Schemas, Tables, Keys and Constraints

Example zip contains full script. As a result we will have the following objects:

Budget database diagram

Important! Name field of Item, Company, Manager, Account tables should have a unique key.
This field is used to define an ID for Name field from Excel.

Creation Payments Import Procedure and Payments Import

There are many methods for import data to SQL Server.

For example use we will create etl.viewSimplePayment view that outputs payments data and etl.uspUpdatePayment procedure that imports payments with simultaneous normalization.

Using of intermediate etl.viewSimplePayment let's to switch data source or change authentication it the future.

As we can see above the only not existing item, account and company names are inserted.

Payments table is deleted and fully filled during each import. This is simplest way for example goals.

Creation of New Cash Flow Reports in New Database

Stored procedures creation is an ordinary task. As a result we will have following database objects:

  • xls.viewPayment - Payments view of our database (not source enSimplePayments)
  • dbo.viewCashFlowData - as a data source for stored procedures
  • xls.uspCashFlow
  • xls.uspCashFlowSummary

Creation of Edit Procedures for Plan Payments Table

Plan Payments table contains foreign keys for items, companies, managers (see topics above).

Our task is using in Excel names of objects not IDs.

To do this we will create a database view with names and INSERT, UPDATE and DELETE procedures with parameters as names. The procedures should correctly determine ID for name.

This is typical task of development using Microsoft Excel and SaveToDB.
SaveToDB contains uspGenerateEditProcedures stored procedure for code generation. Of course, then you can modify the code.

Let's add etl.uspGenerateEditProcedures to database and execute it for code generation in target xls schema for dbo.Budget table:

SELECT, INSERT, UPDATE, DELETE procedures generation dialog

The procedure will generate codes for four procedures:

  • xls.uspBudget_select
  • xls.uspBudget_insert
  • xls.uspBudget_update
  • xls.uspBudget_delete

Creation of Sheet for Universal Select and Edit Operations of any Table

Let's create new workbook and then connect to enSimpleBudget and select  dbo.uspBudget_select procedure.

As a result sheet will contain procedure result, plan payments, and user can select any database object using the Query List.

New workbook with SQL Server tables editing feature

Pay attention that the Query List not contains xls.uspBudget_insert, xls.uspBudget_update and xls.uspBudget_delete procedures.
By default SaveToDB excludes procedures with _insert, _update and _delete suffixes.

You can see that _select procedure results are editable as the "Save to DB" button is enable.

Insertion of Plan Payments

Now we can insert plan payments using dbo.uspBudget_select Excel table.
The best way is to copy data from source payments, paste it to editable plan payments table and click "Save to DB" button.
This process is shown in Payments example.

Important! Column order of tables is different so use a copy by columns technique.

Creation of Required Plan and Plan-Fact Cash Flow Reports

Report as stored procedure creation is an ordinary development task. As a result database will contain following objects:

  • xls.uspCashPlan
  • xls.uspCashPlanSummary
  • xls.uspCashPlanFact

Design of Screen Budget Form and Creation of SQL Server Procedures for Data Changes

Budget Form should contain editable month data for required year.
The result form is like this:

Budget form

To implement this form we should create a select procedure (xls.uspBudgetForm) and three procedures for data changes:

  • xls.uspBudgetForm_insert
  • xls.uspBudgetForm_update
  • xls.uspBudgetForm_delete

The codes of these procedures are in example zip. The solution is very interesting and beautiful.

Creation of Query for SaveToDB Query List

There are many objects in the database. Some of them should not be visible for end users.

To solve this task we will create xls.viewQueryList view that will output the following results:

SaveToDB Add-In configuration view defines Query List queries and INSERT, UPDATE, DELETE procedures for database objects

As we can see all objects belong to xls schema. Exactly for this schema we will setup end user rights.

As we can see xls.uspBudget_select and xls.uspBudgetForm output results are editable because  SQL Server INSERT_PROCEDURE, UPDATE_PROCEDURE and DELETE_PROCEDURE are configured for these objects.

For using the new query on a sheet use "Connect to DB" dialog and select the query.
SaveToDB defines available query lists by field signature, so an application can contain as many query lists as needed.

Result Workbook Creation

The database development is complete.
Now we can create a result workbook for users. We add sheets with reports and forms and write short help.

For end users our application is a native Excel workbook.

Universal database table editor sheet

Universal database table editor sheet

Payments

Payments

Editable Plan Payments

Plan payments

Editable Budget Form

Budget form

Cash Flow Budget

CashFlow Budget

Cash Flow Budget Summary

CashFlow Budget Summary

CashFlow Plan-Fact

CashFlow Plan-Fact

Cash Flow

CachFlow

Cash Flow Summary

CashFlow Summary

Other steps

Other steps include:

  • User training.
  • Technology Accept.
  • SQL Server user rights setup.
  • SaveToDB Enterprise Edition setup for users who will input budget data.
  • SaveToDB Community Edition setup for other users.
  • New workbook publishing or e-mailing.

Conclusion

  1. SQL Server and Microsoft Excel with SaveToDB Add-In are suitable for some kinds of corporate applications.
  2. Microsoft Excel with SaveToDB Add-In is simply customizable for working with normalized databases.
  3. Screen forms can be complex unlike simple table rows. It is possible to edit any data and send changes to SQL Server.
  4. INSERT, UPDATE and DELETE procedures are used to change server data.
    SaveToDB generates these procedures for database tables automatically.
    You can generate these procedures yourself using uspGenerateEditProcedures stored procedure and then modify them.
    You can implement these procedures yourself for any complex form.
  5. Even simple systems can be very useful for users due to integration with other corporate applications.

To Top