actuate basic display variables in a matrix

[baxter364]

Hi there.

Been using actuate for a while but only just getting to the point where i need to understand vb-based variable manipulation. My background is Oracle forms and databases so hoping for a helping hand if proferred ...

I am dealing with product codes that are a full description of the items concerned e.g. awidget-black-small, awidget-black-medium, awidget-black-large. These are all returned as individual rows with a quantity per row. I want to be able to display the output in a matrix layout e.g.


awidget
black
small medium large
1 2 1

and have stripped away the strings etc to get each element but struggled so far with crosstabs (which I'm finding don't like to be over-ridden), multiple case statements and positional placement of variables. I end up with a matrix of sorts but with the original number of rows rather than just the one consolidated row I'm after.

If anyone has done something similar I would be grovellingly grateful for some hints / pointers.

thks, rgds

 

[wesw]

I'd try it with SQL using the case statement.
-W

 

[baxter364]

Thanks for the input. However.

Using a CASE statement and retrieving data using sql - if there are 5 source rows (awidget-black-xsmall through to awidget-black-xlarge) then I end up with :

awidget
black
xsmall small medium large xlarge
1 0 0 0 0
0 1 0 0 0
0 0 1 0 0
0 0 0 1 0
0 0 0 0 1



what I'm after is a means of forcing :

awidget
black
xsmall small medium large xlarge
1 1 1 1 1

thks, rgds

 

[wesw]

I assume you have a group section by color,
Then have each column use the following valueExp

count() Where ( [size] = "xsmall" )
next column
count() Where ( [size] = "small" )
etc.

xsmall small medium etc.

-Wes