🚀 New WebDataRocks v. 1.4.Check it out!

đź“ť Write for us!
Check out our guest posting guidelines. We'd love to share your experience and opinion with our dev community. 👩‍💻👨‍💻

Reporting tips & tricks: format data in a pivot table

In the previous tutorial, you learned how to filter pivot table data to show relevant information first. 

Let’s not stop there!

This time you’ll learn how to make your report look visually perfect. For this, you’ll use the interactive formatting feature of WebDataRocks. 

Why formatting is important

An essential part of any report or dashboard is its readability. Your audience should understand its meaning at a glance. 

Therefore, as a finishing masterstroke of your data analysis, you can format essential data to make it easy to digest. 

In this tutorial, we will consider two main types of pivot table formatting, namely:

  • Number formatting
  • Conditional formatting

Want to get straight to the practice part?

Jump to the Live demos section.

Otherwise, let’s dive into the motivation behind data formatting first.

What is the number formatting?

The number format defines how numerical values are shown on the grid. It is useful in many cases, for example:

  • Formatting financial data (e.g., adding and aligning currency symbols, showing values as percentages, etc.).
  • Making data look cleaner by controlling the number of digits after the decimal separator.
  • Handling regional difference, e.g., writing decimal numbers with comma or period, formatting the thousandths place.

Apart from the cases mentioned above, you can also:

  • Align values on the right or left side of the cell
  • Control how empty cells or infinity values are displayed
  • Define the maximum number of symbols to show in a cell

To examine all the number formatting options, check out our detailed Pivot table number formatting guide. 

How to use number formatting in a pivot table

You can set a number format for particular measures of the pivot table widget in any of the following ways:

  1. Via the UI 
  2. Here is how end-users can format their data via the intuitive and comfy pop-ups:

  3. In code

The programmatical approach gives you more freedom to implement custom logic of formatting. 

Let’s see the samples and gain some practical coding skills.

  1. Format values of “Price” with a dollar symbol.
  2. Format values as percentages.
  3. Change the number of decimal places displayed in a pivot table.

What is conditional formatting?

Conditional formatting is a particular formatting type by cells’ values.

The name itself suggests that formatting is based on conditions, i.e., user-defined rules that describe how values have to be formatted. This rule is a logical statement, also known as a boolean expression). The rules are always applied to the pivot table measures. 

With conditional formatting, you can change the color of the cell’s background and text and manage the report’s typography: font color, family, and size.

This feature helps you highlight information that is crucial to see first. It also allows you to make trends in data more noticeable. 

How to use conditional formatting in a pivot table

  1. The most convenient way to apply conditional formatting is via the user interface.

A comfy color picker lets you choose the color that suits your reporting goals best:

You can also set a custom hex color value for text or background.

  1. In code, it is as simple as defining a tiny JavaScript object in your report’s structure. 

For example, here is how you can color pivot grid cells green based on the values of the “Revenue” measure:

"conditions": [
        {
            "formula": "#value > 600",
            "measure": "Revenue",
            "format": {
                "backgroundColor": "#8BC34A",
                "color": "#FFFFFF",
                "fontFamily": "Arial",
                "fontSize": "12px"
            }
        }
    ]

Live demos ?‍??‍?

  1. Format values of “Price” with a dollar symbol
  2. Format values as percentages
  3. Change the number of decimal places displayed in a pivot table
  4. Apply conditional formatting to the pivot grid
  5. Apply conditional formatting to the data grid

Wrapping it all up

Now you made sure how flexible WebDataRocks Pivot is when it comes to prettifying your data. Not only can you analyze your data fast but also prettify your report neatly and make it speak.

Useful reading ? 

Documentation

More reporting tips & tricks

To improve your reporting skills, learn how to filter pivot table data, and set a data slice.

Ready to share results? Not sure how to do it best? We’ve got you covered here as well!

Move on to a detailed tutorial on how to save and export a pivot grid report.

Learn about the pivot table essentials with the detailed UI guide

Advanced pivot table formatting

