List Box 1 have Products A, B, C and List Box 2 have Items 1, 2, 3 (all these items belongs to product B only). Now on the report, I want to see Product A, B, C and Items 1, 2 only.
The problem is, when I am selecting Item 1 and 2, its deselecting Product A and C and only showing Product B with Item 1 and 2. I have see all these selection on the report, which will be Product A, B and C with item 1 and 2.
Please let me know, if you know any way around. Thanks in advance.
And when you select Products A, B and C and items 1 and 2, you want to see this?
Product Item Quantity A 5 B 1 2 B 2 4 C 3
I can think of a few approaches.
You could go ahead and establish a 'none' item when there is no sub category. Then you could select items 1, 2 and 'none' to get the results. I think this would be my preference.
You could combine the Product and Item fields into a "Product Item" field, with values A, B1, B2, B3, C and D for the example above. You could then select A, B1, B2 and C to get the results you want.
You could make a "Select Item" field disconnected from the main data. Then you could use a condition to get the desired combination: sum(if(len(Item)<1 or Item="Select Item",Quantity)).
You could probably do the "Select Item" with set analysis too, which would execute faster.
You say that all of your items are for product B. If you select any items, you can logically only see product B. There are no items for products A and C, so how can you expect to see items that don't exist on your report? Am I missing something?
I am sorry for the confusion. I took a hypothetical example. You can suppose there is no sub category for product A and C, however product B have 3 sub items in it. Product A and C also have sale and product B have sale at the product level as well we can classify them at Items level.
And when you select Products A, B and C and items 1 and 2, you want to see this?
Product Item Quantity A 5 B 1 2 B 2 4 C 3
I can think of a few approaches.
You could go ahead and establish a 'none' item when there is no sub category. Then you could select items 1, 2 and 'none' to get the results. I think this would be my preference.
You could combine the Product and Item fields into a "Product Item" field, with values A, B1, B2, B3, C and D for the example above. You could then select A, B1, B2 and C to get the results you want.
You could make a "Select Item" field disconnected from the main data. Then you could use a condition to get the desired combination: sum(if(len(Item)<1 or Item="Select Item",Quantity)).
You could probably do the "Select Item" with set analysis too, which would execute faster.
I was able to make this type of selection by setting up a flag. In both tables, I set up a field (Sale) with a 1 or 0 depending on whether it should be selected or not. I named them the same, but I'm not sure if they shouldn't be two different names.
By selecting 1 in the Sale column, I get A, C and the 2 Items in B.
Here's the Inline I used:
LOAD * INLINE [ Product, Sale A, 1 B, 0 C, 1 ]; LOAD * INLINE [ Product, SubItem, Sale B, 1, 1 B, 2, 0 B, 3, 1 ];
EDIT: I posted before seeing John's reply. One of his suggestions should work for you.
