Problem with Export to excel with list navigation

Hi All,


I am trying to export the data from the screen into an excel sheet for this i have used "RecordListToExcel" widget. In the same screen , the list navigation has been used and the max line count for this is 20. The data source used for "RecordListToExcel" is the "SQL" table that fetches the data. When ever i export the data to excel it is not fetching all the records from the table. Suppose if there are 150 records , it fetches only 21 records and i can export all the records if i go to the last page of navigation and do it but  if i export from the first page it again exports only 21 records. Could you please tell me a way to work this out?


Thanks,

Mamini.

Mamini Konidena wrote:

Hi All,


I am trying to export the data from the screen into an excel sheet for this i have used "RecordListToExcel" widget. In the same screen , the list navigation has been used and the max line count for this is 20. The data source used for "RecordListToExcel" is the "SQL" table that fetches the data. When ever i export the data to excel it is not fetching all the records from the table. Suppose if there are 150 records , it fetches only 21 records and i can export all the records if i go to the last page of navigation and do it but  if i export from the first page it again exports only 21 records. Could you please tell me a way to work this out?


Thanks,

Mamini.


Are you selecting data from the record widget  while exporting to excel ?? . Can you provide the oml to test ?

Pranav Pandey wrote:

Mamini Konidena wrote:

Hi All,


I am trying to export the data from the screen into an excel sheet for this i have used "RecordListToExcel" widget. In the same screen , the list navigation has been used and the max line count for this is 20. The data source used for "RecordListToExcel" is the "SQL" table that fetches the data. When ever i export the data to excel it is not fetching all the records from the table. Suppose if there are 150 records , it fetches only 21 records and i can export all the records if i go to the last page of navigation and do it but  if i export from the first page it again exports only 21 records. Could you please tell me a way to work this out?


Thanks,

Mamini.


Are you selecting data from the record widget  while exporting to excel ?? . Can you provide the oml to test ?


No, not the record widget that is used in the screen.  while exporting to excel the data source is the sql table from the screen preparation.

Mamini Konidena wrote:

Pranav Pandey wrote:

Mamini Konidena wrote:

Hi All,


I am trying to export the data from the screen into an excel sheet for this i have used "RecordListToExcel" widget. In the same screen , the list navigation has been used and the max line count for this is 20. The data source used for "RecordListToExcel" is the "SQL" table that fetches the data. When ever i export the data to excel it is not fetching all the records from the table. Suppose if there are 150 records , it fetches only 21 records and i can export all the records if i go to the last page of navigation and do it but  if i export from the first page it again exports only 21 records. Could you please tell me a way to work this out?


Thanks,

Mamini.


Are you selecting data from the record widget  while exporting to excel ?? . Can you provide the oml to test ?


No, not the record widget that is used in the screen.  while exporting to excel the data source is the sql table from the screen preparation.


Can you send me the Oml file for reference . 

Mamini Konidena wrote:

Pranav Pandey wrote:

Mamini Konidena wrote:

Hi All,


I am trying to export the data from the screen into an excel sheet for this i have used "RecordListToExcel" widget. In the same screen , the list navigation has been used and the max line count for this is 20. The data source used for "RecordListToExcel" is the "SQL" table that fetches the data. When ever i export the data to excel it is not fetching all the records from the table. Suppose if there are 150 records , it fetches only 21 records and i can export all the records if i go to the last page of navigation and do it but  if i export from the first page it again exports only 21 records. Could you please tell me a way to work this out?


Thanks,

Mamini.


Are you selecting data from the record widget  while exporting to excel ?? . Can you provide the oml to test ?


No, not the record widget that is used in the screen.  while exporting to excel the data source is the sql table from the screen preparation.

Your sql or aggregate probably has max-records set to something. 


Roman wrote:

Mamini Konidena wrote:

Pranav Pandey wrote:

Mamini Konidena wrote:

Hi All,


I am trying to export the data from the screen into an excel sheet for this i have used "RecordListToExcel" widget. In the same screen , the list navigation has been used and the max line count for this is 20. The data source used for "RecordListToExcel" is the "SQL" table that fetches the data. When ever i export the data to excel it is not fetching all the records from the table. Suppose if there are 150 records , it fetches only 21 records and i can export all the records if i go to the last page of navigation and do it but  if i export from the first page it again exports only 21 records. Could you please tell me a way to work this out?


Thanks,

Mamini.


Are you selecting data from the record widget  while exporting to excel ?? . Can you provide the oml to test ?


No, not the record widget that is used in the screen.  while exporting to excel the data source is the sql table from the screen preparation.

Your sql or aggregate probably has max-records set to something. 



No, it is empty.

Mamini Konidena wrote:

Roman wrote:

Mamini Konidena wrote:

Pranav Pandey wrote:

Mamini Konidena wrote:

Hi All,


I am trying to export the data from the screen into an excel sheet for this i have used "RecordListToExcel" widget. In the same screen , the list navigation has been used and the max line count for this is 20. The data source used for "RecordListToExcel" is the "SQL" table that fetches the data. When ever i export the data to excel it is not fetching all the records from the table. Suppose if there are 150 records , it fetches only 21 records and i can export all the records if i go to the last page of navigation and do it but  if i export from the first page it again exports only 21 records. Could you please tell me a way to work this out?


Thanks,

Mamini.


Are you selecting data from the record widget  while exporting to excel ?? . Can you provide the oml to test ?


No, not the record widget that is used in the screen.  while exporting to excel the data source is the sql table from the screen preparation.

Your sql or aggregate probably has max-records set to something. 



No, it is empty.

Like Pranav Pandey said can't do anything before we see the oml. 


I was having the same issue.  I believe the resolution was suggested above and he said he had done it but it didn't work, It worked for me.  The lower highlighted one (under the Widgets) was suggested in the dropdown for record list in "RecordListToExcel".  I used that but it only returned the rows on the screen (Line Count set to 20). The upper highlighted part in the screenshot is the list from the preparation and it works.  


Hi everyone,

The platform automatically optimizes Aggregates to only fetch the information that is used (t relies on static analysis of your Web Screen and actions to determine what is used). Because of this, and since your TableRecords' StartIndex changes as you paginate, the number of records fetched also changes. On the first page, only 20(+1) records will be fetched by the Aggregate because only 20 are displayed (according to the TableRecords' LineCount) - the +1 is added so it is easy to determine if there are more records after the requested ones.

For the SQL tool on the other hand, the platform doesn't optimize anything (it's the developer's responsibility to write optimised SQL). That is why Mike's implementation works as he expects, and the others (using Aggregates) don't.

The solution for everyone using Aggregates would be to: on your screen action use a Refresh Data (for the Aggregate you are using as the source of your excel data, and then use the RecordListToExcel action, passing it the List output from the Aggregate directly. Note that this will take into consideration any filters and ordering you may have applied to the table, so if you want unfiltered/unsorted data you need to, instead of using a Refresh Data, create your own Aggregate/SQL in its place (and use that query's output on the RecordListToExcel)