Welcome to our ultimate video guide on turning spreadsheets into apps. This video series will cover everything you need to create web applications with your Excel files.

Part 4 will teach you how to build charts, forms and reports for your Excel web app.

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 the application interfaces that you will, later on, be embedding into your website to access all the functionality.

Building a Combined Chart and Report DataPage

To build your first DataPage, click on the DataPages object on the main left-hand panel. You’ll find in the resulting screen that no DataPages currently exist, so go ahead and click the New DataPage link on top 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, Reports, Calendars, Charts and HTML DataPages if you’re technically adept.

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 Step 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 if you decide to build a search interface, view a chart along with your results on the Results Page, and drill into details if necessary.

    Move on by clicking Next.

  • Wizard Step 2 – DataPage Data Source

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

    Under DataPage Properties, 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 – 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.

    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, make the following modifications:

    1. Check the Restrict Access checkbox.
    2. Make sure that the Require Authentication is selected, which is the first radio button.
    3. Select 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 Step 3 – Search Type

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

    Under Data Selection 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, 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, make the following modifications:

    1. Check the box to Restrict record access based on user identity.
    2. In the dropdown for Identity field in Authentication, make sure Rep_ID is selected.
    3. The dropdown for Matching field in current data source should also have Rep_ID selected.

    Matching your Rep_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 Step 4 – Select Filtering Fields

    This wizard will ask 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 Step 5 – Select Results Page Fields

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

    Select the fields you want to use in the results page here. Transfer the following from your Available fields to your Selected Fields by clicking the “>” button:

    1. Product_Name
    2. Sale_Location_City
    3. Sale_Location_State
    4. Sale_Amount
    5. Date_of_Sale

    Move on by clicking Next.

  • Wizard Step 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, leave Add Records, Edit Records and Delete Records unchecked for now. Move on by clicking Next.

  • Wizard Step 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 – Make the following modifications in the Standard tab:

      • Check the box that will Enable data grouping by this field.
      • In the dropdown for Grouping display, select Collapsible group.
      • Select Expanded by default from the two radio buttons underneath.
    2. Aggregate 1 – Add a basic calculation field to display total sales by clicking the insert symbol at the bottom of the DataPage Elements panel. Then select Totals & Aggregation. Next, implement the following modifications:

      • In the Standard tab, under Aggregate Options, select Sale_Amount from your list of Available Fields.
      • Click the ‘>‘ button to include it in your Selected Fields.
      • Make sure that Function is selected in the radio button underneath.
      • Make sure that Sum is selected in the dropdown beside it.

    Move on by clicking Next.

  • Wizard Step 8 – Results Page Options

    This screen allows you to configure Default sort order, Records per page and more. Skip this portion for now and click Next.

  • Wizard Step 9 – Select Chart Type

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

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

    Click Next to continue.

  • Wizard Step 10 – Configure Chart Options

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

    Make the following modifications under Chart Elements:

    1. Chart Settings – Under General Options, type in Monthly Sales as the Title and By Product as 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.

    3. Category (X Axis) – Make the following modifications:

      • Under General Options, type in Month as the Axis Title.
      • In the dropdown for Category field, select Date_of_Sale.
      • In the dropdown for Group date by, select Date rollup.
      • In the dropdown for Date rollup, select Month.
    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) – Make the following modifications:

      • Under General Options, type in Total Sales as the Axis Title.
      • Under Display Options, in Formatting, click Edit.
      • In the resulting popup window, select the radio button for Custom.
      • In the dropdown for Format type, select Currency.
      • 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.

    Move on by clicking Next.

  • Wizard Step 11 – Chart Page Options

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

  • Wizard Step 12 – Details Page

    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 here to check the DataPage you’ve 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, close your preview page and click Finish. You’ve now created your very first DataPage. Note that you can edit it anytime if you need to make additional modifications.

Building a Submission Form

