MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

PowerBuilder Reports Creation and Issues

Published on 17 February 17
794
0
1
PowerBuilder - Complex Report/Datawindow Issues
When ever you are doing modifications to the Datawindow datasource of existing Reports-

Some times if the report is very complex (assume having 20 computed fiedls) , When you replace the original data source, Compute fields disappear from the Datawindow painter.What will be the solution??
Also sometimes if you try to get the datasource. PB itself crashes and closes. We can modify those reports by the below steps.
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!!!





PowerBuilder - Complex Report/Datawindow Issues

When ever you are doing modifications to the Datawindow datasource of existing Reports-

Some times if the report is very complex (assume having 20 computed fiedls) , When you replace the original data source, Compute fields disappear from the Datawindow painter.What will be the solution??

Also sometimes if you try to get the datasource. PB itself crashes and closes. We can modify those reports by the below steps.

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!!!

This blog is listed under Development & Implementations Community

Related Posts:
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top