Version: DSP 6.2.1 --Note, these steps should only be performed against a 6.2.1 instance
In prior versions of Transform and DSW there was a hard coded limit of 50,000 rows for an Excel format report. Reports with more than 50,000 rows were saved in .csv format but with a .txt extension.
As of patch # 6.2.1009, the maximum number of rows that can be created as an Excel file is configurable via a database setting. The setting will be fully implemented and exposed in the UI in version 6.3.1.
To determine if this patch has been applied to the system, run the following query:
SELECT [PatchID]
,[ScriptID]
,[ScriptComment]
,[AddedOn]
,[AddedBy]
,[CompletedOn]
FROM [DSPCommon].[dbo].[ztPatchLog]
WHERE PatchID = '6.2.1009'
If this query does not return a record, you will need to apply the latest DSP 6.2.1 patch.
When the patch is verified to be in place you will need to manually update two fields in Console.dbo.ztParam; ReportMaxRows and ReportMaxSupportedRows. The default for these fields is 50,000. Increasing these values to 100,000 will result in Excel files being written out for all reports with 100,000 rows or less.
Example syntax to increase the number of records to include in .xlsx files:
UPDATE [Console].[dbo].[ztParam]
SET ReportMaxRows = 100000, ReportMaxSupportedRows = 100000
WHERE One = 1
NOTE: The process of generating reports in Excel is substantially more resource intensive than as a csv/text file. Increasing this parameter to greater than 50,000 creates a risk for a negative performance impact on the application server. The impact will be on IIS processes and could cause slowness of the application for all users and ultimately even out of memory errors. If undesirable consequences result, reduce the number above.
NOTE: Excel also has limits to the number of rows it can display. The actual limit depends on the version of Excel. No parameter in Transform can override Excel’s capabilities.