| "constitutive" sum at crosstab [message #1255793] |
Mon, 24 February 2014 20:42 |
 pkwien 93
Messages: 1
Registered: February 2014 |
Junior Member |
|
|
Hello,
currently I stuck at generating a report which displays the account balance group by account and month.
The Report should display the account per row and the Month per Col.
I have a table which contains following information:
- ID
- account
- postingDate
- amount
I created a data set with an computed column for the Month.
Then I started to create a cross tab with following configuration:
- Month-Group (containing the computed column)
- Account-Group (containing the account)
- SummaryField (amount "SUM")
With that setup I get the sum of amount of account postings per Month and account, but not the account balance.
Using SQL I can calculate the account balance on day X using a filter in where:
SELECT account,SUM(amount) FROM accPostings2014 WHERE postingDate < '2014-03-01';
I found out that I can change the Aggregation for the SummaryField to only one group, but I don't have access to the postingDate field to set the filter.
An other solution would be to calculate the balance using the previous value (SUM of January + SUM of February, ...). But is there a way to access the previous col/row?
Many thanks in advance,
pkwien 93
Hint: The table contains only accountPostings per year and for each account there is a "balance per 01.01.2014" entry, so there are no turn of years possible.
|
|
|
Powered by
FUDForum. Page generated in 0.03602 seconds
] 
Search
Help
Register
Login
Home
