BusinessObjects Board

Is it possible to build this conditional rule?

building reports desktopintelligence
#1

First off I’m using version 4.2 sp3

My test data is below
id clin_cpt pb_cpt
1 123 123
2 122 121
2 123 122

I’m trying to build a conditional format that will highlight the clin_cpt row, if that clin_cpt value is in the pb_cpt column for the same id. So in this case the clin_cpt (123) for id 1 would be highlighted because the same value is in id1 pb_cpt.

For id2 the clin_cpt of 122 would also be highlighted as it matches the pb_cpt value on the next row (but it is the same id). Yes there will be some duplicate id’s.

I was thinking this would be perfect as a conditional formatting for a break section (id) but could not figure out how to do it. Is this possible?

cplummer (BOB member since 2018-05-01)

#2

Highlighting id 1 is a basic rule - clint_cpt = pb_cpt as you may have already discovered.

What has caused the two rows to produce what they have for id2? I’d expect a Cartesian product of
2 122 121
2 123 122
2 122 122
2 123 121
instead of what you’ve put below.

#3

I’m comparing the primary clinical cpt code versus the primary professional billed cpt code, then the secondary of each and so on. So an id might have multiples of each code.

cplummer (BOB member since 2018-05-01)

#4

Is your dataset only these three columns though?

#5

The tables I’m pulling from have numerous other fields, but for this request I’m only returning the id, clinical cpt code, professional billed cpt code and the surgery date. In my example I left the date out as I was just trying to simplify the example.

cplummer (BOB member since 2018-05-01)

#6

Final question that I can think of - would you want all rows highlighting for ID2, or just where clin_cpt exists as pb_cpt (i.e. row 2, 3 or both?)

#7

I have a solution. Use two queries. No break needed.

1.make data01 (Query1)

id clin_cpt pb_cpt id&clin_cpt id&pb_cpt
1 123 123 1123 1123
2 122 121 2122 2121
2 123 122 2123 2122

2.make data02 (Query2)

id&pb_cpt Dimension id&pb_cpt Detail
(<–make it Dimension) (<–make it Detail, connects to id&pb_cpt Dimension)
1123 1123
2121 2121
2122 2122

3.join these two queries

Link id&pb_cpt Dimension in data02 to id&clin_cpt in data01

  1. add in data

us data01 as base data. Then pull in id&pb_cpt Detail from data02 ( ONLY THE DETAIL ONE , NOT THE DIMENSION ONE). The result would be like this:

see attachment

There are some strange values in row 4, you can use some filter to hide it.

English is not my first language. I tried my best to let myself understood.

Try it and let me know if this helps. Thanks
4 add in data.jpg

bonotatsujin :taiwan: (BOB member since 2017-12-20)