Jump to content

BIRT Developer Center Download


Photo
- - - - -

Custom aggregation column in crosstab with last data

crosstab datacube aggregation

  • Please log in to reply
7 replies to this topic

#1 lemonch

lemonch

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 28 January 2015 - 04:36 AM

Hi,

 

I have a crosstab, and want a custom total column with last values. I have createad the grand total column and there i put the data i want. I have createad an aggregation with last as the function to aggregate. The problem is that it works great when there is data, but when there is a null value, i dont want the prior last data. I would rather put a 0. How can  i simulate that if the last value is null, put a 0 in the custom grand total column. 

 

Like this is what shows

 

              Jan      Feb      Mar     Apr        Last

Rice       null      null       54       null         54

Oil          44       null        14       66          66

Salt          5       null        null     null           5

 

But i want THIS output and dont know how to get it

 

              Jan      Feb      Mar     Apr        Last

Rice       null      null       54       null           0

Oil          44       null        14       66          66

Salt          5       null        null     null           0



#2 mwilliams

mwilliams

    BIRT Guru

  • Members
  • 14821 posts

Posted 28 January 2015 - 08:04 AM

Without a value, it'll always pull the last cell value with an actual value in it. Outside of making sure each cell has a value by adding 0 values to your data, you could  use persistent global variables to store the latest value of each row and just use the grand total column to report this PGV value. There may be other ways, but this is the first that comes to mind. What is your BIRT version, I can make an example for you, if needed.


Regards,

Michael Williams

eSignLive Evangelism & Community Manager | eSignLive by VASCO
 
Find me on:
Twitter
Facebook
Blog

LinkedIn

eSignLive Developer Community
 
Email me:
Google: mwilliams.silanis@gmail.com


#3 lemonch

lemonch

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 28 January 2015 - 08:50 AM

Yes, if you could provide an example would be great. I have the 3.7.1 version



#4 mwilliams

mwilliams

    BIRT Guru

  • Members
  • 14821 posts

Posted 28 January 2015 - 11:16 AM

Take a look at this sample. There is script in the onCreate of the measure element and then script to grab the appropriate value from the PGV in the dynamic textbox in the grand total column. Hope this helps.

Attached Files


Regards,

Michael Williams

eSignLive Evangelism & Community Manager | eSignLive by VASCO
 
Find me on:
Twitter
Facebook
Blog

LinkedIn

eSignLive Developer Community
 
Email me:
Google: mwilliams.silanis@gmail.com


#5 lemonch

lemonch

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 29 January 2015 - 04:08 AM

Worked like a charm! Thank you very very much!



#6 mwilliams

mwilliams

    BIRT Guru

  • Members
  • 14821 posts

Posted 29 January 2015 - 07:46 AM

Great! Let us know whenever you have questions. :)


Regards,

Michael Williams

eSignLive Evangelism & Community Manager | eSignLive by VASCO
 
Find me on:
Twitter
Facebook
Blog

LinkedIn

eSignLive Developer Community
 
Email me:
Google: mwilliams.silanis@gmail.com


#7 blueboy410

blueboy410

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 16 February 2016 - 06:58 PM

Hi mwilliams,

 

How do you insert the cell in the Column Area after the Level - date? Same with the cell in the Aggregation.

Somehow I cannot do it.. Is it my BIRT version?



#8 deepak.jyothiprakash

deepak.jyothiprakash

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 17 June 2017 - 11:46 AM

Hi Michael ,

 

is there any way to do custom aggregation in the cross tab, 

 

I have three Columns like Col A = -100 , Col B = 100, Col C = 200, but my total should not include Col A(no matter if its positive or negative).

 

Please do help me.

 

Thanks in Advance.







Also tagged with one or more of these keywords: crosstab, datacube, aggregation