Now that your reps can log in and view their own sales, the next DataPage you need to build should 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 Step 1 – DataPage Type

    Click on Forms, select Submission Form, then click Next to continue.

  • Wizard Step 2 – DataPage Data Source

    Configure the following fields under DataPage Properties:

    1. Select data source – Select demo_tbl_sales to input new sales data to your sales table.

    2. Enter DataPage name – Call this DataPage: Add new Sale.

    Under Access and Security, make the following modifications:

    1. Check the Restrict Access checkbox.
    2. Make sure that the first radio button to Require Authentication is selected.
    3. Select Demo Reps Login in the dropdown.
  • Wizard Step 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 Step 4 – Configure Fields

    Before you continue, click Preview to see what your form looks like. Then apply these modifications on the following fields under DataPage Elements:

    How to Convert Excel to a Web App | Part 4: Building Forms & Reports
    1. Product_Name – To turn this from a text field into a dropdown menu that displays all available products from your products table, follow the steps below:

      • Under General Options, in the Form Element dropdown menu, select Dropdown.
      • Under Field Options, in the Source dropdown menu, select Lookup table or view.
      • Under the Lookup Table tab, in the dropdown menu for Table or view, select demo_tbl_products.
      • Make sure that in the dropdown menus for Field for display and Field for value, Product_Name is selected.
    2. Rep_ID – Since you wouldn’t want your reps to manually input their ID every time they submit the form, you’ll need to automatically stamp the ID from when they log in to your sales table. Follow the steps below to apply this function:

      • Under General Options, in the Form Element dropdown, select Hidden.
      • Under Field Options, in the first and second dropdown menus of On load, receive, select Authentication Fields and Rep_ID respectively.
    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 procedure as Product_Name with the changes below:

      • Under the Lookup Table tab, in the dropdown menu for Table or view, select demo_tbl_states.
      • Make sure that in the dropdown menus for Field for display and Field for value, States is selected.

    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 rep adds their sale, the data will also be reflected on your My Sales DataPage.

Duplicating a DataPage

Duplicating an existing DataPage is simple. For this next one, find the My Sales DataPage 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 just duplicate and modify the DataPage.

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

  • Wizard Step 3 – Search Type

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

  • Wizard Step 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 Step 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 make the following modifications:

    1. Check the box that will Enable data grouping by this field.
    2. In the dropdown for Grouping display, select Collapsible group.
    3. Select Collapsed by default from the two radio buttons underneath.
  • Wizard Step 8 – Results Page Options

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

  • Wizard Step 9 – Select Chart Type

    Under Bar Charts, select Bar.

  • Wizard Step 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 Step 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.

    Click Finish to save your work.

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. But the IDs aren’t identified by name.

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 Caspio by applying table relationships.

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

Simply follow the steps below:

  1. Right-click Tables from the main left-hand panel, then open the object in a new tab.
  2. Click Relationships at the top of the resulting screen.
  3. In the list of Tables on the left, check the boxes for demo_tbl_reps and demo_tbl_sales.
  4. Click the Rep_ID field on the demo_tbl_reps container, then drag your mouse pointer to the Rep_ID field of demo_tbl_sales. Both tables should share one common field.
  5. In the resulting pop-up window, in the dropdown menu for Display Value, select Name.
  6. Check the box underneath to Use display value on DataPages.

Caspio will identify the table relationship you’ve created as 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. 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 respective 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

Simply follow the steps below:

  1. Within the DataPages object, click the New Folder link on top and name it Admin.
  2. Next, click the New Folder link again to create your second folder and name it Reps.
  3. Since all three DataPages currently on your list are all under the user group Reps, check the boxes of all three to their left.
  4. Click the Bulk Actions link on top.
  5. Expand Sales Management and select Reps folder.
  6. Click Move.

