Getting From Aaarrgh to Aaahh: Building a Business Intelligence Report from Scratch for Beginners Part III of III

This is the Wrap-Up of the III Part Blog Series

You will get the most value from this blog if you review Part II first, because I am going to build upon the same report that we built in that blog. It may be easier to follow along if you have already built the Sales Comparison report for yourself.

Let’s Get Started

From your Peachtree company, using the menu, Reports & Forms select Business Intelligence Setup, then Report Manager.  Now Let’s Run the Sales Comparison Report using the default dates.

Create 2 Dashboards from our data – The Good Stuff & The Bad Stuff

In just a moment we are going to graphically display our sales comparison information by creating 2 Dashboards from our data — The Good Stuff and The Bad Stuff.  We will display the Top & Bottom Customers from both Sales Periods and the Top & Bottom Items from both periods using Excel Pivot Tables and Graphs.

Build Source Pivot Tables that will feed the Dashboards

Let’s make a few more sheets for our workbook to give a home to our Pivot Tables needed for the 2 Dashboards we are going to build.

Create 6 new worksheets.  Now we will rename them The Good Stuff, The Bad Stuff, Top 5 Customers, Bottom 5 Customers, Top 5 Items, and Bottom 5 Items.

Now Let’s Focus on the Customer Information

Let’s 1st populate the Top 5 Customers Pivot Table-Period 1

Position cursor on Period 1 Sheet and insert new Pivot Table from Raw Data on Top 5 Customers in B4.  Drag Customer Name to Row Label box and Line Sales Amount to Value box.

You could drag Line Quantity to the Value box if you wanted to compare quantities instead of dollars.  We will be comparing dollars for this exercise.

Type Period 1 in cell B2.

Now let’s pull over Period 2 to the Top 5 Customers sheet

Position cursor on Period 2 Sheet and insert new Pivot Table from Raw Data on Top 5 Customers in F4.  Bring in the same fields to build the table.

Type Period 2 in cell F2.

Modify Both Pivot Tables

Pull down the filter button on the Period 1 Pivot Table and select more sort options.  Select Descending radio button and change box to Sum of Line Sales.  This will sort the data highest to lowest.

Select filter button again and select Value Filters and Top 10.  Then change to top 5. OK.

Left select Sales Amount in the Value box, select Value Field settings, select Number Format button, Number and Use 1000 Separator and decimals places 2. OK.

Repeat for Period 2.  Pull down the filter button on the Period 2 Pivot Table and select more sort options.  Select Descending radio button and change box to Sum of Line Sales.  This will sort the data highest to lowest.

Select filter button again and select Value Filters and Top 10.  Then change to top 5. OK.

Left select Sales Amount in the Value box, select Value Field settings, select Number Format button, Number and Use 1000 Separator and decimals places 2

Now let’s copy our Top 5 sheet over to our Bottom 5 sheet to save some build time

Highlight the entire sheet & right select copy

Go to the Bottom 5 sheet and highlight the entire sheet.  Then right select & choose Paste

Change Top to Bottom

All we have to do is change the value filter from Top to Bottom

On the Period 1 Pivot Table select filter button and select Value Filters and Top 10.  Then change to Bottom 5.

Repeat for Period 2 Pivot Table.  Select filter button again and select Value Filters and Top 10. Then change to Bottom 5.

Now Let’s Work on our Item Sheets

Now let’s populate the Top 5 Items Pivot Table-Period 1

Position cursor on Period 1 Sheet and insert new Pivot Table from Raw Data on Top 5 Items in B4.

Drag Item Description to Row Label box and Line Sales Amount to Value box.

Type Period 1 in cell B2.

Now let’s pull over Period 2 to the Top 5 Items sheet

Position cursor on Period 2 Sheet and insert new Pivot Table from Raw Data on Top 5 Items in F4. Bring in the same fields to build the table.  Type Period 2 in cell F2.

Modify Both Pivot Tables

Pull down the filter button on the Period 1 Pivot Table and select more sort options.

Select Descending radio button and change box to Sum of Line Sales.  This will sort the data, highest to lowest.

Select filter button again and select Value Filters and Top 10.  Then change to top 5. OK.

Left select Sales Amount in the Value box, select value fields setting, select number with 2 decimals and thousand separator. OK.

Repeat for Period 2.  Pull down the filter button on the Period 2 Pivot Table and select more sort options.

Select Descending radio button and change box to Sum of Line Sales. This will sort the data highest to lowest.

Select filter button again and select Value Filters and Top 10.  Then change to top 5. OK.

Left select Sales Amount in the Value box, select value fields setting, select number with 2 decimals and thousand separator. OK.

Now let’s copy our Top 5 sheet over to our Bottom 5 sheet to save some build time

Highlight the entire sheet & right select copy

Go to the Bottom 5 sheet and highlight the entire sheet.  Then right select & choose Paste

