Pass Report Detail Function Variables to Report Footer

[Former Member]

I need to track and accumulate the costs for different "PROJECTS"  and print them out on the report in the detail section.  The "PROJECTS" names are in a string variable delimited by a semi-colon, coming from a database table.  Because of this, I cannot use any of the built-in CRYSTAL grouping or summary function.

I use a CRYSTAL Formula "TheProjectsAndCostsAvg" to pass all the cost variables and project-name string variable to a function, where I split out all the data to get the various Project name(s) and cost(s).

I build string variables for each "Project' name and their costs, then pass them back to the report to print out in the detail section.

What I would like to do is accumulate the totals, that I am printing in the detail section, in the function for each project, create a string variable which includes the project names and dollar amounts.  I would like to pass this string variable back to the Report and print it out in the Report-Footer.  I have not figured out how to pass variables created in the detail section to the report footer section.

I enclosed a copy of the Formula, The Function and output.

Thanks,  Vince

TheScript2.txt.zip

[abhilash_kumar · Accepted solution]

Hi Vince,

Could you also post a mock-up of what data you'd like to see on the footer based on the screenshot you've posted above?

That makes it easier to propose a formula.

-Abhilash

[Former Member · Accepted solution]

Abhilash:

I have enclosed 3 files:

• 1 text file w/ the formulas and scripts
• An image of the design mode
• An image file of the report output

Note that the Detail section has the TYPE underlined, on the left, w/ the name underneath w/ the corresponding PROJECT in bold, they are using.  The Name of the TYPE along w/ the total cost for the TYPE is then printed.

On the Report Footer, I need to track the costs per PROJECT. (and also by TYPE, but I will work on that next).

If you look at report-footer at the end of page 2, you will note that the only 'total' PROJECT costs that are being tracked are from the very last TYPE, which is just above the report-footer.

Thank you

Vince

Formulas_and_Functions_Scripts.txt.zip

[abhilash_kumar · Accepted solution]

So, are you looking for a distinct listing of the Name and a sum for that 'Name' on the report footer?

Something like:

The Total Cost for Project Scope : $ xxx

The Total Cost for Catalina Project : $ xxx

and so on...

Would you be able to attach the report with saved data as it is easier that way?

To attach the report, make sure it has saved data first, then change its extension from .rpt to .txt and click the 'use advanced editor' link in the reply window here.

You can also send the report to my e-mail (e-mail is in my profile) if you think that's better.

-Abhilash

[Former Member · Accepted solution]

Abhilash:

Thanks, I sent it to your profile email.

[abhilash_kumar · Accepted solution]

Here's what you need to do:

1) Create a formula with this code and place it on the details section:

WhilePrintingRecords;
local stringvar array s := split({@TheProjectsAndCostsAvg},chr(10));
numbervar i;
currencyvar catalina;
currencyvar scope;
currencyvar sitew;
currencyvar siteb;


for i := 1 to ubound(s) do
(
    if instr(s[i], "SCOPE") > 0 then
        scope := scope + ccur(Trim(split(s[i],":")[2]))
    else if instr(s[i], "CATALINA") > 0 then
        catalina := catalina + ccur(Trim(split(s[i],":")[2]))
    else if instr(s[i], "SITE-W") > 0 then
        sitew := sitew + ccur(Trim(split(s[i],":")[2]))
    else if instr(s[i], "SITE-B") > 0 then
        siteb := siteb + ccur(Trim(split(s[i],":")[2]));

);
"";

2) Create another formula with this code and place this on the report footer:

WhilePrintingRecords;
currencyvar catalina;
currencyvar scope;
currencyvar sitew;
currencyvar siteb;


"The Total Cost for the SCOPE Project       : " &scope & chr(13) &
"The Total Cost for the CATALINA Project    : " &catalina & chr(13) &
"The Total Cost for the SITE-W Project      : " &sitew & chr(13) &
"The Total Cost for the SITE-B Project      : " &siteb

Right-click this field > format field > common tab > check the 'can grow' option.

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Abhilash:

Wow, I have been disecting the scripts to understand what you did.

I didn't know that you can build a formula in the detail section and reference the variables in the Footer section.

I think I understand that you can have a formula point to a custom function and have that function's output sucked into a formula for processing.  I couldn't figure out how to process the same data 2 times, in 2 different ways.

If I understand this correctly, the &scope, &catalina, &siteb, &sitew in the Display-Formula is referencing those like variables in the Build-Formula.  Is this correct?

Also, I know that I can build custom functions using visual basic.  Can I also use visual basic to build formulas?

Please do respond.  I am wanting to learn more about how to build these functions and formulas.  This is great!!  I owe you lunch.

Thank you,

Vince

[abhilash_kumar · Accepted solution]

Hey Vince,

Your understanding about the variables being referenced from the formula on the details section is correct.

And yes, you can use 'VB Syntax' to create a formula too. In the formula editor, select 'VB Syntax' from the dropdown on the top that defaults to 'CR Syntax'.

-Abhilash

[Former Member]

Vince

Try the below ex:

You just need to force Crystal Reports to evaluate your function, so in your F2 function, add WhilePrintingRecords; to the first line, like this:

WhilePrintingRecords; datetimevar endts; endts; 

This should force the report to evaluate the formula as it prints each grouping.

Thank,

DJ