Pivot Functionality for Excel Export

Hey,   is it possible to export two entities into one Excel Export?   I have an entity ‘Employee’ and an entity ‘CalculatedCalendar’, which stores the availability of this employee for a specific month. The Calendar is calculated so I don’t have to store hundreds of values for just one employee.  Now I want to export a list to excel, where you have some employee details in the first columns and then hundreds of columns for the calculated calendar. Basically a column for each month.   So the columns look like this: Department     Last Name     First Name     Oct.20     Nov.20     ….     Dec.27 HR                      Smith              John                active      active                gone  My questions are:  Is it possible to pivot two entities into one list? How would be the next steps using the Excel Exporter?  Do I need to enter the column data manually in the Exporter? (would be a huge amount of work, since there are hundreds of columns)   I don’t have much experience using the Excel Exporter, so any help is appreciated.    
1 answers

The best idea would be to do the pivot already in Mendix, but for your use case, mapping of 100’s of columns, there could be another option. You do need the Excel Exporter module for this:

  1. Create an empty Excel file with exactly the macro needed, probably by first created a filled Excel,creating and saving the macro that can generate the pivot table and then deleting all worksheets.
  2. Configure your template to use a template (XLSM) itself, being this empty Excel file with macro
  3. Configure both worksheets within the template, one worksheet for the Employee another for the Calendar

Once the user downloads the Excel file, based on the above configured template, the user needs to trigger the macro with the chosen short key combination. 

In this way the data comes from Mendix but the heavy lifting of generating such a large pivot table is still stored in Excel.