Search code examples
sqlreporting-servicesreporting

Fiscal week are not coming in order

I am writing a query in which i want to order it by FiscalPeriod as you can see here but i am not getting the expected result the fiscal weeks are coming in random sequence. my order by clause looks like

ORDER BY CAST(SUBSTRING(FiscalPeriod, 4, 2) AS INT), CONVERT(DATETIME, FiscalPeriod, 106)

data type in FiscalPeriod is nvarchar, and format is like '07-Apr-02' where 07 is fiscal month and 02 week number in current month.

I am expecting fiscal week should be in sequence.

Solution

  • try this...

    ORDER BY CAST(SUBSTRING(FiscalPeriod, 1,2) + SUBSTRING(FiscalPeriod, 8,2) as INT)

    If FiscalPeriod is '07-Apr-02' then SUBSTRING(FiscalPeriod, 1,2) gives us '07' and SUBSTRING(FiscalPeriod, 8,2) gives is '02'. Concatenate them to to give '0702' and then CAST this to an INT to give 702

    Ifg you want to use this in SSRS to order columns etc, then you probably don't need to use ORDER BY in your dataset query, instead you can just add a new column that contains this value and then order the column group by this value in SSRS.

    So instead of

    SELECT ClusterName, EmployeeName, SomeOtherColume 
FROM myTable 
ORDER BY CAST(SUBSTRING(FiscalPeriod, 1,2) + SUBSTRING(FiscalPeriod, 8,2) as INT)

    Just do this instead

    SELECT ClusterName, EmployeeName, SomeOtherColume 
   CAST(SUBSTRING(FiscalPeriod, 1,2) + SUBSTRING(FiscalPeriod, 8,2) as INT) as FPSort
FROM myTable

    In the matrix column group, use FPSort as the sort column.