Extend the reach of Microsoft Excel into the Cloud

(an integrated Excel/DataWeb solution)

The need: collect data from multiple sources and report in MS Excel

Do you have a periodic task that forces you to collect information and send out a report? Do you find yourself spending time: sending out spreadsheets to several people to fill out? reminding people ('battle?') to get the information back? re-formatting 1/2 the data that comes back because people formatted it wrong? producing a report by collapsing data from all these individual spreadsheets? resending several versions of collapsed reports because of 'last minute updates'? repeating this process monthly? weekly? daily?

See OpSource used this solution to save time and money.

The solution: Use DataWeb to collect information easily; Excel Web Queries for beautiful, real time reporting

If so, DataWeb's built in Microsoft Excel integration can significantly simplify this process. This document shows how you can easily (in a matter of minutes):

1. create a DataWeb repository, user forms to collect information and 'grid' report view
2. invite any users anywhere with an email address and web browser to securely submit information
3. create a Microsoft Excel refreshable web query to import DataWeb grid view data
4. create an Excel report that can be sent out to anyone ....
5. refresh the Excel Web Query so the current spreadsheet will import the most current DataWeb data with just a few mouse clicks

1) Create DataWeb repository and user forms

The following steps 1 & 2 assume that you are just getting started with DataWeb. If you already are familiar with DataWeb tables, views and users, you may want to skip to step 3.

Creating a new data table and input/edit forms to store information is easy with DataWeb. Once you sign up for a DataWeb developer account, simply get to any page within the easy-to-use, browser-based development environment (click the 'Edit Page' from any public facing web page). To create a new table, simply click File->New->Data Table.

This brings up the New Table dialog, which allows you to create new fields which allows you to create new fields for a new table. Click the Custom Fields tab, add Fields (name, data type, caption).

You can also use this view to quickly create new lookup lists (that will result in giving users a drop down selection as they add or edit data). Fill in name, data type = 'Text' and caption heading for a new field, then click options. Add the options in the free text box (one per line), then click 'ok'.

Click next on the New Data Table dialogue to bring up a dialogue to specify the table name.

Click Create Table and not only is the data table created in this application's database, but a new '.view' is created as well. You will be brought to the 'Design view' of the view file (inside the development environment). The design view of this file allows you to format grid, detail, insert and update views of the data (as well as specifications for each field).

Once satisfied with the view layout, click the Browse button to see exactly what the end user would see. In this end-user view test out the detail, edit, delete and insert features of your new data view. For example, push the Insert button and enter some new data. Now you have an input form that enforces standard data collection and places all information collected in single table!

For brevity's sake, we have only 'scratched the surface' about tables and views. Please see the online documentation for more information.

2) Invite Users To Add Data

Now that you have a place to store data, you can invite people to securly add data. But before inviting anyone, you should determine what users are going to have what permissions to interact with your data.

For our example, let's require that we want the users inserting the data to edit only their data. Some managers who get reports should be able to see everyone's data, but not edit it. Other managers should be able to see and edit everyone's data. To establish or verify these rules, we need to look at the folder permissions related to the data folder. Start by finding and clicking the data folder in the file directory, in this case SampleData. Then click Security->Folder Permissions. This brings up the Folder Permissions dialogue, where you can set these rules by changing the 'Role' drop down for each group.

In the example shown:

• Only logged in application members can access the insert and report forms (Anonymous role = 'No Access')
• The 'Guests' group will see everyone's data, but only be able to edit thier own (Guests role = 'Author')
• The 'Members' group will see everyone's data, but will not be able to add or edit anything (Members role = 'Reader')
• The 'Staff' group will see be able edit and add any data from anyone (Staff role = 'Editor')

Now you can create memberships for specific users. To invite new users, simply click Security->Application Members within the development environment. This brings up the Member List dialogue, click the New Member button to bring up the New Members dialogue. Add the email address, specify the correct Group (determined from the roles described above) and click OK. You can also use this view to 'promote', 'demote' or delete application users at any time.

The new user will receive an email with a link that will allow set up a password (the email address is their user name). Every login form also automatically handles lost passwords - no administrative involvement required! Once invited and logged in, DataWeb automatically records who inserted and last modified every piece of data. You can even easily set up email reminders complete with links that go out to all 'Guest' users the 1st of the month if they have not yet updated/inserted new data!

Again, please see the online documentation for more advance user permissions and feature options.

3) Create and distrubute the Microsoft Excel Worksheet refreshable web query

Now that you have used DataWeb views (forms) and tables to collect the data, lets create an Microsoft Excel report and chart of the DataWeb data collected

Start by creating a new spreadsheet document in Excel. Select the first cell in your new document which you want results from the DataWeb grid to appear. From the Data menu, select Get External Data, and select From Web.

A New Web Query window appears. Enter the URL of the DataWeb application grid page view you wish to query in the Address field. The New Web Query window will automatically detect the tables within the page loaded. Select the arrow that surrounds the innermost table in the grid view. Then click Import.

The Import Data dialogue pops up, allowing you to select the destination of the data. Select the upper-right destination cell and click OK.

The DataWeb data will be imported into your spreadsheet!

You can now use data in your Excel worksheet to do further reporting, dashboarding, data analysis, and charting.

Before sending the sheet out to be viewed by other users/managers, you may want to set the refresh properties for the web query. Start by selecting the destination cell of the web query, right click and select Data Range Properties. Once the External Data Range Properties dialogue box pops up, set the appropriate setting for refresh control (e.g. refresh data automatically every time the document is open).

4) Have Excel users view most current DataWeb data at any time

Now that you have created and distributed the Excel spreadsheet, your manager users can view the most current data (and any charts you may have created) by simply opening the worksheet!

There is one caveot: in order for your manager users to avoid logging in every time the query refreshes, you may want to have them log into DataWeb with the 'remember me' box checked. (You may want to use the note field when you invite new members to stress "always use the 'remember me' checkbox when logging in".):

Conclusion

The DataWeb/Excel Web Query solution can allow any users with an email and a browser to record and update real-time data easily and accurately. The data is safe and professionally managed. The DataWeb platform extends the reach of Excel from the office desktop to the Internet. DataWeb is unique in its ability to seamlessly integrate with Excel, and it has made gathering and reporting real time information much easier for your employees.