Hi all,
I have a fairly simple use case that is proving tricky to migrate to v12.
In v11/OfficePlus, it works like this:
1. User selects some dimension elements (year, period, company, etc.) and clicks a button
2. An Excel macro creates a data area with a null filter based on the user's selections (selected element for N elements, base elements beneath the selected element for C elements). It iterates through the area, and lists certain coordinates (think each company with values) in a new workbook. Then it copies some formulas (DBGET, AT.GET) into the new workbook for each element.
3. The result: the user gets a new workbook with a list of all e.g. companies that contain data, their attributes and data with an intact connection to the database (i.e., not a values-only export).
Since we cannot access the database via macros in v12, I need to find another solution for step 2.
Self-Service has the necessary functions (select elements, show base elements beneath selection, null filter...), and exporting the report definition from Self-Service and importing it into Excel would give us a result pretty close to the original - but it would demand a _lot_ more knowledge and work from the user, and that's frankly not realistic.
Another idea would be to have a hidden Excel sheet, read all data for the selected elements in that sheet, and then use a "conventional" macro to copy all coordinates with non-zero values to a new workbook, but that would be too performance-heavy. (Especially since I haven't found a way to dynamically retrieve all base elements beneath a selected element via formulas, only _all_ dimension elements.)
Does anyone have any better ideas or suggestions for what to try?
Best regards,
Kai
