Custom Sorting gives wrong measure values in Crosstab

[rprameshwor]

Hello ,

I got a crosstab with multiple measures  and nested dimensions on the row . at column header i have Week Description [ week 1 , week 2 .. ]


The sorting on the column header is applied according to a date-item  , say , weeksort  defined as :

case
when
[presentation layer].[time].[wk_desc]='wk 1'
then 1
[presentation layer].[time].[wk_desc]='wk 2'
then 2 ........... and so on..

Whenever i apply sorting to the week column header based on custom data-item [weeksort] , the measure values are the same on all the weeks .
But when sorting is removed the report runs as expected with correct values for each metrics in different weeks.

How could sorting lead to such erroneous data ? is the sorting approach mistake  or there are any  better approaches for the sorting.

Thanks ..

[IceTea]

Is your crosstab based on relational data or dimensional data (dmr, cube...)?

Can you add a sortable Week-Element from your metadata to the crosstab header? So you can sort directyl on this element. If you dislike to show this sortable element, just replace the text with the description-element.

[raj_aries]

Hi,

If its relational source, I think you can use _week_of_year([Date]) as sortitem, incase if there are multiple years there will be same week numbers so you can make them a numeric value something like _week_of_year([Date])+Year([Date]) and use it a sortitem.

Hope this helps !

Regards
Raj