If, by any chance, there is an option that you haven’t found in WebDataRocks, we recommend testing Flexmonster Pivot Table & Charts – an even more powerful pivot table component. It offers extra number formatting options: negative numbers formatting, centered alignment of values, and more. 

Last time, you learned how to arrange fields on the grid and filter data records in a table.

In this tutorial, you will learn how to save your pivot table reports fast and conveniently.

(more…)

We continue exploring the functionality of WebDataRocks Pivot – a reporting tool that integrates seamlessly with the most popular front-end frameworks. You can add it to your product and provide end-users with data visualization and analytics capabilities. 

In the previous part of the “Reporting tips & tricks” series, you learned about the purposes of the pivot table’s data slice.

This time, you will learn how to make your reporting more advanced by creating filters in the pivot table.

In WebDataRocks Pivot, you can filter your data interactively or with a simple code configuration. 

If you’d like to learn by doing without reading the explanations, jump straight to the section with live demos that show how to filter pivot table data in different ways. 

The tutorial is divided into two main parts:

  1. Filtering data via the UI
  2. Filtering data with code

But first, let’s have a quick theoretical introduction. 

What is filtering?

With filtering, you can perform a more in-depth analysis. To filter the data means to show a part of the data that meets a certain criterion. In other words, filtering is used to show specific information that’s relevant to a question a data analyst is asking.

Once the data is filtered, it’s narrowed to a smaller portion and doesn’t distract with unnecessary details. 

Types of filters

WebDataRocks Pivot provides three main types of filters to end-users. 

You can filter data by:

  • field members (label filter)
  • values (number filter)

These two types filter data in specific rows or columns. Another filter type that stands aside is a report filter. It filters the entire report to show specific data records. In WebDataRocks Pivot, you can place a report filter above the grid’s header by dragging and dropping a field from the Field List.

How to filter data in the pivot table

1. Filtering data via the visual controls

The pivot table is designed to let you filter data on the fly. 

Let us guide you through the filtering via the UI process. 

How to filter data by members

After you configured the report, open the field values (members) list by clicking the field’s caption. 

Check or uncheck the boxes near the field’s members that you want to include or exclude from the resulting data subset.

If the list of members is too large, using the search box comes in handy. 

How to filter data by value

A value (number) filter filters records in a row or column field based on aggregated values. 

There are two types of number filters you can use:

  • Top X values 
  • Bottom X values

where X is the number of records to show on the grid.

This is pretty much similar to Excel’s filters by values you got used to. 

To use a filter by value, click the field’s caption on the grid, select the number of data records, and the criteria – the highest or lowest measure’s values to display.

How to set a report filter

A report filter is applied to the entire report to show values for specific items. 

First, open the Field List and choose the field to filter by. Next, open the list of members by clicking the field’s caption and filter data by members or values.  

The area above the grid’s header is called the report filtering area:

How to apply multiple filters in the pivot table

You can also apply multiple filters to the hierarchy. For example, here’s how you can combine a filter by value and by member:


As you see, configuring filtering in a report is as easy as several clicks. 

Now let’s get some coding practice and do the same but using JavaScript.

2. Filtering data with code

How to filter data by members

In the chosen hierarchy, add the filter object and specify an array of members. The negation property controls whether to include these members to the subset of fields shown on the grid or not.

Here’s a tiny code snippet that shows how to exclude two specific members from the Country hierarchy that is placed into rows:

"rows": [{
    "uniqueName": "Country",
    "filter": {
        "members": [
            "Country.United Kingdom",
            "Country.United States"
        ],
        "negation": true
    }
}]

How to filter data by value

In the filter’s object of a specific hierarchy, define the filter type, the number of records to show, and a measure to filter by:

"columns": [{
    "uniqueName": "Category",
    "filter": {
        "type": "top",
        "quantity": 2,
        "measure": "Discount"
    }
}]

How to set a report filter

Setting a report filter is similar to the above approach. The only difference is that doesn’t relate to any specific row or columns – the changes are applied to the entire grid. You can choose here any hierarchy you want to filter the data by. 

