Welcome to our ultimate video guide on turning spreadsheets into apps. This video series will cover everything you need to learn to create web applications using your Excel files, from importing the file online to deploying dynamic forms and reports to your website.

SUMMARIZED VIDEO TRANSCRIPT

This tutorial will guide you in developing nine different applications without writing a single line of code. But first, you must be familiar with the Caspio DataPage.

DataPages are essentially app interfaces, or widgets, that are built exclusively on the Caspio platform. Once completed, they can be deployed on any website in just a few clicks. Now it’s time to build your first one.

Building a Combined Chart and Report DataPage

To start building your application interface, click on the DataPages object on the left-hand panel of the Caspio web app. You’ll find in the resulting screen that no DataPages currently exist, so go ahead and click New DataPage near the top of the screen to launch Caspio’s point-and-click DataPage wizard.

On the left-hand side of the wizard, you’ll see a navigation menu that features all the different apps you can build:

  • Forms – Create interfaces for submitting records to your Caspio database, or forms to accommodate password recovery.
  • Reports – Create tabular reports (with or without a chart), grids, lists, details and pivot tables to view and edit records on your website.
  • Calendars – View records in a monthly or weekly layout.
  • Charts – See a visual representation of your data through area charts, bar charts, column charts, line and step charts, pie charts and more.
  • HTML – Used by technically adept developers to complement their app.

DATAPAGE 1: My Sales

