Need to filter on aggregated lines.

building reports desktopintelligence
1

Using BusObj 6.5.1.

I have a report returning using data from a universe and from an Exel spreadsheet, linked on columns with a common value, an id #. The spreadsheet has a single row for each id#. The universe returns varying numbers of rows for each id#. One of the universe columns returns a measure, and that is the value which varies in the rows for each id#. There is a corresponding value from the spreadsheet which is supposed to match up with the sum of those measure values for each id#. If I uncheck the “Avoid Duplicate Rows Aggregation” in the Format Table, the universe measure items sum up just fine for each id#.

What I need to do is to filter out each row for which the difference between the spreadsheet value and the aggregated measure is less than one, but apparently the Format/Filter is only working on the unaggregated rows and won’t touch the aggregated value.

Does anyone have a suggestion? Thanks.

Kent Wendler (BOB member since 2006-01-20)

2

Filtering on aggregated measures is “technically” not possible … but then you already knew that smile.

The workaround is to create a “flag” variable of sorts. Create a variable with “if” logic that evaluates to say, a “Y” or N" value. Then set the filter where that flag variable equals Y.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

Great idea, except it doesn’t seem to work.

I added a column with a variable

= If (Abs() < 1) Then “Drop” Else “Keep”

where is the spreadsheet value (only 1 row per id#) minus the universe value (multiple rows).

The "Drop"s and "Keep"s appeared correctly, but applying the filter has no effect. All the "Drop"s remain. If I turn off aggregation the filter works correctly, but I need the aggreated value from the universe for a correct comparison.

I will welcome further suggestions.

Kent Wendler (BOB member since 2006-01-20)

4

Don’t filter, use a Ranking instead. If you have values “Drop” and “Keep” then you will want to select the “bottom 1 value” as Keep comes after Drop alphabetically. This is, unfortunately, the best workaround that I’ve seen with BusinessObjects and filtering on aggregates. It has problems (what if everything is “Drop” then it will all show up instead of all being filtered.)

Another option is to create a sub-query that eliminates the data before it gets to the report.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

It won’t let me apply a ranking. Ranking is disabled.

I’m also struggling to figure out how to set up a subquery that will in effect aggregate the measure object before returning the query. I’ve already tried setting up a user object which tries to sum the measure object in the query and used that in the query panel, but it still returned the multiple rows…

:hb: :wah:

Kent Wendler (BOB member since 2006-01-20)

6

Make sure you are ranking on something other than the “Keep”/“Drop” object. In other words, pick a different dimension object from the block, click the rank button, and use the “Keep”/“Drop” object as the ranking item for the selected object. You cannot rank directly on this object. In most cases when I use this trick I don’t use words like you have but use a zero and one, then it is easier to explain. If you set each value to 1 when ou want to keep it, and then rank for the “top 1” value on some other element in the block… hopefully you see where this is going.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

I changed it to 1’s and 0’s. However the Rank setting popup window does not include that column as a “Based on” option. It does include the two columns used in the calculation, but that does me no good. :hb: :crazy_face:

Kent Wendler (BOB member since 2006-01-20)

8

Is the 1/0 column based on values from more than one data provider? If so, then that’s the reason it won’t let you rank. Very frustrating, I know, but that’s a limitation. :x

Dave Rathbun :us: (BOB member since 2002-06-06)

9

Yes, it is. One is an Exel spreadsheet and the other is a universe.:-1:

Kent Wendler (BOB member since 2006-01-20)

10

Take a look at this information. It describes a sneaky workaround. The linked posts have a warning, but i’ll repeat it here. The technique seems to work just fine (we have several production reports using it successfully), but is clearly not intended to work that way. Proceed at your own risk.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

11

This is devolving into a theatre of the absurd. :cuss:

To recap:

I have two data providers. The first is a spreadsheet. For each id# it provides one row with three numbers. The second is a universe. For each id# (which links to the id# of the first data provider) it provides a varying number of rows, each of which has one measure object.

The sum of the measure objects - the aggregated value - for a given id# from the second data provider is supposed to match the sum of the three values for that same id# from the first data provider.

I want to provide a table listing those which differ by at least 1.

:arrow_forward: BusObj does not permit me to filter using the aggregated value from the second data provider.

So I carefully start to set up the fudge workaround in the preceeding response: I set up the flag measure object using only the measure object from the universe, then I set up the ranking of a measure object based on the flag dimension object. So far, so good. Then I paste in to the table the correct definition of the flag object using the calculations from the first data provider; and bang!, the calculated columns from the first data provider get the #ERROR flag.

:arrow_forward: BusObj will apparently permit no calculations from the first data provider to be used in computing the value of the ranking measure object.

So it appears at this point I have three options:

  1. Making a “business case” for the universe developers to define a new measure object in the universe which will return only 1 row per id# – probably the best choice, although I’ve been told this can be difficult to “push through”.

  2. Saving the aggregated table as a spreadsheet, reimporting it, then filtering it - extra manual work, including restoring the formatting.

  3. Sectioning the report, and hiding the blocks based on the calculated values in the footers - ugly looking because of the residual space left for each hidden section, plus all the setup work.

Other than these, I am still open to more “wizardly” ideas.

Kent Wendler (BOB member since 2006-01-20)

12

Question … are the amount columns from the Excel spreadsheet defined as measures (pink dots) or dimensions (blue cubes)? That could be the entire issue … they should be measures.

In the attached sample I created two data providers. They happen to be from the same universe, but I varied them as far as the number of dimensions returned (though both contain customer) and the amount of data returned (one is a single year only). I was able to create a filter that listed only those customers where the amount didn’t match … no ranking, no “avoid duplicate row aggregation”, nothing like that.
Compare measures between data providers.rep (80.0 KB)

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

13

Thanks for all the trouble you’ve taken to try to help. :slight_smile:

All three spreadsheet values show as measures.

I’ve looked at your sample. The differences that I see are that the data providers are both universes - you said they were the same universe; I’m doing a sum of the three measure values from one row of one data provider and comparing that to the aggregate of the one measure from the other; and I’m permitting a difference of up to 1 to be an acceptable match and not be displayed.

Whether it’s the calculating of the sum of the three measures from the first data provider, or that data provider being a spreadsheet, both of these or something else I do not yet know. It just doesn’t work.

Kent Wendler (BOB member since 2006-01-20)

14

I’m not sure how well I will be able to explain this, but I’ll try.

If you don’t need to do the sum() you can try relying on the projection function instead. Use the context operation to “imply” the sum, but don’t list it specifically. In your example, you mention an “id” value that is present in both, and you want to compare the sum of the 3 rows from XLS with the sum of the rows for that same ID from the universe, and filter those that do not match within some tolerance.

Please try this… some of which you have no doubt already been through.

  1. Create the XLS data provider
  2. Make sure that the amount field is a measure, and has a projection function of Sum
  3. Create the UNV data provider
  4. Make sure that the amount field is a measure, and has a projection function of Sum
  5. Link the two data providers on ID
  6. Display the ID, XLS.amount, and UNV.amount (weird syntax, hopefully you get my idea)
  7. Click on the ID column
  8. Click on the Insert Filter option. Note: don’t right-click or use the filter button, you need to get to the complex filter definition screen
  9. Going from memory here, you will add a filter on ID, then click the “Define” button
  10. Enter the syntax for the filter:
<XLS.amount> In <ID> <> <UNV.amount> In <ID>

Note that you have not used an aggregate function here, so you should not get a warning about that. :slight_smile:

I don’t have busobj readily available at the moment (posting from home, not my laptop) but please see if this works, or if you can even follow my suggested steps. If it works, hurray. :slight_smile: If not, I will post an attachment with some sample data showing the idea.

Dave Rathbun :us: (BOB member since 2002-06-06)

15

Dave,

Thank you for your continued suggestions, and I apologize for the delay in my response. I have been flogging this since I returned to the office on Monday.

Using your notation I believe that the difficulty comes from the fact that although there is only one XLS.amount for each id#, there are varying multiple UNV.amount’s. So BusObj creates a separate row for each UNV.amount, which show if the Avoid Duplicate Rows Aggregation box is checked. If the Avoid Duplicate Rows Aggregation box is not checked then only 1 row per id# is displayed, and all the UNV.amount’s for each id# are implicitly summed and displayed as the value for that row and column.

What I need is for that single summed value to appear when the Avoid Duplicate Rows Aggregation box is checked. Presumably then I could use that single summed value along with the XLS.amount to calculate a new value which I could then use to filter out the id#'s for which the XLS.amount and the summed UNV.amount are within 1 of each other.

With respect to your enumerated steps, I admit to quickly becoming somewhat confused. According to the reference I’m using (Business Office: The Complete Reference by Cindi Howson, 2003 ed.) Projection Aggregates can only be set by a universe designer. For various administrative/managerial reasons this is very difficult to do in my shop, although I am trying.

Perhaps, though, you mean using context operators; i.e. Sum(<UNV.amount> context_operator InputContext) context_operator OutputContext? If so, I have been trying every reasonable combination I can think of, but I cannot get the desired result to appear.

I did try defining a filter, similar to your suggestion, only as Abs(<XLS.amount> In <id#> - <UNV.amount> In <id#>) >= 1, but it didn’t work, aggregated or not. But there were those multiple rows per id# when not aggregated, which I haven’t been able to eliminate.

I’ve also tried drilling and constructing a new block whose columns are defined upon the original, to no success. Perhaps, if there actually is a solution of that sort I just haven’t found the right combination of setup details.

Further thoughts and suggests will be, as always, quite welcome.

Kent Wendler (BOB member since 2006-01-20)

16

Projection functions are set by the Designer, yes. But based on your description they already are. You can validate this by opening the Data Manager (the “cube” button on the toolbar), open the universe data provider, click the measure object you want to check, then click the Definition tab. It should show you (but not allow you to change) the projection function.

Regarding the filter, you specifically do not want to include a Sum() function, just the context operator.

Dave Rathbun :us: (BOB member since 2002-06-06)

17

I’ve played with a sample, and the trick as I outlined it no longer works in 6.5. At least so far. If I can get something working I’ll post an attachment.

Dave Rathbun :us: (BOB member since 2002-06-06)