Resolver One: Pivot Chart, Pivot Table, and Auto filter

Pivot Charts, Pivot Tables and Auto filter are three tools that help you analyze spreadsheet data. Resolver One currently does not include these 3 features, but since it is based on the IronPython programming language the data can be easily exported to another system for analysis. In this example we will create a spreadsheet with data from the System.DateTime object and then export the data into Microsoft Excel for analysis.

Many business applications utilize complex business objects that help manage the workflow of processes or procedures. For instance, a company may have a .NET business object that represents a product that they sell and can be used to manage the sales, inventory or production of that product. By encoding the business rules into a .NET object, those rules can be used across multiple business systems to reduce system errors and make maintenance easier. In this example we will be using the System.DateTime object to generate our worksheet data and analyze the number of days in a given date range. We will also use a Pivot Chart to visualize the data in ways that may be difficult to do from a normal spreadsheet or chart.

To start, we will open Resolver One and load the “Rsl Pivot Chart, Pivot Table, and Auto filter V1.4.rsl” workbook. The next step is to use the Select buttons to set the Start Date and End Date. Instead of using a simple text entry, which would allow users to enter invalid data, we have chosen to use a customized data entry form that is based on a Windows.Forms.MonthCalendar control. When you press the Select Button, a new form will appear and you can select the date from the calendar. Users can use the arrows to change the month, or they can click on the month or year in the header to change those values. To select the Start Date simply click the day on the calendar and then press the save button.

Once the Start Date and End Date are selected, you will see that the spreadsheet gets loaded with data for each day in the date range. In our case this data is generated by creating a DateID for each row and then using Column Level Formulae to fill in the data for each row. The table below shows how easy it is to fill in the entire spreadsheet using only one Formula for each column. For more details, please refer to the Code Overview section.

Index	Column Name	Column Formulae
A	DateID	None. Value set by PreConstantsCode.LoadWorksheet Method
B	Date	=StartDate.Value.AddDays(A_)
C	DayOfMonth	=B_.dt.Day
D	Month	=B_.dt.ToString("MMM")
E	Year	=B_.dt.Year
F	DayOfWeek	=B_.dt.DayOfWeek
G	DayOfYear	=B_.dt.DayOfYear
H	DayType	=IF(B_.dt.DayOfWeek in (DayOfWeek.Sunday, DayOfWeek.Saturday), 'Weekend', 'Weekday')
I	WeekOfMonth	=B_.WeekOfMonth()

Now that the data is loaded, we can click the Pivot button to export the data into a Microsoft Excel spreadsheet that includes an Pivot Chart, Pivot Table, and Auto filter. In order for this to work you must have a licensed copy of Microsoft Excel installed on the computer used to run Resolver One. The data in the Resolver One Workbook will be packed into an array and then loaded into a new Excel spreadsheet using COM automation. This also means that if you make any changes to the original spreadsheet in Resolver One you will need to recreate the pivot table.

For more information about how to use the Pivot Chart, Pivot Table, and Auto filter, please refer to the Data Analysis section.