Ordering Filters from Stored Procedure.
18 June, 2015
I'm trying to create a report using a stored procedure that has four parameters in it. When I am in the first page of the create view section, the order of the parameters is the same as that in the stored procedure, BUT, when I move on to page two, the order changes (and is then the order that the parameters will appear in the report). How do I either get the view to keep the parameter order as in the stored procedure, or, how do I change the parameter order in the report?
We've been aware of the random sorting of filters from a stored procedure for some time, which appears to have been part of the initial design in Yellowfin.
An Enhancement Request # 103920 was lodged some time ago to allow filter sorting, however this hasn't reached the attention of the Development team as yet.
If you require any clarification on this, please let me know.
FYI, as a workaround you can manually edit the SortOrder value in the ReportFilter table. The query below will help you find the data that needs to be updated.
DECLARE @ReportNameContains as NVarChar(Max) = 'TypeReportNameHere'
SELECT rh.ReportName, ft.ShortDescription 'FieldDesc', rf.SortOrder, rf.FilterId, 'ReportFilter' 'InTable' FROM ReportFilter rf
LEFT JOIN ReportFieldTemplate ft on ft.FieldTemplateId = rf.FieldTemplateId
LEFT JOIN ReportHeader rh on rh.ReportId = rf.ReportId
LEFT JOIN ReportView rv on rv.ViewId = rh.ViewId
WHERE rv.ViewTypeCode = 'PROCEDURE' AND rv.ViewStatusCode in ('DRAFT','OPEN') AND rh.ReportName Like ('%' + @ReportNameContains + '%')
ORDER BY rh.ReportName, rf.SortOrder
We are planning on revamping the view builder over the next few releases, and this is one of our pain points which is to be reviewed.
I didn't know there was a workaround though.. NICE WORK JASON!!!!!!!!!!!!
This is going to be extremely useful for other clients.
Thanks again for passing on this info.