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!

 

[Scroller52]

no takers?

 

[lbass]

I'm assuming the titles are repeating for each year?

Place this formul in the detail section:

whileprintingrecords;
stringvar title;
if year({title.promotedate}) = {carry.year} then
title := {title.promotetitle} else
title := title;

In the group #2 footer, add this formula:
whileprintingrecords;
stringvar title;

Not sure whether you need a reset formula. I'm assuming this is linked to the main report on the employee(?) field.

-LB

 

[Scroller52]

Hi LB,

I did as you said and the field is coming up blank. I placed the formula:

Var Title:
whileprintingrecords;
stringvar title;
if year({Title.Promotion Date}) = tonumber({Carry.Year of GL date (YYYY)}) then
title := {Title.Promote Title} else
title := title;

in the details section of the subreport. Then put the following formula in the group footer 2 section:
Title:
whileprintingrecords;
stringvar title;

Yes it is being linked to the main report via the 'Investor Name' formula and I am not sure whether or not this needs a reset clause.

 

[Scroller52]

Actually, my data set may have an issue. let me resolve that and retry.

 

[lbass]

I'm assuming all of what you described is within the sub--including the display formula--since I didn't set these up as shared variables (necessary if displayed in the main report).

-LB

 

[Scroller52]

The formula is still coming up as blank. Any ideas why? everything is within the subreport and being shown in the subreport.

 

[lbass]

Are the results showing up correctly in the detail section if you unsuppress the details?

Did you add a reset formula? If so, where did you put it and what are the contents of the formula?

-LB

 

[Scroller52]

Did not set up the reset formula.
Unhiding the details section shows a blank value for it as well.

 

[lbass]

Please show the actual content of the formulas you set up then.

-LB

 

[Scroller52]

Var Title:
whileprintingrecords;
stringvar title;
if year({Title.Promotion Date}) = tonumber({Carry.Year of GL date (YYYY)}) then
title := {Title.Promotion Title} else
title := title;

I put this formula in the details section within the subreport.

Title:
whileprintingrecords;
stringvar title;

This is in the GF2 within the subreport.

 

[lbass]

If it is not working in the detail section then it suggests either that PromotionDate and Year of GL date don't match (could Year of GL date not contain four digits?) or that some field is null, probably the promotion fields, so try this:

whileprintingrecords;
stringvar title;
if isnull({Title.Promotion Date}) or
isnull({Title.Promotion Title}) or
year({Title.Promotion Date}) [red]<>[/red]tonumber({Carry.Year of GL date (YYYY)})then
title := title else
title := {Title.Promotion Title};

-LB

 

[Scroller52]

Thanks LB!
This resolved the title issue, however it caused another issue within the report.

It is multiplying the Dist Amt by the number of positions for that investor. So now the report looks like this:

Investor Year Distr Title
Inv1 2002 300 Analyst
Inv1 2003 300 Analyst
Inv1 2004 300 Analyst
Inv1 2005 150 Director
Inv1 2006 150 Director
Inv1 2007 150 Director
Inv1 2008 150 Director
Inv1 2009 150 Managing Director
Inv1 2010 225 Managing Director
Inv1 2011 180 Managing Director

Why did it cause this?

 

[lbass]

I don't know how you are calculating it, but you probably should be using a running total that evaluates on change of some field.

-LB

 

[Scroller52]

I was calculating the fields just using a summary field based on a formula. That was working perfectly without the title logic. Now that the title logic is working it is multiplying the summaries.

Using a running total, I am able to get the entries to look right for one column, but not the others? For instance, I have two formulas:
Dist 1
if({Carry.Legal Entity Preferred Name}="Fund I")
then
{Carry.Carried Interest}/1000

Dist 2
if({Carry.Legal Entity Preferred Name}="Fund II")
then
{Carry.Carried Interest}/1000

I created running totals for both of them. They are a summary type, set to evaluate on change of group for the year and to reset on change of group for the year as well.

The numbers for running total 1 is showing perfectly, but there are missing numbers for running total 2? What is going on?

 

[lbass]

What summary are you using? Should be a sum. You should also be showing these in the group footer for year.

-LB

 

[Scroller52]

Yes, the summary type for the running totals are sums and they are displayed in the group footer for year.

The RT for the first formula works fine, however the second and all other formulas are not? Have you ever seen that? I double checked to make sure that the RT's are the same except for the summary field.

 

[lbass]

Could one of the fields be null? Are you sure the name in the formula is exactly the same as shown in the database? Same case?

-LB

 

[Scroller52]

Will check the data set when I get in tomorrow morning.

thanks so much again for all your help.

 

[Scroller52]

The table looks good, the dataset looks like this:

Inv Invshort Entity Year Amt1 Amt2
Inv1 Fund 1 2000 000 100
Inv1 Fund 1 2001 000 200
Inv1 Fund 1 2002 000 300
Inv1 Fund 1 2003 000 400
Inv1 Fund 1 2004 000 500
Inv1 Fund 2 2000 000 100
Inv1 Fund 2 2001 000 200
Inv1 Fund 2 2002 000 300
Inv1 Fund 2 2003 000 400
Inv1 Fund 2 2004 000 500

The data is grouped based on a formula:
if(isnull({Carry.Investor Short Name})
or{Carry.Investor Short Name}=""
or{Carry.Investor Short Name}="'")
then {Carry.Investor}
else {Carry.Investor Short Name}
and then grouped by year.

The two formula's I created to pull the data in looks like this:
"Fund 1"
if({Carry.Legal Entity Preferred Name}="Fund I")
then
{Carry.Carried Interest}/1000

"Fund 2"
if({Carry.Legal Entity Preferred Name}="Fund II")
then
{Carry.Carried Interest}/1000

The field Carried Interest = Amt2 from the sample table above.

Using the running total, the data is displaying the entries for Fund 1 correctly, but is missing some entries for Fund 2. So my report currently looks like this:

Year Fund I Fund II
2000 100
2001 200
2002 300 300
2003 400 400
2004 500

When the entries are being pulled back for Fund II, they are correct. Does this make sense...?