Problem With Case Sensitivity and CrossTab
Hi,
Bit of a problem with FastReports3 and crosstab. I've been using it for about 4-5 months (upgrading from 2.53 to 3 along the way), and just came across this.
SQL queries are case sensitive. I.E. "Widget" and "WIDGET" are two different things.
Ex. Query data for Sales amounts for Categories vs. Sales Person might look like:
Category Salesperson Sum(Amount)
Widget Joe 1345.50
WIDGET Joe 280
Misc Joe 500
Other Joe 750
FR3 does not seem to be case sensitive. I.E. "Widget" and "WIDGET" are considered the same thing. Fair enough.
The problem comes up when it comes to reports that need to do grouping or cross-tab summaries. In a cross-tab summary, it will only print out one row or column header, but DOES NOT merge the totals. It shifts the data down, which makes totals appear in the wrong row.
Ex. You'd think the cross-tab would look like:
Joe
Widget 1345.50
WIDGET 280
Misc 500
Other 750
TOTAL 2875.5
But it FR3 displays it like:
Joe
----
WIDGET 1345.50
Misc 280
Other 500
TOTAL 2125.5
(notice the total is wrong, and the actual amount for Other goes to la-la-land)
The workaround for now is to uppercase everything we're grouping on.
Ex. make the select query look like:
Select UPPER(category), salesperson from <etcetc>
and if the query has a "group by", put it there too:
Group By Upper(category), salesperson, <etc>
Have other people come across this yet? Is there a fix in the works?
Btw, I'm using FR 3.07 and Firebird 1.5 as a Database...
Bit of a problem with FastReports3 and crosstab. I've been using it for about 4-5 months (upgrading from 2.53 to 3 along the way), and just came across this.
SQL queries are case sensitive. I.E. "Widget" and "WIDGET" are two different things.
Ex. Query data for Sales amounts for Categories vs. Sales Person might look like:
Category Salesperson Sum(Amount)
Widget Joe 1345.50
WIDGET Joe 280
Misc Joe 500
Other Joe 750
FR3 does not seem to be case sensitive. I.E. "Widget" and "WIDGET" are considered the same thing. Fair enough.
The problem comes up when it comes to reports that need to do grouping or cross-tab summaries. In a cross-tab summary, it will only print out one row or column header, but DOES NOT merge the totals. It shifts the data down, which makes totals appear in the wrong row.
Ex. You'd think the cross-tab would look like:
Joe
Widget 1345.50
WIDGET 280
Misc 500
Other 750
TOTAL 2875.5
But it FR3 displays it like:
Joe
----
WIDGET 1345.50
Misc 280
Other 500
TOTAL 2125.5
(notice the total is wrong, and the actual amount for Other goes to la-la-land)
The workaround for now is to uppercase everything we're grouping on.
Ex. make the select query look like:
Select UPPER(category), salesperson from <etcetc>
and if the query has a "group by", put it there too:
Group By Upper(category), salesperson, <etc>
Have other people come across this yet? Is there a fix in the works?
Btw, I'm using FR 3.07 and Firebird 1.5 as a Database...
Comments