Passing Summarized Formulas to a Subreport to Sort

[safari7391]

I need help passing summarized formulas from a main report to a subreport. Also taking the same formulas and having the ability to sort on them through a parameter in the main report.

Reason:
The reason a subreport is involved, it’s to pass the summarized formulas from the main report to the subreport, so the summarized formulas from the main report can be sorted.

 

[lbass]

Please explain more specifically what you are trying to do. What is your report structure? What is the content of your "summarized formula"? What is the purpose of the parameter? Are you by any chance trying to use a subreport to rank the summaries in the main report without actually sorting them?

-LB

 

[safari7391]

Shared NumberVar totalseconds;
Shared NumberVar remainingseconds;
That is one summarized formula that I have created

Shared NumberVar hours;
Shared NumberVar minutes;
Shared NumberVar seconds;


totalseconds := ({@_Avg_Call_Work_Time_Agent});
hours := Truncate(totalseconds/3600);
ToText(hours,0) + " hrs ";
remainingseconds := TotalSeconds - (Hours*3600);
minutes := Int(Truncate((remainingseconds/60)));
seconds := (totalseconds) - (hours * 3600) - (minutes * 60);
totext(hours,"00") + ":" + totext(minutes,"00") + ":" + totext(seconds,"00");

The purpose with the parameters is to sort the summarized formulas.
I need to use a subreport to sort the summarized formulas, because its not possible to sort summarized formulas in the main report.

 

[safari7391]

The first thing I a trying to do is pass some formulas from the main report to the subreport. Second I would like to be able to sort from those fields I pass over to the subreport. Those are the first 2 steps I need to take in this task.

 

[lbass]

As far as I know, using a subreport does not solve the problem of not being able to sort this formula in the main report--you wouldn't be able to sort by it in a subreport either.

However, if your goal is only to sort by this formula, then remove the "shared" from each variable declaration, and then you will see that you can use the formula for sorting in the main report.

The "shared" designation forces the formula to be evaluated "whileprintingrecords"--which means it will not be available for sorting. The only reason to use "shared" with a variable is if you need to pass it to or from a subreport. If you remove the "shared," you can sort.

-LB

 

[safari7391]

Even after removing "shared" from the formula it still did not work.

Here are 2 other formulas I am trying to use so I can sort but not have any luck with them.
1 - Sum({Daily_Metrics.acdcalls},{@Agent_Name})

2 - if Sum ({Daily_Metrics.ti_stafftime}, {@Agent_Name}) = 0 then
0
else if (Sum ({Daily_Metrics.ti_stafftime}, {@Agent_Name}) < 3600)then
{@_Calls_Handled_Agent} else
({@_Calls_Handled_Agent}/(Sum ({Daily_Metrics.ti_stafftime}, {@Agent_Name})/3600))

My task is to sort from these 3 different types of formulas.

 

[lbass]

I realized before you posted that I hadn't taken into account your formula {@_Avg_Call_Work_Time_Agent}. If you have any summaries in the formula, you can't use the regular "sort records" feature. For inserted summaries, you can use the topN/group sort feature--which means you could probably do a group sort on:

Sum({Daily_Metrics.acdcalls},{@Agent_Name})

However, your second formula will not work for any kind of sort unless you convert the summaries to SQL expressions or find some other way of converting each summary into the equivalent of a database field on which a summary can be inserted. I cannot tell how complicated it would be to use a SQL expression solution, because you are using nested formulas. As a matter of course, you should ALWAYS post the contents of formulas, since the contents may drive what solutions are available. It is also important to post the version of CR. If SQL expressions are available to you, please provide this information and then maybe I can help with the SQL expression approach.

-LB

 

[safari7391]

Their is a report available that is used for an example with sorting summaried fields. The name of the example report is Grouping / Sorting on a WhilePrintRecords Formula, but all of the formulas are in Basic Syntax and it would be helpful if you can convert the following 4 formulas to Crystal Syntax.

shared country as string
if length(country) < 250 then country = country + {Customer.Country}(1 to 5) + ","
formula = country

shared country as string
shared countryii as string
if length(country) >= 250 then countryii = countryii + {Customer.Country}(1 to 5) + ","
formula = countryii

shared addtostring as string
dim salesratio as number
shared country as string

if length(country) < 250 then salesratio = 1000 * DistinctCount ({Customer.Customer Name}, {Customer.Country}) / Sum ({Customer.Last Year's Sales}, {Customer.Country})
if length(country) < 250 then addtostring = addtostring + totext(salesratio,2) + ","
formula = addtostring

shared addtostringii as string
dim salesratioii as number
shared country as string

if length(country) >= 250 then salesratioii = 1000 * DistinctCount ({Customer.Customer Name}, {Customer.Country}) / Sum ({Customer.Last Year's Sales}, {Customer.Country})
if length(country) >= 250 then addtostringii = addtostringii + totext(salesratioii,2) + ","
formula = addtostringii

Hopefully this might get me started down the right path.

 

[safari7391]

Also I am working with Crystal Report XI and connect to a access database through and ODBC connnection. What do you mean by andn SQL expression?

 

[lbass]

Where did you find this sample report? If it came with CR XI, I'll take a look at it tomorrow.

If you look in your field explorer, you should find SQL expression as one of your options. However, in CR XI, instead of using a SQL expression, I think you would need to use "Add Command" to create a SQL query that includes subqueries that create the necessary summaries.

-LB

 

[safari7391]

Can you also convert this formula from Basic Syntax to Crystal Syntax. The other 4 formulas goes in the main report and this formula goes in to the subreport.

dim mousy as number
dim kitty as number
dim doggy as number
dim groupera as string
dim grouperb as string

groupera = "," + {?Pm-@Add Country}
grouperb = "," + {?Pm-@Add Country II}

mousy = instr(groupera, {Customer.Country}(1 to 5))
kitty = instr(grouperb, {Customer.Country}(1 to 5))
if mousy > 0 then doggy = mousy - length(replace(groupera(1 to mousy),",","")) else _
if kitty > 0 then doggy = kitty - length(replace(grouperb(1 to kitty),",",""))
if mousy > 0 then formula = split({?Pm-@Add Ratio}, ",")(doggy) else _
if kitty > 0 then formula = split({?Pm-@Add Ratio II}, ",")(doggy)

 

[safari7391]

I got the report from Crystal Technical Support. How can I send you the report example or maybe I can post it some where so you can retrieve it?

 

[lbass]

It is forum policy to stay within the thread. The forum is not a coding service, but even though I'm not familiar with Basic syntax, I might have given this a shot--if I could learn from it, by seeing exactly how this is supposed to work. But--you have made it even more difficult by mixing formulas--some seem to be based on your data, and some seem to be from different samples. So, are you willing to explain the Crystal Support's suggested solution? Or to provide the reference number if this is from the Business Objects CR Knowledge Base? If so, why don't you submit main report and subreport formulas from the sample, and then I or someone might take a stab at converting them.

-LB

 

[safari7391]

Thank you LB for all your help and trying to understand, but I have come up with another approach to this issue. I created a summarized query and link it to the table. This gave me the option to do what I was looking to accomplish and also not to deal with any subreport. So thanks again and maybe next time we can work together where I can explain the problem much clearer.