Eclipse Community Forums: BIRT » Accumulations in CrossTabs 2

Home » Archived » BIRT » Accumulations in CrossTabs 2

Accumulations in CrossTabs 2 [message #691825]

Sat, 02 July 2011 09:16

Eclipse User

Hi,

I am totally lost. I simply do not understand how to define the necessary aggregations.

Now from scratch with sample data:

Data:

Dim1/Year	Dim1/Quarter	Dim1/Month	Dim1/Day Of Month	Dim2	Dim3/Level1	Dim3/Level2	Dim4	M D1 D2 last	M D1 D2 agg	M D2 D3 agg
2000		1		1		1			D2-1	D3L1-1		D3L2-1		D4-1	1		1		1
2000		1		1		2			D2-1	D3L1-1		D3L2-1		D4-1	2		2		1
2000		1		1		3			D2-1	D3L1-1		D3L2-1		D4-1	4		3		1
2000		1		1		1			D2-2	D3L1-1		D3L2-1		D4-1	8		4		2
2000		1		1		2			D2-2	D3L1-1		D3L2-1		D4-1	16		5		2
2000		1		1		3			D2-2	D3L1-1		D3L2-1		D4-1	32		6		2
2000		1		1		1			D2-1	D3L1-1		D3L2-2		D4-1	1		1		3
2000		1		1		2			D2-1	D3L1-1		D3L2-2		D4-1	2		2		3
2000		1		1		3			D2-1	D3L1-1		D3L2-2		D4-1	4		3		3
2000		1		1		1			D2-2	D3L1-1		D3L2-2		D4-1	8		4		4
2000		1		1		2			D2-2	D3L1-1		D3L2-2		D4-1	16		5		4
2000		1		1		3			D2-2	D3L1-1		D3L2-2		D4-1	32		6		4
2000		1		1		1			D2-1	D3L1-1		D3L2-1		D4-2	1		1		1
2000		1		1		2			D2-1	D3L1-1		D3L2-1		D4-2	2		2		1
2000		1		1		3			D2-1	D3L1-1		D3L2-1		D4-2	4		3		1
2000		1		1		1			D2-2	D3L1-1		D3L2-1		D4-2	8		4		2
2000		1		1		2			D2-2	D3L1-1		D3L2-1		D4-2	16		5		2
2000		1		1		3			D2-2	D3L1-1		D3L2-1		D4-2	32		6		2
2000		1		1		1			D2-1	D3L1-1		D3L2-2		D4-2	1		1		3
2000		1		1		2			D2-1	D3L1-1		D3L2-2		D4-2	2		2		3
2000		1		1		3			D2-1	D3L1-1		D3L2-2		D4-2	4		3		3
2000		1		1		1			D2-2	D3L1-1		D3L2-2		D4-2	8		4		4
2000		1		1		2			D2-2	D3L1-1		D3L2-2		D4-2	16		5		4
2000		1		1		3			D2-2	D3L1-1		D3L2-2		D4-2	32		6		4

Expected crosstab:

					2000	2000
					Q1	Q1
					Jan	Feb
D2-1	D3L1-1	D3L2-1	M D1 D2		4	
D2-1	D3L1-1	D3L2-1	M D1 D2 agg	6	
D2-1	D3L1-1	D3L2-1	M D2 D3 agg	3	
D2-1	D3L1-1	D3L2-2	M D1 D2		4	
D2-1	D3L1-1	D3L2-2	M D1 D2 agg	6	
D2-1	D3L1-1	D3L2-2	M D2 D3 agg	9	
D2-2	D3L1-1	D3L2-1	M D1 D2		32	
D2-2	D3L1-1	D3L2-1	M D1 D2 agg	15	
D2-2	D3L1-1	D3L2-1	M D2 D3 agg	6	
D2-2	D3L1-1	D3L2-2	M D1 D2		32	
D2-2	D3L1-1	D3L2-2	M D1 D2 agg	15	
D2-2	D3L1-1	D3L2-2	M D2 D3 agg	12

All measures are independent from Dim4. So for all rows in the set it should just take any row (e.g. first), and all aggregations shall just add 1 of these rows.

Measure "M1 M2 last" is just depending on dimension M1 and M2. The last value in the period of Dim1 is to be displayed.

"M D1 D2 agg" is just depending on dimension M1 and M2. It should be aggregated over the period of Dim1, selecting on Dim2, and ignoring all other dimensions (meaning just to take 1 row for each D1-D2 combination.

"M D2 D3 agg" is the same for all Dim1. It should be aggregate within Dim2 over Dim3/Level1 if Dim3/Level2 is hidden.

I generate the report design with DEAPI. The user shall then be able to refine the layout. E.g. he should be able to change the aggregation over levels by just using the "show/hide levels" menu.

I would like to keep the cube as generic as possible to allow all possible combinations of dependence between measures and dimensions.

So what aggregations do I have to set for the AggregationCells of the measures, what DataBindings are needed?

Can I or must I define multiple cubes for the different dependencies of measures from dimensions?

Thanks,
Marco

Re: Accumulations in CrossTabs 2 [message #691839 is a reply to message #691825]

Sat, 02 July 2011 10:31

Eclipse User

One fundamental problem seems to be that I cannot aggregate on cube dimensions if they are not also dimensions of the crosstab, right?

Re: Accumulations in CrossTabs 2 [message #692132 is a reply to message #691839]

Sun, 03 July 2011 13:03

Eclipse User

Marco,

I am pretty certain this is correct. I converted your example to a
rptdesign and a csv file for a starting point. Can you have a look at
it to see what needs to change?

Jason

On 7/2/2011 10:31 AM, Marco wrote:
> One fundamental problem seems to be that I cannot aggregate on cube
> dimensions if they are not also dimensions of the crosstab, right?
>

Attachment: example.zip
(Size: 3.69KB, Downloaded 164 times)

Re: Accumulations in CrossTabs 2 [message #692133 is a reply to message #692132]

Sun, 03 July 2011 13:04

Eclipse User

BTW you will need to change the location of the csv file in the data source.

Jason

On 7/3/2011 1:03 PM, Jason Weathersby wrote:
> Marco,
>
> I am pretty certain this is correct. I converted your example to a
> rptdesign and a csv file for a starting point. Can you have a look at it
> to see what needs to change?
>
> Jason
>
> On 7/2/2011 10:31 AM, Marco wrote:
>> One fundamental problem seems to be that I cannot aggregate on cube
>> dimensions if they are not also dimensions of the crosstab, right?
>>
>

Re: Accumulations in CrossTabs 2 [message #692149 is a reply to message #692133]

Sun, 03 July 2011 14:06

Eclipse User

Jason,

Thanks a lot for your example. Now I can better explain what I want.

<D2-1 D3L1-1 D3L2-1 M D1 D2 last> should be 4 because there are 12 rows for this criteria:
2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-1 1 1 1
2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-1 2 2 1
2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-1 4 3 1
2000 1 1 1 D2-1 D3L1-1 D3L2-2 D4-1 1 1 3
2000 1 1 2 D2-1 D3L1-1 D3L2-2 D4-1 2 2 3
2000 1 1 3 D2-1 D3L1-1 D3L2-2 D4-1 4 3 3
2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-2 1 1 1
2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-2 2 2 1
2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-2 4 3 1
2000 1 1 1 D2-1 D3L1-1 D3L2-2 D4-2 1 1 3
2000 1 1 2 D2-1 D3L1-1 D3L2-2 D4-2 2 2 3
2000 1 1 3 D2-1 D3L1-1 D3L2-2 D4-2 4 3 3

And it should not sum it up but take the last for Dim1 which is 4 no matter what Dim3 and Dim4 are. Dim 3 and Dim4 are irrelevant, so just take any one of this 4. So it should ignore all duplicates which just differ outside of the criteria (Dim1 and Dim2), and take last instead of sum. It seems that your implementation ignores Dim3 or Dim4 instead of both.

<D2-1 D3L1-1 D3L2-1 M D1 D2 agg> should be 6 because there are the same 12 rows, and it should just take the first 3 rows as the other 9 rows are duplicates within the criteria. Here it should be summed up over Dim1 which makes 6. So again it should ignore duplicates which just differ outside of the criteria.

<D2-1 D3L1-1 D3L2-1 M D2 D3 agg> should be 3 because there are 6 rows matching the criteria:
2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-1 1 1 1
2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-1 2 2 1
2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-1 4 3 1
2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-2 1 1 1
2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-2 2 2 1
2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-2 4 3 1

Again the second 3 are just duplicates to the first 3 as they just differ in Dim4 which is to be ignored. So the first three are to be taken and summed up over Dim1 which makes 3.

My requirements might look strange. But this is really what I need.

It would be great if you help me further.

Thanks,
Marco

Re: Accumulations in CrossTabs 2 [message #693056 is a reply to message #692149]

Tue, 05 July 2011 13:18

Eclipse User

Marco,

Look at the revised example. Specifically look at the filters on the
expressions. Is this what you want?

Jason

On 7/3/2011 2:06 PM, Marco wrote:
> Jason,
>
> Thanks a lot for your example. Now I can better explain what I want.
>
> <D2-1 D3L1-1 D3L2-1 M D1 D2 last> should be 4 because there are 12 rows
> for this criteria:
> 2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-1 1 1 1
> 2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-1 2 2 1
> 2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-1 4 3 1
> 2000 1 1 1 D2-1 D3L1-1 D3L2-2 D4-1 1 1 3
> 2000 1 1 2 D2-1 D3L1-1 D3L2-2 D4-1 2 2 3
> 2000 1 1 3 D2-1 D3L1-1 D3L2-2 D4-1 4 3 3
> 2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-2 1 1 1
> 2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-2 2 2 1
> 2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-2 4 3 1
> 2000 1 1 1 D2-1 D3L1-1 D3L2-2 D4-2 1 1 3
> 2000 1 1 2 D2-1 D3L1-1 D3L2-2 D4-2 2 2 3
> 2000 1 1 3 D2-1 D3L1-1 D3L2-2 D4-2 4 3 3
>
> And it should not sum it up but take the last for Dim1 which is 4 no
> matter what Dim3 and Dim4 are. Dim 3 and Dim4 are irrelevant, so just
> take any one of this 4. So it should ignore all duplicates which just
> differ outside of the criteria (Dim1 and Dim2), and take last instead of
> sum. It seems that your implementation ignores Dim3 or Dim4 instead of
> both.
>
> <D2-1 D3L1-1 D3L2-1 M D1 D2 agg> should be 6 because there are the same
> 12 rows, and it should just take the first 3 rows as the other 9 rows
> are duplicates within the criteria. Here it should be summed up over
> Dim1 which makes 6. So again it should ignore duplicates which just
> differ outside of the criteria.
>
> <D2-1 D3L1-1 D3L2-1 M D2 D3 agg> should be 3 because there are 6 rows
> matching the criteria:
> 2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-1 1 1 1
> 2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-1 2 2 1
> 2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-1 4 3 1
> 2000 1 1 1 D2-1 D3L1-1 D3L2-1 D4-2 1 1 1
> 2000 1 1 2 D2-1 D3L1-1 D3L2-1 D4-2 2 2 1
> 2000 1 1 3 D2-1 D3L1-1 D3L2-1 D4-2 4 3 1
>
> Again the second 3 are just duplicates to the first 3 as they just
> differ in Dim4 which is to be ignored. So the first three are to be
> taken and summed up over Dim1 which makes 3.
>
> My requirements might look strange. But this is really what I need.
>
> It would be great if you help me further.
>
> Thanks,
> Marco

Attachment: dimtest.rptdesign
(Size: 44.90KB, Downloaded 168 times)

Re: Accumulations in CrossTabs 2 [message #694381 is a reply to message #693056]

Fri, 08 July 2011 09:11

Eclipse User

Jason,

Your approach to filter on specific values does not work for a few reasons. The main one is that I do not know the specific values when creating the design, and they may change.

Can I setup filters in a generic way to select just one row per distinct value of a dimension?

Thanks,
Marco

Re: Accumulations in CrossTabs 2 [message #694587 is a reply to message #694381]

Fri, 08 July 2011 18:22

Eclipse User

Marco,

You could change the expression based on parameter etc. It would depend
on where you want that value to come from.

Jason

On 7/8/2011 9:11 AM, Marco wrote:
> Jason,
>
> Your approach to filter on specific values does not work for a few
> reasons. The main one is that I do not know the specific values when
> creating the design, and they may change.
>
> Can I setup filters in a generic way to select just one row per distinct
> value of a dimension?
>
> Thanks,
> Marco

Previous Topic:	BIRT Engine Runtime Error
Next Topic:	JNDI setting ignored

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 22 20:49:14 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter