Displaying formula calcuation in CrossTab with summation

[oanion]

I’m using SQL Server 2000 and Crystal Reports 10

PORTFOLIO
​

CLIENTNAME

BalancePlaced 4619611.11
​

TotalPaid 270287.93
​

LiqRate X
​

I’m trying to develop a crosstab query that has the values listed above. The LiqRate is Total Paid/BalancePlaced, therefore, X= .058. The BalancePlaced and the TotalPaid are both summarized fields within the crosstab, and this is accuate, however, when I try to calculate the Liq Rate in a Crosstab, it forces a summation to be made. I do not want a summation of the Liq Rate. I simply need the LiqRate to be calulcated as Total Paid/BalancePlaced. Does anybody have any ideas?

 

[lbass]

Are you allowed to use summaries in crosstab formulas in CR10? If "BalancePlaced" is the sum of {table.BalancePlaced} and "TotalPaid" is the sum of {table.TotalPaid}, then aren't you really looking for:

sum({table.TotalPaid},{table.clientname})/sum({table.BalancePlaced},{table.clientname})

If you can be specific about what your current summaries are(using table and field names and specifying the summary) and what you want for LiqRate there might be a solution using a SQL expression.

-LB

 

[oanion]

Ibass,

You are abosolutely correct. Sorry about that. The actual calculation for liq rate would be
sum({table.TotalPaid},{table.clientname})/sum({table.BalancePlaced},{table.clientname}). However, whenever I try to place it in the crosstab in the above mentioned format, it forces me to do some type of summation.

 

[lbass]

If CR 10 allows you to enter this as a summary (you couldn't do that in CR 8.0), couldn't you then just use a maximum as a summary, since you would expect only one value per cell anyway?

-LB

 

[oanion]

I just tried using the maximum function. It looks like the result for the Liq rate is the maximum Liq Rate for all the portfolios belonging to one client. A client can have several portfolios.

 

[lbass]

Try the following. Create a SQL expression {%LiqRate}:

(select sum(AKA.`TotalPaid`) from Table AKA where
AKA.`ClientName` = Table.`ClientName` and
AKA.`Portfolio` = Table.`Portfolio`) /
(select sum(AKA.`BalancedPlaced`) from Table AKA where
AKA.`ClientName` = Table.`ClientName` and
AKA.`Portfolio` = Table.`Portfolio`)

Replace "Table" with your table name wherever it occurs in the above formula and replace "TotalPaid" and "Portfolio" (whatever you are using for the column field) with your exact field names, if different. Leave "AKA" as is, since it is an alias table name.

Next add {%LiqRate} as a field to summarize in your crosstab. Leave the summary as "sum" until you exit the crosstab expert, since changing it in the expert can cause CR to crash (in my experience). Once you have exited the expert, right click on the LiqRate field and choose "Change summary operation" and change the summary to maximum. Since the value will be the same at both the detail and the group levels, a maximum will provide the correct value.

-LB

 

[oanion]

It seems that the SQL Expression Editor in Crystal 10 does not know how to evaluate the "Select" . Also, the sum function cannot be used in the SQL Expression Editor. Any other ideas? Is there anyway to write a view or a store procedure in SQL Server that creates a cross tab, and then display the results in Crystal?

 

[lbass]

Please note that when there is an error in a SQL expression the error message will say "syntax error found here" and place the cursor in front of the sum, but that isn't necessarily what or where the error is. Unlike the formula editor, the error messages aren't necessarily very helpful.

Please try copying the SQL expression that you developed into the thread so we can troubleshoot it. The punctuation can vary by datasource, so you might also try double-clicking on a field in the SQL expression field list to see how the punctuation is returned, and then use that in the expression.

Please note that I have tested this solution and know it is workable under some, if not all, situations.

-LB

 

[KKond]

Hi, I had a simila problem.
I don't know if u'r prob is solver or not. In crosstab, u cannot have diff summaries in groups and totals. if u selcet sum, then even ur totals will also be sum. If u want to have diff, summary types in groups and totals, have 2 cross tabs, one with groups(with supress grand totals) and one with supressing details and just show grand totals and overlay these report.
This worked out fine for me.