Cognos Impromptu - "linking" tables - revenue

[niteflite]

I'm a newbie at impromptu. I'm trying to run a very basic report. I've succeeded at getting the project to show up, along with the project start/end date, project ceiling $, project type. Now I need to show how much has been billed to each project but when I insert the field for revenue since inception of contract, it gives me multiple lines of data that show different values in the revenue field. There's also an organization field that can vary within the same project (i think it shows the group within the company the people billing to the project are from; different groups bill to the same project) but i'm not sure if that is part of the problem or not.

I did enter criteria for the year and period.

Thanks.

 

[GilMerc]

Are you linking the tables in a catalog?

Gilles.

 

[DoubleD]

You probably need to do a Total on the revenue field. As I'm guessing that in a single Year and Period, multiple groups can bill against a single Project.

I am what I am based on the decisions I have made.

DoubleD

 

[niteflite]

i didn't "link" anything per se. i just opened up a new table, selected the "projects" catalog, and used the wizard since all of the various tables and their fields are listed to the left.

the variables were taken from different tables, from different levels.

 

[griffindm]

Are you using a vendor-supplied catalog, or one constructed internally? What is the package you are reporting against?

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[niteflite]

i'm embarrassed to say that i don't know whether it is vendor-supplied or was constructed internally. impromptu is the interface i'm using to run reports off of data residing in costpoint.

this project catalog gives me the following first level folders/tables:

basic project information
project account groups
ceilings
employees and labor categories
estimate to complete
fees
pools
project budgets
project total budgets
project vendors
report tables
user defined tables
other project data

i've drawn data from "Basic Project Information" and Revenue Reporting," which falls within "Report Tables."

 

[griffindm]

Fortunately, Costpoint is my forte.

Your problem is probably in one of two areas, possibly both of them.

The fact tables you should be reporting against are PSR_HDR and PSR_FINAL_DATA (with a parent-child relationship), as this combination of tables contains both revenue and billing data. These tables may give multiple rows for a single project for a single year and period in two possible scenarios.

Your company may be using sub-periods, which means that you need to filter on a specific sub-period.

The more probable cause is that revenue in PSR_FINAL_DATA may be split among multiple performing organizations. Add organization to your report and see if you see multiple values for those projects returning more than one revenue org.

To eliminate the multiple rows, group on project, remove org from the report (if it's there) and change the revenue to an aggregate using the Sum (Total) function.

Hope this helps.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[niteflite]

yes, we do use subperiods, and yes, the organization code is there. however, i need the org code to query for my division only.

 

[griffindm]

Niteflite,

Just add an org detail filter to the report to limit it to only your org(s).

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[niteflite]

thanks. i think i'm making progress here.

another question, though - our projects can fall under 3 levels - 1st, 2nd, 3rd. there is a "level" field in the projects table. I would want the third levels to sum up to the 2nd level, and the 2nd levels to sum up to the 1st level revenue, but it looks like they are all being displayed independently.

how do i get around this?

thanks.

 

[griffindm]

That's true. Each level shows just it's own costs and revenue. You have to 'rollup' the child project tasks to get the true amounts at the parent level.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[GilMerc]

niteflite,

It's possible to sum level 2 & 3.
First, you must create a calculation field for each sum and define them as a «cumulative sum» (I have a french version, please correct my expression if it's wrong) for a level 2 or a level 3 depend a calculation field.
For exemple,
Calculation_level_2 = Cumulative_Sum(level_1) for level_2.
Calculation_level_3 = Cumulative_Sum(Calculation_level_2) for level_3.
Second, group a report on level 3 and level 2.
Third, show a footer of these two groups.
Fourth, place a calculation field in these footer.

You will show a sum of level 1 in a level 2 footer and a sum of level 2 sum in a level 3.

Gilles.