【Question】

1.       I have followed the instruction about FIRST function and it works. But from my side, the data table looks like this:

ID -- TagName   --  TagValue

 

1 --  StringTag  --  string1

 

1  --  StringTag --  string2

 

2  --  NumberTag -- 123

 

2  --  NumberTag --  45

 

FIRST or LAST only displays the first or last TagValue. How can I achieve this in data cube?

 

ID – StringTag  -- NumberTag

 

1 --  string1, string2  --

 

2 --  --  123, 45

 

2.       Same problem as the previous one and I would like to know is there any way to have an output like the above-mentioned?

Date -- Category – Icon

 

1  --  cat1  -- ic1

 

1  --  cat2  -- ic2

 

In the crosstab I need to have something like this:

Date -- Category -- Icon

 

1         -- cat1,cat2 -- ic1,ic2

 

【Answer】

1. The crosstab control doesn’t support grouping and concatenation over source data in such a format. We can transpose it to the following format and then present it with the crosstab control.

ID	TagName	values
1	StringTag	string1,string2
2	NumberTag	123,45

 

It’s convenient to code the transposition in SPL (Structured Process Language):

	A
1	$select ID,TagName,TagValue   from tb1
2	=A1.group(ID,TagName; ~.(TagValue).concat@c():values)

BIRT can access esProc via JDBC. About more examples, see How to Call an SPL Script in BIRT.

 

2.       Similar to the first question, you can transpose the source data to a two-dimensional table in SPL and then present it with table control:

	A
1	$select Date,Category,Icon   from tb2
2	=A1.group(Date;   ~.(Category).concat@c():Cats,~.(Icon).string():Icons)

“~” represents a group of records; “(Category)” means getting a set of field values; concat@c function concatenates members of a set into a string delimited by comma.