Conditional format - sorting

[Danielbryanuk]

I have created a boolean variable (if prompt = x, for example)...and when true I want to select a member on the report to sort by. I set the format by selecting yes on the value window, then selecting the report member and clicking the sort button, then double clicking the green section to return to normal.

But, the sorting remains after clicking the green button, and it doesn't work when running the report...

Can conditional formatting be used to sort a report, or do I need to do something else?

Thanks
Dan

 

[blom0344]

Conditional formatting is just what it says to be , it's there to apply some form of format to existing data. It is not meant to change the way a report behaves.

A very simple solution is to create 2 report pages , each with their own sorting applied and then use the boolean to conditionally hide one of the pages (and of course show the one you intend to use with the proper sorting)

Ties Blom

 

[Danielbryanuk]

Great thanks Ties, again....

 

[Danielbryanuk]

Just one question Ties, can you set the box type property to none for the page - it seems I can only do it individually for header, page body and footer?

Thanks
Dan

 

[blom0344]

Don't know, I always use a render variable (boolean) to show/hide pages. Works great!

Ties Blom

 

[Danielbryanuk]

Ties, I was just wondering if you could help with the final step on this for me? here's the scenario...

Again using 8.3 Report studio, I have a list report which is based on query 1. Query 1 is a join between Q2 and Q3, using outer joins on 5 data items, plus lots of additional calcuated data items. For 3 of these 5 joined data items, Q1 uses a coalesce function to get a unique list of items from Q1 and Q2, e.g. Coalesce([Q1].[item1],[Q2].[item1])

In the report I am required to display always the first 2 joined data items from Query1, and then optionally one of the 3 remaining joined data items, plus all the additional calculated data items from Query1. I have prompt items for all 3 optional data items, using html to restrict the user to selecting a value from only 1 of the 3. Instead of using one data item with a case statement to determine which of the 3 to use (as this requires the user to submit values for all 3 prompts, which is impossible due to the html), the report therefore contains all joined data items, the last three of which are layout calcs conditionally rendered by referencing the appropriate prompts, ie. if paramdisplayvalue(prompt for data item 3) is not null then render.

The report runs ok, but a problem arises with duplication of the two mandatory joined data items. Even if none of the last 3 joined data items are rendered in the report, the two mandatory joined items are repeated by a factor of the number of changes in one of the last 3 joined items. e.g data items 1 and 2 are duplicated if they are related to more than one data item 3,4 or 5.

I have tried to group on all 5 joined data items in the report to no avail. I believe the problem is that the rendering doesn't mean the items aren't included in the query level. The obvious answer therefore is that I should just have one data item, using a statement to decide which of the final 3 joined data items to use...but this means the user is prompted for inputs to all three whereas I want them to simply select a value for one of the three and use this decision to decide which columns to render in the report.

Does that make any sense at all?

Is there a way to avoid layout calcs, such as another function that references the paramdisplayvalues at runtime so the user just has to select an input for one fo the three prompts?

Hope you, or another kind soul can help as my computer is edgeing nearer the window!!!

Thanks
Dan

 

[NEAR]

Hey Dude, Just thought you should know that regardless of your conditional formatting results, both queries will execute at run time. If performance is an issue this could and unneeded overhead to your report.

A suggestion would be to convert to native SQL and place a condition around the Group By etc...

Another option to consider would be to create a custom data item with a conditional statement and then use its results for your sort.

 

[blom0344]

Native SQL means you lose any means to include future changes and will force you to allow report studio users to use this. AFAIK this is normally not activated.

Without entirely understanding your last post, Dan, I believe in some Cognos 8 function the option was added to exclude dataitems from a query that are not used. Can this be a solution to your problem?

Ties Blom

 

[Danielbryanuk]

Sorry for not being clear enough, it's pretty difficult to explain. Basically I would like to find a statement to use in a query to detect whether any of three optional prompts have been completed (that is, a string has been entered by the user), without requiring the user to enter a string for each of the prompts (the user is restricted to entering a string for only one of the three prompts.)

In the past I used a case statement in one data item as follows:

case
when paramdisplayvalue(prompt1) is not null
then prompt 1
when paramdisplayvalue(prompt2) is not null
then prompt 2
when paramdisplayvalue(prompt3) is not null
then prompt 3
else 'no selection'
end

The problem is that after the prompt page the user is prompted to enter strings for the remaining two unchosen prompts as well. I do not want this to happen.

Is there another construct I can use? I am convinced that the repeating issue im my previous post will be eradicated by using only one data item, rather than including three and conditionally hiding those for which a string has not been entered.

 

[blom0344]

Any chance you can prepopulate the superfluous prompts with a value that effectively means no filter will be effected?
We use a zero value as a collection and then apply a filter like:

[somedataitem] = ........... or (?someparameter? = 0)


Ties Blom