| Creating a Crosstab within a Crosstab [message #1781750] |
Tue, 13 February 2018 01:12 |
 d Dd
Messages: 1
Registered: February 2018 |
Junior Member |
|
|
Hi all, new user here. I've been assigned on a project to get some reports built and have been working on this report for days!
The report is supposed to be a sum of total amount billed to all accounts by month, with a subset of payments made to those accounts by month.
For example:
-------------------------------------------------------------------------------------------------
MONTH BILLED..........TOTAL..............MONTH CREDITED........TOTAL
11/2017........................5000........................11/2017...................1000
.......................................................................12/2017...................1000
........................................................................1/2018....................1000
12/2017........................2000.........................12/2017..................1500
.......................................................................1/2018......................500
------------------------------------------------------------------------------------------------
The way my very limited experience tells me to do this is to have a crosstab with month billed and billed total, and then another crosstab within that one showing the month credited and credit totals.
The problem I am having is limiting the inner crosstab based on the outer crosstab. For example, on the top row of my example, the 1000 for 'month credited' is a sum of ONLY credits that were made in 11/2017 on accounts BILLED in 11/2017.
I've tried using multiple data sets, joint data sets, multiple data cubes, combined data cubes, etc. Admittedly, I am really inexperienced with BIRT and am kind of learning all this as I go, but can't figure this issue out.
My latest attempt is using a query that returns a bill_id, bill_month, bill_amount, credit_month, credit_amount, and cred_entrys_parent_bill_month. If I could crosstab the bill_month as the group, with the bill_amount as the sum, then throw another crosstab inside there with the credit month as the group, with the credit_amount as the sum, all I would need then is to limit the inner crosstab on the outer crosstab where the inner 'cred_entrys_parent_bill_month', with the outer's, right?
As I keep trying new approaches, I find myself inching toward doing the entire layout in the SQL and just dumping that in a BIRT table. I write fairly advanced queries for a living and know I could do that and get something going, but I know that isn't anywhere near taking advantage of what BIRT does best...
Any ideas? Thanks in advance
|
|
|
Powered by
FUDForum. Page generated in 0.03352 seconds
] 
Search
Help
Register
Login
Home