Here’s how you can filter data records by a specific business type:

"reportFilters": [{
    "uniqueName": "Business Type",
    "filter": {
        "members": [
            "Business Type.Warehouse"
        ]
    }
}]

Examples & demos

The best way to learn is by practice. 

These demos will help you to understand how to add filters to the pivot table component. Feel free to experiment with code.

Feedback

We hope you enjoyed this tutorial. 

We would be happy to hear your thoughts on your progress with pivot table reporting. If any questions arise, contact our team on Forum

To keep updated with new blog posts, you are welcome to follow WebDataRocks on Twitter

What’s next?

This article is a part of the introduction to the web pivot table terminology. We do our best to show how to solve real-life use cases with our reporting tool. 

Find more useful tips & tricks in the following blog posts and documentation:

What is R Shiny?

R Shiny is a package for building interactive web apps with R. 

The distinguishing feature of R Shiny is that you can build analytical apps with minimum lines of code and simple syntax, and put them on the web in a short time.

Pivot for R Shiny

Great news for the data science and analytics community!

Now you can use our pivot table component in R Shiny projects.

We are extremely grateful to our active community for contributions. Thankfully to Mohamed El Fodil Ihaddaden, now our users can perform data analysis with WebDataRocks in R Shiny apps. 

R Shiny comes with a great collection of interactive widgets. We believe WebDataRocks perfectly complements it. With the pivot table and simple syntax of R, you can build a dashboard that assists in connecting data scientists with decision-makers.

Features & benefits of Pivot for R

The role of WebDataRocks lies in aggregating and presenting your data in a neat tabular report. By handling all the data-related calculations, it acts as an engine for your dashboard. So, once it’s added to the dashboard, end-users can:

  • Drag and drop fields on the grid
  • Slice and dice data to compose a unique report
  • Sort and filter data records
  • Highlight values with conditional formatting
  • Format numbers on the grid
  • Export reports into a suitable format

And more! To dive into the world of WebDataRocks features, peek into our UI guide.

Creating a simple analytical app with R Shiny

Let’s apply our knowledge in practice and create a single-file R Shiny app with basic reporting functionality.

Create a new directory (e.g., reporting_app) and the app.R file that contains a user-interface definition and a server script. Run the R Studio and make this folder your working directory.

Next, install the pivtalibrary – a wrapper for R:

remotes::install_github("feddelegrand7/pivta")

Then, load the package to the app:

library(pivta)

Next, render the pivot table using pivta() function and passing the path to a data source and the report configuration as inputs.

pivta(dsource = "https://cdn.webdatarocks.com/data/data.csv", report = "https://cdn.webdatarocks.com/reports/report.json")

Here is how the full code of the app looks like:

library(shiny)
library(pivta)
ui <- fluidPage(
  
  # App title ----
  titlePanel("Reporting app"),
  pivtaOutput(outputId = "pivot_table")
)
server <- function(input, output) {
  
  output$pivot_table <- renderPivta({
    pivta(dsource = "https://cdn.webdatarocks.com/data/data.csv", report = "https://cdn.webdatarocks.com/reports/report.json")
  })
}
shinyApp(ui = ui, server = server)

Despite being simple, it creates an app with powerful pivoting features. 

Results

Now you have an interactive dashboard in your R Shiny app. You can extend the app's look and feel by tailoring the UI, add more interactive elements and graphs to your dashboard, or even implement more complex server-side logic. 

Be open to experiments. Make your analysis more interactive, comprehensive, and powerful with WebDataRocks for R. 

Code 

Find the R wrapper for WebDataRocks Pivot Table on GitHub

If you’ve been following our release updates, then you definitely know we released integration with amCharts.

We feel this new shiny feature needs to be covered in more detail.

If you feel this way, too, make yourself comfortable and let’s figure out what this integration can bring to your reporting and data visualization processes.

(more…)

Learn data, and you can tell stories that more people don’t even know about yet but are eager to hear.

– Nathan Yau

Alongside with critical thinking and programming skills, being able to tell a story with your data is a must-have 21st-century skill.

Regardless of the sphere you are working in, sooner or later you’ll have to present the results of your work and share some of your expertise about the topic with the audience. The main goal is to communicate your messages clearly. As a rule, to find the answers to your questions, you need to use the data that comes from the various data sources in a raw format.

Therefore, to turn the data into insights, it’s crucial to acquire skills and master techniques of effective working with the data. In this case, data visualization blogs are of great assistance. Reading about experiences of professionals, you may become inspired to start new projects, learn how to get a better understanding of your data and come up with new ideas to make your data presentations shine.

Continuing our data visualization project, we’ve decided to make a list of excellent, to our opinion, blogs which are created to help you enhance the storytelling-based approach to data visualization.

Information is beautiful

This blog is designed to convey the art of data visualization. As soon as you are taken to its main page, you may become struck by rich and engaging visualizations. The blog’s mission is to show how to present data in captivating graphics and make the numbers speak loudly. All infographics are approached with care so as to help grasp the main ideas hidden behind them effortlessly. Visualizations answer various relevant questions related to demographics, politics, economics, etc.

For example, check out interactive infographics about Hollywood films and see it with your own eyes.

Data Plus Science

This blog is full of resources and tips that help you get better at Tableau. Tutorials are of special interest for data analysts – they are detailed and incredibly helpful for building any kind of chart.

You can also find here visualizations of various trends in the data.

The Functional Art

This blog is created to help you get smarter about design and reading visual information.

If you love long reads – you’ve absolutely come to the right place.

The blog contains profound books overviews which help improve your graphical literacy. In addition, you can take a look at examples of astounding data visualization and storytelling or check out the author’s books related to data visualization.

We recommend following this blog to always stay informed of the latest overviews and tutorials.

AnyChart blog

This blog is famous for offering the DataViz Weekly overview which highlights the coolest visualization projects found on the web.

If you prefer learning by doing, you can dive into tutorials and practice creating stunning charts with JavaScript.

FlowingData

The blog’s founder is Nathan Yau, a statistician, whose mission is to communicate the importance of data visualization’s role to everyone.

FlowingData can be called a paradise for R programmers & statisticians. Besides, it contains versatile content for all tastes and purposes.

Not only can you get a deeper understanding of the ways data influences our daily life but also learn how to create visualizations like an expert (mostly in R). We recommend paying attention to the guides which focus on specific topics and courses which explain in a step-by-step manner how to excel at data visualization in R.

Modern Data

This is one more blog where you can master the technique of presenting your message simply and clearly.

A multitude of tutorials will help you learn key principles of data visualization with Plotly. All tutorials are freely available to anyone who is interested in data science and visualization.

Plotly Blog

In the data science world, Plotly needs no introduction. It is a high-level charting library and a popular data visualization toolbox. Not only the company develops a top-notch product but also provides a top quality multipurpose blog.

The Plotly Blog is a holy grail for data scientists, data visualization enthusiasts, mathematicians, and engineers. Here you can make use of tutorials and guidelines to master visualization techniques and become an expert in the data visualization field.

DataQuest Blog

The DataQuest Blog is a treasure trove of up-to-date articles dedicated to data science and career paths. You can get here the essential knowledge required to become a skilled data scientist, reveal new ways of working with data and discover scholarships. Whether you are a Python or R programmer, you’ll find something beneficial for yourself. At your service, there are career tips that help sharpen both technical and soft skills, student stories and courses which are divided into missions for convenience – such a breaking a big task into smaller ones help you feel less overwhelmed with studying new things.

The blog gives you the feeling that the content is created with the care of learning experience and keeping students motivated and deeply engaged in what they are studying.

Here you have an opportunity to learn the building concepts of data science and choose your own path you want to excel at.

Driven By Data

This blog is a collection of tailored infographics which provide with insights on political, demographic, social and economic topics. It’s curated by Gregor Aisch – a data journalist whose work is acknowledged by Society for News Design Malofiej Infographics Summit.

