Need help aggregating SUM

[#1]

Hi-

I was hoping to find the best solution to  aggregate/display values based on user selections. I am working with Reporting Tree (account hierarchy) function.

1. When user picks top level nod (2222222), values on the pivot table do not aggregate but instead report displays lines that roll under selected parent node.

My results look like this:

------56                       - 250   

------77                       - 250   

Where i would it to look like this

---22222

- 500

2. At the moment if user selects base level account, i get accurate values/# of lines. I would like to keep this option as well.

Report strucutre:

Reporting Tree      Amount Sum(Amount)

11111                      $1000 

---22222                   - 500

------56                       - 250   

------77                       - 250                   

---33333                   - 500

------78                      - 250

------99                      - 250

Please let help.

thanks

[#2]

Hi,

How is the structure of your database?

You can show this result with your table has the structure:

DIM_1, DIM_2, DIM_3, VALUE

11111, 22222, 56, 250

11111, 22222, 77, 250

11111, 33333, 78, 250

11111, 33333, 99, 250

create a pivot table with dimensions DIM_1, DIM_2, DIM_3 and expression SUM(VALUE).

[#3]

I am using a reporting tree for my accounts with multiple levels of parent children combination. I am defining the strucuture as part of my script.

Sample

Concatenate 

LOAD Account.Lev01 as Account,

Account.Lev14

& IF(Account.Lev13<>Account.Lev14 , '/' & Account.Lev13,'')

& IF(Account.Lev12<>Account.Lev13 , '/' & Account.Lev12,'')

& IF(Account.Lev11<>Account.Lev12 , '/' & Account.Lev11,'')

& IF(Account.Lev10<>Account.Lev11 , '/' & Account.Lev10,'')

& IF(Account.Lev09<>Account.Lev10 , '/' & Account.Lev09,'')

& IF(Account.Lev08<>Account.Lev09 , '/' & Account.Lev08,'')

& IF(Account.Lev07<>Account.Lev08 , '/' & Account.Lev07,'')

& IF(Account.Lev06<>Account.Lev07 , '/' & Account.Lev06,'')

AS AccountTree

  Resident Account_Raw;

Concatenate 

LOAD Account.Lev01 as Account,

Account.Lev14

& IF(Account.Lev13<>Account.Lev14 , '/' & Account.Lev13,'')

& IF(Account.Lev12<>Account.Lev13 , '/' & Account.Lev12,'')

& IF(Account.Lev11<>Account.Lev12 , '/' & Account.Lev11,'')

& IF(Account.Lev10<>Account.Lev11 , '/' & Account.Lev10,'')

& IF(Account.Lev09<>Account.Lev10 , '/' & Account.Lev09,'')

& IF(Account.Lev08<>Account.Lev09 , '/' & Account.Lev08,'')

& IF(Account.Lev07<>Account.Lev08 , '/' & Account.Lev07,'')

& IF(Account.Lev06<>Account.Lev07 , '/' & Account.Lev06,'')

& IF(Account.Lev05<>Account.Lev06 , '/' & Account.Lev05,'')

ETC

[#4]

In your expression try this:

SUM ( IF ( Account LIKE '*' & AccountTree & '*', your_value )  )

[#5]

Hi Edurardo,

After I applied the above expression, values are no longer showing up on the report.

What else can I do?

Thank you for helping!

[#6]

hi

you can send your file qvw or create a test model so we can see how the words would be?

[#7]

I can't i dont know how to disconnect data from this file, otherwise i would!

[#8]

Dou you can export the data in qvd file?

I need to know how the data are

[#9]

I am sorry how do i do that?

[#10]

In your script for all tables that you create put this code

STORE * FROM YOUR_TABLE_NAME INTO YOUR_TABLE_NAME.qvd;

ex.:

TABLE_A:

LOAD ID, VALUE

FROM TEMPORARY_TABLE;

STORE * FROM TABLE_A INTO TABLE_A.qvd;