Hide a row based on a condition involving previous row

building reports webintelligence
1

Can anyone help me do the following?

I have 4 dimension objects. An ItemName, Part #, and quality code A & B (QCA & QCB).

I need to grab all Part #'s per ItemName that have a QCA & QCB of 5&2, but if there are 5&2’s on a ItemName then I need all QCA & QCB of 6&1. My report requirements are to not show 6&1 if a 5&2 Part # exist on an ItemName.

I’ve was able to do it in SQL, but it ran for almost 5 hours as I have millions of Part #'s. If I return the union of the two it comes back in 10 seconds, but then I have many ItemNames that have both a 5&2 and 6&1 Part #'s.

What I would like to do is filter a row based on a previous row’s data. Is there a hide command that would allow me to hide a row if it meets multiple criteria, including matching a previous row’s Item Name?

Example data:

ItemName           Part #   QCA  QCB
1 1/2" Screw       12345      5       2
1 1/2" Screw       12349      6       1
2 3/4" Screw       23212      6       1
3"  Screw          12212      5       2
6"  Bolt           34567      5       2
6"  Bolt           34561      6       1

My Report: 

ItemName           Part #   QCA  QCB
1 1/2" Screw       12345      5       2
2 3/4" Screw       23212      6       1
3"  Screw          12212      5       2
6"  Bolt           34567      5       2

"All 6&1 Part#'s were removed if a 5&2 was on the same ItemName"

Many reports with similar logic will have to be done and I do have the option to use DeskI if needed.

Schmitty (BOB member since 2006-11-16)

2

Hi

Using calculation contexts will help.

Create a variable called “Does Item Have QCA=5 and QCB=2 ?” with this formula;


=Max((If([QCA]=5;If([QCB]=2;"Y";"N");"N") In  Body) In <ItemName>

Display a datablock with the following columns:
Item Name
Part #
QCA
QCB
Does Item Have QCA=5 and QCB=2 ?

Apply a break to Item Name

Add a column, and inside it, create a new variable called “Show Row Flag” with this formula;


=If([Does Item Have QCA=5 and QCB=2 ?]="Y";If([QCA]=6;If([QCB]=1;0;1);1);1)

Apply a top 1 ranking to the Part # column, based on “Show Row Flag”

Delete any rows you don’t want to see.

Does this help you?

jac :australia: (BOB member since 2005-05-10)

3

Unfortunately, I was unable to get WebI to accept In for the code on the “Does Item Have QCA=5 and QCB=2 ?” formula.

The second formula I am working right now, but the paranethis are not matching up, so I am trying to modify that one to work.

Will I have to keep these columns in my report? Or can I delete them after the ranking has been done?

Schmitty (BOB member since 2006-11-16)

4

ah you are right. I have edited my post above accordingly

you won’t need to keep them in the report, no.

I created the syntax in full client (v6.5.4). Attached is the report. This should hlep you.
Document1.rep (75.0 KB)

jac :australia: (BOB member since 2005-05-10)

5

Wow, thanks! I’ll check it out.

Schmitty (BOB member since 2006-11-16)

6

Great I got it to work. Of course they want the report saved as text with an output of ItemName,Part#QCAQCB, which I usually do by converting setting everything as strings and concatenating with the single comma. Which would remove the ranking, but I’ll see if they can’t edit their other programs that read the data.

Thanks again!

Schmitty (BOB member since 2006-11-16)

7

Good to hear you got it working. I am confused as per your comment above why the ranking would be removed, though. You can have QCAQCB as a concatenated column in your datablock. But you could also have a QCA column and a QCB column, but HIDE them. That way everything should work fine…

jac :australia: (BOB member since 2005-05-10)

8

I must have had misinformation on hiding columns. I’ve read some threads and thought you couldn’t do it, but I found a thread that told me how.

Now, I have my report exactly how my customer needs it. I’m fairly new to B.O. and I’m the only ‘expert’ here.

Thanks for your help!

Schmitty (BOB member since 2006-11-16)

9

Hi,

I got quite a similar problem : here is the kind of data I have :

Name Attribute HasSeveralAttValue
A X 1
A Y 1
B X 0
C X 1
C Y 1

Name and Attribute are dimensions from my Query, HasSeveralAttValue is a measure variable I created to flag whether several Attributes are linked to a given Name or not.

And I would like to get something like that :

A

  • att X
  • att Y

C

  • att X
  • att Y

(using section or breaks for A&C should be ok)

using the tip about rank on my flag I can have the table :
A X 1
A Y 1
C X 1
C Y 1

but if I add a break then I got :
A

  • att X
  • att Y
    B
  • att X
    C
  • att X
  • att Y

:frowning:

Im using BO XI R2 SP3, I have tried to use section that would hide when my flag = 0, but the “hide when empty” does not seem to work upon measure.

ese-aSH :fr: (BOB member since 2007-05-22)