BO Webi - Need Variable to Remove Nulls From Results

jsisock79 0
Viewed 32

I'm looking to create a variable that will take the take the numeric value for a dimension. I tried removing Nulls in my query details, but that won't work because some results only have a Null value (see screenshot) and I was losing results that way.

I also need the variable for use in a cross tab table so I can do a count of each acuity level. I tried creating a Max variable on the acuity field =Max([Acuity Level]). That works for the main tab, but it doesn't work in a cross tab table. Please see attached screenshots for more details.

enter image description here

Acuity Crosstab

enter image description here

Column: Acuity Level

Row: Tracking Date =FormatDate([Start Tracking Date & Time];"MM/dd/yyyy")

Body: # of Patients =Count([Financial Number])

business-objects
1 Answers

First off I created a query with your test data so I could drop into a free-hand SQL query so I have an example with which I can work. I added Row Number to maintain the row order of your data.

enter image description here

My approach requires three variables. A different approach may be possible requiring less variables or the formulas in the variables could be consolidated. However, I like to keep them separated for better understanding of the logical progression and better maintainability.

Var Acuity Level Adjusted gets set to -1 if the Acuity Level is Null and otherwise leave it as is just to make it easier to deal with...

=If(IsNull([Acuity Level]); -1; [Acuity Level])

Var Max Acuity Level is the greatest value of Var Acuity Level Adjusted within each combination of Patient Name and Encounter Type. This is called a calculation context. I do not understand the nuances of this topic well enough to explain why what I have below works, but it does. I refer to that previous link a lot. Also, this is why it was important that I picked -1 to replace Null.

=Max([Var Acuity Level Adjusted]) In ([Patient Name]; [Encounter Type])

Var Max Filter flags the row where the first two variables are equal. This variable is necessary because you cannot filter based on one object relative to another object.

=If([Var Acuity Level Adjusted] = [Var Max Acuity Level]; 1; 0)

Now if I add those variables it looks like this...

enter image description here

Then we can add a filter to only show the records where Var Max Filter = 1. You can hide the extra columns or even delete them from the table.

enter image description here

Hope you can apply this to your situation.

userPzE20
Related