Bookmark this page Print this page

Producing Pivot Tables & Graphs from Durell

First select the policies you want included in your report. Do this on the Policies screen in Durell, either via the “Signpost” button menu item for “Mailshotting”, or by clicking the policy mailshot markers so they get set to “Y” in the MS column.

 PT Policy list.jpg

Then open the Document Viewer, select “New” and start a “Spreadsheet”. On the following “Link to Excel” pop-up, set “Select records” to “Policies, Mailshot = Yes” and choose the “Data field set” to use (e.g. “Spreadsheet”). If required, you may click the “Setup” button to create a new data set to send a different selection of data to Excel.

 PT Link to Excel.jpg

This will export the information you have selected into Excel, as shown below.

 PT Exported info.jpg

Now in Excel 2010, click the “Insert” tab along the top, then click “Pivot Table”, as shown below….

PT icon.jpg

Select the data you want to report on by clicking the icon at the far right of the “Table/Range” field…

 PT Select range.jpg

…and then by highlighting all of your exported data.

 PT Select exported.jpg

Then choose to create a table in a “New Worksheet”…

PT New worksheet.jpg

…and drag the appropriate headings (or data fields) into the ‘Row’, ‘Column’, or ‘Values’ boxes, as per the illustrated example below, which is to display how much premium each adviser has earned from the different kinds of policies…

PT Fields to add.jpg   PT Drag fields.jpg

This will result in a table with product types down the left and adviser initials across the top…

 PT Resulting table.jpg

You can then add a “filter” by dragging another heading (or data field) into the Report Filter box, such as “Occupation”, shown below…

 PT Occupation.jpg

…to allow you to re-display the table for just one or a selection of occupations….

 PT Re-selection.jpg

You can then enhance the design and colour of your table using the ‘Design’ tab to choose one of the appearance options…

 PT Appearance.jpg

…for example to change its style to that shown below.

 PT Style.jpg

And finally you can create a graph simply by clicking ‘PivotChart’ icon, shown (centre right) below…

 PT Type of graph.jpg

…which will open a menu from which you can chose the type of graph…

 PT Example graph.jpg

…after which it will appear. You can choose to display it in the same screen or in its own window, and like the pivot table you can choose to change its colour, appearance and style before printing, saving or adding to a Powerpoint presentation.