String Conversion

[kchaudhry]

I am using CR 8.5 with SQL server 2000. I have a formula which inlculdes a String field and a Number field. Here is what I have so far.

if
{Participant_Move_in_Trans.Trans_Category} = "Local"
and
{Participant_Move_in_Trans.Tier_Level} = 1.00
and
{Participant_Move_in_Trans.Prod_Com_Elig} = "Y"
and
CStr({plan_manager.set_year}) = Left ({Participant_Move_in_Trans.Commission_Qualifying_Date}, 4)
And
CStr({plan_manager.set_period_num}) = Mid ({Participant_Move_in_Trans.Commission_Qualifying_Date},5,2)
then
1
else
0

{Participant_Move_in_Trans.Commission_Qualifying_Date} is a string field with data like "20030801"
{plan_manager.set_year} is a number field with data like "2003"
{plan_manager.set_year} is a number field with data like "8"


My problem is that I am not sure how to convert a number into a string field, not even sure if this is required. When I put the sum of this formula in my group summary it gives me 0.

Can anyone tell me how can I fix this?

Thanks in advance.




Kchaudhry

 

[Reebo99]

Try:

if
{Participant_Move_in_Trans.Trans_Category} = "Local"
and
{Participant_Move_in_Trans.Tier_Level} = 1.00
and
{Participant_Move_in_Trans.Prod_Com_Elig} = "Y"
and
ToText({plan_manager.set_year},0,"&quot = Left ({Participant_Move_in_Trans.Commission_Qualifying_Date}, 4)
And
ToText({plan_manager.set_period_num},0,"&quot = Mid ({Participant_Move_in_Trans.Commission_Qualifying_Date},5,2)
then
1
else
0


Reebo
UK

 

[Reebo99]

Sorry, got the last one wrong:

if
{Participant_Move_in_Trans.Trans_Category} = "Local"
and
{Participant_Move_in_Trans.Tier_Level} = 1.00
and
{Participant_Move_in_Trans.Prod_Com_Elig} = "Y"
and
ToText({plan_manager.set_year},0,"&quot = Left ({Participant_Move_in_Trans.Commission_Qualifying_Date}, 4)
And
ToText({plan_manager.set_period_num},"00&quot = Mid ({Participant_Move_in_Trans.Commission_Qualifying_Date},5,2)
then
1
else
0


Reebo
UK

 

[kchaudhry]

Reebo99,

Thanks for your reply. I tried this but it is still giving me zero.

Any other suggestions?

Kchaudhry

 

[Madawc]

As an experiment, try eliminating the tests one at a time, and also putting the test on the print line, to see what you get.

My first suspect would be
{Participant_Move_in_Trans.Tier_Level} = 1.00
Crystal has an unfortuunate habit of storing infinitessimal values and then refising to match identical numbers on the basis of those invisible numbers. Try
Round({Participant_Move_in_Trans.Tier_Level}, 0) = 1
or
Round({Participant_Move_in_Trans.Tier_Level}, 2) = 1.00


Madawc Williams
East Anglia, Great Britain

 

[kchaudhry]

I have tried the following

if
{Participant_Move_in_Trans.Trans_Category} = "Local"
and
{Participant_Move_in_Trans.Tier_Level} = 1.00
and
{Participant_Move_in_Trans.Prod_Com_Elig} = "Y"
then 1 else 0

This returns me the correct results. The problem starts when I try to add in the string field.

Kchaudhry

 

[kchaudhry]

Does anyone have any idea how I can fix this problem?

Kchaudhry

 

[nagornyi]

How do you know it should not be zero? So many ANDs, just one is false, the result is zero.. Here is what I would have done. Display all fields participating in formula, then create several separate formulas for each component of your formula and display them next to the corresponding fields. Look at what comes up. The problem will be localized immidiately.

 

[kchaudhry]

I have tried to run some SQL queries on the database and I getting exactly what I want. This is how I know that the data should not be zero.

The problem comes in when I try to add the following:

CStr({plan_manager.set_year}) = Left ({Participant_Move_in_Trans.Commission_Qualifying_Date}, 4)
And
CStr({plan_manager.set_period_num}) = Mid ({Participant_Move_in_Trans.Commission_Qualifying_Date},5,2)

I am not very familiar with conversions and need some help with writing the correct formula.

Hope you understand where I am coming from.

Kchaudhry

 

[kchaudhry]

I am not sure if this will help but here is the query which I am using in SQL Query Analyzer to check the validity of the data:

Select Count(Participant_Id) As countPID, Participant_Move_in_Trans.Participant_Id
from
Participant_Move_in_Trans, plan_manager
where
plan_manager.set_year = left(Participant_Move_in_Trans.Commission_Qualifying_Date, 4)
And
plan_manager.set_period_num = Substring(participant_Move_in_Trans.Commission_Qualifying_Date, 5, 2)
And
Participant_Move_in_Trans.Prod_Com_Elig = 'Y'
And
Participant_Move_in_Trans.Trans_Category = 'Local'
And Participant_Move_in_Trans.Tier_Level = '1.00'
Group By Participant_Id

Thanks,

Kchaudhry

 

[synapsevampire]

Why not share the SQL you used on the database?

Try:

{plan_manager.set_year} = val(Left ({trim(Participant_Move_in_Trans.Commission_Qualifying_Date}), 4))

And
{plan_manager.set_period_num} = val(Mid (trim({Participant_Move_in_Trans.Commission_Qualifying_Date}),5,2))

-k

 

[kchaudhry]

synapsevampire,

I think I was couple of minutes late in posting my SQL query. Sorry about that, well I have tried your suggestion.
It is still giving me zero in that field.

Any other suggestions?

Appreciate your help.

Kchaudhry

 

[nagornyi]

The correct data you see on DB side not necessrily come coreect to Crystal. Display your fields in report, create each conversion as separate formula and also display. Just to debug. If the problem still not seen, please post the output here.

 

[kchaudhry]

nagornyi,

As per your suggestion I have broken the forumla into 4 separate formula. Here are the formulas and the output.

if {Participant_Move_in_Trans.Trans_Category} = "Local" then 1 else 0
Output = 1

if {Participant_Move_in_Trans.Tier_Level} = 1.00 then 1 else 0
Output = 1

if {Participant_Move_in_Trans.Prod_Com_Elig} = "Y" then 1
else 0
Output = 1

if {plan_manager.set_year} = val(Left (trim({Participant_Move_in_Trans.Commission_Qualifying_Date}), 4)) And {plan_manager.set_period_num} = val(Mid (trim({Participant_Move_in_Trans.Commission_Qualifying_Date}),5,2)) then 1 else 0
Output = 0

As supstected as soon as I add the last portion of the formula, I start getting the zero. I am not able to understand why would this work in SQL and not in Crystal?

















Kchaudhry

 

[nagornyi]

If you meant the formulae working in SQL... well, those may be quite different formulae. The Crystal syntax is not guaranteed to agree with any DB SQL. Now what's left is to break down the last formula into several the same way and see what those parts bring back. Also display the original fields to keep track if they getting processed as you believe they should.

 

[kchaudhry]

I have done the breakdown of the rest of the formula. Here is what happens:

We have to check if the year is equal on both sides of the equation. The problem is that:

- the LEFT side is a type "int" in the database. Crystal reports accepts it as type Number.
- the Right side is a type "String".
- in order for this to work we have to convert either side to be the same as the other side.
- if i try CStr(Number) to convert the left side to type "String", the formula fails.
- if i try ToNumber(String) to convert the right side, the formula fails again.

Any ideas how I can fix this?

Kchaudhry

 

[lbass]

if {plan_manager.set_year} = val(Left ({trim(Participant_Move_in_Trans.Commission_Qualifying_Date}), 4))

and

totext({plan_manager.set_period_num},"00&quot = Mid (trim({Participant_Move_in_Trans.Commission_Qualifying_Date}),5,2))
then 1 else 0

In other words, I wonder if the problem is that{plan_manager.set_period_num} can be one or two digits, while the other side of the equation is always 2 digits.

-LB

 

[kchaudhry]

LB,

I tried your formula. Here is the result.

True if:

totext(8, "00&quot = Mid(trim({Participant_Move_in_Trans.Commission_Qualifying_Date}),5,2)

False if:

totext({plan_manager.set_period_num}, "00&quot = Mid(trim({Participant_Move_in_Trans.Commission_Qualifying_Date}),5,2)

Although the field above is "int" in the database and recognized as "Number" in CR. Also, the data in the field is 8.

Kchaudhry

 

[lbass]

Please test the following. Create two formulas, place them on your report, and report back the sample results:

totext({plan_manager.set_period_num}, "00&quot

Mid(trim({Participant_Move_in_Trans.Commission_Qualifying_Date}),5,2)

You might also want to do the same for the year clause of the formula, for two additional formulas.

-LB

 

[kchaudhry]

LB,

The I have created and placed both the forumlas on the report.

Here are the results.

Formula 1 : "00"
Formula 2 : "08"

Appreciate all your help

Kchaudhry