Value prompt - word wrap long text inside of a value prompt

[ry1633]

Hi all,

I've ran into a problem that I'm not sure how to proceed with.    I'm working with a simple Value Prompt with a column that has 3 possible values.  Problem is that one of those columns has *very* long text statement of some regulatory information that is about 800 characters long.   Running the report shrinks the column to a tolerable level, but putting it into a Value Prompt makes the column very very long, so much so that it extends clear off the page on the right side, and would be very unwieldy for the user to work with.

My table is called [RESTRICTIONS] and has three values:
'51 - some value'
'52 - another value'
'186 - massive long description statement here of 800 chars'

Using it in a Value Prompt for filtering,  how can I either Word Wrap, shrink, truncate, or otherwise shorten that value, and still have it display for the user in a useful and clear way?

[Michael75]

Are you asking for a way to display a generic text for the 186 rows, or some special text reformatting to reduce the 800 chars to a more manageable 50 or so?

If the former, and assuming that your table looks like this:

RESTRICTIONS_ID
RESTRICTIONS_DESC

then you could redefine RESTRICTIONS_DESC as follows:

CASE
  WHEN [RESTRICTIONS].[RESTRICTIONS_ID] IN (51, 52)
  THEN [RESTRICTIONS].[RESTRICTIONS_DESC]
  ELSE 'The actual text in the table is far too long to display in this prompt'
END

Whether truncation would be an option or not is something only you know - it depends on the actual text.

[ry1633]

RESTRICTIONS is only one column in my table with those three options in it.

I'd be willing to settle for a standard Word Wrap too - but I don't know how to put that into a Value Prompt either.

[Michael75]

Yes, it's clear from your first post that you're only dealing with one column. Sorry  :(

I don't think word wrap in prompt values is possible. Perhaps some stellar Cognoise person will step in to contradict me...

Failing that, I'll re-jig my first suggestion:

CASE
  WHEN substring ([RESTRICTIONS].[RESTRICTIONS], 1, 2) IN ('51', '52')
  THEN [RESTRICTIONS].[RESTRICTIONS]
  ELSE 'The actual text in the table is far too long to display in this prompt'
END

You might want to add some code here to take care of values other than 51, 52 & 186 which could come along later.

[ry1633]

Oh no problem.  I haven't been doing Cognos too long, so I always hesitant to ask things because I feel a bit inferior about all of this still :)

How do you put a CASE statement inside of a Value Prompt?

[Michael75]

I always hesitant to ask things because I feel a bit inferior about all of this still :)

Join the club  :)

I can see the following scenario for you, assuming that you've got a prompt page containing a Value Prompt, and that we're talking about a single select prompt.

- qry_Prompt contains the column [RESTRICTIONS].[RESTRICTIONS], defined as in my previous post. The Value Prompt is associated with this query, and sets a parameter called p_RESTRICTIONS

- qry_Main feeds your container (list, crosstab or whatever), and has the filter

Code Select Expand

[RESTRICTIONS].[RESTRICTIONS] = ?p_RESTRICTIONS?

If you want to allow two or all three of the possibilities, change the prompt property Multi-Select from No to Yes, and use this filter

Code Select Expand

[RESTRICTIONS].[RESTRICTIONS] in (?p_RESTRICTIONS?)

Does this help?

[ry1633]

I've just been putting the Value prompts right in the report itself, so the user could do everything they want right there.  I made a table above the report list and below the header.    My data sources are Oracle TNS connections and views of tables, so they look like this:

[TNS Connection Name].[View Name].[RESTRICTIONS]


And so right now, my Value Prompt just references it this way:   [TNS Connection Name].[View Name].[RESTRICTIONS] in_range ?Parameter1?

[Michael75]

Hi,

I'd missed your earlier question:

Code Select Expand

How do you put a CASE statement inside of a Value Prompt?

The answer is that, while it is technically possible and can work, it is not good practice and you shouldn't be doing it :)  Lynn has often posted clearly and eloquently on this point, most recently here: http://www.cognoise.com/index.php?topic=28164.0

But the CASE logic I posted wasn't intended to go into a Value Prompt or a filter. It was meant to go into the definition of your actual column - in this case

Code Select Expand

[TNS Connection Name].[View Name].[RESTRICTIONS]
in order to display a text of a manageable length instead of the 800c. of your 186 message.

So the definition of this column would now look like this:

Code Select Expand

CASE
  WHEN substring ([TNS Connection Name].[View Name].[RESTRICTIONS], 1, 2) IN ('51', '52')
  THEN [TNS Connection Name].[View Name].[RESTRICTIONS]
  ELSE 'The actual text in the table is far too long to display in this prompt'
END

Next, your filter.
Ignore my earlier remarks about a prompt page - my technique will work equally well with a Value Prompt in the report page (or indeed, with a prompt generated by the presence of a filter referencing ?Parameter1? or whatever).

The in_range operator is used to select, well, a range of values. Most often it's used in conjunction with a Date Prompt on which the Range property is set to Yes, but it can also be used for other values, e.g. I want to select all codes between 350 & 500.

This doesn't seem best suited to your case, i.e. selecting from values of 51, 52 & 186. Here's my suggestion again, using the two queries I suggested earlier, and amended with your table name.

Single select:

Code Select Expand

[TNS Connection Name].[View Name].[RESTRICTIONS] = ?p_RESTRICTIONS?

Multi-select:

Code Select Expand

[TNS Connection Name].[View Name].[RESTRICTIONS] in (?p_RESTRICTIONS?)

Does this help?

[ry1633]

I'll try that. How do you put the case statement into the column?  Do you right-click/select the column header or the first cell right below it?

[Michael75]

Behind the list report you've created, Report Studio has created a query whose name defaults to Query1. This query will contain all the columns you've referenced in your report because they should appear in the layout, or possibly because you've dragged them in in order to filter on them.

How to get to the definition of the column in Query1 whose definition should be changed? There are several techniques, and I'll give you what I consider to be the easiest. In the Page Explorer (the default pane of Report Studio) right click on the header of the column in question, then choose Edit Query Expression. Here you can paste in my suggested CASE . . . WHEN code, and validate it by clicking on the green tick box.

NB I realise from your reply that you may not be at all familiar with the Query Explorer pane, in which case my suggested "multi query" approach will be mumbo jumbo to you :-[  If that's the case, implement and test what you can, and post back when you have further questions.

[ry1633]

I'll wade in and see what I can do.  I've only been working with Cognos about 6 months and have only taken one class - so I do know some stuff, but am still a novice on a lot of things. :)