BusinessObjects Board

Measure Object to Flag Valid Rows

building reports desktopintelligence
#1

In BO 5.1, I need a measure object to flag rows as valid. I need to know how to create the object in the universe. I have two data providers: one contains all the data I want in the report, but too many rows; I need a filter. The second data provider contains dimensions for only the rows I want in the report; I need a measure object in the second data provider to include in the report data so I can filter the report for only the valid/flagged rows; the data providers are linked via the common dimensions.

I think I need a “Flag” object that is a constant = 1. I can filter the data for only the rows that contain the Flag =1. I want the “Flag” available for all queries on the universe. When I create the object, it requires a table; I don’t necessarily want it tied to a table.

How do I create the object or is there a better way to accomplish my goal of filtering rows by linking the data providers and including a “Flag”/valid row object.

Thanks.

Buck :us: (BOB member since 2007-04-10)

#2

On what condition you want to have a FLAG. ? What database are you using ?

In case of Oracle, you can use CASE or DECODE constructs

BO_Chief :us: (BOB member since 2004-06-06)

#3

Hi. Thanks for the reply.

I am using Oracle 8i. What I am trying to do is simulate in BO a multiple column sub select in the where clause to include only the desired rows for the report:

d=dimension; m=measure; a=attribute

select d1, d2, d3, d4, d5, d6, m1, m2
from table …
where …
and (d1,d2,d3,d4) in (select d1,d2,d3,d4,m3 from table …)

m3 is a measure object as a flag = 1.

In the first data provider, I have all the report columns; I need to filter the rows. In the second data provider, I have four dimensions and one measure object. The dimensions contain the values in the sub select (desired rows); the flag, m3, is used to flag the desired rows.

In BO, I link the four dimensions from the two data providers to include the flag, m3, in the report data. In the report, I can add a filter where the flag = 1 to include only the desired rows.

I don’t think I can do this in BO5.1 because I cannot change d5 and d6 to attributes. Because the dimensions in both data providers are not the same, I can’t include all the measures (m1,m2,m3) in the report.

Bottom-line, I don’t think I can do it. Do you agree?

Buck :us: (BOB member since 2007-04-10)

#4

From the above lines, your issue looks to me like you want to synchronize the DP’s and bring the data into a single table… right ?

If yes, then you can do it… in the reporter.

BO_Chief :us: (BOB member since 2004-06-06)

#5

Correct. I am synchronizing/linking the data providers to make one table, however, when I add m3, the values for m1 and m2 are not visible. What do I do?

I am using the 5.1 Reporter.

DataProvider 1:
d1,d2,d3,d4,d5,d6,m1,m2

DataProvider 2:
d1,d2,d3,d4,m3

d1,d2,d3 and d4 are linked (synchronized) between the two data providers.

Table/report:
d1,d2,d3,d4,d5,d6,m1,m2,m3

Buck :us: (BOB member since 2007-04-10)

#6

What is the WHERE condition for your sub-query in your earlier post ?

If you have m3=1 in your subquery then you are already filtering at the Query Panel Level… :yesnod:

So, I want to check what you have in there …? Please post the code here…?

BO_Chief :us: (BOB member since 2004-06-06)

#7

Hi:

As a last resort, I deleted the other data providers in the report that were not used for this table … it worked! :!: All the values for the measure objects were in the table. The other data providers were linked automatically by BO; I have no clue what was going on. :?:

Bottom-line, the table is working now. Thanks very much for your help.

To answer your question regarding the subquery:

The subquery uses set operations; it can’t be used in the main query; it is limited to four dimensions/columns; I use the measure, DRUG.AMT_WAC_UNIT, as the valid row flag:

