CR8.5 (and CE8) with an Oracle DB
I have a table of projects; each project may or may not have an estimated revenue amount/date and an actual revenue amount/date. These two dates are unrelated other than residing in the same table (i.e., they are almost never the same date). For example, project 1 may have an estimated rev date of June 05 but an actual rev date of Nov 05, project 2 may have an estimated rev date of May 05 and no actual rev date, project 3 may have an estimated rev date and an actual rev date of Mar 05.
What I would like to do is show cumulative estimated revenue over a 12-month period AND cumulative actual revenue over a 12-month period. Management wants to see this information in both a 2-series bar chart and in a cross-tab, however I am stuck trying to get these unrelated values to display without using subreports (I tried using subreports and when I try to chart the variables that are passed back to the master it won’t let me because it can’t chart runtime data).
I started by creating a periods table so that I could group by month (there are some months with no data and I wanted to show that empty value). I linked the date value from the periods table to my estimated revenue date field and made it an outer join so that all months will show when I group by month.
I created a running total that sums the estimated revenue amount field, evaluates each record and never resets. I can then graph this and it displays as a cumulative total from month to month – this works perfectly. Where my problem comes in is in trying to do the same thing with the actual revenue amount/date fields. If I use the same project table and add a link between the actual date field and the date value from the periods table, it only returns projects that have an estimated date and an actual date in the same month (about 3 of 50 records). I tried adding a second project table with an alias name and linking it to the date lookup table too but it’s returning strange numbers (I can’t tell where it’s getting its values from so I can’t explain what it’s returning).
So basically I want to create a bar chart across a year’s time span, with 2 series in each month: 1 bar showing estimated revenue for that month, the other bar showing actual revenue for that month (and also display these values in a cross-tab). I have been at this for 3 solid days and I am out of ideas.
Any thoughts? Thanks!!
I have a table of projects; each project may or may not have an estimated revenue amount/date and an actual revenue amount/date. These two dates are unrelated other than residing in the same table (i.e., they are almost never the same date). For example, project 1 may have an estimated rev date of June 05 but an actual rev date of Nov 05, project 2 may have an estimated rev date of May 05 and no actual rev date, project 3 may have an estimated rev date and an actual rev date of Mar 05.
What I would like to do is show cumulative estimated revenue over a 12-month period AND cumulative actual revenue over a 12-month period. Management wants to see this information in both a 2-series bar chart and in a cross-tab, however I am stuck trying to get these unrelated values to display without using subreports (I tried using subreports and when I try to chart the variables that are passed back to the master it won’t let me because it can’t chart runtime data).
I started by creating a periods table so that I could group by month (there are some months with no data and I wanted to show that empty value). I linked the date value from the periods table to my estimated revenue date field and made it an outer join so that all months will show when I group by month.
I created a running total that sums the estimated revenue amount field, evaluates each record and never resets. I can then graph this and it displays as a cumulative total from month to month – this works perfectly. Where my problem comes in is in trying to do the same thing with the actual revenue amount/date fields. If I use the same project table and add a link between the actual date field and the date value from the periods table, it only returns projects that have an estimated date and an actual date in the same month (about 3 of 50 records). I tried adding a second project table with an alias name and linking it to the date lookup table too but it’s returning strange numbers (I can’t tell where it’s getting its values from so I can’t explain what it’s returning).
So basically I want to create a bar chart across a year’s time span, with 2 series in each month: 1 bar showing estimated revenue for that month, the other bar showing actual revenue for that month (and also display these values in a cross-tab). I have been at this for 3 solid days and I am out of ideas.
Any thoughts? Thanks!!