Visit its website to immerse the data visualization world, full of surprises and hidden insights.  

The Economist: Graphic detail

A professional blog created by best analysts of the Economist. Here you can sense a feeling of true data journalism as it is and enjoy data storytelling in its very best.

Daily charts are dedicated mostly to the topics the journalists are specialized in – social science, ecology, and, of course, economics. The blog is updated every weekday.

Tableau Blog

Alongside with product’s information, news and tips on using Tableau, this blog is a great source of inspiration for data analysts. Here you can dive into experiences of like-minded people from all over the world, learn about the social impact of data and become motivated to start new data visualization projects. Tutorials are definitely worth your attention – with their help, you can learn to create visualizations like a pro.

Final words

We do hope the above-mentioned blogs will set you on the right path of bringing your data storytelling to life.

Don’t be afraid to face difficulties – break out of your comfort zone and challenge yourself at every opportunity.  You have the whole potential to join the community of data visualization experts. Start with practice and the fruits of your work won’t be far behind.

Living in the age of information overload, it’s hard not to become overwhelmed by myriads of front-end and back-end development courses found on the web.

So as not to let you stumble upon, we’ve carefully selected the best courses that offer only relevant and actual knowledge on the topic. Each of them is the result of tight cooperation between industry experts and world-renowned universities. 

If you are thinking about starting a career path either as a front-end or back-end developer, this roundup is for you. 

Front-end development courses

Introduction to web development 

by the University of California, Davis on Coursera

This course is often recommended as the best for complete beginners but it also helps to refresh basic knowledge of experienced developers. This course presents an excellent start for everyone who is eager to become a web developer and doesn’t know where to start.

With the course, you’ll get a profound understanding of how websites work, how they are built, how browsers and devices interact with the web. As for practical skills, you’ll learn how to create dynamic web pages, get familiar with programming language concepts, and deploy your first website. 

Prerequisites: you can enroll in this course without any previous experience in programming or web development. 

It takes around 16 hours to finish the course. We are sure it will prepare you for future challenges of more advanced web development. Give it a try!

Front-End Web Development with React

by The Hongkong University of Science and Technology on Coursera

This course is a part of Full-Stack Web Development with React specialization. As the name implies, it’s dedicated to building front-end applications with the help of the React library. As the course progresses, you’ll get the idea of creating responsive UI design with Reactstrap, grasp basic React concepts, learn about Flux architecture, Redux, client-server communication, REST API and how to use React router in SPAs (single-page apps). 

Prerequisites: solid knowledge of Bootstrap 4, JavaScript and ES5 are recommended.

It takes around 32 hours to finish the course. 

As a result, you’ll be comfortable with both creating responsive React-Redux apps and designing its architecture.

Multiplatform Mobile App Development with React Native

by The Hongkong University of Science and Technology on Coursera

Are you more interested in developing mobile apps? Then you should pay close attention to this React Native course. This is another course of the same specialization but focused mainly on developing cross-platform iOS and Android apps with React Native, Redux and the Expo SDK.

Prerequisites: basic knowledge of programming concepts, HTML, CSS, and JavaScript. No previous competence of React is required. 

It takes 27 hours to complete the course. 

As a result, you’ll know how to build multiplatform applications, use React Native, Expo SDK, and Redux. 

Try this course to acquire and master your React and JavaScript skills!

Single Page Web Applications with AngularJS 

by Johns Hopkins University on Coursera

If you prefer AngularJS to React, we have something for you. During this course, you’ll deepen your knowledge of the following concepts: dependency injection for reusing existing services, writing own services, creating reusable HTML components with AngularJS data binding and extending HTML syntax with directives. Plus, you’ll learn about routing for multiple views and performing unit-testing. As a final project, you’ll build a well-structured and tested web app and deploy it to the cloud. 

Prerequisites: only a good understanding of HTML, CSS, and JavaScript is required. 

It takes 37 hours to complete the course. 

Web development on Experts Exchange

by Experts Exchange

