Sorting Months Name by calendar Order in Powerpivot Slicer
Like January, February, March….
There is no direct way to sort this you need to do workaround
for slicers.
I am giving Example where I was using month Names from SQL query.
Sample Query that will fetch All MonthNames from Master
database view spt_Values.
SELECT number, DATENAME(MONTH, '2014-' + CAST(number as varchar(2)) + '-1') monthname
FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12
ORDER BY Number
We will insert this
record in a temp table so that we can write a query to temp table
SELECT number, DATENAME(MONTH, '2014-' + CAST(number as varchar(2)) + '-1') monthname
into #temp from master.dbo.spt_values
WHERE Type = 'P' and number between 1 and 12
ORDER BY Number
Select * from #temp
Output
number monthname
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
So if you will create a
Slicer based on this Data you can produce the Issue you will have slicer sorted
in alphabetical order like below.
Issue Screenshot
Issue Screenshot
Solution: In order to sort in Natural Order you need to add a Prefix
before your month name this is the only workaround that you can do with Slicer.
Note: Don't forget to
add zero before 1,2….9 otherwise it will not sort your slicer as required.
So here is the SQL code to add prefix.
select case [MonthName] when 'January' then '01January'
when 'February' then '02February'
when 'March' then '03March'
when 'April' then '04April'
when 'May' then '05May'
when 'June' then '06June'
when 'July' then '07July'
when 'August' then '08August'
when 'September' then '09September'
when 'October' then '10October'
when 'November' then '11November'
when 'December' then '12December'
end [MonthName]
from #temp
Refresh your Report
having MonthName Slicer and Sort from A to Z.The Slicer will get sorted in a
Natural Sort from January to December as below.
Note : If you don't want to change anything in your sql code you can achieve the same result by following msdn blog-http://blogs.msdn.com/b/analysisservices/archive/2010/04/21/sorting-month-by-natural-sort-order.aspx
efpropbal_e Candace Johnson Free Download
ReplyDeletesturhapfestfo