BusinessObjects Board

Group object with multiple associated values

building reports webintelligence
#1

I have a report in which one object (Component Item Number) sometimes has multiple associated objects (LOCATOR). I’m trying to organize this report in two ways:

  • Group all COMPONENT ITEM NUMBER entries together
  • Sort the report by LOCATOR. In the case that an item has multiple LOCATORs, the report can either be organized by the LOCATOR that is alphabetically first or organized such that these objects appear at the beginning/end of the report.

I haven’t been able to accomplish both simultaneously. Initially, I grouped COMPONENT ITEM NUMBERs into sections, but couldn’t get these to sort. I then created a sort variable which concatenates LOCATOR + COMPONENT ITEM NUMBER and used a break to group the items by this variable, but in that case I can’t get the sort variable to pull more than one locator (which I hoped would pull a #MULTIVALUE error). Any thoughts about other approaches to the problem?

image
image1201×216 26 KB

#2

what about all the fields and sections ? is there any hidden sorting ?
remove everything and try to build the report step by step with sorting to find the error.

#3

Your sort variable is killing you on this. It will sort by that because you’ve broen on it and, as above, is in effect sorting on Locator then Component Item Number.

What is your desired behaviour?

#4

Yes, I agree that the sort variable is the issue, I’ve just had a hard time getting it to do what I want.
The goal is:

  • if a COMPONENT ITEM NUMBER has more than one associated SUBINVENTORY CODE, mark it in some way so it is segregated from other items
  • for all COMPONENT ITEM NUMBERs with exactly one associated SUBINVENTORY CODE, sort items first by LOCATOR then by COMPONENT ITEM NUMBER.
#5

Okay, so you need a better sorting variable.

Something like:
=IF(Count([SUBINVERTORY CODE];Distinct) IN ([Component Item Number])>1;“01”+[Component Item Number];“02”+[Locator])
Then sort by that object first and Component Item Number second - the syntax may not be exact on the above, but the general logic is that if there’s a count distinct of subinventory code greater than 1, prefix it with 01 and add Component Item Number, else prefix it with 02 and then add locator.

That should get you to the sort order that you want. Note that you can include the item in the table but also hide it.

#6

That was exactly what I needed! Thanks a lot, that was super helpful

#7

Glad to be of help. :slight_smile: