Phone: 1-415-230-0560 Contact Us

Example Goals

  1. To show a concept of SaveToDB usage for application development.
  2. To show a concept of working with normalized databases 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.

This example is included into SaveToDB SDK. Example's data stored in SQL Azure.
So, you can try this example right after download.

Also, you may deploy this example to your on-premise 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. There are many ready budget applications.
  2. Otherwise, the required system is very simple and cleared.
    We can use current applications like 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 the new database.
  4. Create edit procedures for plan payments table as it uses foreign keys.
  5. Create a workbook and add a 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 the 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.
  15. 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, and 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, we will create the etl.viewSimplePayment view that outputs payments data and the etl.uspUpdatePayment procedure that imports payments with simultaneous normalization.

Using the intermediary etl.viewSimplePayment view allows switching data source or change authentication it the future.

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

Payments table is deleted and fully filled during each import. This is the 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 a 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, the sheet will contain procedure result, plan payments, and the 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 enabled.

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 into the editable table of plan payments 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 a required year.
The resulting 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 to 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 add-in setup.
  • Publishing or e-mailing a new workbook.

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.