Display promotion title by year 1

[Scroller52]

Hi, using Crystal XI release 2 on a SQL database.

I have two tables in a subreport that look like this:

Table: Carry
Investor Inv Short Name Year Distr
Inv1 Inv1 2002 100
Inv1 Inv1 2003 100
Inv1 Inv1 2004 100
Inv1 Inv1 2005 50
Inv1 Inv1 2006 50
Inv1 Inv1 2007 50
Inv1 Inv1 2008 50
Inv1 Inv1 2009 50
Inv1 Inv1 2010 50
Inv1 Inv1 2011 50
Inv1 trust Inv1 2010 25
Inv1 trust Inv1 2011 10

Table: Title
Investor Inv Short Name Promote Date Promote Title
Inv1 Inv1 1/1/2002 Analyst
Inv1 Inv1 5/18/2005 Director
Inv1 Inv1 12/25/2009 Managing Director

The report is grouped based on a formula called Investor_Name which is as follows:

if(isnull({Carry.Investor Short Name})
or{Carry.Investor Short Name}=""
or{Carry.Investor Short Name}="'")
then {Carry.Investor}
else {Carry.Investor Short Name}

This is used to combine the report for Inv1 and Inv1 trust. The report is grouped by:
Investor_Name, and Year.

I have the tables linked together via Investor and Inv Short Name. The dist amount I am showing is coming out perfectly, in the last column of the report, I would like to show the year at which the employee is promoted so that the report looks something like this:

Investor Year Distr Title
Inv1 2002 100 Analyst
Inv1 2003 100 Analyst
Inv1 2004 100 Analyst
Inv1 2005 50 Director
Inv1 2006 50 Director
Inv1 2007 50 Director
Inv1 2008 50 Director
Inv1 2009 50 Managing Director
Inv1 2010 75 Managing Director
Inv1 2011 60 Managing Director

How can this be done? Currently, only the latest title is coming up on the report. The data is shown on the 2nd group footer (by year). Any help is greatly appreciated!

 

[lbass]

In your formula you are using Roman numerals I,II, but the data appears to be using 1,2.

-LB

 

[Scroller52]

Sorry about that, that is just a typo on my part. The data is using roman numerals as well. The report displays the amounts for Fund I perfectly using the running totals.

 

[lbass]

Please look at data in the detail section and see if any field is null.

-LB

 

[Scroller52]

There are no null fields in the details sections. However in my table, there are rows that have nulls for other columns not relavant in this report. does that make a difference?

 

[lbass]

No, that shouldn't matter. Please explain exactly how you have set up the running total that is not showing up in some cases, and also verify that you have not suppressed the value if zero.

Also, you know that running total must be located in a group footer section, right?

-LB

 

[Scroller52]

The post from yesterday at 12:51 is how I setup the running totals for the case that is and is not working.

I have separate formulas to pull the data back for particular Funds, and then I created running totals based on those formulas. They are Sum running totals set to evaluate based on the year and to reset based on the year groupings.

And yes, the running totals are displayed in the group footers of the subreport.

 

[lbass]

I am not following this. What does your detail level data look like when you place the following fields/formulas/rts on the report in the DETAIL section:

{@InvName} {table.Year} {@Fund1} {@Fund2} {#RTFund1} {#RTFund2}

And I am assuming you are summing your conditional formulas in the RTs.

-LB

 

[Scroller52]

If I put the those formulas in the Details section, my report looks like this:

Investor Year @Fund1 @Fund2 RTFund1 RTFund 2
Inv1 2003 - - - -
Inv1 2004 - 388 - 388
Inv1 2004 - - - 388
Inv1 2005 3,111 - 3,111 -
Inv1 2005 - 10,960 3,111 -
Inv1 2005 - - 3,111 -
Inv1 2006 1,536 - 1,536 -
Inv1 2006 - 35,462 1,536 -
Inv1 2006 - - 1,536 -
Inv1 2007 - 39,010 - 39,010
Inv1 2007 - - - 39,010
Inv1 2008 - 2,298 - 2,298
Inv1 2008 - - - 2,298
Inv1 2008 - - - 2,298
Inv1 2009 - 1,906 - 1,906
Inv1 2009 - - - 1,906
Inv1 2010 (1,679) - (1,679) -
Inv1 2010 - - (1,679) -
Inv1 2010 - - (1,679) -
Inv1 2010 - - (1,679) -
Inv1 2010 - - (1,679) -

I hope this makes sense. From this example the @Fund1 and RTFund 1 columns match, but the @Fund2 and RTFund2 do not. It omits the 10,960 and 35,462 entries from years 2005 and 2006. I have other columns in the report page as well, and the same issue appears for those columns as well.

 

[lbass]

Add the fields in the FundII formula to the detail section and see what appears for those two years. I'm guessing you have a null or a change in the name.

{Carry.Legal Entity Preferred Name} {Carry.Carried Interest}

-LB

 

[Scroller52]

I do not see any nulls values or a change in the name of the field. When using the @Fund2 formula, using the normal summary field brings in the correct data.

When adding those fields into the details section, it shows:

Year LE Pref Name Carried Interest
2003 RS -
2004 Fund I 387,882
2004 RS -
2005 Fund I 3,111,064
2005 Fund II 10,960,398
2005 RS -
2006 Fund I 1,536,236
2006 Fund II 35,462,293
2006 RS -
2007 Fund II 39,010,282
2007 RS -
2008 Fund II 2,298,237
2008 Fund III 5,772,074
2008 RS -
2009 Fund II 1,906,190
2009 RS -
2010 Fund I (1,678,649)
2010 Fund II -
2010 Fund III 105,664
2010 Fund IV -
2010 RS -
2011 Fund III 24,621,598
2011 Fund IV 12,414,189
2011 RS -

I hope the #'s line up right this time. But it shows that the data for Fund II is coming in correctly for those two years.

 

[lbass]

I just realized that you set up the running totals to evaluate on change of year, so it is only checking the value of the first record in each year. The two missing values were in later records. What you really want to do is set the evaluation in the running totals to "evaluate for each record". Since each fund appears only once per year, this will work.

-LB

 

[Scroller52]

Wow that was exactly it. thanks so much LB. If I needed subtotals for the running totals, I would need to create a new running total for them right?

 

[lbass]

Yes.

-LB

 

[Scroller52]

Much appreciation. I swore I tried the RT with different settings...