The first DataPage you’ll learn to build is a combination of a tabular report and a chart. It should serve as a valuable resource for sales reps to log in and view their own sales.

  • Wizard 1 – DataPage Type

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    Click on the Reports tab of the DataPage Wizard, then click on Combined Chart and Report. You’ll then see an illustrated preview of what you’re building at the bottom. It shows that you’ll have the ability to search data, view a chart along with your tabular results, and drill into details if necessary.

    Move on by clicking Next.

  • Wizard 2 – DataPage Data Source

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    Under DataPage Properties of the resulting screen, you have four fields you can configure:

    1. Select data source – Clicking this dropdown will show you a list of all five of your tables. Select demo_tbl_sales to pull all the sales information from your sales table.

    2. Enter DataPage name – Since this DataPage will be built to show data limited to the rep who logged in, call this DataPage: My Sales.

    3. Select Style – You can apply different styles to change the aesthetics of your DataPage here. You can even create your own style when you go to the Styles object in Caspio’s main left-hand panel. For now, stick with the default selection.

    4. Select Localization – This dropdown will allow you to select different regional settings for your DataPage. You can change the language of your forms and reports and apply different date formats as well. If you wish to configure your own localization, go to the Localizations object in Caspio’s main left-hand panel.

    Under Access and Security, check the Restrict Access checkbox. Make sure that the first radio button to Require Authentication is selected, then choose Demo Reps Login in the dropdown. This is the exact authentication you’ve built in Part 3 for your sales representatives.

    Move on by clicking Next.

  • Wizard 3 – Search Type

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    Under Data Selection of the resulting screen, you can build a search interface by clicking the first radio button to Allow users to select data using a search form. Since you don’t need your reps to have the ability to search, you can have them view data right away after logging in by selecting the second radio button to Filter data based on your pre-defined criteria.

    Then under Record Level Security, check the box to Restrict record access based on user identity. The two dropdowns underneath should have Rep_ID for both Identity field in Authentication and Matching field in current data source. Matching up both ID fields correctly between your authentication data source (demo_tbl_reps) and DataPage data source (demo_tbl_sales) will enable you to implement record level security, meaning your sales reps can only view their own data upon logging in.

    Move on by clicking Next.

  • Wizard 4 – Select Filtering Fields

    This part of the wizard will prompt you to filter data based on your Available Fields. Since we won’t be applying other filters on the results beyond record level security, bypass this screen by clicking Next.

  • Wizard 5 – Select Results Page Fields

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    You’ll need to select the fields to be displayed in the results page here. Click Product_Name from your Available fields and click the “>” button to include it in your Selected Fields. Do the same for Sale_Location_City, Sale_Location_State, Sale_Amount and Date_of_Sale.

    Move on by clicking Next.

  • Wizard 6 – Results Page Editing Options

    If you want to give your sales reps the ability to edit data on the results page, you can allow them to edit here. But to keep things simple in this tutorial, leave the Add Records, Edit Records and Delete Records options unchecked for now.

    Move on by clicking Next.

  • Wizard 7 – Configure Results Page Fields

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    Two modifications must be made under DataPage Elements:

    1. Product_Name – In the Standard tab, check the box under Label to Enable data grouping by this field; then in the dropdown for Grouping display, select Collapsible group; then from the two radio buttons underneath, select Expanded by default.

    2. Aggregate 1 – Add a basic calculation field that will compute for total sales by clicking the insert symbol at the bottom of the DataPage Elements panel. Then from the menu under Calculation, select Totals & Aggregation. The resulting screen in the Standard tab should show your Available Fields under Aggregate Options. Select Sale_Amount and click the “>” button to include it in your Selected Fields.

      Note that you have the option to create your own Formula by selecting the second radio button underneath. For this tutorial, stick with the default Function of Sum in the first radio button.

    Move on by clicking Next.

  • Wizard 8 – Results Page Options

    This screen allows you to configure Default sort order, Records per page and more. Skip this portion for now and continue to the next page by clicking Next.

  • Wizard 9 – Select Chart Type

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    This wizard will prompt you to choose the kind of chart that could best display your data. For this use case, select Stacked Column under Column Charts then continue to the next page.

  • Wizard 10 – Configure Chart Options

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    Configure each of the Chart Elements on the left-hand panel:

    1. Chart Settings – Under General Options, type in Monthly Sales as the Title and By Product in the Subtitle.
    2. Data Source Settings – In configuring how to chart your data, select the first radio button to Create series from data in a single field. Note that you can refer to the illustration guides underneath each radio button, or try and preview both options to determine the right chart format for your app.
    3. Category (X Axis) – Under General Options, type in Month as the Axis Title, select Date_of_Sale in the dropdown for Category field, select Date rollup in the dropdown for Group date by, and select Month in the dropdown for Date rollup.
    4. Series – The plot here should be based on the Product_Name so leave the default selection in the Data series field as is.
    5. Values (Y Axis) – Under General Options, type in Total Sales as the Axis Title and leave the rest as is. Then under Display Options, change the Formatting into currency by clicking the Edit button, then select the radio button for Custom in the popup window, then select Currency in the dropdown for Format type, then click OK.
    6. Value – Since you’re trying to get the sum of the total sales, select Sale_Amount in the dropdown for Value field. The Aggregation should already be Sum, and the box to Replace missing values with zero already checked.

    Move on by clicking Next.

  • Wizard 11 – Chart Page Options

    There’s no need to make any changes in the chart options so go ahead and click Next.

  • Wizard 12 – Details Page

    Should you choose to enable the Details Page by selecting the first radio button, a special link will be generated inside your DataPage that will show additional information. Since all the data you need is already in the results page, select the second radio button to have No Details Page.

    You can also click the Preview button from here to check the DataPage you have built. Upon doing so, you’ll be prompted to log in as a sales rep. Go ahead and use your sample account for Max (Email: max@rep.com, Password: maxw) to view his sales information.

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    You should be able to observe the following for DATAPAGE 1:

    1. A chart that displays all the revenue based on product sold per month
    2. A tabular report that is grouped by product
    3. Collapsible product groups with sums of total sales
    4. A grand total that shows the sum of all the sales at the bottom

    After making sure that everything’s in order, click Finish.

You’ve now created your very first DataPage. You can edit and preview your DataPage anytime at the DataPages object in the left-hand panel of the Caspio Platform.

Building a Submission Form

Now that your reps can view their own sales, the next DataPage you should build must allow them to input their own sales information. Click on New DataPage to get started.

DATAPAGE 2: Add New Sale

