Hi,
I asked a question yesterday (which was answered - thank you!) about displaying specific 'version' of business data. My example yesterday was about customer information. When a person moves then we get two rows of data, one with the old address and one with the new address.
The solution I was given works fine for one set of data where I have a single set of values to display.
However I now have to display multiple rows of data, each of which link to a specific 'version' of the business data.
I have Order information and for each Order there may be multiple Items. So Order#1 includes 4 items with different quantities, Order#2 includes 3 items each with different quantities etc. And then an Order gets changed, which is where the different 'version' comes into play.
As an example, Order#1 originally includes Item = 100 with a quantity of 5. Then the customer changes that to a quantity of 10. We now have two versions of Order#1. Some sample raw data is shown below.
| order_id | order_ver | item_nbr | qty | order_comment |
| 1000 | 1 | 100 | 15 | need blue ones |
| 1000 | 1 | 110 | 23 | |
| 1000 | 1 | 120 | 2 | pre-packaged |
| 1000 | 2 | 100 | 15 | want red ones |
| 1000 | 2 | 110 | 4 | |
| 1000 | 2 | 120 | 2 | |
| 1000 | 2 | 130 | 45 | |
| 2000 | 1 | 100 | 4 | |
| 2000 | 1 | 140 | 9 | |
| 3000 | 1 | 100 | 2 | |
| 3000 | 1 | 110 | 23 | |
| 3000 | 2 | 100 | 3 | |
| 3000 | 2 | 110 | 22 | |
| 3000 | 3 | 100 | 14 | |
| 3000 | 3 | 120 | 2 |
I need my user to be able to select an Order_ID and two Order_ver's. There should be two separate lists (tables) displayed, one for one 'version' and the second for the other 'version'.
I don't think that I can use a 'Table Box' because I can't find a way to exclude certain rows from the display. Using this type of display I have to use real fields, I can't add an expression as a displayed column. So I thought to use a 'Straight Table' with a Calculated Dimension as my control to show/hide rows as required and then use the 'Suppress When Value Is Null' option.
I cannot get the 'calculated dimension' syntax to generate anything other than "Error in calculated dimension". When I edit the 'calculated dimension' the 'Edit Expression' dialog shows no red lines, errors etc, so I'm guessing that the basic syntax is correct.
I have also included a calculated expression using exactly the same syntax. That displays a value or Null exactly as I'd expect (and want) but it seems there is something different about using this logic in a Calculated Dimension. And I understand that Dimensions and Expressions are different, but the subtle difference here is eluding me.
I have attached a small qvw file which includes the above data, my straight table where the calculated expression displays correctly but the calculated dimension gives this error.
Note that the qvw file also includes the 'single value' selection and display which works fine.That can be largely ignored for this discussion.
As always, all help greatly appreciated.
Cheers,
Dave
