This adapter is about enabling good reporting for an organization.
Good reporting is both an art and a science. Mostly when organizations try to report off their data the results are underwhelming. The issue is you need to coach a team to agree on what is important to collect data on.
Then you need good feedback mechanisms for the team to see what is going on and refine what they pay attention to.
The Excel Adapter makes it simple set up a webservice in Iguana which can both serve and receive data from Microsoft Excel which is an excellent tool for implementing reporting using “Pivot Tables”.
Expand | ||
---|---|---|
| ||
This makes it useful to be able to feed data from Iguana into Excel. |
The Excel Adapter shows how a webservice in Iguana can be set up to serve (and also receive) data to Excel.
Excel is helpful for reporting because it’s pivot tables and graphing features are powerful for presenting data.
Expand | ||
---|---|---|
| ||
By following these steps, you can easily fetch and send data between Excel and Iguana using the adapter. |
Expand | ||
---|---|---|
| ||
By reviewing the annotated code in |
Expand | ||
---|---|---|
| ||
By leveraging Excel table objects, the adapter ensures that data is dynamically updated and formatted properly. This setup provides flexibility for storing and customizing data retrieval and storage processes. Similarly for sending data we have the following configuration table: |
Expand | ||
---|---|---|
| ||
Excel Tables: A Unique FeatureExcel table objects are a powerful feature that simplify the management of tabular data. They allow addition of columns and rows, and automatically update dependent features like PivotTables. When a PivotTable references a table object as its data source, it stays synchronized with any updates to the table. How the Iguana Excel Adapter WorksThe Excel adapter code integrates with Iguana's webservice to efficiently populate Excel tables with data:
Key Advantage: Preserving CustomizationsThe algorithm is designed to preserve existing tables, including any user-added columns. For example, if a user adds custom columns with formulas referencing other columns in the table, these customizations remain intact even when the table is refreshed with new data. This makes the adapter particularly user-friendly, enabling dynamic updates without losing manual enhancements or custom calculations. |
FAQ
Expand | ||
---|---|---|
| ||
Look at the APP/APPspreadSheet.lua and exercise it with the GetSpreadsheet sample data. This shows how the APP/APPiguanaFeed.xlsm is modified to have the VBA code. Excel .xlsm files are essentially zipped bundles of XML files. You can unzip them, modify the content programmatically, and zip them back up. They also include a vbaProject.bin file, which stores VBA code. The adapter uses this approach to add my VBA code for the adaptor. You can see this piece in action by open the translator project and looking how how GetSpreadsheet sample data request is implemented in APP/APPspreadSheet. |
Expand | ||
---|---|---|
| ||
|
Expand | ||
---|---|---|
| ||
Look at APP/APPpull.lua to see how the data needs to be formatted. In a real application you would most likely be querying the data from a SQL database and then formatting it in the same way as the application. If you want to add additional web services then edit this code from main:
And define additional functions. You can specify which table to populate in excel by editing the Table column entry in the Query table. That can be done purely in excel without altering the back end code. |
Expand | ||
---|---|---|
| ||
Writing code in Visual Basic for Applications is challenging, so I chose a format that’s simple to parse and generate in VB, avoiding the need for a complex parser. Since we control both ends of the ‘pipe’ in this case it makes sense to do it this way. |
Expand | ||
---|---|---|
| ||
Currently, the code uses Iguana's session API for login, relying on Iguana's own login credentials. However, it doesn't maintain a session and simply authenticates via HTTP basic authentication. For simplicity, the example code doesn't restrict data access per user, but this could be implemented using |
Advice about Reporting
Expand | ||||
---|---|---|---|---|
| I prefer not to use built
| |||
Built-in reporting tools from applications. While they’re quicker for getting initial resultsbecause, while they’re quick to set up, they often hit limits and can’t cross-reference combine data from different systems, like a customer management system and an accounting application. It more powerful to build reports from scratch by By extracting data from the source systems and storing store it in ‘shadow’ database tables that mirror the source data separate 'shadow' tables for easy querying gives more power and flexibility.This is It’s a first-principles approach more effective to build reports from scratch by extracting data from the source systems and storing it in ‘shadow’ database tables that mirror the source data for easy querying.This is a first principles approachmethod to give more flexibility and power when building reports. |
Expand | ||
---|---|---|
| ||
Good reporting helps organizations communicate better, but it often falls short because teams don’t focus on the right things. The Key Steps:
When everyone is clear on what’s important and keeps learning from the data, reporting becomes a valuable tool for success. |
Expand | ||
---|---|---|
| ||
Pivot Tables are a feature in spreadsheet tools like Excel that allow you to quickly summarize, analyze, and organize large amounts of data in a clear and dynamic way. Why Are Pivot Tables Excellent for Presenting Data?
In short, Pivot Tables transform complex data into easy-to-understand summaries, making them a powerful tool for data presentation and decision-making. |
Expand | ||
---|---|---|
| ||
Getting data is all about making stories map to codes. Coding stories is difficult because it essentially means getting a team to negotiate and create a shared language to see common trends in the conversations they are having. The same idea applies whether you are a medical team dealing with patients or company dealing with customers. Over formalized traditional IT systems often make it difficult for a team to evolve a language to talk about the problems they are seeing. One clever ‘hack’ is to use the practice of putting #hash_tags into notes. Almost all applications support free form text and thus you put hash tags into this text as a simple fast way to ‘code’ data. | ||
Expand | ||
| ||
Excel .xlsm files are essentially zipped bundles of XML files. You can unzip them, modify the content programmatically, and zip them back up. They also include a vbaProject.bin file, which stores VBA code. The adapter uses this approach to add my VBA code for the adaptor. |