The goal of this DataPage is to have an online web form for your reps to submit sales information to your sales table.

  • Wizard 1 – DataPage Type

    Click on Forms, select Submission Form, then click Next to move on to the next screen.

  • Wizard 2 – DataPage Data Source

    Configure the following fields under DataPage Properties and leave the rest as is:

    1. Select data source – Select demo_tbl_sales in the dropdown since your goal is to be able to input new sales data to your sales table.

    2. Enter DataPage name – Go ahead and call this DataPage Add new Sale.

      Under Access and Security, check the Restrict Access checkbox, then select Demo Reps Login in the dropdown for Require Authentication.

  • Wizard 3 – Select Fields

    On this screen, instead of including one field at a time from your Available Fields to your Selected Fields, simply click the “>” button to execute bulk transfer. You’ll be using all of them on your submission form.

  • Wizard 4 – Configure Fields

    Before you continue, click Preview to check the current state of your form. Then apply these modifications under DataPage Elements:

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports
    1. Product_Name – The result of this modification will be a dropdown menu that automatically displays all available products from your imported products table. There is no need to manually type them on your submission form.

      Under General Options, select Dropdown in the Form Element dropdown menu. Then under Field Options, select Lookup table or view in the dropdown menu for Source. A tab labelled Lookup Table should be selected underneath, go ahead and choose demo_tbl_products in the dropdown menu for Table or view. You’ll then find that the Field for display and Field for value dropdowns both have Product_Name selected by default.

    2. Rep_ID – Since you wouldn’t want your reps to manually input their ID each time they submit the form, you’ll need to automatically stamp the ID from the login authentication to your sales table.

      Under General Options, select Hidden in the Form Element dropdown menu. Then under Field Options, select Authentication Fields and Rep_ID respectively in the first and second dropdown menus of On load, receive.

    3. Sale_Location_State – Manually typing the state each time isn’t ideal as well, especially if you already have a lookup table. Simply follow the same modification procedure of Product_Name, then select demo_tbl_states for the Table or view dropdown and States for the Field for display and Field for value dropdowns.

    Click Finish to save your changes. The resulting screen should show both DataPages you’ve created, which can be edited and previewed anytime.

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports

    Upon clicking the Preview link of Add New Sale, you should be able to test DATAPAGE 2 by submitting any sample data and checking if they are seen in DATAPAGE 1:

Now, whenever a representative adds their sale using this web form, the data will also be reflected on your My Sales DataPage.

Duplicating a DataPage

Duplicating an existing DataPage is simple. In your list of DataPages, find My Sales and click More to open a dropdown menu, then click Duplicate. A new DataPage should appear on your list that is prompting you to give it a name. Type in Company Sales and click Save.

To begin with the modifications, click Edit.

DATAPAGE 3: Company Sales

The third DataPage you’ll build will be the last one for the sales reps. It should allow them to log in and view a report of all the sales brought in by the other reps. One way to build it is through the same steps you’ve followed in creating My Sales, but it would be quicker if you duplicate a similar DataPage and modify it.

Here are the only pages you’ll be configuring in the Combined Chart and Report wizard to complete your new DataPage:

  • Wizard 3 – Search Type

    Under Record Level Security, uncheck Restrict record access based on user identity to display all the sales data.

  • Wizard 5 – Select Results Page Fields

    Include Rep_ID from your Available Fields to your Selected Fields. This will serve to display your sales reps on both the table and the chart.

  • Wizard 7 – Configure Results Page Fields

    Under DataPage Elements, select your Rep_ID field and bring it to the top by clicking the up-arrow symbol at the bottom of the panel. Then under General Options, check Enable data grouping by this field.

  • Wizard 8 – Results Page Options

    Under Display Options, configure the Records per page by changing it from 25 to 500.

  • Wizard 9 – Select Chart Type

    Under Bar Charts, select Bar.

  • Wizard 10 – Configure Chart Options

    Make the following modifications under Chart Elements:

    1. Chart Settings – Under General Options, type in Company Sales as the Title and by Rep as the Subtitle.
    2. Data Source Settings – Select the second radio button to Use a separate field for each series.
    3. Category (Y Axis) – Under General Options, type in Name as the Axis Title and select Rep_ID in the dropdown for Category field.
  • Wizard 11 – Chart Page Options

    Configure your Chart sort order to display total sale amount from highest to lowest. Change the selected field on the first dropdown into SUM(Sale_Amount) and click the box beside it to change the setting to Z→A.

Go ahead and click Finish once done with the modifications.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

When you click Preview back in the main DataPages screen, you’ll find that your chart list is from highest to lowest, and that the sales in the tabular report are grouped by Rep_ID. The IDs themselves are hard to decipher.

Implementing Table Relationships

One thing you need to address in your last DataPage is determining the reps behind the Rep_IDs. This can easily be solved within the Caspio platform by applying table relationships.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

Begin by right-clicking Tables from the main left-hand panel, then open the object in a new tab, then click Relationships at the top of the resulting screen, then click the checkboxes for demo_tbl_reps and demo_tbl_sales in the list of Tables on the left.

