If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Combining Two Levels of a Project into One Row of Data

Started by msalice1, 08 Dec 2020 09:56:36 AM

Previous topic - Next topic

msalice1

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


msalice1

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!