Web development includes all aspects of presenting content on intranets and the Internet, including delivery development, protocols, languages and standards, server software, browser clients, databases and multimedia generation.

Experts-Exchange provides articles, courses, videos and forums for all web development related aspects. Courses range from basic to intermediate levels and include training courses to prepare participants for exams.

Prerequisites: you are required to know programming in general all the basics and more advanced information you will find there.


Server-side development courses

Let’s proceed to server-side development now. 

Server-side Development with NodeJS, Express, and MongoDB 

by The Hongkong University of Science and Technology on Coursera

This excellent course focuses on server-side aspects of web development. You’ll get the idea of web protocols (HTTP and HTTPS), NodeJS modules, CRUD operations, NoSQL databases (namely MongoDB and Mongoose), REST concepts, and Backend as a Service approaches. 

Prerequisites: all you need to know is basic HTML, CSS, and JavaScript. 

In total, it requires around 4 weeks of intensive study to complete the course.

As a result, you’ll be proficient at configuring the backend server using NodeJS, building a RESTful API from it and handling authentication and security. 

Building Web Applications in PHP 

by the University of Michigan on Coursera

Are you fond of PHP? Then you’ll like this course. During this time, you’ll learn about the basic structure of a web app, request and response cycles, HTML, CSS, syntax and data structures of PHP. The course covers also concepts of error handling and superglobal variables. 

Prerequisites: you are required to know programming in general (in any language). It’s also recommended to take the Web Design for Everybody specialization before starting. 

It takes approximately 19 hours to finish the course. 

By the end of the course, you’ll develop the practical skills of installing PHP and MySQL environments like MAMP or XAMPP and create an application that is perfect to be added to your portfolio.

Responsive Web Design Certification 

by freeCodeCamp

When it comes to development, it’s hard to find better courses than on freeCodeCamp. Here you can gain knowledge in various areas and connect with the developers’ community on the Forum.

Besides, all the courses are completely free for everyone.

We’d like to draw your attention to Responsive Web Design Certification. To complete the entire certification, it takes approximately 300 hours. Yes, it’s a lot of work but that’s worth it. It’s the most comprehensive web development course we could find. Here you’ll get everything to kick-start a career in front-end development. 

The course covers all the concepts of basic HTML and HTML5, CSS, Applied Visual Design, Applied Accessibility, Responsive Web Design principles, CSS Flexbox, CSS Grid. As a result, you can put your knowledge into practice by creating an educational project on a given topic.

Foundations of Front-End Web Development 

by Udemy

This course will help you to lay the ground for web development as well. You’ll walk through the major concepts of HTML, CSS, JavaScript, and jQuery. You’ll even get a chance to master Backbone.js

Prerequisites: no previous knowledge of the topics is required. 

After the completion, you’ll get enough knowledge to become a junior front-end developer. It will be easier for you to approach any resources on web development.

Web Application Development with JavaScript and MongoDB 

by Goldsmiths, the University of London on Coursera

This course is a part of the Responsive Website Development specialization and aimed at developing skills in advanced web application programming: building, testing, and deploying mobile-ready web applications from the bottom upwards. 

It’s often completed by entrepreneurs, software engineers, data engineers, and technical support associates. During the course, you’ll learn how to control data read and write access, access the database, and use the SimpleSchema system. Besides, you’ll get acquainted with TextCircle – a collaborative code editing environment. 

To consolidate what has been learned, you’ll dive into various programming assignments, theoretical and practical quizzes. In the final module, you’ll develop an application using Meteor folders.

Prerequisites: as background knowledge, it’s assumed that you have basic programming skills and basic ideas of ICT concepts.  

It takes approximately 19 hours to complete the course. But that’s not a strict requirement – feel free to work at your own pace. 


More about web development

If you can’t decide on a framework for your application, dive into this overview of the best front-end frameworks in 2019.

If you are searching for more tutorials and courses for web development, try GitConnected. The main advantage of this resource is that there are plenty of articles and courses collected in a single place. You can filter them by category and choose your perfect match.