Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Grouping and charting

Status
Not open for further replies.

xsw1971

Programmer
Jun 21, 2001
153
US
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!!
 
Does anyone have any thoughts on this? Is what I'm asking for even possible?
Thanks!
 
For charting, I think you could use a subreport for the revenue data, and then use the method outlined in thread767-1089708. However, I don't think you can get these values in the same crosstab this way, so I think you are better off using a left join FROM your period table to each of the other tables, and then using running totals for both the chart and for creating a manual crosstab, since you will have row inflation. Thread767-1089708 might still help with the charting.

-LB
 
Thanks for the reply lbass! I ended up with your second solution but it's giving me some wierd data. I'm going to tinker with this some before posting again, see if I can figure this one out.

Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top