Change Top to Bottom…again

All we have to do is change the value filter from Top to Bottom

On the Period 1 Pivot Table select filter button again and select Value Filters and

Top 10.  Then change to Bottom 5.

Repeat for Period 2 Pivot Table.  Select filter button again and select Value Filters and Top 10.  Then change to Bottom 5.

The Good Stuff Dashboard

Now our source sheets are in place, let’s build some dashboards

OK, our first Dashboard is going to be built on the Good Stuff tab

Let’s Create our 1st Graphic for The Good Stuff Dashboard

On the Top 5 Customer sheet, Position cursor on Period 1 Pivot Table.  Go to Pivot Table Tools, Options, Pivot Chart.

Select Column Styles.  Right select on the new chart and Copy.

Go to Good Stuff sheet in position A1 and Ctrl-V to paste.

Reduce the column width of column H to the right border of your chart.

Collapse row 15 to line up the bottom of the chart.

Right select on the Customer Name Filter and select Hide all field buttons on chart.

Right select on Total legend and delete.  Right select on Total at the top and change to “Top 5 Customers – Period 1”.

Go to Pivot Chart Tools Design and select Style 44.  4th from left on bottom row

Now We Are Ready to Continue With the 2nd Graphic

Return to Top 5 Customers sheet and Position cursor on Period 2 Pivot Table.

Go to Pivot Table Tools, Options, Pivot Chart.  Select Column Styles.

Right select on the new chart and Copy.  Go to Good Stuff sheet in position I1 and Ctrl-V to paste.

Right select on the Customer Name Filter and select Hide all field buttons on chart.

Right select on Total legend and delete.  Right select on Total at the top and change to “Top 5 Customers – Period 2”.

Go to Pivot Chart Tools Design and select Style 47.  2nd from right

Are We Having Fun Yet?

On the Top 5 Items sheet, Position cursor on Period 1 Pivot Table. Go to Pivot Table Tools, Options, Pivot Chart.  Select Exploded Pie in 3D Styles. Right select on the new chart and Copy.  Go to Good Stuff sheet in position A16 and Ctrl-V to paste.

Right select on Item Description Filter and select Hide all field buttons on chart.Right select on Total at the top and change to “Top 5 Items – Period 1”. Go to Pivot Chart Tables Design and select Style 42.  2nd from left

On the Top 5 Items sheet, Position cursor on Period 2 Pivot Table. Go to Pivot Table Tools, Options, Pivot Chart.  Select Exploded Pie in 3D Styles. Right select on the new chart and Copy.  Go to Good Stuff sheet in position I16 and Ctrl-V to paste.

Right select on Item Description Filter and select Hide all field buttons on chart. Right select on Total at the top and change to “Top 5 Items – Period 2”.

Go to Pivot Chart Tools Design and select Style 47.  2nd from right

Polish Off the Dashboard

In cell D31 type “Period 1”.  Go to Sale Comparison sheet and copy cell C5 to Good Stuff cell D32.  Center both new fields and make the font size 16 bold

In cell L31 type “Period 2”.  Go to Sale Comparison sheet and copy cell I5 to Good Stuff cell L32. Center both new fields and make the font size 16 bold

Collapse column P to match the edge of the charts. Highlight P33 to A30, right click and select format and make the fill black.

Also, make the font light blue

Change the color of The Good Stuff sheet tab to green.

The 1st Dashboard is now complete & beautiful!!

Save our progress by creating and linking again.

The Bad Stuff Dashboard (how would you know the other stuff was good,

unless you identified the Bad?)

Let’s start by making a Pivot Chart for Bottom 5 Customers-Period 1 on Bottom Customers sheet. On the Bottom 5 Customer sheet, Position cursor on Period 1 Pivot Table. Go to Pivot Table Tools, Options, Pivot Chart.  Select Column Styles. Right select on the new chart and Copy. Go to The Bad Stuff sheet in position A1 and select Ctrl-V to paste.

Reduce the column width of column H to the right border of your chart. Collapse row 15 to line up the bottom of the chart. Right select on the Customer Name Filter and select Hide all field buttons on chart. Right select on Total legend and delete.

Right select on Total at the top and change to “Bottom 5 Customers – Period 1”.

Go to Pivot Chart Tools Design and select Style 45.  4th from right on bottom row

Continue On with Period 2

Position cursor on Bottom 5 Customers Period 2 Pivot Table and create a Column-style Pivot Chart & place in cell I1 of the Bad Stuff dashboard.

Right select on the Customer Name Filter and select Hide all field buttons on chart. Right select on Total legend and delete.  Right select on Total at the top and change to “Bottom 5 Customers – Period 2”.

Go to Pivot Chart Tools Design and select Style 46.  3rd from right

