Order based on prompt in dimensional report

[Ammus1234]

Hello All,

I want to use a prompt inside the order function in dimensional reporting.
For example  order(?prompt1?,[Measure],ASC)

This gives me an error XQE-PLN-0020 The V5 Query could not be planned by the Query Service. The 'V5OrderFunction' node is invalid for the OLAP Transformation Library and the Query Service Planner is currently not able to resolve it.

Prompt is a static prompt, and I tried to give the MUN as the use value.

I created a sample report based on go sales (analysis). Please find attached the same.
This gives me error Found an internal error on node 'V5EdgeGroup'. Unable to get the edge group hierarchy.

Any help would be highly appreciated

Thanks in advance

[Lynn]

Posting an example using the samples is an excellent way to get assistance, so thanks for taking the time to do that!

I think the key element you are missing is how to construct a simple prompt in the dimensional world. Of course prompt macros can be used, but there is a simpler way.

For a single select prompt on a particular level you can use this:

Code Select Expand

[Sales (analysis)].[Time].[Time].[Year] -> ?SelectYear?

To make that same thing multi-select, just wrap it in a set function like this:

Code Select Expand

set ( [Sales (analysis)].[Time].[Time].[Year] -> ?SelectYear? )

You can use that for your row data item expression and then use layout sorting to get the years in descending revenue order, or you can wrap the whole thing in an order function.

See attached for a revised version of your example. I removed the prompt control entirely, but you can add one back if you prefer. It has a crosstab that uses the layout sorting and another one that uses the order function.

[Ammus1234]

Hi Lynn,

Thanks for your answer.

But my requirement is a little different. I want to pass a prompt instead of a specific item [Sales (analysis)].[Time].[Time].[Year] in the dataitem OrderPromptYearsForRows

The reason is my first column is selected using a prompt. So I want to sort the report based on the dynamically selected member.
I am looking for something like below.

order ( ?First Column?, [Revenue], DESC )

I will try to create another sample report with dynamic column.

[Lynn]

Hi Lynn,

Thanks for your answer.

But my requirement is a little different. I want to pass a prompt instead of a specific item [Sales (analysis)].[Time].[Time].[Year] in the dataitem OrderPromptYearsForRows

The reason is my first column is selected using a prompt. So I want to sort the report based on the dynamically selected member.
I am looking for something like below.

order ( ?First Column?, [Revenue], DESC )

I will try to create another sample report with dynamic column.

Your syntax ?First Column? is not correct for a dimensional source. I explained how to do that in my original reply but I'm not sure you followed me. Maybe the link below will explain it better. You should be able to wrap it within an order function.

http://pic.dhe.ibm.com/infocenter/cfpm/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc%2Fug_cr_rptstd_id21457cr_rptstd_wrkdat_add_prompt.html

[Ammus1234]

Yes, I mean the member on which I want to sort is dynamic. The member comes from a prompt.
order (<prompt>, [Revenue], DESC )

[Lynn]

ok, so did you try it? Did it do as you expected?