by Stephen Horsfield
7. January 2008 19:41
Overview
How can you make your Commerce Server reports easy to maintain, and how can you make it work with long SQL queries? Did you even know that long SQL queries won't always work?!
The problem
The query is stored in the Report table in SQL Server as an nvarchar(2000) field. This means you've only got 2000 characters to work with. In fact, the limit is smaller than this because of processing that Commerce Server is doing itself. I noticed problems around 900 characters, which seems a bit small!
The solution
The best solution I found was to reference a SQL view that was created for the purpose. Using this, the SQL query just needed to select the particular fields, apply particular filters (such as date filtering) and sorting. This meant that changes to the underlying report execution could be managed by adjusting the view rather than the SQL in the Report table. The view can also be used by many similar reports, which in our case was for about 10 regional sites.
One main caveat was the use of Commerce Server date report parameters. The filtering on this only worked when it was in the Report Query field directly.
Versions
Metadata
Subscribe