Need to Pull data
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22946
Printed Date: 28 Apr 2024 at 6:13pm
Topic: Need to Pull data
Posted By: Lymo7
Subject: Need to Pull data
Date Posted: 12 Aug 2021 at 10:25pm
Hi,
I have a report that needs to be altered so that each month it is run it pulls in data based on the Policy Start Date which is in the same month. For Example, if I run today (13/08/2021) it's pulls all polices which have a start date in August.
I have a report which does something similar but is based on the policy renewal date and even though this could work if the policy doesn't change their renewal date, I'd rather it was based on the Policy Start Date. For this report it uses the Month function and the line in the Select is: {@Renewal Month} = Month (CurrentDate). The Renewal Month formula has connecting formulae. Below is the Renewal Month formula and the connecting formulae.
Renewal Month:
(DatePart("m", {@Contract end for Feb}))
Contract end for Feb:
if
DatePart ("d",{Coverage_Group_View.Policy_End_Date} ) = 28
and
DatePart ("m",{Coverage_Group_View.Policy_End_Date} ) = 02
then
Date(year({Coverage_Group_View.Policy_End_Date}),03 ,01) else
{@Actual Renewal Date}
Actual Renewal Date:
{Coverage_Group_View.Policy_End_Date} + 1
Do I need to follow the same as above for Policy Start Date?
I tried the below line in my new report but it's producing an error saying it's looking for a date.
{@Policy Start Date} = Month (CurrentDate)
Can someone please help me here?
Thanks :-)
|
Replies:
Posted By: lockwelle
Date Posted: 17 Aug 2021 at 7:29am
you would probably want month and year...
Month({@Policy Start Date}) = Month(CurrentDate)
AND
Year({@Policy Start Date}) = Year(CurrentDate)
though, don't you really want something like:
Month({table.PolicyStartDate}) = Month(CurrentDate)
Isn't the value in the table what you want to 'filter' on?
HTH
|
Posted By: Lymo7
Date Posted: 17 Aug 2021 at 10:10pm
Thanks for your reply.
I found the issue I had was I entered the below without the Month function preceding {@Policy Start Date}.
{@Policy Start Date} = Month (CurrentDate)
i can't filter as the month will change every month.
I'm interested on your point including year too.
So would this return me any policies that has a start date in August 2021?
Thanks
Lymo
|
Posted By: lockwelle
Date Posted: 18 Aug 2021 at 1:27pm
What I don't know, is would it return you any policies with a start date in August 2020...or next year will it return the August 2021 data as well as the August 2022 data.
That is why I included the year, to filter out past data being returned...unless that is the desired outcome.
HTH
|
|