Select specific version of business data

Anonymous — Thu, 05 Oct 2017 09:51:42 GMT

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

Re: Select specific version of business data

Anonymous — Sat, 07 Oct 2017 15:05:44 GMT

Hi all,

I've managed to get this sorted and thought that I would share my solution for anyone else who has the same requirement.

I had intended to attach a working qvw file but I can't see an 'attach' button/link when replying to a post. Sorry.

I have used two 'straight tables' for the display, one is for the lowest selected version and the other is for the highest selected version.

"Low Selected Version" display

Under 'Layout / Show' I select Conditional and use the following expression: Count(distinct( order_id ))= 1 and GetPossibleCount(order_ver) > 0

I have a Calculated Dimension (labelled as DIM_LowVerInd):

- the expression is: =If(order_ver = Min(TOTAL Aggr(Min({$}order_ver), order_id)),order_ver,null())

- for this calculated dimension I tick the 'Suppress when value is Null' box

- for this calculated dimension i have selected 'hide column' on the Presentation tab

"High Selected Version" display

Under 'Layout / Show' I select Conditional and use the following expression: Count(distinct( order_id ))= 1 and GetPossibleCount(order_ver) = 2

I have a Calculated Dimension (labelled as DIM_HighVerInd):

- the expression is: =If(order_ver = Max(TOTAL Aggr(Max({$}order_ver), order_id)),order_ver,null())