Custom sorting

[Universe]

I need help with the custom sorting the report based on cell output of one column. I have Quote status column which has multiple outputs such as "Quote Open", "Quote Lost", etc.

I tried using case statement where when quote status is Quote open, put it on top of the list.

If status = quote open then 1. for example.

[Universe]

I tried to follow the following directions in IBM website:

https://www.ibm.com/support/knowledgecenter/SSFUEU_7.0.0/com.ibm.swg.ba.cognos.Report_Authors_Guide.7.0.0.doc/t_rag_using_custom_sorting.html

[khabaleshri]

Are you getting error or incorrect result?  If you have error then add newly created custom sort column in list 'properties' layout. If not then check final query tabular data for sort column.how column returns value for each record.
If sort column value aggregates then change aggregation property to None.

[Universe]

I am having trouble assigning numbers to the column values for custom sorting purpose.

Case ([Presentation].[Quotes].[Quote Status] + 0)
when [Presentation].[Quotes].[Quote Status] = "Quote Open" then 1
when [Presentation].[Quotes].[Quote Status] = "Quote Completed" then 2
when [Presentation].[Quotes].[Quote Status] = "Quote Open" then 3
else 4
end

Getting error:

QE-DEF-0260 Parsing error or near position: 114

[adam_mc]

Try:

Case
when [Presentation].[Quotes].[Quote Status] = 'Quote Open' then 1
when [Presentation].[Quotes].[Quote Status] = 'Quote Completed' then 2
when [Presentation].[Quotes].[Quote Status] = 'Quote Open' then 3
else 4
end

Use single quotes around your literals rather than double quotes.
Hope this helps,
Adam.

[Universe]

Thanks Adam. That worked. I now have a "Sort Order" data item. I need to sort Quote Status column based on the value of the cell. I have the data item (Sort Order) and assigned a number to each of the quote status. Now, How do I get Quote Status column to sort based on "Sort Order" data item?

[Universe]

I was able to sort "Quote Status" column using the "Sort Order" data item by going to Data > Sort > Edit Layout Sorting. I then dragged and dropped the "Sort Order" to the "Sort List" folder inside "Quote Status". Thanks Adam for your great help on the sort code.

[Universe]

I have got one more challenge to overcome. We have two columns "Customer" and "PM (Program Manager)". I want to sort PM column based on one particular value of the Customer column. There is a *TBD" value for records that does not have a particular customer assigned.

I created a data item and had this in expression:

Code Select Expand

Case
when [Customer (Business Partner)] like '*TBD*' then ASC
else 0
end


The expression is giving me error. I also tried

Code Select Expand

Case
when [Customer (Business Partner)] ends with 'TBD' then ASC
else 0
end

No luck. Any suggestions. I think it's because of the * character.

[Universe]

I solved this issue with assigning value with text starting from letter a through z. and then sorting that data item.