Can Impromptu hand muli-pass sql

[hotdwarch]

A couple years ago it seemed that Impromptu could not handle multi-pass sql (outer joins of answer sets). Essentially if you have metrics/facts such as sales at the daily grain and sales goals at the monthly grain, and you include both of these metrics as columns on an Impromptu Report the answers (a few years ago) was incorrect. An examination of the underlying sql being generated by Impromptu clearly was not the multipass sql needed.

Any comments?

 

[griffindm]

Actually this has been addressed in the current version (6.0) via the capability to use a report as a data source in a second report. It is not quite as elegant as multipass SQL, but it has the same effect practically. You create and save a report, then put the report into the catalog as a datasource (use the Hotfile button, the docs are real weak on this). Then use the report like any real table source. The report is run on demand and the results of ITS query are then passed to the second report. It eliminates one of the last remaining uses for true hotfiles.

Hope this helps.

Dave Griffin

 

[DerekR]

Does this work on IWR as well, and if so, does the external report get added to the package when publishing the report.

 

[griffindm]

Haven't tried it on IWR yet. One caveat I can pass along. The method does NOT support outer joins between the original pass and any data combined in the second pass. That's a shame as I've had to revert to hotfiles for this.

Regards,

Dave Griffin