Crystal Backlog Report mods - function help 1

[adamroof]

i have a formula for determining and grouping on week numbers for a backlog report (SO) it comes out as below and does not order too well, unless i specify order and add all 52 to 53 weeks manually.

51.00-06
52.00-06
53.00-06
1.00-07
2.00-07
3.00-07

and so forth
but without a sort specification, it will do this

1.00-07
13.00-07
2.00-07
3.00-07
48.00-06
52.00-06

so the prob is the < 10s need to have a leading zero
we dont need the .00, but thats caused during the Round function. Do you have any input that i can do to fix this so i wont need to add 52 sort entries on 10 reports each new year?

"WeekNumberGeneral" - Group #2 Header Sorted

if {@WeekNumber06} > 53 then CStr({@WeekNumber07Year}) else
if {@WeekNumber05} > 52 then CStr({@WeekNumber06Year}) else
if {@WeekNumber04} > 52 then CStr({@WeekNumber05Year}) else
if {@WeekNumber03} > 52 then CStr({@WeekNumber04Year}) else
If {@WeekNumber03} <= 52 then CStr({@WeekNumber03Year}) else "0"

"WeekNumber07Year"

formula = {@WeekNumber07} & "-07"

Heres is where i think we can make the "fix"
"WeekNumber07"

formula = Round(datediff( "W" , #12/31/06# , {SO_SalesOrderDetail.PromiseDate},0 ) + 1)

Other weeknumbers are the same with just the start date adjustment and an 06 or 05 in the year.

 

[bdreed35]

You can force the CStr function to return a leading zero like this:

CStr({table.field},"00")

I would probably do this in your WeekNumberXX formula(s):

formula = CStr(Round(datediff( "W" , #12/31/06# , {SO_SalesOrderDetail.PromiseDate},0 ) + 1),"00")

BTW, there are Crystal specific forums on this site that will help you get an answer alot quicker if you want to post there in the future.

~Brian

 

[adamroof]

thanks!

i had found the crystal forums after i posted here and didnt want to cross post. I figured Mas90 backlog specific might get better response here tho.

that worked nifty, had to make a change to the Year forumula too, so it'll sort the years proper tho.

WeekNumber07

formula = CStr(Round(datediff( "W" , #12/31/06# , {SO_SalesOrderDetail.PromiseDate},0 ) + 1),"00")

WeekNumber07Year

formula = "2007-" & {@WeekNumber07}

no change to the WeekNumberGeneral

Now i dont have to manual sort anymore!

Thanks Again!