Recommended approach for customization?

[miguel3000]

I need to customize Web 7.5.x so that when an HTML document is executed, the following events take place in the web server:

1. Embedded report 1 is exported to existing Excel workbook (and pre-export macro is executed)

2. Embedded report 2 is exported to same workbook as in 1 (and post-export macro is executed)

4. Copy of Excel workbook is saved to a directory in web server

5. Document in web browser returns link to new excel workbook

Do you have any recommendations on how to best achieve the steps outlined?

Note that the export options are defined for each of the embedded reports via Microstrategy Desktop. Manual export via Web is not an option. Scheduling report execution is also not an option.

Any hints would be appreciated.

Miguel

 

[chael]

What avout using Narrowcaster for this?

 

[miguel3000]

Narrowcaster is not an option either (no budget for licensing it). I'm looking into capturing the report XML and passing it into a custom dll that would launch Excel on the server, but I haven't been able to figure out how to get the pre- and post-macro options from the report yet.

 

[chael]

Because you are working with 7.5.x you can use a transform bean to capture the 'event' of a report being run in web. Since we are in a sort of whiteboarding forum here, let's think through a couple of these steps:

1. When the report runs, we need to let an external process (a custom .dll) know that the HTML document is being run. This can be done via a transform bean via some sort of call (http, web service). The SDK (if you own it) will give you some insight here...I suggest spending some time looking at the case studies.

2. The parameter that needs to be passed from web is the document ID. We can then use this ID and the MSTR API to figure out what the embedded report elements are. If the document is prompted or if there are security filters involved we'll also need to know those and pass those in as parameters.

3. Once the .dll has all the requisite information if can run the report and via the API kick off the reports on the i-server...at this point we can request to return only the XML of the report.

4. With the proper XML returned you are then free to let some Excel VBA macros format the XML...you can launch your macros from with the Excel object itself.

I think there are several other ways to do this...my steps are just an idea.

For the link within the HTML document...I think it would be easiest if you knew what the file directory was going to be ahead of time. That way you could simply hard code it.

Chael

 

[miguel3000]

Thanks for the tips. I solved the challenge by doing the following:

1. Created a custom document handler which, based on the description of the document, sets the document execution flag to return XML only.

2. Created a custom document transform which also checks for the document description. If the document description contains a particular string pattern, the transform reads an XSL file from the filesystem, applies it to the document XML, and exports the results to an Excel template.

3. Once the document data is in the Excel template, a new file is created by saving the modified template to folder in the web server. The document transform then returns a link to the new Excel file instead of displaying the document data.

4. When the user opens the newly created Excel file, a series of macros are automatically run to format the data.

It sounds more complicated than it actually is. Hopefully in a future version MicroStrategy will automatically apply the Excel export settings for reports run via Web.

Miguel

 

[FLB]

You might want to check out MSI 8 before you invest too much time in this custom work...

2 cents,
FLB