Now you can simply expand the Reps folder to find all three related DataPages. You don’t have to deal with a long, unsorted list that will prove troublesome when editing 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 the New DataPage link on top.

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 Step 1 – DataPage Type

    Click the Forms tab and select Submission Form.

  • Wizard Step 2 – DataPage Data Source

    Configure the following fields under DataPage Properties:

    1. Select data source – Select demo_tbl_reps.
    2. Enter DataPage name – Call this DataPage: Add New Rep.

    Under Access and Security, make the following modifications:

    1. Check the Restrict Access checkbox.
    2. Make sure that the first radio button to Require Authentication is selected.
    3. Select Demo Admin Login in the dropdown.
  • Wizard Step 3 – Select Fields

    Transfer all Available Fields to your Selected Fields by clicking the “>>” symbol.

  • Wizard Step 4 – Configure Fields

    Apply these modifications:

    1. Under DataPage Elements, select Name.
    2. On Standard tab, under Field Options, check the box that will make this field Required.

    Note that you can also enable some options here to have password strength, and minimum strength 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 (Email: john@admin.com, Password: test). You should be able to see four required text fields (Name, Email, Password, Confirm Password) and a Submit button. As an admin, you can also create a password on behalf of the employee.

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 Step 1 – DataPage Type

    Click the Reports tab and select Tabular.

  • Wizard Step 2 – DataPage Data Source

    Follow the same configuration in Wizard Step 2 of DATAPAGE 4, except type in View/Manage Reps when you enter the DataPage name.

  • Wizard Step 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 Step 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 Step 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 Step 6 – Select Results Page Fields

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

  • Wizard Step 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 Step 8 – Configure Results Page Fields

    No changes required here so skip to the next page.

  • Wizard Step 9 – Results Page Options

    Again, no changes required here.

  • Wizard Step 10 – Details Page

    Select the first radio button that says Yes, enable Details Page.

  • Wizard Step 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 your list of Selected Fields.

  • Wizard Step 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.

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

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

  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 inline edits on the results table
  4. A Details link that will allow you to edit more information (including the Password field)

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.

  • Wizard Step 1 – DataPage Type

    Click the Reports tab and select Combined Chart and Report.

  • Wizard Step 2 – DataPage Data Source

    Configure the following fields under DataPage Properties:

    1. Select data source – Select demo_tbl_sales.
    2. Enter DataPage name – Call this DataPage: View/Manage Sales.

    Under Access and Security, follow the same admin configuration with DATAPAGE 4.

  • Wizard Step 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 Step 4 – Select Search Fields

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

  • Wizard Step 5 – Configure Search Fields

    Make the following modifications on these fields under DataPage Elements:

    1. Product_Name – Apply the modifications below under the Standard tab:

      • Under General Options, change the Form element into a Listbox.

      • Under Field Options, in the Source dropdown menu, select Both.

      • Under the Custom Values tab below, on the Display text field, type in Search all.

      • In the Value text field beside it, delete the mirrored input.

      • And under the Lookup Table tab, in the Table or view dropdown, select demo_tbl_products.

      Lastly, click on the Advanced tab on top and apply the modifications below:

      • 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 also implement these modifications in the Standard tab:

      • Under Field Options, in the Source dropdown menu, select Both.

      • Under the Custom Values tab, on the Display text field, type in Search Any.

      • In the Value text field beside it, delete the mirrored input.

    3. Date_of_Sale – To be able to search by date range, make the following modifications:

      • At the bottom of the DataPage Elements panel, click the insert symbol button while the Date_of_Sale field is selected.

      • Select New Criteria. This will add Criteria 1 and Criteria 2 underneath

      • Click the Elements tab of Date_of_Sale on top.

      • From the Logical operator between criteria radio buttons, select AND

      • Under DataPage Elements, click Criteria 1.

        • Change the Label into Date of Sale (from).
        • For your Comparison type, select Greater Than or Equal.
      • Under DataPage Elements, click Criteria 2.

        • Change the Label into Date of Sale (to).
        • For your Comparison type, select Less Than or Equal.
  • Wizard Step 6 – Select Results Page Fields

    Transfer the following from your Available Fields to your Selected Fields:

    1. Product_Name
    2. Rep_ID
    3. Sale_Location_City
    4. Sale_Location_State
    5. Sale_Amount
    6. Date_of_Sale
  • Wizard Step 7 – Results Page Editing Options

    Check the following boxes under Edit Records and Delete Records so your admin can edit by row, edit in bulk and delete by row whenever necessary:

    1. Inline edit (one record at a time)

    2. Bulk edit (applies same updates across multiple records)

    3. Inline delete (one record at a time)

  • Wizard Step 8 – Configure Results Page Fields

    Make the following modifications under DataPage Elements:

    1. Rep_IDs – Apply the modifications below under the Standard tab:

      • Under General Options, check the box to Enable data grouping by lookup values.

      • For the Grouping display dropdown, select Collapsible group.

      • From the radio buttons underneath, select Collapsed by default.

    2. Aggregate 1 – Make the following modifications similar to Wizard Step 7 in DATAPAGE 1:

      • Click the insert symbol at the bottom of the DataPage Elements panel.

      • Select Totals & Aggregation.

      • In the Standard tab, under Aggregate Options, select Sale_Amount from your list of Available Fields.

      • Click the ‘>‘ button to include it in your Selected Fields.

      • Make sure that Function is selected in the radio button underneath.

      • Make sure that Sum is selected in the dropdown beside it.

  • Wizard Step 9 – Configure Fields for Bulk Edit

    For this use case, you only need the ability to assign a sale to a different rep. Apply this by keeping the Rep_ID field and removing the rest: Click ‘X‘ at the bottom of the panel, then Yes in the confirmation box for each highlighted field you’ll be removing.

  • Wizard Step 10 – Results Page Options

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

  • Wizard Step 11 – Select Chart Type

    Under Bar Charts, select Stacked bar.

  • Wizard Step 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 as the Subtitle.

    2. Data Source Settings – Select the first radio button to Create series from data in a single field.

    3. Category (Y Axis) – Make the following modifications:

      • Under General Options, type in Name as the Axis Title.
      • In the dropdown for Category field, select Rep_ID.
      • In the dropdown for Group date by, select Date rollup.
      • In the dropdown for Date rollup, select Month.
    4. Values (X Axis) – Make the following modifications:

      • Under General Options, type in Total as the Axis Title.
      • Under Display Options, in Formatting, click Edit.
      • In the resulting popup window, select the radio button for Custom.
      • In the dropdown for Format type, select Currency.
      • Click OK.
    5. Value – In the dropdown for Value field, select Sale_Amount.

  • Wizard Step 13 – Chart Page Options

    No changes required here so skip to the next page.

  • Wizard Step 14 – Details Page

    Select the second radio button to have No Details Page. Then click Finish.

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

