Tag Archives: grouping and nesting

Events, Excel, OfficeWriter, Webinars

New Webinar! Make Reports that Measure Up

Take a look at how OfficeWriter can turn your drab Excel reports into chart-topping spreadsheets. This month, it’s all about music as we cover your favorite bands, artists, and labels.

In this webinar we’ll cover:

  • Grouping and nesting in Excel
  • Using SQL Server Reporting Services (SSRS)
  • Charting in Excel and relationships between genre, artists, labels, and album price

When: Friday, August 23, 2013 at 1 P.M. EST

*Register early as space is limited.

Can’t attend? Register anyway, and we’ll send a copy of the slides and recording following the webinar.



Business Intelligence, Excel, OfficeWriter

Creating a Sales Pipeline Report with ExcelWriter

One of the great features of ExcelWriter is Grouping and Nesting feature of ExcelTemplate. Grouping and Nesting allows you to easily format your data in order to make it more readable. This feature is especially handy when applied to a common report, such as a sales pipeline report. Internally at SoftArtisans, we use ExcelWriter with Grouping and Nesting when generating our own sales pipeline report! By taking advantage of this feature, you can get fancier looking reports with little additional effort – ExcelWriter does the formatting work for you!

Before we begin, let me say that I will not attempt to tell you how to query your data as I’m sure everyone will be using different CRM systems. However, no matter how you’re querying your data (be it SQL, or a web service like SOAP or REST) the process is generally the same.

To start, you need to decide how you want to group your data. In most cases you’ll want to group first by month or quarter; then you could group by salesperson. These can be whatever you want, but you must have a column in your result set that will represent each.

For example, we group our own sales report data by: Continue reading Creating a Sales Pipeline Report with ExcelWriter →

ExcelWriter, Knowledge Base

Grouping and Nesting vs. PivotTables in ExcelTemplate

Although PivotTables and ExcelWriter’s Grouping and Nesting can produce similar looking results when used with ExcelTemplate, these options provide different functionality. This post outlines the pros and cons of using both options.

Pivot Tables

Pivot tables are a native Excel feature that are designed to sort and filter aggregate data. You can use them to transpose and combine data to present grouped views.

Useful Features

  • Group data – This can be done horizontally in row groups or vertically in column groups.
  • Sort data – Pivot tables automatically sort and group data.
  • Produce grouped calculations – i.e. the sum over all of the sales for a region, or the larger expense for a particular quarter
  • Transpose data

Limitations (when working with ExcelWriter)

  • You cannot pre-select filters, create groupings by hand etc. – Any feature that requires data to be in the pivot table cannot be selected in the template because the data won’t be imported until after the report is generated
  • Pivot tables have limited style options. Pivot tables can have table styles, but creating custom headers/footers for groups is challenging
  • You cannot put anything to the right or below the pivot table. This is because when the pivot table is populated, it will expand to the right and down. It will overwrite anything that was in those cells. For this reason it is recommended that you do not have multiple pivot tables on a worksheet.
  • Pivot tables do not allow for text in the data values area of the table. Text is only allowed in the report filters, row labels, or column labels.

PivotTables in OfficeWriter Docs:

Grouping and Nesting

ExcelWriter Grouping and Nesting allows you to format grouped data in OOXML (XLSX, XLSM) files using ExcelTemplate.

Features

  • Custom formatting – You have the ability to create multi-row headers and footers that contain whatever formulas, formatting, and content you’d like.
  • ‘Push down’ behavior – ExcelTemplate automatically pushes down rows when inserting data. This means that anything below a grouping block will be retained in the final report.
  • Accepts any data type – There are no limitations on the type of data that be represented (unlike pivot tables which only allow non-numerical data in the row/column labels).

Limitations

  • Data must be sorted ahead of time, otherwise Grouping and Nesting will not work properly (see information below on how to create Grouped and Nested reports)
  • Unable to group over columns

Grouping and Nesting in OfficeWriter Docs:

Excel, OfficeWriter

Grouping and Nesting on Multiple Columns

Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers to display flat data in a grouped and nested format using the ExcelTemplate object and a OOXML (.xlsx) template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!

Introduction

In my previous post, I showed how José, a salesperson at PostOne Bicycles, was able to easily pick out the sales made by the sales team in different regions by using a %%group marker. His coworker, Jillian, wants to go further than that, breaking down sales first by region, then by Salesperson, so that she can see from a glance whose sales are strong or weak in each region, and whether she needs to improve her performance. That’s easy to do thanks to the nesting feature of ExcelTemplate grouping and nesting.

Nested grouping with multiple groups

Jillian can group by multiple columns with nesting by placing additional %%group markers in more columns. Continue reading Grouping and Nesting on Multiple Columns →

Excel, OfficeWriter

Using ExcelTemplate Grouping

Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers to display flat data in a grouped and nested format using the ExcelTemplate object and a OOXML (.xlsx) template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!

Introduction

If you have hierarchical data in an Excel report, grouping the data is a good way to make it easier to read. ExcelTemplate now features grouping a nesting options that make it easy to group hierarchical data. The basic feature of ExcelTemplate grouping and nesting is removing unnecessary repeated values from a field if it is given hierarchical data in a flat format. We’ll focus on a single group in this post; for information on grouping and nesting multiple groups, you can check our documentation, and be sure to check back here for further blog posts. Advanced features such as headers and footers are also discussed in our documentation and will also be described in a future blog post.

Creating a Data Set for grouping

Though grouping and nesting is meant to show hierarchical data, the input data for a template with grouping and nesting must be in flat format. This means that a data set for nesting and grouping will have repeated values for fields that are higher in the hierarchy. In this case, salesperson José Saraiva of PostOne Bicycles is faced with sales data that looks like this: Continue reading Using ExcelTemplate Grouping →