(
SELECT DISTINCT
  FILL_VIEW.NBR_LOCATION,
  FILL_VIEW.NBR_RX,
  FILL_VIEW.NBR_FILL,
  FILL_VIEW.NBR_FILL_PARTIAL,
  DRUG.AMT_WAC_UNIT
FROM
  FILL_VIEW,
  DRUG
WHERE
  ( FILL_VIEW.NBR_NDC=DRUG.NBR_NDC  )
  AND  (
  FILL_VIEW.NBR_LOCATION  IN  @variable('3. Enter Location Number(s)')
  AND  DRUG.NAME_DRUG  IN  @variable('4. Enter Drug Name(s)')
  AND  FILL_VIEW.DATE_FILL_ENTERED  IN  (SELECT
  DATE.DATE_GREG
FROM
  DATE
WHERE
  ( 
  DATE.DATE_GREG  BETWEEN  @variable('1. Enter the beginning date to include: (mm/dd/yyyy)') AND @variable('2. Enter the last date to include: (mm/dd/yyyy)')
  )
)
  )
MINUS  
(
SELECT DISTINCT
  FILL_VIEW.NBR_LOCATION,
  FILL_VIEW.NBR_RX,
  FILL_VIEW.NBR_FILL,
  FILL_VIEW.NBR_FILL_PARTIAL,
  DRUG.AMT_WAC_UNIT
FROM
  FILL_VIEW,
  DRUG
WHERE
  ( FILL_VIEW.NBR_NDC=DRUG.NBR_NDC  )
  AND  (
  FILL_VIEW.CODE_FILL_STATUS  !=  'SD'
  AND  FILL_VIEW.DATE_FILL_ENTERED  IN  (SELECT
  DATE.DATE_GREG
FROM
  DATE
WHERE
  ( 
  DATE.DATE_GREG  BETWEEN  @variable('1. Enter the beginning date to include: (mm/dd/yyyy)') AND @variable('2. Enter the last date to include: (mm/dd/yyyy)')
  )
)
  AND  FILL_VIEW.NBR_LOCATION  IN  @variable('3. Enter Location Number(s)')
  AND  DRUG.NAME_DRUG  IN  @variable('4. Enter Drug Name(s)')
  )
GROUP BY
  FILL_VIEW.NBR_LOCATION, 
  FILL_VIEW.NBR_RX, 
  FILL_VIEW.NBR_FILL, 
  FILL_VIEW.NBR_FILL_PARTIAL, 
  DRUG.AMT_WAC_UNIT
HAVING
  ( 
  sum(FILL_VIEW.IND_ADD_DELETE)  <  1
  )
UNION  
(
SELECT DISTINCT
  FILL_VIEW.NBR_LOCATION,
  FILL_VIEW.NBR_RX,
  FILL_VIEW.NBR_FILL,
  FILL_VIEW.NBR_FILL_PARTIAL,
  DRUG.AMT_WAC_UNIT
FROM
  FILL_VIEW,
  DRUG
WHERE
  ( FILL_VIEW.NBR_NDC=DRUG.NBR_NDC  )
  AND  (
  FILL_VIEW.CODE_SCRIPT_TYPE  IN  ('N', 'C', 'R')
  AND  DRUG.NAME_DRUG  IN  @variable('4. Enter Drug Name(s)')
  AND  FILL_VIEW.NBR_LOCATION  IN  @variable('3. Enter Location Number(s)')
  AND  FILL_VIEW.DATE_FILL_ENTERED  IN  (SELECT
  DATE.DATE_GREG
FROM
  DATE
WHERE
  ( 
  DATE.DATE_GREG  BETWEEN  @variable('1. Enter the beginning date to include: (mm/dd/yyyy)') AND @variable('2. Enter the last date to include: (mm/dd/yyyy)')
  )
)
  )
INTERSECT  
SELECT DISTINCT
  FILL_VIEW.NBR_LOCATION,
  FILL_VIEW.NBR_RX,
  FILL_VIEW.NBR_FILL,
  FILL_VIEW.NBR_FILL_PARTIAL,
  DRUG.AMT_WAC_UNIT
FROM
  FILL_VIEW,
  DRUG
WHERE
  ( FILL_VIEW.NBR_NDC=DRUG.NBR_NDC  )
  AND  (
  FILL_VIEW.CODE_SCRIPT_TYPE  IN  ('D')
  AND  FILL_VIEW.DATE_FILL_ENTERED  IN  (SELECT
  DATE.DATE_GREG
FROM
  DATE
WHERE
  ( 
  DATE.DATE_GREG  BETWEEN  @variable('1. Enter the beginning date to include: (mm/dd/yyyy)') AND @variable('2. Enter the last date to include: (mm/dd/yyyy)')
  )
)
  AND  FILL_VIEW.NBR_LOCATION  IN  @variable('3. Enter Location Number(s)')
  AND  DRUG.NAME_DRUG  IN  @variable('4. Enter Drug Name(s)')
  )
)
)
)

[Edited, when posting code samples please use the code option for formatting. It will preserve any indenting or formatting that you may have done. Thank you, Andreas.]

Buck :us: (BOB member since 2007-04-10)

#8

If you have multiple data providers, and when they have dimensions with the same name (trunc blanks) then BO will automatically links them.

If you want to understand more about synchronizing DP’s then check this presentation

https://bobj-board.org/t/53239

Hope that helps and good to know that your report is working.

BO_Chief :us: (BOB member since 2004-06-06)

#9

Thanks. The PowerPoint helps.

When I added the measure from the linked data provider, the context of the measures in the table changes; this is why there is no value.

It changes from:

= In (<Rx Number(Fertility Data)>, , , , , , , <NDC 11 (Formatted)>, <Prescriber.Last Name>, <Prescriber.First Name>, <Prescriber.Middle Initial>, <Prescriber.Address>, <Prescriber.City>, <Prescriber.State>, <Prescriber.Zip5>, , <Prescriber.Phone Number>, , , )

To:
= In (<Rx Number(Fertility Data)>, , , , , , , <NDC 11 (Formatted)>, <Prescriber.Last Name>, <Prescriber.First Name>, <Prescriber.Middle Initial>, <Prescriber.Address>, <Prescriber.City>, <Prescriber.State>, <Prescriber.Zip5>, , <Prescriber.Phone Number>, , , , = RowIndex())

I don’t know why I am getting the = RowIndex()). I can manually change the context to = In (<Rx Number(Fertility Data)>) to get a value, however, I am not confident that this is the best solution; I get a gut feeling that something else wrong.

The measure object I was adding was not an aggregate; this is why the RowIndex() was added to the context. It worked fine with an aggregate object; no extended syntax was required.

Buck :us: (BOB member since 2007-04-10)