Now Let’s Move Over to the Bottom 5 Items

     By now this should all be getting quite familiar

          But don’t stop reading…there is some really good stuff coming, scouts honor!

Let’s make a 3D pie chart from Bottom 5 Items Period 1 & drop it into cell A16 on our Bad Stuff dashboard. Right select on Item Description Filter and select Hide all field buttons on chart. Right select on Total at the top and change to “Bottom 5 Items – Period 1”.

Go to Pivot Chart Tools Design and select Style 45.  4th from right

One Last Chart for our Dashboard

From Bottom 5 Items Period 2

Make another 3D pie chart and can drop it into cell I16 on the Bad Stuff dashboard. Right select on Item Description Filter and select Hide all field buttons on chart. Right select on Total at the top and change to “Bottom 5 Items – Period 2”.

Go to Pivot Chart Tools Design and select Style 46.  3rd from right

Now let’s give our dashboard the footer information & to save time we will just copy it over from the Good Stuff dashboard

Grab cells A30 – P33 from The Good Stuff & click CTRL-V in cell A30 of The Bad Stuff

If the right edge is ugly, adjust the column to line it all up

Go ahead and make The Bad Stuff tab red

**TIP** If you are using Microsoft Office 2010 you could add some cool slicers

to the Dashboards but this would be a discussion for another time.

Final Clean-Up

OK, we are done with all the building, let’s just clean up and do one final test

When the Sales Comparison report is run we only want the user to see our cool pivot tables and dashboards so we will hide all unnecessary sheets.

Right click on the Period 1 tab and select Hide.

Do the same for tabs Sheet 2, Period 2, Top 5 Customers, Bottom 5 Customers, Top 5 Items, Bottom 5 Items.

For the 3 remaining sheets select the View Tab and uncheck the Formula Bar, Headings & Gridlines. 

Notice how clean the final sheet looks.

Let’s Create & Link and return to the Report Manager for some housekeeping tasks.

On both standard reports return to the parameter tab to remove the defaults for the date range parameters.  Select Sales Period 1 and on the Parameter Tab select properties by right clicking on the first Date parameter.  In the Properties window delete January 1, 2012.  Repeat for the End Date deleting the default January 31, 2012.

Do the same for Sales Period 2.  Select Sales Period 2 and on the Parameter Tab select properties by right clicking on the first Date parameter.  In the Properties window delete February 1, 2012 and for the End Date delete February 29, 2012.

This gives the report user the power to compare any 2 periods >> yesterday to today; last week to this week; last month to the same month last year…and so on

The user would never run the two boring Standards Reports alone —  they would only run the jazzy Union Report Sales Comparison.

So let’s hide Sales Period1 and Sales Period 2 from the reports menu in Peachtree. On the Properties Tab on both Standard Reports select Show Advanced and uncheck Make Available in Menus and checkmark Report Is Hidden. Apply.

Now let’s make sure that the Union Report is listed in Peachtree’s Reports & Forms menu under Sage Peachtree Business Intelligence.

On the Properties Tab of the Sales Comparison report select Show Advanced and checkmark Make Available in Menus and uncheck Report Is Hidden. Apply.

In the report description field you can place any text that you want to appear in Peachtree to describe the report or identify the report author.

For this exercise type “This very cool report was created by the Dave & Darlene team” then Click Apply.

The Final Report Run to Make Sure it is Perfect (because perfect is good!)

Run the report out again; however, this time you will have to enter the default dates for Period 2 February 1, 2012 to February 29, 2012 and Period 1 January 1, 2012 to January 31, 2012 – Or any two date ranges you want to see on the report!

Before you do your final Create & Link, select the sheet you want to be your opening sheet.

You can even create a nice splash screen with a menu containing hyperlinks to other sheets or handy details about the behavior or instructions for your new report.  Placing your logo on such a menu page really looks nice.

Select Sales Comparison sheet to make this the sheet report will open to when run.

Create & Link for the final time.

Let’s Test Drive our shiny new report in Peachtree

(becoming Sage 50)

To verify that Sales Comparison is now in the list of SPBI reports return to your Peachtree company and go to Reports & Forms menu / Business Intelligence.

Find your Folder in the List – Sales Comparison

Notice the Report Description you typed in Report Manager on the right side.

Let’s run our report from here by double clicking the report.  Remember that you can use any date range you want for your new report.

That’s It!  I Told You it was Going to be Easy

If you have made it to here, thank you for your patience.  It has been my pleasure presenting this information to all.  The intent of the preceding blog series was to present BI in such a way so as to appeal to BI beginners who have been seeking the courage and basic knowledge to begin building your own reports.  Once you feel comfortable with the concepts shared in this III Part series, I assure you that there is a lot more to learn if you seek to sharpen your BI skills to keen precision. I believe it is difficult to achieve big things without first mastering a ton of small things.

Good Luck on your Journey!