I have users that want to built a report that shows all the products in the same table. Problem is certain fields will only be populated for certain products.
For example, a table with 15 fields, first 5 are constant across both products, 2nd 5 only for product A, 3rd 5 only for product B.
When you select product A from a drop down they want the table to only show the first 10 fields, when you select product B they want the table to only show the first and last 5. If no product is selected all fields should show.
Am I right in thinking this could be achieved with the union to combine results?
Assuming that you have the same total number of columns for each product (in your example product A and B both have 10) then yes, you could use a UNION. The first part of the UNION would include the 5 common fields and the additional 5 that pertain to product A. The second part would have the common ones and also those that belong to B. You could include an extra column into which you'd store a string, either 'productA' or 'productB'. Then could then filter on this extra column to show the product you're interested in.
But if the number of columns isn't the same for both products that won't work. You could pull all the columns into the analysis and then create a separate table view for each product. You could exclude out whatever columns that don't make sense and then use a view selector to move between the different tables.
I guess it depends upon how you want to display the data. How many different products are there?