Calling Datamart report as Pre-SQL?

[sgoodman7]

Is it possible to run a datamart report as a pre-report or pre-sql statement? I did copy the SQL of the datamart report as a pre-statement, but I want to be able to apply the prompts to this pre-query. I would like the user to only have to run one report, and not have to remember to run the datamart report prior to running the regular report.

Any suggestions will be appreciated.

Thanks

 

[reisw]

How often is the data changing in the datamart? You could just schedule it to run either on a daily basis or more frequently. Also, make sure that the report that uses the datamart does not get cached so it always reads from the latest datamart. Another option might be if you took the datamart sql and made it a stored procedure and had the report call that before it ran. This way it would update your table first and then run the report.

 

[sgoodman7]

I took the SQL and ran it as a Report Pre Statementwhich works fine. My problem is that I want to be able to use the report prompts in this pre-sql to reduce the size of the datamart. Are the prompt values stored anywhere, where they could be accessed and used as variables in the Pre-SQL statement?

 

[reisw]

The prompt values are stored in the Metadata repository. Unless you can somehow get it out of there I don't think so. How many possible choices are available from the prompts? If there aren't too many you could create a different report for each prompt answer. Not exactly the best scenario but... Another possibility is to run the datamart for all of the selections and then filter it in the report that uses the datamart.

 

[sgoodman7]

There are too many values to create separate reports. If I could utilize the prompts in the Pre-SQL, it would not only reduce the size of the datamart, but would significantly reduce the run time.

Thanks for the response

 

[entaroadun]

Don't know if this will work, but try putting both reports in a document object, and have the user run the document object. It's worth a shot...

 

[chael]

Why not just create an 'official' table out of the one you are trying to datamart? Sure you can run also sorts of pre-SQL to drop and load the table you are working with, including using stored procedures to do a lot of the dirty work for you, but if you are going to use the damn thing a lot, just treat it like an aggregate table and model it into you project.

But maybe what you are really trying to do is use a report filter....i.e., use the results of a report within the prompt/filter portion of another report. I think you have several options here...the latter is more elegant if it applies.

Chael

 

[chael]

umm, does anyone know if reports that are used as filters can simultaneously kick off datamarts if it is in there report definition? Never thought of this...

 

[sgoodman7]

I've already tried both using the datamart as a filter and as a document object. MSTR won't let you use anything but a report for a document object. When I tried to use a filter, it blew chunks during the SQL creation.
I think what we will have to do is create a nightly script to create the datamart table.

Thanks for your suggestions.