Scroll

Break Data Into Columns

Suppose you have a report like the one below showing customers and how many purchases they have made in the past year.

This report may be simple and easy to understand but by no means has it extracted real business value from your data. For instance it would certainly help to know that B’s Beverages makes all its purchases in the beginning of spring or that last year there were no purchases in February.

This post will walk through modifying the report to show sales broken down by month. In the process we will also highlight a few tips for using the report designer.

 

Adding Columns and Formatting

The image below shows the report designer for the original report.

 

Normally to add a column we would click at the top of a column and select ‘Insert Columns Before’ (see below). This command would add a singular column between A and B.

 

To save time we can add multiple columns at once. Click on A, then while holding shift click the last column (E).  Now when you select ‘Insert columns After’ five columns will be added. Repeat this until there are twelve columns between the customer name and the number of purchases.

 

To fit all the columns on a single page we can re-size each column by click and dragging on the right side. Don’t worry about matching the width each column we will do that shortly.

 

Next we want the title to spread from columns A to N. Shift click to select all of row one and then click the ‘Merge selected button.

 

Type the abbreviations of each month in the appropriate column. Select columns B – M and click ‘Set All Width Identical’.

 

 

Type the abbreviations of each month in the appropriate column. Select columns B – M and click ‘Set All Width Identical’.

With all these modifications done the report looks ready to have the data added.

 

Putting Data into Rows

Since we are already in a group footer that sorts by Customer the relevant data is the date of each order. For each Date we want to check if the month corresponds to the column we are in then add 1 to the number of purchases if it does. This can be done in the formula editor. Since we are embedding functions, start with the Sum function to keep the total. Drag in the If then the Month function to check the month of the order. The resulting formula is “=Sum( If(Month({Orders.OrderDate})= 01, 1, 0))”. For help with functions see this article.

 

Clicking OK will add the formula to the selected cell. Instead of using the formula editor eleven more times hold CTRL click the formula and drag it to each of the other cells.

 

Now each cell checks if the month is January and does the sum accordingly. To fix this click on each cell and change the if condition to the appropriate month.

 

Running the report now we see that we are almost done, but the chart has a lot of zeros that make it look cluttered. Also we would like to see the totals for each month at the bottom of the report.

 

Add a report footer by clicking in the left most column. Using the CTRL drag  copy row three into the new section.

 

To remove the zeros hold shift and select the formulas in the Company Name Footer. Click the format cells menu. In the number tab check the ‘Blank When Zero’ and make sure the decimal places are set to 0.

Clicking ok and run the report.

 


Hidden Article Information

Article Author
Natalie
created 2015-11-05 23:09:33 UTC
updated 2016-02-19 20:10:51 UTC

Labels
formulas, data, formatting, columns,
Have more questions? Submit a request