Since the common field between both tables is Rep_ID, click the field on the demo_tbl_reps container and drag your mouse pointer to the Rep_ID of demo_tbl_sales. A pop-up should appear that will allow you to edit their relationship settings. Simply select Name in the dropdown menu for Display Value, then check the box underneath to Use display value on DataPages.

The table relationship you’ve created is called one-to-many. It means that a unique Rep_ID from your reps table can be linked to multiple sales on your sales table.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

You can now click Save Layout on top and go back to your DataPages to check your changes. Click Preview on your Company Sales DataPage and log in as Max (Email: max@rep.com, Password: maxw). You’ll then notice that all the Rep_IDs are now replaced with their corresponding names.

Creating Folders for Your DataPages

One neat trick you can do when building multiple DataPages in Caspio is to organize them in folders. This section will quickly teach you how to sort your list of DataPages by user groups.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

Within the DataPages object, click New Folder above the window of your DataPage list and name it, Admin. Then click New Folder again to create another one, name it Reps.

Since all three DataPages currently on your list are all under the user group Reps, click the checkboxes of all three to their left. Then click Bulk Actions above the DataPage list window and find your Reps folder under Sales Management. Then finally, click Move.

Now, you can simply expand the folder to find all three related DataPages. You don’t have to end up with a long, unsorted list that will prove troublesome down the road when modifying functionalities.

Building Your Admin DataPages

There are six more DataPages you’ll need to build, this time for the Admin portal. Begin by clicking the Admin folder you’ve recently created, then click New DataPage above to open the DataPage wizard again.

DATAPAGE 4: Add New Rep

This DataPage will allow your admins to add a sales rep to your reps table. What you’ll need is another submission form.

    • Wizard 1 – DataPage Type

 

Click the Forms tab and select Submission Form.

    • Wizard 2 – DataPage Data Source

 

Under DataPage Properties, select demo_tbl_reps from the Select data source dropdown and type in Add New Rep in the text field to Enter DataPage name. Then under Access and Security, check Restrict Access and select the radio button to Require Authentication, then select Demo Admin Login from its dropdown menu.

    • Wizard 3 – Select Fields

 

Include all Available Fields that can be transferred (Name, Email, Password) to Selected Fields by clicking the “>>” symbol.

    • Wizard 4 – Configure Fields

 

Under DataPage Elements, select Name. Then within the standard tab on the right, under Field Options, check the box that will make the Name field Required.

When you’re done, click Finish to save your DataPage. You should find it within your Admin folder on your main DataPages screen.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

Now, try checking your new submission form by clicking Preview, then log in using your sample admin account from your admin table (Email: john@admin.com, Password: test). You should be able to see four text fields (Name, Email, Password, Confirm Password) and a Submit button.

DATAPAGE 5: View/Manage Reps

The next DataPage you’ll build for your admins will allow them to manage every sales representative from your reps table. What you’ll need here is a tabular report. Make sure that your Admin folder is opened before clicking the New DataPage link on top.

    • Wizard 1 – DataPage Type

 

Click the Reports tab and select Tabular.

    • Wizard 2 – DataPage Data Source

 

Follow the same configuration in Wizard 2 of DATAPAGE 4, except in the text field to Enter DataPage name where you should type in View/Manage Reps.

    • Wizard 3 – Search Type

 

Under Data Selection, build a search form by selecting the first radio button to Allow users to select data using a search form. You can then choose whether to Display search form and results on separate screens (default) or Display search form above the results.

    • Wizard 4 – Select Search Fields

 

Transfer Name and Email from your list of Available Fields to your Selected Fields. This way, you can search for reps inside your database based on those fields.

    • Wizard 5 – Configure Search Fields

 

Under General Options of both the Name and Email fields, set the Comparison Type to Contains to have a broader results range.

    • Wizard 6 – Select Results Page Fields

 

Again, include Name and Email from your list of Available Fields to your Selected Fields.

    • Wizard 7 – Results Page Editing Options

 

Under Edit Records, check the first box to implement Inline edit (one record at a time). This will allow your admins to edit a row directly from the results page.

    • Wizard 8 – Configure Results Page Fields

 

No changes required here so skip to the next page.

    • Wizard 9 – Results Page Options

 

Again, no changes are required here.

    • Wizard 10 – Details Page

 

