0
I am using SBM 11.0.1.
I have some scheduled reports which are having more than 5000 records and sending them to email as excel format. The report executed properly and sent email. But that excel does not contain all records. Only first 1000 records exported.
If choose as PDF format then all records are coming. But we really needed them as in excel format.
Anyone facing same issue, is it SBM limitation or any setting needs to be changed from my end?
Please suggest if any workaround is there for this.

Thank you.

Location [ View Larger Map ]

Responses (1)
  • Accepted Answer

    Friday, July 01 2016, 01:02 PM - #Permalink
    0
    If you run the same report in SBM and manually export it to Excel, does it export 1000 records or 5000 records? I have a feeling you are hitting the same limit as with manually exported records.

    There is currently a hard coded limit of 1000 records that can be exported to an Excel format from a listing report. The export for scheduled reports is probably using the same limit. The following steps can be taken to increase this limit.

    WARNING--- There will be performance ramifications and the system could crash. If this limit is set too high and someone runs a report (any report) that returns many thousands of records, performance could degrade quite a bit and there is potential that the system could crash. So, I would make sure that this scheduled report is setup to run during off peak hours.

    If you want to accept this risk, you can follow these steps to increase the limit.

    1. Run the following sql query against the SBM Application Engine database

    select TS_LASTID from TS_LASTIDS where TS_NAME='SystemSettings'

    2. Note the number returned by step 1. ( For our example we'll assume it's 65 )
    3. Add 1 to the number from Step 2 ( For our example this will be 66 )
    4. Decide what the maximum number of records a user can export to excel ( For our example we'll assume 2000 )
    5. Run the following sql query against the SBM Application Engine database substituting your values from Step 3 and 4 where appropriate

    INSERT INTO [TS_SYSTEMSETTINGS]
    ([TS_ID]
    ,[TS_NAME]
    ,[TS_DATATYPE]
    ,[TS_LONGVALUE]
    ,[TS_STRINGVALUE]
    ,[TS_LONGDEFAULT]
    ,[TS_STRINGDEFAULT])
    VALUES
    (66,'MaxExportToExcelItems',1,2000,'2000',2000,'2000')
    GO

    6. This should return a message that one record was added.
    7. Run the following sql query against the SBM Application Engine database substituting your value from Step 3 where appropriate

    update TS_LASTIDS set TS_LASTID = 66 where TS_NAME='SystemSettings'

    8. Restart IIS for your changes to take affect.
    9. Run a listing report with more than 1000 values (or wait for the scheduled report to run) and when you export to Excel they should export up to the value you specified.
    • Sandeep Reddy K
      more than a month ago
      Hi Thanks for reply.

      When I am manually exporting report to excel, all records have been exported.
      This problem occurs only when I scheduled the report to export to excel and send to mail.
      Will the above solution work for this specific problem?
    • Vickie Ortega
      more than a month ago
      I don't have any idea if this will work or not, and I don't have a system to try it on. It's just an idea. You'll have to try it out and see how it goes. You can always reverse the steps if it doesn't work. Or, you'll have to adjust the report to only return 1000 items.
    The reply is currently minimized Show
Your Reply

Recent Tweets