Trying to sort a field in a cross tab report

[mp3909]

Hi,

I have a data item in my cross tab called Product Group.

It has values "Car", "Van", "Bus", "Lorry", "Truck".

When I run the report, it gets ordered in this way: "Lorry", "Truck", "Car", "Van", "Bus"

I don't want it in that order. I want a custom sort so that it shows in this order: "Van", "Lorry", "Truck", "Bus", "Car".

Therefore I created a separate data item within the same query that contains Product Group called Sort_Product_Group in which I have defined the following Case-When statement:

CASE
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Van' THEN 1
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Lorry' THEN 2
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Truck' THEN 3
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Bus' THEN 4
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Car' THEN 5
END


I then go over to my crosstab, click on the data item I want to apply the custom sort on, view its properties and under Data > Sorting I define it to be sorted by Sort_Product_Group. When I run the report, the data item doesn't appear to have been sorted.

Have I missed a step?

[igfortun]

Are you applying the sort to Product group based on Sort_Prodcut_Group?

[oscarca]

Strange.. works perfectly fine for me.

[MFGF]

Hi,

I have a data item in my cross tab called Product Group.

It has values "Car", "Van", "Bus", "Lorry", "Truck".

When I run the report, it gets ordered in this way: "Lorry", "Truck", "Car", "Van", "Bus"

I don't want it in that order. I want a custom sort so that it shows in this order: "Van", "Lorry", "Truck", "Bus", "Car".

Therefore I created a separate data item within the same query that contains Product Group called Sort_Product_Group in which I have defined the following Case-When statement:

CASE
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Van' THEN 1
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Lorry' THEN 2
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Truck' THEN 3
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Bus' THEN 4
   WHEN [Business View].[ETP File].[PRODUCT_GROUP] = 'Car' THEN 5
END


I then go over to my crosstab, click on the data item I want to apply the custom sort on, view its properties and under Data > Sorting I define it to be sorted by Sort_Product_Group. When I run the report, the data item doesn't appear to have been sorted.

Have I missed a step?

Have you added the new calculated item to the Properties property of the data item in your crosstab?

MF.

[oscarca]

Have you added the new calculated item to the Properties property of the data item in your crosstab?

MF.

It shouldnt be necessary if the calculated item and the Product group is in the same query. Not in 11.1.7 at least but maybe not working in older versions.