Modifying SQL in Impromptu 2

[shearbec]

Hi All,

I am writing some reports based out of an Informix database. The catalogs were built for us when our original system was setup. Basically, I need to connect several different tables and it's using a propietary way of joining them. I have gone into my profile settings and looked at the SQL statement and it's joining the tables not how I want. I want to be able to modify that statement. Is there anyway I can do that? I've been told you can do this, but no one knows how. Any ideas?

 

[Shek]

First select all the fields into the report that you want to be displayed. Press Ok. Go to profile, Sql and press edit. Now you can edit the query that is generated using the pre-made joins. ALl the new filters you will have to apply in this SQL query itself.

 

[shearbec]

That would be great if I had an Edit button. I only have a Copy button. Anybody know what configuration needs to be changed so I can see it. My boss & I are new at this and any help would be appreciated.

Thanks.

 

[stanielson]

You need the Impromptu Admin Edition to get the Edit button. If you get a hold of the Admin version select all the fields into the report that you want to be displayed. Click OK. Click on the profile SQL tab. Next hold down the ALT key + the Ctrl Key + Shift Key and click edit all at the same time. This will show you the Magic Query (this is an easter egg inside of Impromptu to show you how the SQL will get passed to your Informix database). You should be aware that you will loose the group by, sort by and some ability to aggregate your data in Immpromptu once you click the edit button and verify your query. This means that you will have to edit the SQL for this report everytime and want be able to choose fields from the Catalog.

Hope this helps.

Stan

 

[gopalr]

If I modify the SQL then all my calculations are not available. I am not getting the Filter tab.
I want to modify the Query, still I want to use the filter (basically i want to execute a stored procedure as the source and i have to perform Sum, count etc., on the retrieved database field).

Any suggestions.

Thanks in Advacne.

Gopal R