Include a Details view by selecting the first radio button that says Yes, enable Details Page.

    • Wizard 11 – Select Details Page Fields

 

Display all the information in the Available Fields by clicking the “>” button. The Rep_ID, Name, Email and Password fields should be included in the list of Selected Fields.

    • Wizard 12 – Configure Details Page Fields

 

The goal here is to be able to update the Name, Email and Password of your sales reps:

    1. Rep_ID – Keep the Form element as Display Only since you don’t need to update it.
    2. Name – Change the Form element to Text Field.
    3. Email – Change the Form element to Email.
    4. Password – Under Password Options, uncheck Require current password validation. This will allow your admin to make changes to a rep’s password, even without knowing the current one.

When you’re done, click Finish to save your DataPage.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

You should be able to observe the following when you Preview your DATAPAGE 5:

  1. A search form for the Name and Email of a sales rep
  2. A list of all your sales reps upon searching blank Name and Email fields
  3. An Edit link on the right that will allow you to make changes on the results table
  4. A Details link that will allow you to edit more information (including the Password field) on another page

DATAPAGE 6: View/Manage Reps

The next DataPage should allow admins to view all the sales for each of the reps, and reassign any given sale to somebody else. Again, make sure your Admin folder is opened before clicking the New DataPage link on top.

    • Wizard 1 – DataPage Type

 

Click the Reports tab and select Combined Chart and Report.

    • Wizard 2 – DataPage Data Source

 

Under DataPage Properties, select demo_tbl_sales from the Select data source dropdown, then type in View/Manage Sales in the text field to Enter DataPage name. And like DATAPAGE 4 and 5, Restrict access through your Demo Admin Login authentication.

    • Wizard 3 – Search Type

 

Under Data Selection, build a search form by selecting the first radio button to Allow users to select data using a search form.

    • Wizard 4 – Select Search Fields

 

Include Product_Name, Rep_ID and Date_of_Sale from your list of Available Fields to your Selected Fields.

    • Wizard 5 – Configure Search Fields

 

Make the following configurations on the fields under DataPage Elements:

      1. Product_Name – Under General Options in the Standard tab, change the Form element into a Listbox. Then under Field Options, select Both from the Source dropdown menu. And in the Custom Values tab below, type in Search all on the Display text field and delete the same input in the Value text field beside it. Then in the Lookup Table tab, select demo_tbl_products in the Table or view dropdown.
        Lastly, go to the Advanced tab on top. Under Field Options, check the box that will Allow multi-select. Go back to the Standard tab when you’re done.
      2. Rep_ID – This field should automatically be configured due to the table relationship you previously applied. But one minor modification you’ll need to do under Field Options is changing the selected Source from Lookup table or view to Both. Then under the Custom Values tab, type in Search Any on the Display text field and delete the same input in the Value text field beside it.
      3. Date_of_Sale – To be able to search by date range, click the insert symbol at the bottom of the DataPage Elements panel, and select New Criteria. You will then see two new options underneath. But first in the Elements tab of Date_of_Sale, select the Logical operator between criteria to be AND.
        1. Criteria 1 – Change the Label into Date of Sale (from) and select the Comparison type to be Greater Than or Equal.
        2. Criteria 2 – Change the Label into Date of Sale (to) and select the Comparison type to be Less Than or Equal.
    • Wizard 6 – Select Results Page Fields

 

Transfer Product_Name, Rep_ID, Sale_Location_City, Sale_Location_State, Sale_Amount and Date_of_Sale from your Available Fields to your Selected Fields for the results page.

    • Wizard 7 – Results Page Editing Options

 

Under Edit Records, check the first and third boxes to implement Inline edit (one record at a time) and Bulk edit (applies same updates across multiple records). Then under Delete Records, check the first box to implement Inline delete (one record at a time). Now your admin can edit by row, edit in bulk and delete by row whenever necessary.

    • Wizard 8 – Configure Results Page Fields

 

Make the following configurations on your DataPage Elements:

      • Rep_ID – Under General Options, check the box to Enable data grouping by lookup values. Then for the Grouping display, select Collapsible group from the dropdown and Collapsed by default from the radio buttons underneath.
      • Aggregate 1 – Similar to your aggregate from Wizard 7 in DATAPAGE 1, click the insert symbol at the bottom of your DataPage Elements panel and select Totals & Aggregation. The resulting screen in the Standard tab should show Available Fields under Aggregate Options. Select Sale_Amount and click the “>” button to include the field in your Selected Fields.
    • Wizard 9 – Configure Fields for Bulk Edit

 

