Sql-server – SSRS 2016 report is failing with ‘EXCELOPENXML’ render format error

There is currently nothing in the Mobile Report editor itself that indicates a capacity to order parameters and most graphs have no ordering capabilities (a couple of the bar charts allow for value or label based chart arrangement - part of the reason I was thrown off here!).

However, one can accomplish sorting from the data sets used in the report. The use of a single data set may cause issues in the parameters... so a standard ORDER BY x, x... approach will only go so far. Using the same values and a different data set, one can accomplish sorting and maintain filtering abilities.

To illustrate, the following query in Mobile Reports gives an oddly sorted State list, since it is actually sorted, in expected SQL fashion, after warehouse and business center lists.

SELECT
     WarehouseID
    ,PostalCode
    ,City
    ,County
    ,State
    ,BusinessCenter
FROM ...
ORDER BY WarehouseID, BusinessCenter, State

The ordering above solves some parameter list sorting and fixes legend color displays in the report, but doesn't address the State parameter sorting. Another data set with the exact same values contained in State can be used for the parameter, which enables correctly sorted and still functional parameter lists.

SELECT DISTINCT
State
FROM DIM.Geography
ORDER BY State

This resolves consistency of legend location and coloring across the dashboard and solves parameter selection list sorting.

SQL Server Reporting Services (SSRS) is a server-based platform for hosting reports. Report builder is client tool that may be used to create reports to deploy to SSRS. As such, you would use SSRS to manage security on for the reports that you may have deployed with Report Builder.