For some strange reason, my brain can not comprehend what needs to be done! I don't know why?
Here is the situation:
I have 2 levels of a [Project ID] (Level 2 and Level 3) So I combined them together with a CAST:
CAST ([Project Status Report].[Project Summary Report Header].[Project ID],varchar(9))
But I'm still getting 2 Rows per each [Project ID] Data
Level 2 [Project ID] represents the top level of the project with and are laying in one row of the following:
The following columns are made:
1) [Project Status Report].[Project Summary Report Header].[Project Value Total Amount]
2) [Project Status Report].[Project Summary Report Header].[Amount Billed]
and
Level 3 of the [Project ID] represents the bottom level of the project with the following on a second row of the [Project ID]:
The following Columns are made:
3) IF ([Project Status Report].[Project Summary Report Final Data].[Account ID] in ('50-20-01','50-20-03','50-20-05'))
THEN ([Project Status Report].[Project Summary Report Final Data].[Prior Year Incurred Amount]+[Project Status Report].[Project Summary Report Final Data].[YTD Incurred Amount])
ELSE ('0')
4) IF ([Project Status Report].[Project Summary Report Final Data].[Account ID] in ('52-10-30','53-10-01','53-10-03'))
THEN ([Project Status Report].[Project Summary Report Final Data].[Prior Year Incurred Amount]+[Project Status Report].[Project Summary Report Final Data].[YTD Incurred Amount])
ELSE ('0')
5) IF ([Project Status Report].[Project Summary Report Final Data].[Account ID] in ('50-10-00','50-10-01'))
THEN ([Project Status Report].[Project Summary Report Final Data].[Prior Year Incurred Amount]+[Project Status Report].[Project Summary Report Final Data].[YTD Incurred Amount])
ELSE ('0')
What am I missing to combine the 2 levels together? The Grouping isn't working, tried that! :(
https://drive.google.com/file/d/1aH0hOuhx6veuMkRyzo6m8u6ZZw80MiAq/
Thank you,
Alice
Never mind,
I ended up solving the equation with a little budge..
It ended up that I had only 1 query; I need to make 3 queries in order to allow what I needed to get done!
Thanks all for looking!