CODEWITHSUNDEEP
crystal-reportscrystal-reports-2008
Saif AL-Qiari
Saif AL-Qiari

Reputation: 469

Crystal Formula to display the latest

A table has workout columns starting from workout1 till workout7.

I want to create formula to check the latest workout (higher) and display it in crystal report. For example, check if workout7 have value then display it before any other workout columns else check workout6, If it value, display it before other columns and so on.

But I think if-else statement is not the right choice to perform this job. What is the right way to do that?

Any help would be appreciated,

Table

Upvotes: 1

Views: 158

Answers (2)

CoderT
CoderT

Reputation: 182

The value needs to be checked if is not null, and then if the value is not null, print it, else continue checking the steps in descending approach, and whenever the value is found just print it.

if NOT ISNULL ({EB_Tam_DailyMv.dmv_workout10}) then
{EB_Tam_DailyMv.dmv_workout10}

else  if NOT  ISNULL ({EB_Tam_DailyMv.dmv_workout9}) then
{EB_Tam_DailyMv.dmv_workout9}

else if  NOT ISNULL ({EB_Tam_DailyMv.dmv_workout8}) then 
{EB_Tam_DailyMv.dmv_workout8}

else if  NOT ISNULL ({EB_Tam_DailyMv.dmv_workout7}) then 
{EB_Tam_DailyMv.dmv_workout7} 

else if NOT ISNULL({EB_Tam_DailyMv.dmv_workout6}) then 
{EB_Tam_DailyMv.dmv_workout6}

else if NOT ISNULL({EB_Tam_DailyMv.dmv_workout5}) then 
{EB_Tam_DailyMv.dmv_workout5}

 else if NOT ISNULL({EB_Tam_DailyMv.dmv_workout4}) then 
 {EB_Tam_DailyMv.dmv_workout4}

 else if NOT ISNULL ({EB_Tam_DailyMv.dmv_workout3}) then 
 {EB_Tam_DailyMv.dmv_workout3}

else if NOT ISNULL ({EB_Tam_DailyMv.dmv_workout2}) then 
{EB_Tam_DailyMv.dmv_workout2}

else if NOT ISNULL ({EB_Tam_DailyMv.dmv_workout1}) then 
{EB_Tam_DailyMv.dmv_workout1}

else "00:00"

Upvotes: 1

Ajay2707
Ajay2707

Reputation: 5798

You can do it both side, good to do in sqlserver side for better performance.

For sqlserver side this is a sample.

create table tempworkout (dmv_workpatten varchar(50), dmv_workin1 varchar(50) , dmv_workout1 varchar(50)
, dmv_workin2 varchar(50) , dmv_workout2 varchar(50)
, dmv_workin3 varchar(50) , dmv_workout3 varchar(50)
, dmv_workin4 varchar(50) , dmv_workout4 varchar(50)
, dmv_workin5 varchar(50) , dmv_workout5 varchar(50)
, dmv_workin6 varchar(50) , dmv_workout6 varchar(50)
, dmv_workin7 varchar(50) , dmv_workout7 varchar(50))

insert into tempworkout values ('WP1', 1,2,3,4,5,6,7,8,9,10,11,12,13,14)
,('WP1', 2,3,4,5,6,7,8,9,10,11,12,13,14, 15)
,('WP1', 3,4,5,6,7,8,9,10,11,12,13,null,15,null)
,('WP1', 4,5,6,7,8,9,10,11,12,null,14,null,16,null)
,('WP1', 4,5,6,7,8,9,10,null,12,null,14,null,16,null)

select * from tempworkout

select COALESCE(dmv_workout7, COALESCE(dmv_workout6, COALESCE(dmv_workout5,COALESCE(dmv_workout4,COALESCE(dmv_workout3,COALESCE(dmv_workout2,dmv_workout1)))))),
* from tempworkout

insert into tempworkout values ('WP1', 1,null,3,null,5,null,7,null,9,null,11,null,13,null)

select COALESCE(dmv_workout7, COALESCE(dmv_workout6, COALESCE(dmv_workout5,COALESCE(dmv_workout4,COALESCE(dmv_workout3,COALESCE(dmv_workout2,dmv_workout1)))))),
* from tempworkout
drop table tempworkout

Problem with your comparison in crystal report. At crystal report side, you can use like: (Check the syntax)

if Not IsNull({table1.id})  then
   (if {table1.name} <> "a" then
      var1 := "Hello"
   else
      var1 := "Hi";)
else
   var1 := "Bye";

http://scn.sap.com/thread/3279432

Nested if else in Crystal Reports

Upvotes: 0

Related Questions