Search code examples
sql-servervb.netcrystal-reports

Getting Max and Min Values in Crystal Report Formula

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.

Solution

  • 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#}) ;