Tough grouping/formula problem

[bogjacket]

Please bear with me, as I'm no CR expert.

I'm running CR XI and SQL 2000.

I have a xls workbook that we loosely call a stairstep report, which tracks (manual data-entry) the performance of portfolios as we add them to our system. The report shows the performance by month. So, the date each portfolio is first entered is month 1 and that number increments each month. So, at any given time, each portfolio (record) may be on a different month(our first records are now on month 29, and I just added another, so it's on month 1.)

What I need is to be able to group by portfolio, by month. The higher level group should be the portfolio name, and within that I need to break it down by each month (1 through whatever) that a particular record has been in our system. I have the date each portfolio was created in a sql table.

Again, one portfolio may have 24 months worth of data (24 subgroupings), while another may only have 1 or 2. I have no problem with the simple grouping by each portfolio, but I don't know where to begin to create groups within that group for each month.

Does this make any sense, or should I try to explain more?

tia, Brad

 

[MrBillSC]

Are you saying that if a portfolio as been in the system for 24 month it should appear in 24 groups (groups 1, 2,...24)?

MrBill

 

[CarolCook]

Any time you insert a date-driven group in Crystal, you have the option of controlling the group so that it prints by week, by month, etc. So each portfolio will start with the month it started and go month by month.

Simply create a group that breaks on the date you brought the portfolio in and see the options that the system offers you.

Hope that helps.

 

[bogjacket]

No, sorry. It's difficult to explain what I don't really understand.

I'd like to have one group per page, and that group should be the portfolio name. Within each page I'd like to repeat a set of calculations for each month that the portfolio has existed in the db. So, a group for a portfolio that has been around for 24 months should contain 24 sets of calculations, numbered 1 through 24. A brand new portfolio group would only have one month's worth of calcualtions.


Portfolio A
Month 1: SUM= $999 ROI% = 5.4
Month 2: SUM= $1500 ROI% = 5.9
...and so on for each month.
Porfolio B
Month 1: SUM = $2323 ROI% = 6.8
...

The month represents the number of months since the record's creation.

I also need to be able to graph those calculations on a line graph, for each portfolio.

Does that make sense?

 

[lbass]

The problem is that you are not identifying your fields or what they contain. I'm wondering whether you have separate fields like {table.month1}, {table.month2}, etc., each containing amounts or something. D

o you have an actual date field? If so, does it record each update so that you have separate records for dates in one month versus another, or does the date field only contain the creation date?

-LB

 

[CarolCook]

OK - you can have a group within a group. The 'outer' or topmost group is the Portfolio (New Page After), the next group is the month. You would sum the investment amount(?) and figure the ROI in the group footer for each month. You would have a month group footer for each month the portfolio has been around.

 

[bogjacket]

OK, I think I see what I've not been telling you.

Each record in the source table represents a payment made to a particular porfolio, and there are multiple records per month, per portfolio. So, each record in the source has: portfolio name, payment, date, sytemmonth, systemyear.

For each porfolio, I need to first find month one, let's say month 1 for portfolio A is 04/01/99 (April '99). So,in the source table, systemmonth = 4 and systemyear = 1999.

Therefor, SUM for Month 1 = SELECT SUM(payment) where portfolio='A' AND systemmonth='4' AND systemyear='1999'. I need to repeat that sort of logic for that portfolio incrementing systemmonth/systemyear up to the present date all the while representing the Month in the report as 1,2,3,4...

Does that clear it up?

 

[CarolCook]

A little. You need to do two things. First, to segregate the data into months, you need the two groups. One would be the portfolio and the other would be your 'date'. When you create your second group on Date, you will see that you can group this date by month. You will have one footer for each month. The sum of the payment will be in the group footer for the date and you will have one footer for each month the portfolio has been in your database. 12 months - 12 footers. 24 months - 24 footers.

Second, the month increment. This can be done by declaring a variable, putting it in the date group footer section and clearing it in the portfolio header:

Create a formula and put in the Date Group Footer (it is important that it go in the date group footer because you want to increment this variable one increment but only when it cycles a new month)

Create a formula - call it MN:

WhilePrintingRecords;
numbervar MONTHNUM := MONTHNUM + 1

You can then use it in your group footer to show for instance: Month <MN> ROI = $$$

You also need to create this formula and put in the Portfolio Group Header

Create another formula, call it MNClear:

WhilePrintingRecords;
numbervar MONTHNUM := 0

This will zero out the increment to 'start over' for each new portfolio.

One last thing - you don't have to show the detail for each posting in the actual month - you can suppress the inner detail and only show the totals you create in the date group footer.

Hope this helps.

 

[bogjacket]

Yes, Carol. That helps a lot. I'll get started on that.

THanks to all for the responses, so far.

 

[lbass]

You still didn't clarify what is in the date field.

-LB

 

[bogjacket]

Sorry, lbass. Each time a payment is made to a portfolio, a record is created and a [table.datepaid] field is set to the current date.

If a payment posted today. datepaid(datetime) would be 09/01/2006, systemmonth would be '9', and system year would be '2006'