Formatting Repeaters

[Gustafia]

Up to a year ago, we were still using Impromptu 7.4 with an Oracle DB. I was using the Oracle MODEL function in many reports to punctuate list of entries different ways all in one field:

- a, b, c, d, e
- a, b, c, d and e. (code below)
- each entries separated by a carriage return so it shows on a separate line

I picked up this code on the net that I adapted based on the formatting.

Code Select Expand


select WBS_NUMBER, string
from ( select WBS_NUMBER, string, position, val_count
   from ( select WBS_NUMBER, COUNTRY_ENGLISH_COMMON_NAME from DW_PROJECT_COUNTRY_V a, DW_COUNTRY_V b where a.COUNTRY_CODE=b.COUNTRY_CODE ) t2
   model
     partition by ( WBS_NUMBER )
     dimension by ( row_number() over ( partition by WBS_NUMBER order by COUNTRY_ENGLISH_COMMON_NAME ) as position )
     measures ( COUNTRY_ENGLISH_COMMON_NAME , cast( null as varchar2(4000) ) as string , count(*) over ( partition by WBS_NUMBER ) val_count )
     rules
     (
       string[ any ] order by position =
         case
         when COUNTRY_ENGLISH_COMMON_NAME[ cv()-1 ] is null then COUNTRY_ENGLISH_COMMON_NAME[ cv() ]
         when COUNTRY_ENGLISH_COMMON_NAME[ cv()+1 ] is null then string[ cv()-1 ] || ' and ' || COUNTRY_ENGLISH_COMMON_NAME[ cv() ] || '.'
         else string[ cv()-1 ] || ', ' || COUNTRY_ENGLISH_COMMON_NAME[ cv() ]
         end
     )
  ) t2a
where val_count = position
order by WBS_NUMBER


In January, we moved to Cognos 10 with SQL Server DB. The only that comes close to MODEL is Repeaters. The problem is that you can't conditionally format the content based on the position of the entry in the list like the MODEL rules above. It will just repeat (a, b, c, d, e,) similar to the solution in http://www.cognoise.com/index.php/topic,27326.msg87433.html#msg87433. Having the option to format the first, last and the in between a certain way would be fantastic.

I've heard of stored procedures but IT is overwhelm in trying to fix our framework model. We also do not have access to the SQL object for security reasons (they don't want us to append data :o). If possible, having a solution that I can implement in queries would be preferable since it will always be based on results of existing queries.

Any help to resolve this is greatly appreciated since I need to do that in Excel and it is very labor intensive.

[hespora]

The problem is that you can't conditionally format the content based on the position of the entry in the list

You almost got it there. What you do is, you don't conditionally format, but conditionally populate.

let [data] be your original data item.

create a data item [and] as

Code Select Expand

case rank ([data] desc) when 1 then 'and ' else '' end

create a data item [comma] as

Code Select Expand

case rank ([data] desc) when 1 then '' else ', ' end

Now, put the data items [and] [data] [comma] into your repeater in that order.

that's how to do your second example. first one, just leave out the [and] item. third one, just put [data] inside a 1x1 table inside the repeater.

/Edit: Forgot two things: a) for this to work, you have to sort your repeater by [data] ascending; b) if you want something to happen at a different position in the list, you have to use different sorting in the rank functions. For example, for the first item, you'd use rank ([data] asc) instead and compare to 1. For the second-to-last item, you rank descending and compara to 2, and so forth.