Getting Max and Min Values in Crystal Report Formula

0
votes

I am Using Crystal Report with VS 2012 with Dataset and Populating the Report with Run Time. I am getting Repetitive rows for My Report, It is because the Data is Entered with Different Period (Month/Year) like the salary of Person for Month and Year. So What I thought to get rid of the Repetition of rows is I am going to Get the Values of Summarized (Group footer) portion of a Particular Employee instead of GETTING DETAIL SECTION where Every record repeats instead of Period. I have achieved it, But the Problem is the Minimum Function is not getting the accurate Minimum Period as Shown the picture. enter image description here The formula for I which I used to Show Minimum and Maximum period values is:

 Maximum ({DataTable1.Period}, {DataTable1.PPO_NO})&" - "& Minimum ({DataTable1.Period}, {DataTable1.PPO_NO})

and My Formula for getting Employee Month and Year are: 

SELECT PPONO, Name, Father_Name,Designation,BPS,Claimant,Age,Type,IOT,  TblPPOBook.Pay_Month + '/' + CAST(TblPPOBook.Pay_Year AS nvarchar(4)) AS Period, Pension, Allow etc. From EmployeeTable Where PayDate Between Date1 ANd Date2.

The Early help in this regard will be highly appreciated.

1
sql-servervb.netcrystal-reports
what is datatype of period? string or date? – Siva
String, Infact it is consisting of Two String Columns merged with Query. One Column is for Month and other one is for Year and then I merge it with query. – ZahidKakar
Try converting to date and then apply min and max functions – Siva
I did it with CDate but out of luck. – ZahidKakar
sorry this needs to be changed further... ( Maximum ((CDate({DataTable1.Period}), {DataTable1.PPO_NO})))&" - "&( Minimum ((CDate({DataTable1.Period}), {DataTable1.PPO_NO}))) – Siva

1 Answers

2
votes

Create a formula to convert string to date i.e

@String to date:= Datevar Date_Period:=CDate({Period});

Second use this formula to find min and max date i.e

Maximum ({@String to date}, {PPO No#})& " to " & Minimum ({@String to date}, {PPO No#}) ;