6 Replies Latest reply: Oct 18, 2012 11:47 AM by Juan Pedro Hidalgo RSS

    Double Aggr() in expression

    Juan Pedro Hidalgo

      Hey everyone i have the need to create a chart that always show the total amount paid for a selected project/projects. Im having some troubles using 2 times the aggr() function since is not returning me what i expect

       

      Here the expression im working on.

       

      AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), PROJECT)

       

      This does what i want keeps the chart always with the total amount paid for the project ignoring low-level selections. Problem is that i need to insert a second AGGR() to avoid wrong results caused by rateal payments and similar stuff

       

      AGGR(sum(AGGR({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT, INVOICE_PAYMENT_ID)), PROJECT)

       

      This one instead is giving me the right amounts but when i do a low level selection, like Invoice_number, changes the chart and shows only the payment for the selected invoice.

       

       

      Cant figure out how to solve this issue any help piece of advice will be apreciated

       

      Regards,

      Juan Pedro

        • Re: Double Aggr() in expression
          Stefan Wühl

          Try

           

          AGGR(

          sum({1}  AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), INVOICE_PAYMENT_ID))

          , PROJECT)

            • Re: Double Aggr() in expression
              Juan Pedro Hidalgo

              Hey there ty for your anserw,

               

              Your expression is half working keeps the chart invariated but the aggregation is not correct at some point since my results are falsed.

               

              Will work on it a bit maybe i can fix it

               

              regards

              Juan Pedro

                • Re: Double Aggr() in expression
                  Stefan Wühl

                  Try to duplicate the inner set expression:

                   

                  AGGR(

                  sum({1<PROJECT_ID= P ()>}  AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), INVOICE_PAYMENT_ID))

                  , PROJECT)

                    • Re: Double Aggr() in expression
                      Juan Pedro Hidalgo

                      Was my first idea aswell but nothing is changing. im really out of idea's on this one the single aggr() works more than fine but when combined something thats not suppose to happen happens.

                        • Re: Double Aggr() in expression
                          Stefan Wühl

                          Maybe you need to use the NODISTINCT qualifier with the aggr() function (one or the other, or both), and depending in which context you are using the aggr() function, it might require an additional aggregation around the complete current expression.

                           

                          Like

                          sum(AGGR(

                          sum({1<PROJECT_ID= P ()>}  AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), INVOICE_PAYMENT_ID))

                          , PROJECT))

                           

                          I'm also not sure if you really need to aggr() functions, one embedded into the other, but it's hard to say without knowing more about your data model and expression context.

                           

                          Could you maybe upload a small sample that demonstrates your issue?

                            • Re: Double Aggr() in expression
                              Juan Pedro Hidalgo

                              Hey, im not at work anymore so cant send you a sample right now.

                               

                              I will try to describe the situation a bit better and tomorrow i will post a sample.

                               

                              - In my report i have summary page

                              - The summary page contains.

                              - Invoices chart

                              - Payments chart

                              - total project cost (The Chart that is giving me problems.)

                               

                              Invoices and Payments are returning right results and this is a fact.

                               

                              Now what i want is:

                              - When a user selects a project the chart shows the total cost for the project

                              - A low level selection (like payment_number) wont affect the chart. This means i only want Project to be considered in this specific chart leaving apart all the others selections.

                              - Since i have different payments i have to use the aggr() function to make the results right.

                               

                              I can say the expression is not working fine at the moment because the amount in payments and total project cost should be identical when no low level selection is made.

                               

                              Hope it Helps

                              Regards,

                              Juan Pedro