How to create a web-based pivot table with m-Power
Analyzing data in a spreadsheet works until you need to share it, secure it, or keep one version of the truth. This video takes an Excel file of sales and forecast data and turns it into a database-driven web pivot table in about five minutes, no coding.
What you'll learn
This short walkthrough builds a web pivot table over spreadsheet data with the m-Power Development Platform. Here's what you'll see:
Turn a spreadsheet into a web pivot table
Upload an Excel file and m-Power builds the database table and pivot report for you, with field names and types detected automatically.
Slice the data from every angle
Set rows, columns, and dimensions, then drill from region to salesperson to product to compare actual sales against forecast.
Work from one version of the truth
Replace scattered spreadsheets with a single source your whole team can open by URL, so nobody analyzes stale numbers.
Add security when you need it
Put the pivot table behind a login or apply row-level security so each user sees only the data they are authorized to view.
Full transcript
Prefer to read? Here's the complete transcript of the video.
In this video, I'll show you how to create a web-based pivot table in about 5 minutes.
To start things off, let's take a look at my data. Here I have a spreadsheet of sales and forecast data for my fictional bike company. It's broken down by sales region, salesperson, category, and product for the last 3 years.
Now, there's a lot of data here and I want to put it into a pivot table so I can easily analyze it from different angles and compare actual sales vs. forecast.
I could do that here in Excel, but making a web-based pivot table is better for a few reasons. It's easier to share with others, it gives me a single version of truth...so I don't have multiple spreadsheets floating around, and it lets me add security.
So, that's why I want to create a web pivot table, now let me show you how. I'll be using our development platform, m-Power, as it makes the process so simple.
To get started I'll need to name my report and choose a template, which basically provides the layout and features for the application I'm creating.
Now, I need to tell m-Power where my data is...but it's still in that spreadsheet. So, I'll just upload that spreadsheet into a database table. Now here, m-Power has analyzed my spreadsheet and created field names, types, and sizes based on the data. I can adjust any of that here...but I don't need to this time so I'll just move right along.
Okay, so my spreadsheet is now in a database table. I'll just select that table and choose all fields for my report.
Okay, so once I have my data selected, I'll just tell m-Power which fields to use as dimensions. For a pivot table, these are the options I'll be able to sort my report by.
Okay, so now I'm all done. Now, before I hit the build button, let me dive into the report designer options. Here, I can change a lot of features, including what type of report I want to create. Right now it's a standard report, but I'd like to make it a pivot report. I could even come back later and make it an interactive report or ranking report if I want. It's very flexible.
Also, over here, I can set which row and column fields should appear by default. Let's make it default to show product categories as columns and region and salesperson as rows. And finally, I can even add filters. For instance, what if I want to have the option to only view data for one or two of my product categories. I'll set up a filter that lets me or other users select which categories to display in my pivot table with checkboxes. Now, m-Power does let you create database-driven option lists that can be reused across all applications...but I don't have any set up now so I'll just add each option manually. Of course, if I was making a lot of applications, those database-driven option lists would save a lot of time.
Okay, that looks good. Now I'll just build and wait while m-Power puts everything together here.
Now...how long did that take? Maybe 5 minutes at most and I've taken a spreadsheet and turned it into a pivot table. Now that it's built, let's check it out.
As you can see, it's sorting my data by category up here and by region on the side. If I drill into each region, I can see each salesperson and their sales.
Now, what if I want to view this data differently. Maybe instead of salesperson, I want to drill into products. And...maybe I want to view 2021 sales data and forecast data. I can set up that view really fast. Now I can compare which regions met their forecast numbers last year and which are falling behind.
And finally, let's check out that filter. What if I only want to see accessories? It's pretty easy to filter my data that way. I could play around with this for quite a while and show you all the different ways to view your data...but for sake of time I'll leave it here.
Now, I could do a lot more here. Because this is a web application, I'd just need to share this URL with anyone I want to view the data. But, what if I want to secure this data? I could go into m-Power and setup security so users would have to login to view the data. Or, I could add multi-tenant security, so each user can only see the data they're authorized to see. Or, I could create applications to maintain this data. This is really just the tip of the iceberg here, but there's a lot more possible.
If you'd like to learn more about m-Power or set up a demo, visit us on the web at mrc-productivity.com. Thanks for watching.
See what you could build from your spreadsheets
We'll walk through m-Power live, using your data and your use case, and answer any questions your team has.