Hi folks,
I am a report designer and I use BOXI r2 sp4. After researching the boards, I asked my design team to create the Sort-Order prompt object at the universe level.
From what I understood, they created it in an independent dervived table. I need to have it joined to another table that contains the “measures” but I don’t know what kind of joins should be constructed.
In the report, I’d like to use it for something like the following:
if(Prompt - Sort Order=“Descending”;cost of service)…
I would greatly appreciate the advice of the SMEs on how to complete this requirement.
Wannetta
Here’s what I submitted to my design team after researching the topic here and in the SAP Webi forum
Here’s some additional info for you, hope this helps clarify…I’m working off an AS/400 which has some unique constraints, depending on your db, you may be able to simplify this…
A) Create a derived table:
Select’Ascending’ as Ascending,‘Descending’ as Descending from dual
B) Create a new dimension object named Ascending, based on the Ascending field you just created
C) Create a new dimension object named Descending, based on the Descending field
D) Create a new dimension in the universe:Name: Prompt - Sort Order
Select: case when @Prompt(‘Select sort order’,‘A’,’{‘Ascending’,‘Descending’},mono,constrained,persistent,{‘Ascending’},) = ‘Ascending’ then @Select(Ascending) else @Select(Descending) end
*This will prompt users to select Ascending or Descending as your sort order and sets Ascending as the default and remembers the last selection.
If you don’t want that, change to not_persistent and eliminate the {‘Ascending’} but keep the commas. Depending on your db, you may be able to eliminate B and C and set the values directly in the case statement, but the AS/400 will not let me, it has to refer to a database “field” in the case statement
E) Export your universe
F) In your WebI report, include the Prompt - Sort Order object in the Results Object
G) In WebI, create a variable,
cv_Ascending:Formula: if(Prompt - Sort Order=“Ascending”;FieldName)
H) In WebI, create a variable,
cv_Descending:Formula: if(Prompt - Sort Order=“Descending”;FieldName)
I) Put cv_Ascending in the first column of your table and set the sort order to ascending
J) Put cv_Descending in the second column of your table and set the sort order to descending
wthomas 
(BOB member since 2008-09-04)