You should be able to see 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.
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 Step 1 – DataPage Type

    Under the Charts tab, in Lines and Step Charts, select Line.

  • Wizard Step 2 – DataPage Data Source

    Follow the same configuration of Wizard Step 2 in DATAPAGE 6, except type in Total Sales by Month when you enter the DataPage name.

  • Wizard Step 3 – Search Type

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

  • Wizard Step 4 – Search Filtering Fields

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

  • Wizard Step 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 as the Subtitle.

    2. Data Source Settings – Select the second radio button to Create series from data in a single field.

    3. Category (X Axis) – Make the following modifications:

      • Under General Options, type in Month as the Axis Title.
      • In the dropdown for Category field, select Date_of_Sale.
      • In the dropdown for Group date by, select Date rollup.
      • In the dropdown for Date rollup, select Month.
    4. Value (Y Axis) – Make the following modifications.

      • Under General Options, type in Total Sales as the Axis title.
      • Under Display Options, in Formatting, click Edit.
      • In the resulting popup window, select the radio button for Custom.
      • In the dropdown for Format type, select Currency.
      • Click OK.
    5. Value – Make the following modifications:

      • In the Value field dropdown, select Sale_Amount.
      • In the Marker dropdown, select Diamond.

    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 Step 1 – DataPage Type

    Under the Charts tab, in Bar Charts, select Bar.

  • Wizard Step 2 – DataPage Data Source

    Follow the same configuration of Wizard Step 2 in DATAPAGE 6, except type in Top Performers when you enter the DataPage name.

  • Wizard Step 3 – Search Type

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

  • Wizard Step 4 – Search Filtering Fields

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

  • Wizard Step 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) – Make the following modifications:

      • Under General Options, type in Name as the Axis title.

      • In the dropdown for Category field, select Rep_ID.

    4. Value (X Axis) – Make the following modifications:

      • Under General Options, type in Total Sales as the Axis title.

      • Under Display Options, in Formatting, click Edit.

      • In the resulting popup window, select the radio button for Custom.

      • In the dropdown for Format type, select Currency.

      • Click OK.

    5. Value – In the Value field dropdown, select Sale_Amount.
  • Wizard Step 6 – Chart Page Options

    In the Chart sort order dropdown, select SUM(Sale_Amount) and click the box beside it to sort by 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

Pivot tables will allow you to see fields at the top and on the left-hand side, 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 Step 1 – DataPage Type

    Click the Reports tab and select Pivot Table.

  • Wizard Step 2 – DataPage Data Source

    Follow the same configuration of Wizard Step 2 in DATAPAGE 6, except type in Sales Pivot when you enter the DataPage name.

  • Wizard Step 3 – Search Type

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

  • Wizard Step 4 – Search Filtering Fields

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

  • Wizard Step 5 – Configure Pivot Table Fields

    Make the following modifications under DataPage Elements:

    1. Columns – Make the following modifications:

      • Click the “+” symbol beside this field.
      • Select Date_of_Sale.
      • Under General Options, in the Group date by dropdown, select Date rollup.
      • In the Date rollup dropdown, select Month.
    2. Rows – Click the “+” symbol beside this field and select Rep_ID.

    3. Values – Make the following modifications:

      • Click the “+” symbol beside this field.
      • Select Sale_Amount.
      • Make sure that your values are summarized by Sum.
      • Under General Options, in Formatting, click Edit.
      • In the resulting popup window, select the radio button for Custom.
      • In the dropdown for Format type, select Currency.
      • 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 9, you should be able to see a pivot table that shows how much revenue is being brought in per month, and who among the reps are contributing the most.

See You in the Next Video!

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 with your own SQL statements, JavaScript and HTML to enhance the functionality of your web app.

What’s left now is deployment.

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.

Next Steps

NEW FORRESTER WAVE REPORT
See why Forrester ranks Caspio a 'Leader' in low-code platforms in this 2019 report.
TALK TO AN EXPERT
Have a vision for an application? Talk to a Caspio product expert and we’ll help you achieve it.
SEE CASPIO IN ACTION
Want to see if Caspio is a good fit for your needs? Choose a date and time for a personalized demo.