1/ If you are adding additional columns to the Query, then you need to do below things.
i. Datawindow --> Edit Source then search for "retrieve=" this property will contain the total select statement of the datawindow. Just change this with your modified select.
Make sure that you are not removing existing columns in it.
ex: old sql: select column1,column2,...,column10 from table1
new sql: select a.column1,a.column2,...,a.column10,a.column11,..,b.column1,b.column2 from table1 a ,table2 b where a.column1=b.column1.......// You are not removing the existing columns from the SQl
ii. Come back to the Datawindow Design and try to add column to report/Datawinow. Not able to see the new columns in the list??
Adding the new columns in the SQL statement is not sufficient. So what will be the solution?? Here is the answer
iii. Datawindow --> Edit Source
Just above the "retrieve=" youcan find some thing like this "column=(type=char(1) updatewhereclause=yes name=columnx dbname="column" "
Now you understand what you need to do. Yes, we need to add the new columns in that list. Here is the technique to to so
iv. create a datawindow of "SQL Select" type. Click cancel when it asks to select tables. Then Design--> Covert Syntax. Here give the sql used above but only with the new columns.
i.e., select a.column11,..,b.column1,b.column2 from table1 a ,table2 b where a.column1=b.column1......//colums 1- 10 from table not included
v. Retrun to the datawindow painter save as d_test(as you wish) then edit source this datawindow and get the column details and add to the column details to actual datawindow or report's edit source.
Then delete d_test
vi. Now try to add new columns to datawindow design (point ii). You can find them in this list. Add them to reports as you want.
2/ Some times you are asked to show the actual names in save as report instead of displaying the database names
Simply change the "dbname" to the name which you want in "column=(type=char(1) updatewhereclause=yes name=columnx dbname="column" " in the edit source. Thats it.
"column=(type=char(1) updatewhereclause=yes name=columnx dbname="User Name" "
When I save the report as excel the header will contain "User Name" in the Column name instead of the db name.
In this method you no need to worry about the update properties ( Which is a big head ache in some cases)
Hope this trick will be useful for you!!!
Venkatesh Guptha
