Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Accumulations in CrossTabs 2
Accumulations in CrossTabs 2 [message #691825] Sat, 02 July 2011 13:16 Go to next message
Marco  is currently offline Marco Friend
Messages: 216
Registered: August 2009
Senior Member
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 14:31 Go to previous messageGo to next message
Marco  is currently offline Marco Friend
Messages: 216
Registered: August 2009
Senior Member
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 17:03 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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 69 times)
Re: Accumulations in CrossTabs 2 [message #692133 is a reply to message #692132] Sun, 03 July 2011 17:04 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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 18:06 Go to previous messageGo to next message
Marco  is currently offline Marco Friend
Messages: 216
Registered: August 2009
Senior Member
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 17:18 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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
Re: Accumulations in CrossTabs 2 [message #694381 is a reply to message #693056] Fri, 08 July 2011 13:11 Go to previous messageGo to next message
Marco  is currently offline Marco Friend
Messages: 216
Registered: August 2009
Senior Member
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 22:22 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

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:
  


Current Time: Thu Dec 18 13:37:38 GMT 2014

Powered by FUDForum. Page generated in 0.02443 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software