Sorting on a custom with multiple columns
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20140
Printed Date: 01 May 2024 at 8:13pm
Topic: Sorting on a custom with multiple columns
Posted By: charlese
Subject: Sorting on a custom with multiple columns
Date Posted: 21 Oct 2013 at 7:17am
|
Hi, I have got a report which is displaying fee detail information and I need to create a sort. My condition is as follows i.e. if there is no sort string I want to sort by work date and the sort string else I want to sort by work date and timecard orginal index. I created a formulae like this If IsNull ({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) then
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex})
else
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString}) Then in from the Report menu selected Record Sort Expert and choose my new custom field i.e. the formulae. However this does not work, report is not sorting Can anyone please help me. The datatype for Work date is datetime and the datatype for timecard orignal index is integer. Hence that is why I am using ToText to convert it to a string. However if I just have workDate in the formulae it works but as soon as I add the time card orignal index, it does nto work. Kind Regards Emma
|
Replies:
Posted By: DBlank
Date Posted: 21 Oct 2013 at 8:33am
|
if you place the formula field in your details section does it properly display for every row?
|
Posted By: lockwelle
Date Posted: 21 Oct 2013 at 9:44am
could there be a null in the time card original index?
as we all know, adding a NULL to anything is a NULL, and CR won't display an error, which is probably what DBlank was getting at by asking if the formula returns the correct information for every line of detail.
HTH
|
Posted By: charlese
Date Posted: 21 Oct 2013 at 10:24am
|
Hi, I place the formulae in the detail section and the concatenation of work date and timecard orginal index is not working, it is printing nothing. I have put the timecard original index on the detail to see it's value. I can see it is displaying it correctly, there is no null values but the concatenation is not working. In the report it is something like this :Work Date TimeCare Orignal Index Concatenation 23/04/2013 7,208,97 Nothing 29/04/2013 7,212,353 Nothing If IsNull ({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) then
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex})
else
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString}) The first part of this if statement is not working but the second part is working as I can see the concatenation of the formula on the report. Is something wrong with this toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex}) please ? |
Posted By: charlese
Date Posted: 21 Oct 2013 at 10:29am
|
Why would toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex}) return nothing ? Is something wrong with this concatenation. Work date is datetime and TimeCard1__OrigTimeIndex is an integer However the second part i.e. Text({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString}) works fine and I can see the actual value properly concatenated.
|
Posted By: lockwelle
Date Posted: 21 Oct 2013 at 10:36am
I know that it will sound redundant, but have tried a formula like:
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex})
perhaps, the concatenation is ok, perhaps it the the field {ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}...maybe it is not null, but an empty string.
debugging CR can be hard because you think the data looks one way, and it doesn't.
HTH
|
Posted By: charlese
Date Posted: 21 Oct 2013 at 10:49am
|
Ok I have tried this ... but still nothing... the first part of the if statement i.e. the concatenation of work date and timecard index is nothing.. If IsNull ({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) OR {ListofProfDetailTime.SortString} = '' then
CStr({ListofProfDetailTime.WorkDate} & Cstr({ListofProfDetailTime.Timecard1__OrigTimeIndex}))
else
CStr({ListofProfDetailTime.WorkDate} & Cstr({ListofProfDetailTime.SortString}))
|
Posted By: charlese
Date Posted: 21 Oct 2013 at 10:52am
|
Don't understand why concatenation of a datetime and integer is not working !! Work Date TimeCard Orignal Index Concatenation 23/04/2013 7,208,97 Nothing 29/04/2013 7,212,353 Nothing Why would this above not concatenate ???? |
Posted By: lockwelle
Date Posted: 21 Oct 2013 at 10:52am
hmmm...
just create a formula that would display the concatenation that we think is not working. see if that displays correctly. If it does, then the next step is to display the value in the IF...and see what that value is
|
Posted By: charlese
Date Posted: 21 Oct 2013 at 10:55am
|
The second part of the statement is getting concatenated properly. I can see these values on the report where 23/04/2013 00:00:00 is work date and 1 is the sort string 23/04/2013 00:00:001 23/04/2013 00:00:002 23/04/2013 00:00:003 |
Posted By: lockwelle
Date Posted: 21 Oct 2013 at 11:00am
I understand that the 2nd part is displaying correctly. Since the syntax and data type of the 1st part are the same as the second, as you said, it should work. So...to test the theory, display just the 1st part in a formula by itself. If it works, then we know that the IF part is broken. If it doesn't, then I am with you in being confused.
If the 1st part really is working, but is not NULL, then you would use the ELSE of the statement, but I am guessing that it has a null value which is why you created the IF in the first place.
So if we check each and every part of the formula that is not working separately, we SHOULD be able to find the part of the logic that is not working and correct it.
Alas, it can be a pain, and can be a lot of work, but it is the only way to 'see' what CR is seeing.
HTH
|
Posted By: charlese
Date Posted: 21 Oct 2013 at 11:06am
Ok I have created just a formula for the concatenation and that seems to be working... I get 26/04/2013 00:00:007,208,907.0029/04/2013 00:00:007,212,353.00 29/04/2013 00:00:007,212,362.00 So it seems like somethign is wrong with the if statement !!!
|
Posted By: lockwelle
Date Posted: 21 Oct 2013 at 11:09am
so the last thing to do is to display the field {ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString} in the details and see what it's value is.
of course if it null, you won't see anything, but you can of course build a formula like:
isnull({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString})
this will display true or false
you can also build a formula like:
"'" + totext({ListofProfDetailTime.PresTimekeeper1__TkprDate__Title1__SortString}) + "'"
all are just ways to see what the data really looks like
|
Posted By: DBlank
Date Posted: 21 Oct 2013 at 11:09am
|
what happens if you place both of your concantenated 'functional' formula's onto the report at the same time?
Do they both display the correct info or did that chaneg your results?
|
Posted By: lockwelle
Date Posted: 21 Oct 2013 at 11:11am
that's a good point...
I probably would have had both running, so I didn't to inquire.
|
Posted By: charlese
Date Posted: 21 Oct 2013 at 11:12am
|
I am so sorry I have discovered my mistake ... I was testing for the wrong sort string in the if statement !!! feel so stupid now!!! but thank you very much for your help. This has really help me to find out ways to debug my report when I have problems. Many Thanks again.
|
Posted By: charlese
Date Posted: 24 Oct 2013 at 12:16am
|
Hi, I thought I cracked this puzzle but came across another obstacle. I am trying to sort my report detail and I have created a custom sort formula for it as. The business rule is if there is no sortstring I want to sort by work date and OrigTimeIndex else I want to sort by work date and sort string If IsNull ({ListofProfDetailTime.SortString}) then
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.Timecard1__OrigTimeIndex})
else
toText({ListofProfDetailTime.WorkDate}) & ToText({ListofProfDetailTime.SortString}) However when the report is displaying the order of the work date is wrong I am getting something like 07/10/2013 08/09/2013 09/10/2013 10/10/2013 22/09/2013 it is not taking the month into consideration at all. Month 09 is after 10. Can someone please help me to solve this. Kind Regards
|
Posted By: lockwelle
Date Posted: 24 Oct 2013 at 4:47am
ahhh...sorting a string will do that.
What I would do is make 2 groups (instead of the 1), the first would be on the date (as a formula) and the second on the timeIndex (unless it is a time-in which case I would combine it in the first group...I am thinking that it is just a number)
for the first group would be the WorkDate, and then for the second you would have the formula but with just the Index or the sort string.
You can suppress the second group, but all of the records would then come out in the desired order.
HTH
ps, I am sure that there are other ways to accomplish this, this just seems the simplest/quickest/most straight-forward to me
|
Posted By: DBlank
Date Posted: 24 Oct 2013 at 5:35am
|
a second on lockwelle's approach of creating a nactual datetime field to do your sort on. That said you may run into problems in the conversion since you also have a 'sort' field you concantenating in.
if you stay with text use two formulas as lockwelle suggests butt one for sorting that uses a string in
YYYYMMDDtime
and the other that you already have for display
|
Posted By: lockwelle
Date Posted: 24 Oct 2013 at 5:40am
Since I never use a sort expression, just the grouping value, I was replacing the concatenation with the 2 groups, since a number and datetime don't play well together.
You can always display whatever value is desired...
|
|