Code On Time web applications offer the Export to Spreadsheet action. The feature is available in all grid views of your application by default.
Select this option and you will be prompted to download a file. The following screen shot shows the prompt displayed by Internet Explorer 9 at the bottom of the web browser window.
The file has the “*.iqy” extension. This file format is recognized and supported by Microsoft Excel.
If you save the file to the hard drive and open it in the Notepad then you will see a cryptic-looking URL that points to your web application.
If you choose Open option instead then Microsoft Excel will start if you have it installed on your computer. You will see a prompt similar to the one in the next picture.
Microsoft Excel wants to let you know that it is going to download the content, which is not located on your computer. You have to authorize that by clicking on Enable button. Nothing dangerous is coming your way. The content is an XML file produced by your application in response to the URL in the “*.iqy” file.
Click Enable and you will see the prompt shown next.
This time Microsoft Excel contacts your web application. Your application needs to know the identity of the user and requests this information from Excel. That is why you are seeing the prompt.
Enter the identity of the user registered in the membership database. For example,enter admin/admin123% or user/user123% – these are two default users accounts created automatically in the baseline application.
Click OK button and you will see the data from your web application display in Microsoft Excel.
Select Insert tab on the ribbon and choose to insert the PivotTable.
Dialog Create PivotTable is displayed.
Click OK to insert a new sheet with the pivot table.
The pivot table PivotTable1 is empty. Notice the list of fields on the right-hand side of the window. All visible fields from the Orders screen are listed there.
Drag Customer Company Name field to the “Values” box and you will see the total number of orders in the database.
Drag Employee Last Name to the “Row Labels” box and you will see that the most product employee is Ms. Peacock. She has placed 156 orders.
Drag Ship Via Company Name field to “Column Labels” box and you will see the breakdown of orders by employees and shippers delivering the goods to customers.
Numbers don’t always tell the story.
Choose PivotChart item on the right-hand side of the Options ribbon. Select 3-D Cylinder chart and click OK button.
We can now clearly see that Ms. Peacock is heavily utilizing the shipping company United Package. If this is the most expensive shipping option then we should call Ms. Peacock and advise to switch to another freight operator. If this this is the least expensive option then a bonus is due in recognition of an employee thinking about the business bottom line.
You can save this spreadsheet to the hard drive. Open the spreadsheet a week a later and choose Data | Refresh All and you will be prompted to re-enter user name and password – the new data feed will be produced by your application and the charts and cross tables will automatically refresh.
You web application created with Code On Time becomes an information hub of your company. Business users can do ad hoc data analysis with the tool they know best. The business decision makers don’t even need to sign in the web application to know what’s going on. Microsoft Excel charts and pivot table swill let them know everything they need.