Enjoy customized, flexible reporting with the Report Manager module: Part 2 of 3

In part one of this three part series you learnt that Sage Intelligence allows you to manipulate data before placing it into Microsoft^® Excel^®.  It’s a great way of removing unnecessary data.  Now you are ready to create your own customized reports. By applying your Excel skills to further manage your data using PivotTables and PivotCharts or Power View.  And of course save the report, with your customization’s, back to the Report Manager.

Let’s see how we can use the data in “Sheet1” to design a relevant dashboard for your company. Firstly we will look at using a PivotTable, which allows you to extract meaningful data from the raw data on “Sheet1”, and then create a PivotChart which will give you a visual representation of that PivotTable.

1. From the Report Manager run your report into Excel.
2. Click on the data in “Sheet1” of your workbook. Then click on the INSERT tab and select the PivotTable icon, this will open a dialog box which will help you create your PivotTable. To select a table or range, click inside the box and press the F3 key and then select the “RawData” name range. This will ensure that your data is placed into the PivotTable automatically each time you run this report.
3. We want this to be created in a new worksheet, so select OK.
4. An empty PivotTable will open and to view the fields you want to see in the PivotTable click the tick boxes in the PivotTable Field List, and then rearrange them to create your own layout. (Use Excel Help found in Excel or Online if you need some guidance)
5. Rename that new sheet to “Dashboard” so you can identify what the different worksheets are used for.

You might want to create a PivotChart for your dashboard.  When dealing with a lot of complex worksheet data it can be difficult to see the big picture. PivotCharts can help you make sense of this data by placing data into graphs for the analysis of data and is ideal for presentation of data.

1. On the “Dashboard” worksheet, click on the PivotTable and then click the PivotChart icon on the ANALYZE tab.
2. In the Insert Chart dialog box, select the chart type you want to use and click OK this will create a visual display of your data.

One of the cool features of Pivot Charts is the Expand function.  If you set them up correctly, you can right-click on the chart and select Expand, you will then have the ability to drill down into the information from the Pivot Chart.  You can learn how to set up this up by watching this recorded webcast.

Now we look at creating a Power View dashboard using the data in “Sheet1”.  Power View is a great way to present your data, it allows you to be interactive with your tables and charts and lets you further examine the data visually.

1. To create a Power View worksheet you need to change your data in “Sheet1” into a table format. Click on the data in “Sheet1” and then click the Format as Table icon on the HOME tab and select a Table Style. When selecting the data for the table ensure you select the “RawData Name Range” by using the F3 key.
2. Create a Power View worksheet by clicking on the INSERT tab and then Power View icon.
3. In the new worksheet called “Power View1” in your workbook you can select the fields you want to use to create your table.
4. You can then convert these tables into bar/column charts or maps by using the DESIGN You are also able to drill down on these charts just by double clicking on the chart.

Now let’s look at the Report Manager to save this worksheet as a template, so that the next time you run the report you will be able to view your personalized dashboards.

1. While the Excel workbook is still open navigate back to the Report Manager window.
2. Then click Save Excel Template icon in the Home tab and follow the dialog boxes to save the template. The Excel template will be linked to the report in the Report Manager.

We have so many great resources to help you get started on your path to becoming a reporting guru.  Watch all these steps above on this webcast.

If you are not sure how your Excel skills stack up, take a free pre-assessment or contact our Excel on Steroids team about the variety of courses that can help you create incredible dashboards. You can also download our free 40 Microsoft Excel tips and tricks eBook.

To recap;  you have learnt how to create your own dashboard in Excel and then save the report template back to the Report Manager so that your changes are shown on your report every time.

In part three of this three-part series, I will cover how to further improve the report template without changing or losing the original template.