Report Control - Sorting currency columns

Sorting currency columns

Printed From: Codejock Forums
Category: Codejock Products
Forum Name: Report Control
Forum Description: Topics Related to Codejock Report Control
URL: http://forum.codejock.com/forum_posts.asp?TID=19257
Printed Date: 16 September 2024 at 7:48pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com

Topic: Sorting currency columns

Posted By: PurposeSoftware
Subject: Sorting currency columns
Date Posted: 02 December 2011 at 11:33am

I have 2 columns of currency type.

And the user is required to enter a value in either column but not both.

So when they enter a value in either of the cells the other cell gets set to null (blanked).

The problem we had was with sorting the currency.

If I entered 10.00 on the first row, 200.00 on the second and 30.00 on the third the sorting would put the 200.00 in the middle.

So looking at examples on the forum, I changed the item.value to be double and not string. This resolves the sorting issue but I now cannot have a null cell on that column. Further examples in the forum state I can use the item.caption but this seems not the best way to do it as the item.value may get changed in code and the new value is not reflected on the grid. It also causes problems with formatting.

The attached examples are:

CodeJockReportV1.zip - shows the way it originally worked setting a cell to blank when the other is entered.

Enter data as :

A <tab> 10 <cr>

B <tab> 200 <cr>

C <tab> 30 <cr>

Sort the Fixed Price column by clicking the column header.

Notice that the order is wrong.

Also notice how we want it to work, ie. enter a value in the Fixed Price column, then move to the Discount column and enter a value in there. It clears out the Fixed Price cell. And vice versa.

So, the second example attached (CodeJockReportV2.zip) was an attempt to resolve the issue.

The only differences are :

1. AddRecordPart adds the FixedPrice as double and not a string

2. ClearHeaderRow defaults the FixedPrice to zero from null.

3. _BeforeDrawRow formats the value

But I cannot get it to null the other column's cell data and format the number.

Sometimes when entering 8.87 it rounds up to 9.00 but you may not experience this once written correctly. uploads/6827/CodeJockReportV2.zip - uploads/6827/CodeJockReportV2.zip

Replies:

Posted By: jpbro
Date Posted: 02 December 2011 at 11:27pm

I've put together a little sample to demonstrate what I think you are looking for:

uploads/2676/ReportSwapEdit.zip - uploads/2676/ReportSwapEdit.zip

I used Currency values for the numbers (instead of double), and empty strings instead of NULL values. I did this because when I tried using NULL for field values, the RC was randomly sorting the NULL values interspersed with the number values. This appears to be a bug.

Does this help, or have I misunderstood your requirements?

-------------
Product: Xtreme SuitePro (ActiveX) version 16.2.6
Platform: Windows XP - SP3

Language: Visual Basic 6.0 SP6

Posted By: Aaron
Date Posted: 03 December 2011 at 12:49pm

Hi,

Easiest way, to prevent showing '0' is to use Metrics.Text in BeforeDrawRow event

' ** CODE ADDED HERE
    If Item.Index = clmPriceFixed Then
        If Item.Value <> "" Then
            If Item.Value <> 0 Then
                Item.Caption = Format(Trim(Item.Value), "##0.#0")
            Else
                Metrics.Text = ""
            End If
        End If
    End If
    ' ** CODE END

-------------
Product: Xtreme SuitePro (ActiveX) version 15.0.2
Platform: Windows XP (32bit) - SP 2
Language: Visual Basic 6.0

Zero replies is not an option....

Posted By: PurposeSoftware
Date Posted: 05 December 2011 at 4:38am

I have tried adding Aaron's suggestion to my CodeJockReportV1.zip project (don't know if you received that as I can't see it above - even though I am sure I posted it).

However that doesn't allow sorting to work. Still get 200 in the middle.

Will now try jbpro's suggestion.

Posted By: PurposeSoftware
Date Posted: 05 December 2011 at 10:46am

Thanks to both contributors on this.

A mixture of both answers has sorted the problem.

Ultimately it seemed that the setting of the Item.Value in the _ValueChanged event causes the sorting to go wrong.

Thanks again for the very fast response.

Cheers, Col