Hi
This is what you do...
Create two subreports,one for Start Code and One for EndCode.
Steps for Start Code Sub-Report ,follow the same steps for EndCode Subreport
In those two Sub-Reports
Create Group by
UserName and then
Code
Then in Insert Summary Insert Maximum Date for that Code.
suppress all the other sections.
Then Create a Shared Variable to capture the maximum date for the StartCode
Create a Formula for that.
Frm_SharedSt_codedate
WhilePrintingRecords;
Shared datevar StDate;
StDate := date({max_date.date})
Place the formula in suppress section above Group Header.
Then Insert the subreport in Main Report Group Header once done right click the subreport and change subreport links
Move(?ParameterStartCodename} to Fields to Link to and Check the box
Select data in subreport based on field select the code from dropdown.
Then in subreport record selection formula check for the below code is created automatically if not enter the code below
{table.codefieldname} = {?Pm-?StartCodefielname}
So you will get Maxdate for start code for the username ,
Then in main report you will need to create a formula to get the max date for start code
Create a formula
Frm_SharedStCodeDate
WhilePrintingRecords;
Shared datevar StDate;
Place this formula in Group Header or Footer .
once done to calculate diff in weeks use datediff
Create a formula
DiffInWeeks
DateDiff ("w",{@Frm_SharedStDate},{@Frm_SharedEndDate})
For give date the value for weeks is 1
Let me know how it goes,if you want I can mail you the report with your sample data
Cheers
Rahul
IP Logged
