Highlighting a Row in SAP BO

 

SAP Business Objects

I'm being asked to create and maintain Business Objects (BO) reports since the Senior Developer we had working on them retired.

We started having issues where the counts on some of our reports were not balancing.  We have a balancing system that helps us ensure that the number of records sent from one vendor gets to the next vendor and that the receiving vendor has the same number of records.  The report only focused on a 24-hour period.

So I ran the current report over a couple of days and did a manual match.  This was tedious.   So, I decided to change the report to take into account a 48-hour period instead.  My manager wanted the report over a week.  So, anticipating that this could go completely off the rails as we delved more into this issue.  I decided to make the date reports dynamic, this way we could just run the report over whatever time period we wanted/needed.

I generated the report and my manager asked if I could highlight the rows that were not in balance.

The Requirements

So, let's recap, this is what we need to make this report useful:

• Dynamic date entry

• Highlight the values that are not in balance

Prompts

The original query was straight-forward enough.  For this balance control ID, find all of the records since the previous day at midnight.  The original query was actually 3 queries.  This one and two others reflecting different Balance Control ID's, I assume in an effort to keep them grouped together.

Picture: Original Query

In order to make this dynamic, I had to change the date part of the query and make it a value that could be entered.

I made the following tweeks to the query.  Instead of 3 queries, I combined all of the Balance control ID's into one "IN LIST".  

I then removed the "Previous Day" filter and replaced it with two prompt fields; a from and a to.

Picture: From Date

Picture: To Date

Picture: New Query

Now when the query/report is run, the user is presented with the following:

Picture: New Prompt

Formulas

BO is not just an over-glorified report writer.  It is a Business Intelligence tool.  This introductory case study does not do it justice.  Hopefully, in subsequent blog entries, I will be able to show it off more.

You can utilize SAP BO's powerful formula's to achieve many things.  I decided to utilize a 'Count' function to achieve what I needed.  Ideally, we should get two records with the same file name and the same number of records.  So, if we perform a count per file name we should get a count of 2, if we do not, then those are the one we want to highlight.

So I created a variable called, creatively enough, CountOfDups.  And used this formula:

=Count([ALIS_2DAY].[Process ID]) In ([ALIS_2DAY].[Session ID])

Picture: Getting Duplicate process id's in a set of session id's

This was the result:

Conditional Formatting to highlight a row--Kinda

So, now all I need to do is use a Conditional edit to highlight the rows that have 1's instead of 2's.  Yippee!...Not.  I initially could not find a way to highlight a row of data utilizing the conditional formatting tool.  It is subtle and subtle is frustrating to newcomers (hint SAP--document this shit).

Initially, I could get the CountOfDups column highlighted, but honestly, I didn't even want people to see that column.

So, I created the crappy highlighting in hopes of stumbling on to something.

Click [Format] to select the formatting I want the users to see.  Bold black text with a yellow background.

Picture: Change Text

Picture: Change Background

And voila!  I've highlighted a cell I don't want to highlight!

Picture: Highlighted unwanted column

I Googled "SAP BO highlight row", "SAP BO highlight a value using another value", etc.  You name it, I tried it.  I saw that a couple of people said to use 'Alerter', but this is only in a downloadable version of SAP BO, not the version I'm using.  Ugghh!

So, I created another variable and put in the more palatable 'No Match' and added that column.

Picture: Concession--create "No Match" column

To Achieve this:

Picture: Checking out new column

So I clicked the conditional formatting toolbar again.  The idea was to create a new rule to highlight 'No Match'...but:

Picture: Create a new formatting rule

And this happened:

Picture: Wait...What?

What just happened?  How did that just highlight the 'No Match' column?

Well if you look back at figure X.X, the 'NoMatch' column was active and hence highlighted in gray when I clicked on 'Formatting Rules'.  As it turns out, you can apply existing rules to whatever column you have 'active' at the time.

So, needless to say, I did the same thing with the filename (session ID) column:

Picture: We could be on to something

Yippee! For real this time!

But now…I want to get rid of the 'CountOfDups' column.  Would that affect anything?

Picture: Hiding trigger column

And of course it worked…this is SAP BO for goodness sake.  They are the bestselling reporting tool around for a reason.

Picture: Final Result

Thus ends my initial adventure with SAP Business Objects.  As I run into more situations that I think will be of help to you, I'll post more.