Problem With Case Sensitivity and CrossTab

edited 2:33AM in FastReport 3.0
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...

Comments

  • edited 2:33AM
    You can change the frxCrossMatrix.pas file:
    function TfrxCrossHeader.Find(Value: Variant): Integer;
    var
      i: Integer;
    begin
      { find the cell containing the given value }
      Result := -1;
      for i := 0 to Count - 1 do
    //    if AnsiCompareText(VarToStr(Items[i].Value), VarToStr(Value)) = 0 then
        if VarToStr(Items[i].Value) = VarToStr(Value) then
        begin
          Result := i;
          Exit;
        end;
    end;
    

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.