Build better trend reports with Pipelines

One of the main ways Quickbase helps you perfect your unique business processes is by automating routine work. This saves time, improves your employees’ morale, and reduces risk for your business.

Many companies use automations to supply the data for trend reports today. Did you know you can also power trend reports using pipelines? Pipelines is an exciting new technology which makes it easier than ever for you to automate your workflow, even across your ecosystem of software tools. Read on to learn how to set up trend reports using pipelines.

Example use case: managing a portfolio of IT projects

Reporting on trends in your business isn’t limited to specific industries or use cases. However, for the sake of simplicity we’ll focus on a specific example to walk through the process.

Imagine that your company’s IT team uses Quickbase to manage their projects. These projects are wide-ranging, from purchasing and integrating new software platforms, deciding how to upgrade employee laptops, or modernizing their technology ecosystem to protect sensitive customer data.

Imagine that your company’s executive team has identified some key processes that are currently managed in spreadsheets, and this year one of our major priorities is moving those processes into the cloud. Keeping track of the progress of those projects is critical to the company’s success and growth in the coming year.

How pipelines can help

Many Quickbase customers build trend reports to monitor projects and to identify ways they can improve their processes every day. Charts like the one below help you stay ahead of the curve, and you can populate this type of visualization using Pipelines.

Creating a user token

To connect a pipeline with one of your Quickbase apps, you will need a user token. So, before you create your pipeline, it’s best to create the user token. Here’s how to create one:

1. Click your name in the top-right corner of the page.
2. Select My Preferences.
3. Scroll down and click the link next to “Manage User Tokens”.
   
   
4. Click + New User Token.
5. Enter a name for the user token. In this case, for clarity we’ll call the token “User Token for pipeline – weekly project snapshot”.
6. Under “Assign Token to Apps”, select the app your pipeline will be working with. If your pipeline connects multiple Quickbase apps, you would select all those apps here.
7. Click Save, close the User Tokens tab, and return to your app.

Creating a snapshots table

To create a trend report like the one above, you’ll need to snapshot data on a regular basis. You could snapshot hourly, daily, weekly, or monthly. For this example, we’ll snapshot the data weekly. You’ll create the chart in a separate table which stores those snapshots, so you’ll need to create such a table if you don’t have one already. Think about the vital stats you need for your trend reports and create fields for those. For this example, we’ll snapshot Project Name and Status. As you can see above, you don’t need to relate the snapshots table to other tables in your app.

Tracking projects by week

Before creating your pipeline, you’ll need to add a formula to your Projects table. This will stamp each snapshot with the week start date. Here’s how to set up the formula:

1. Open your Projects table and click the gear to access the table settings.
2. Next to Fields, click + New.
3. Name the field Week Start Date.
4. Set the field type to Formula – Date.
5. Click Add.
6. Click the name of the new field to access its properties.
7. In the formula box, enter this formula:
   
   FirstDayOfWeek(Today())
   
   
8. Click Save.
   
   
   

Creating a new pipeline

Since pipelines can work across Quickbase apps, you access pipelines from the app bar.

Here’s how to get started building your pipeline:

1. On the My Pipelines page, click Create a pipeline.
2. At the top of the page, name the pipeline. For this example, we’ll call it “IT Project Manager: Weekly Project Snapshot”. All pipelines you create will be in a single list, across all applications you manage. So we recommend you include the name of the Quickbase app in the pipeline name to keep things organized.
3. On the right, click the Quickbase channel.
4. Click Connect.
5. Enter your realm name. If you’re not sure what your realm name is, look at the address bar and enter whatever appears before “.pipelines.quickbase.com”, such as this:
   
   
6. Return to your other Quickbase tab and copy the user token we created earlier. (To get there, return to your app > click your name at the top of the page > click My Preferences > scroll down and click the link next to “Manage user tokens”).
7. Click Connect to Quickbase.

Next we need to generate the snapshots.

Generating the snapshots

This company has five statuses for its projects: Not Started, Planning, Implementation, Review, and Completed. We want to snapshot all projects that are in progress, which would be any project where the status is Planning, Implementation, or Review. To set up the snapshot creation:

1. Click and drag the Search Records step from the list on the right, into the dotted area on the left.
2. Click in the Table box and select “Projects”.
3. Click Add Conditions.
4. Select the Status.
5. In the box below, select Planning.
6. Click Or, select the Status field again, and select Implementation.
7. Click Or, Select the Status field once more, and select Review.
   
   
8. Click and drag the Create Record step from the right, into the dotted area below the Do box.
   
   
9. Click in the Account box and select the same user token we used in the trigger step. In this example, that would be “User Token for pipeline – weekly project snapshot”.
10. Click in the Table box and select the Project Snapshots.
11. Click the Status option in the middle of the page, then drag it into the Status box on the left.
    
    
12. Click the Project Name option in the middle of the page, then drag it into the Project Name box on the left.
    
    
13. Click the Week Start Date option in the middle of the page, then drag it into the Week Start Date box on the left.
    
    

Next we’ll schedule the pipeline to run weekly.

TIP: To easily navigate around the pipeline builder, use the toggle in the top-right of a step to expand or collapse it.

Setting up the pipeline schedule

Next we’ll schedule when the pipeline should run. For this example, we’ll schedule the pipeline to run weekly.

1. Click Schedule pipeline in the top-right corner.
   
   
2. You can schedule your pipelines to run hourly, daily, weekly, or monthly. Let’s schedule it to run weekly, early in the morning on Mondays. That means there will be a fresh update waiting for your colleagues as they start their week.
   
   
3. Click Schedule pipeline.

Testing the pipeline

After you’ve created or updated a pipeline, you should test it out. You can test a scheduled pipeline by clicking Run pipeline in the top-right corner. After you do, check your app to make sure the pipeline worked as you expected.

Building your trend report

Once you have your pipeline working, you can create a chart on the Project Snapshots table to see the progress of your projects over time. For example, this is a stacked bar chart showing the week-to-week state of open projects: