Alright, Now Let’s Begin Writing the Sales Comparison Report
We are going to build a report using Peachtree (becoming Sage 50) as our data source, but the steps below could be used to create a similar report from any Sage accounting source.
Step 1 of any custom report project is to acquire the report layout. We find that beginning with a sample Spreadsheet works best.
Next, Identify fields and properties needed to develop the report. Data fields and expressions are known as Columns in Business Intelligence.
This analysis will help you determine which container to use for your report.
On the report we will be writing, notice there are two groups of similar information on one sheet. The premise of this report is to compare the same sales information over two different Periods of time. We will use pivot tables to represent the 2 Periods of time you want to compare.
Building the 1st Sub Report (Standard Report)
In order to produce this kind of report, we will build a union report made up of two separate standard reports. Each standard report will have one of the two different Sales Periods. The Standard Reports will be called Sales Period 1 and Sales Period 2.
Columns needed for this report: Company Name, Customer Name, Customer Type, Item Description, Line Quantity, Line Sales Amount, and Date.
Open Report Manager – in the Peachtree company you will be using to write your report.
Now we want to give our new report a home. Right click on Home and select add folder called [Sales Comparison].
First we will build the two Standard reports.
Right click on your new folder and select Add Report. Select Standard Report and call it [Sales Period 1]. Next up comes the list of all existing containers. You can alphabetize the container names by clicking on the name header. For our report let’s select Sales Analysis. The container name can be duplicated. However, each container has a unique ID in the right-most column of the list. We are going to use Container ID 2.
Next, select the fields for the report placing a checkmark in the box next to the field names then select OK.
Company Name, Customer Name, Customer Type, Date, Item Description, Line Quantity, and Line Sales Amount
Select Sales Period 1 report and you will see the properties and controls of the report on the right side of the screen.
Click on Columns Tab – shows fields we selected from the container.
This container includes all types of sales transactions so we need to filter transaction type to be only equal to Invoices and Credit Memos.
On Filter Tab – Click Add button and you will find all fields from the container. You do not have to have the fields in the report Columns to use them as a filter. Select Transaction Type. Since we want more than one transaction type we will use Is In for this report. Click on the Ellipsis… button to bring up the list of choices from the Container. We will checkmark Invoices and Credit Memos. OK.
We want to give the user the ability to select any Sales Period by creating two date Parameters — Begin Date and End Date, which will form a Date Range. From the Parameter Tab select the Add button, scroll down to Date. The first selection will be Greater Than or Equal To. Leave default blank.
Repeat the process to create the ending date parameter. From the Parameter Tab select the Add button, scroll down to Date. Now select Less Than or Equal To. Leave the default blank.
Ok that’s it! The 1st sub report is complete
Building the 2nd Sub Report (Standard Report)
To save time, let’s just copy the Sales Period 1 Report and create Sales Period 2 with it since all the fields and filters are the same. Right click on the Sales Period 1 report and select Copy, Right click on the Sales Comparison folder and select Paste. Notice the copied report is called Copy of Sales Period 1. To rename it right click on the copy and select rename. Change it to Sales Period 2.
Building the Union Report
Now we are ready to create our Union Report using the 2 Standard Reports we just made — Sales Period 1 and Sales Period 2.
Right click on the Sales Comparison folder and select Add Report, This time select Union Report and name the report Sales Comparison. Select Sales Period 1 and Sales Period 2 from the list of all BI reports shown.
Notice that the icon for the Union Report is green and the icon for the Standard Report is blue.
Double click the Sales Comparison report and highlight Union Sub Reports. On the right side notice that both of your Standard Reports are listed. Also notice that both Standard Reports are headed to the same output sheet 1.
**TIP** Now, having both sub reports go to the same raw data sheet 1 isn’t always a bad thing. It would be a great way to create a report following a database rebuild where you want to combine two different databases in one report. As long as the data columns are identical (as they are in our report here), they could populate Sheet 1, one above the other and then could be pulled in together as a pivot table and be able to show a continuous date range spanning the old data and the rebuilt data.
However, for our Sales Comparison report we need the reports headed to separate output sheets. So we need to push each sub report to a different sheet, so right click on Sales Period 2. Select Properties and change the output sheet to 3. The report parameters are headed to sheet 2 so we don’t want to choose that sheet. We will look at the parameters on Sheet 2 a bit later.
1st Run of Union Report
We are ready to run our Union Report. During the report design and development we will stay in Report Manager — later our finished report can be run directly from Peachtree.
In the Report Manager select Sales Comparison and click on the Run icon at the top or right select run.
Because SPBI runs sub reports from the bottom up the first prompt is for Sales Period 2. Enter Begin and End dates by clicking on the Ellipsis button to bring up a calendar. Select February 1, 2012 to February 29, 2012
The second prompt is for Sales Period 1. Select January 1, 2012 to January 31, 2012.
The report is now sending Sales Period 1 Raw Data to Sheet 1 and Sales Period 2 Raw Data to Sheet 3 and Sheet 2 will have the Report Parameters. We will be using the user selected dates from this sheet later in a handy formula.
In Excel notice on both Raw Data sheets (Sheet1 and Sheet3) there are data fields that are blank <Blank>. These can be avoided by using filters on the two standard reports back in the Report Manager.
Also notice that the Raw Data is sorted by Date. In our finished report we want the results to be sorted by Customer Type then Customer, this will be taken care of in our Pivot Tables later.
The first time you run a new report it is not unusual to find something that needs to cleaned-up as we did with this run. So let’s close Excel and do not save. Return to the Report Manager to filter out those blank data fields.
Clean Up Raw Data
To remove the blank data fields we need to add a Filter on both Standard Reports. On Sales Period 1 select the Add button from the Filter tab; select the data field, Item Description then Not Equal to <Blank>.
In Peachtree there are several different item classes that can be assigned to an inventory item. Inventory items with the Item Class = Description such as “Thank you for your business” are of no use to a sales report so we will filter them out as well. To eliminate description items from our report add a Filter for Item Class not equal to 2 (and 2 designates the Description Item Class).
Also add the two filters to Sales Period 2. Item Description Not Equal to <Blank> and Item Class not equal to 2.
To save time in this building and testing phase let’s also enter default date prompts so we don’t have to keep entering the date ranges every time we run the union report..
On both Standard Reports, return to the Parameter tab and this time lets enter the default dates. Select Sales Period 1 and on the Parameter Tab select properties by right clicking on the first Date parameter. In the Properties window, select the ellipsis button next to the Default/Prompt field & populate January 1, 2012. Repeat for the End Date using January 31, 2012; For Sales Period 2 the Begin Date is February 1, 2012 and End Date is February 29, 2012>> we’ll remove these default parameters later when the report is finished.
2nd Run – Check Clean-Up
Go ahead and run the report & notice that the Sales Periods now have the defaults we setup. So now all you need to do is click OK instead of entering the date range parameters.
When we did the first run of the report, we found blank data fields so we did not save our work and returned to the Report Manager to filter out these blanks. Often it takes a couple of practice runs to catch all the items that need to be cleaned up. Let’s see how we did…
Notice blank rows are now removed on Sheet 1 and Sheet 3.
Rename the Sheet 1 to Period 1 and Sheet 3 to Period 2. Right click on the tab and Rename.
We have made some positive progress with our report so let’s Create & Link. Creating & Linking is the magic of BI that renders the data saved for all time and available to Peachtree from the Business Intelligence menu.
While the Excel Workbook is still displayed, return to Report Manager to save our work.
Right click on the Union Report name (Sales Comparison) and select Create and Link. From the list select the Workbook that contains your report. The first time you save your report this will be Book1.
Select Yes when prompted about placing parameters on the Second Sheet.
On the Template File Format screen select the version of Excel to use for the report. We will be using Excel 2007 Template, which works for both 2007 & 2010.
Book 1 will be changed to the report name unless you choose to change it. Just accept the default & click OK.
Now our report is saved with what we have done so far and we can continue to build it. Let’s run the report again using the defaults dates
We are using an Excel 2007 Template for this report. If you are using an older version of Excel, like 2003, you could select that instead, and it will still work for this report. However, we would encourage you to upgrade to the newer version of Microsoft Office for SPBI reporting so you don’t miss out on some of the cool things you can do in the newer version of Excel.
Build Sales Comparison Pivot Tables
Now we are going to begin using our raw data to build the report that the user will see.
1st, lets create a new Sheet to provide a target for our two Pivot Tables and rename it Sales Comparison. Make sure that the new sheet is not in position 1, 2 or 3 — these are reserved for Raw Data and Parameters.
On Period 1 Sheet, click inside the table and select Pivot Table from the Insert tab of the ribbon. Press F3 to see a list of Named Ranges, select Raw Data.
**TIP** Selecting Raw Data (a named ranged that is automatically created by BI). This is a nice feature that has a brain. No matter how many rows of data your date range produces, the named range = Raw Data captures exactly that same number of rows. Yes, it IS magic!
Choose where you want the Pivot Table and select Existing worksheet. Go to the Sales Comparison tab & click in cell B10, then select OK
Place fields onto our pivot table using Period 1 data.
Highlight Customer Type, left click and drag to the Row Label box, left click and drag Customer Name to Row Label box. Left click and drag Item Description to Row Label box. Left click and drag Line Quantity to Values box. Left click and drag Line Sales Amount to Values box.
Clean Up the Pivot Tables
Highlight the Row Labels field in cell B10 and in the formula bar change it to Customer Type-Name.
Rename Vales Fields. Highlight Sum of Line Quantity and change to #Sales, highlight Sum of Line Sales Amount to $Sales.
**TIP** If you get a message when you try to rename a field that it already exists, just add a space to the right of the name in the formula bar.
Format values by clicking on Unit Sales in the Value Box select Value Field Settings, select Number Format button, Number and Use 1000 Separator and decimals places 2. Format Amount Sales with the same settings.
Now let’s create the pivot table for Period 2.
On Period 2 Sheet, click inside the table and select Pivot Table from the Insert tab of the ribbon. Press F3 to see a list of Named Ranges, select Raw Data. Choose where you want the Pivot Table and select Existing worksheet. Go to the Sales Comparison tab & click in cell H10, then select OK
Add all the same fields and formatting as we did for the other pivot table…
To identify the two Sales Periods we will Build Titles on Sales Comparison Sheet.
In cell B7 enter “Sales During Period 1:” In cell H7 enter “Sales During Period 2:” Format both cells as font size 20.
Create Formulas to Display User-Selected Date Ranges
Write an Excel formula to bring in the parameter date ranges from Sheet 2 for both periods.
Before we do let’s go over to Sheet 2 & expand all the columns. Column D contains the date and time the report was run and the user input, in this case our date ranges for Period 1 and Period 2.
Return to Sales Comparison Tab and click in C7 to write our formula. Enter =TEXT( then go to Sheet 2 and select cell D5, now hit F4 to turn this into an Absolute Cell Reference, now type a comma and enter the date format “MM/DD/YY”) type & then ” to ” another & then TEXT ( and select cell D6 and hit F4 again to turn this into an Absolute Cell Reference, type a comma and enter the date format as before. After entering the close parentheses press Enter. The completed formula should look like this:
=TEXT(Sheet2!$D$5, “MM/DD/YY”)&” to “&TEXT(Sheet2!$D$6, “MM/DD/YY”)
To save time you could copy and paste the formula and modify the cells being used. But we are going to write the formula from scratch once again. Practice makes perfect and perfect is good…
In I7 enter =TEXT( then go to Sheet 2 and select cell D3, now hit F4 to turn this into an Absolute Cell Reference, now type a comma and enter the date format “MM/DD/YY”) type & then ” to ” another & then TEXT ( and select cell D4 and hit F4 again to turn this into an Absolute Cell Reference, type a comma and enter the date format as before. After entering the close parentheses press Enter. The completed formula should look like this:
=TEXT(Sheet2!$D$3, “MM/DD/YY”)&” to “&TEXT(Sheet2!$D$4, “MM/DD/YY”)
Align cells B7 & C7 to center using the Middle Alignment button. Also cells H7 & I7.
In cell F5 type “Sales Comparison Report” and change font to 16 point underlined and centered
In cell F3 pull in the Company Name. Type an = sign and then click in cell A2 of Period 1 sheet and Enter. Change font to Bold 20 point and centered
Delete Rows 1, 3 and 7.
Adjust Column Width of Column A to make it skinny.
Let’s give both tables some color
Click inside the Period 1 table & select the Design tab under Pivot Table Tools
Change the Pivot Table Design for Period 1 to Medium 3.
Click inside the Period 2 table & select the Design tab under Pivot Table Tools and change Period 2 to Medium 6.
Right Click the Sales Comparison tab & change the color to blue
Highlight C7, D7, I7 & J7 and right justify the labels over the amount.
The Sales Comparison Tab is now complete!
It is a good practice to save your work as you make progress so let’s return to the Report Manager and Create and Link.
Since we have placed pivot tables in our report since the last time we linked, a Pivot Confirmation screen will come up recommending that you switch off Saved Data With Table Layout. Select Yes.
Each time we link, SPBI will ask us to replace the template. Select Yes.