For this use case, you only need the ability to assign a sale to a different rep. Facilitate this on your DataPage by keeping the Rep_ID field and removing the rest. Click the “X” symbol at the bottom of the panel for each highlighted field you’ll be removing, then click Yes in the confirmation box that will pop up.

    • Wizard 10 – Results Page Options

 

Under Display Options, increase the Records per page to 500.

    • Wizard 11 – Select Chart Type

 

Under Bar Charts, select Stacked bar.

    • Wizard 12 – Configure Chart Options

 

Make the following modifications under Chart Elements:

      1. Chart Settings – Under General Options, type in Total Sales as the Title; and by Rep in the Subtitle.
      2. Data Source Settings – Select the first radio button to Create series from data in a single field.
      3. Category (Y Axis) – Under General Options, type in Name as the Axis title; select Rep_ID in the dropdown for Category field.
      4. Value (X Axis) – Under General Options, type in Total as the Axis title. Then under Display Options, change the Formatting into currency by clicking the Edit button, then select the radio button for Custom in the popup window, then select Currency in the dropdown for Format type, then click the OK button.
      5. Value – Select Sale_Amount in the Value field dropdown and make sure the Aggregation is Sum.
    • Wizard 13 – Chart Page Options

 

No changes required here so skip to the next page.

    • Wizard 14 – Details Page

 

Select the second radio button to have No Details Page.

When you’re done, click Finish to save your DataPage.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

You should be able to observe the following when you Preview your DATAPAGE 6:

  1. A list box where more than one product can be selected
    • Hold Ctrl to click on multiple items
  2. A dropdown that pulls all your sales reps
  3. The ability to conduct a date range search
  4. The ability to show and hide elements from your chart
    • Click the product items on the right-hand side
  5. A collapsible tabular report that is grouped by sales reps
  6. The ability to reassign sales to a different rep, and have it reflected on the chart
    • Click the checkbox of the row or sale you want to reassign
    • Click Edit at the top of your tabular report to open a pop-up window
    • Select a rep you would like to transfer the sale to from the dropdown menu
    • Click Update

You’ve now created three DataPages for your administrators that will each reside in their own web pages when deployed.

Building Your Admin Dashboard

The last three DataPages you’ll learn to build is comprised of two charts and a pivot table. All three will be deployed on one web page to form an administrator dashboard. The goal here is to quickly show aggregate data across all your sales in one place.

DATAPAGE 7: Total Sales by Month

The goal of this DataPage is to show the admin their aggregate sales trend per month through a line chart. Make sure your Admin folder is opened before clicking the New DataPage link on top.

    • Wizard 1 – DataPage Type

 

Click the Charts tab and under Lines and Step Charts, select Line.

    • Wizard 2 – DataPage Data Source

 

Follow the same configuration of Wizard 2 in DATAPAGE 6, except in the text field to Enter DataPage name where you should type in Total Sales by Month.

    • Wizard 3 – Search Type

 

Under Data Selection, select the second radio button to Filter data based on your pre-defined criteria.

    • Wizard 4 – Search Filtering Fields

 

You don’t need to implement any filtering so skip this page by clicking Next.

    • Wizard 5 – Configure Chart Options

 

Make the following modifications under Chart Elements:

    1. Chart Settings – Under General Options, type in Total Sales as the Title; and by Month in the Subtitle.
    2. Data Source Settings – Select the second radio button to Create series from data in a single field.
    3. Category (X Axis) – Under General Options, type in Month as the Axis title, select Date_of_Sale in the dropdown for Category field, select Date rollup in the dropdown for Group date by, and select Month in the dropdown for Date rollup.
    4. Value (Y Axis) – Under General Options, type in Total Sales as the Axis title. Then under Display Options, change the Formatting into currency by clicking the Edit button, then select the radio button for Custom in the popup window, then select Currency in the dropdown for Format type, then click the OK button.
    5. Value – Select Sale_Amount in the Value field dropdown then select the Diamond marker in the Marker dropdown.

When you’re done, click Finish to save your DataPage chart.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

You should be able to view a simple trend of all your sales when you Preview your DATAPAGE 7.

