Custom aggregation for cross tab?

newbie321
edited February 11, 2022 in Analytics #1

What is the best way to introduce a custom aggregation into the crosstab?

Can one use extension points and write a plugin for the crosstab's custom aggregation?

Best Answers

  • jfranken
    #2 Answer ✓

    I could not get this to work using an Aggregation element in a crosstab. However, I tried using a Data element and that did work as shown in the attached example.

    Warning No formatter is installed for the format ipb
  • jfranken
    #3 Answer ✓

    Try temporarily placing this code in the Initialize event for the report:

    java.lang.System.out.println("\r\nInitialize\r\n");

    It will print to the command window either once or twice depending upon whether the run and render happen together or separately.

    Warning No formatter is installed for the format ipb

Answers

  • Can you help answer this question?

    We've noticed this question is over 30 days old and hasn't received a response. We're turning to you, the community, to help answer it.

    This generic response is intended to prompt discussion in this post. The question remains open to your answers, suggestions, and best practices.

    If you posted this question and were able to resolve the issue, please share your solution here with others. If you still need additional help, though, please let us know. Your question and its resolution are important to us, and we want to help.

    David Sciuto

  • What specifically do you mean by custom aggregation? Could you provide an sample case on what you're grouping on, what you're aggregating, and how you're wanting to aggregate?

    Warning No formatter is installed for the format ipb
  • the crosstab usually aggregates only SUM, COUNT, FIRST, etc for its cell. What if I want to aggregate the cells even further in the derived measure column ?
    for example, consider the following cross-tab:
    WEIGHT OF CARS vs MODELS vs MAKES
    Sentra Altima Maxima
    nissan 11lb 10 lb 12 lb

    I want to add at the very right a derived measure, saying 'square root of the total weight ie sqrt(11 + 10 + 12) and have my revised cross tab look like this:
    Sentra Altima Maxima sqrt
    nissan 11lb 10 lb 12 lb 5.74

    How can such custom aggregation be added to the cross-tab?

  • jfranken
    #7 Answer ✓

    I could not get this to work using an Aggregation element in a crosstab. However, I tried using a Data element and that did work as shown in the attached example.

    Warning No formatter is installed for the format ipb
  • @Chad Montgomery said:
    What specifically do you mean by custom aggregation? Could you provide an sample case on what you're grouping on, what you're aggregating, and how you're wanting to aggregate?

    A good example would be a calculation of the year-to-date returns for the return stream.
    We know that that the annualized returns are given by:
    AnnRet = (PRODUCT_i (1+r_i))^(12/N)-1
    where N= number of total returns.

    so if I have return stream of
    J F M A M J J A S O N D
    1% 1% 1% 1% 1% 1% 1% 1% 1% 1% 1% 1%

    how can I get crosstab to calc an annualized return and produce 12.68% ?

    Thanks!!

  • For an unknown reason, the messages are not showing in chronological order in my browser. It looks like the last post is the one talking about annualized returns. Did you try the example I posted on May 14? It allows you to create a custom expression using the values in the crosstab to generate the aggregation. If it doesn't work for this scenario, could you please explain why?

    Warning No formatter is installed for the format ipb
  • newbie321
    edited August 20, 2018 #10

    @jfranken said:
    For an unknown reason, the messages are not showing in chronological order in my browser. It looks like the last post is the one talking about annualized returns. Did you try the example I posted on May 14? It allows you to create a custom expression using the values in the crosstab to generate the aggregation. If it doesn't work for this scenario, could you please explain why?

    Your suggestion on May14 is very good start, but, if I understand correctly, it has a limiting range of mathematical operations.
    There are essentially two operators that you have: 1) square root and 2) summation

    While the square root does not bother me too much, the summation operator does.... It looks like data element defaults to Summation and I do not see a way to change that aggregation to anything else. This means that only a limited range of aggregations is possible.

    I think the more pliable way of tackling this issue is to perform the following:
    1) initialize the variable in the 'initialize' event
    2) perform calcs within onCreate() event handler
    3) use .setDisplayName() to override the visible value of the field.

    Of course, if there is a way not to invoke script and calc annualized return in other way that would be simply stellar.
    Example of annualized return formula for 3 monhts: (1+R1)(1+R2)(1+R3) = (1+r_ann)^(3/12)

    Thanks!

  • @jfranken

    just to follow up on my last comment. So if I were to proceed with my own suggestion (scripting option), it would render correctly under RCP's PDF emitter, but would not render correctly under the default emitter.
    My hunch is because the default rendering does RunAndRender all at once, while PDF emitter does Run first and then Render, correct?

  • Try temporarily placing this code in the Initialize event for the report:

    java.lang.System.out.println("\r\nInitialize\r\n");

    It will print to the command window either once or twice depending upon whether the run and render happen together or separately.

    Warning No formatter is installed for the format ipb