DATAPAGE 8: Top Performers

The goal of this DataPage is to show the admin a bar chart of his reps by order of performance. Make sure your Admin folder is opened before clicking the New DataPage link on top.

  • Wizard 1 – DataPage Type

    Click the Charts tab and under Bar Charts, select Bar.

  • Wizard 2 – DataPage Data Source

    Follow the same configuration of Wizard 2 in DATAPAGE 6, except in the text field to Enter DataPage name where you should type in Top Performers.

  • Wizard 3 – Search Type

    Under Data Selection, select the second radio button to Filter data based on your pre-defined criteria.

  • Wizard 4 – Search Filtering Fields

    You don’t need to implement any filtering so skip this page by clicking Next.

  • Wizard 5 – Configure Chart Options

    Make the following modifications under Chart Elements:

    1. Chart Settings – Under General Options, type in Top Performers as the Title.
    2. Data Source Settings – Select the second radio button to Create series from data in a single field.
    3. Category (Y Axis) – Under General Options, type in Name as the Axis title and select Rep_ID in the dropdown for Category field.
    4. Value (X Axis) – Under General Options, type in Total Sales as the Axis title. Then under Display Options, change the Formatting into currency by clicking the Edit button, then select the radio button for Custom in the popup window, then select Currency in the dropdown for Format type, then click OK.
    5. Value – Select Sale_Amount in the Value field dropdown.
  • Wizard 6 – Chart Page Options

    Select SUM(Sale_Amount) under Chart sort order and click the box beside it to change the setting to Z→A.

When you’re done, click Finish to save your DataPage chart.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

You should be able to view a simple bar chart of your top performers from highest to lowest when you Preview your DATAPAGE 8.

DATAPAGE 9: Sales Pivot

Like in Excel, pivot tables will allow you to see fields on the left-hand side and at the top, with aggregates of your data in the middle. This functionality can easily be built with Caspio as well. Make sure your Admin folder is opened before clicking the New DataPage link on top.

  • Wizard 1 – DataPage Type

    Click the Reports tab and select Pivot Table.

  • Wizard 2 – DataPage Data Source

    Follow the same configuration of Wizard 2 in DATAPAGE 6, except in the text field to Enter DataPage name where you should type in Sales Pivot.

  • Wizard 3 – Search Type

    Under Data Selection, select the second radio button to Filter data based on your pre-defined criteria.

  • Wizard 4 – Search Filtering Fields

    You don’t need to implement any filtering so skip this page by clicking Next.

  • Wizard 5 – Configure Pivot Table Fields

    Make the following modifications under DataPage Elements:

    1. Columns – Click the “+” symbol beside this field and select Date_of_Sale; then under General Options, select Date rollup from the Group date by dropdown; then select Month from the Date rollup dropdown.

    2. Rows – Click the “+” symbol beside this field and select Rep_ID.

    3. Values – Click the “+” symbol beside this field and select Sale_Amount. Make sure that your values are summarized by Sum.

      Then under Display Options, change the Formatting into currency by clicking the Edit button, then select the radio button for Custom in the popup window, then select Currency in the dropdown for Format type, then click OK.

When you’re done, click Finish to save your DataPage.

How to Convert Excel to a Web App | Part 4: Building Forms & Reports

When you Preview your DATAPAGE 8, you should be able to view a pivot table that shows how much revenue is being brought in per month, and who among the reps are contributing the most.

You’ve now completed the entire application, which is a basic level app built in Caspio. Note that you can also build robust and sophisticated applications as well because Caspio allows you to add your own SQL statements, JavaScript and HTML to enhance the functionality of your web app.

See You in the Next Excel to Web Class!

The building process of your DataPages here should be intuitive and easy to follow, even with no technical expertise in coding. What’s left now is to finally deploy them.

In the final video of the series, you’ll learn how to deploy all your DataPages into a website where your users will have access to the entire functionality. Both the reps and the admins should be able to log in, view and edit information, and be able to submit new sales into the app.

Thanks for watching and see you there.

Next Steps

DOWNLOAD FREE REPORT
See why Forrester ranks Caspio a 'Leader' in low-code platforms for business developers in this 2019 report.
REQUEST FREE CONSULTATION
Got a vision for an application? Get free consultation to see how we can help you achieve it.
SCHEDULE A DEMO
Choose the most convenient date and time for you to get a demo from our team.