x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 2 replies
  • Subscribers 76 subscribers
  • Views 5886 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
stephenLan

Quarterly reports based on Join Date (Membership Renewal Report)

SOLVED
Posted By

Hi.

I have a request from a new Sales member of our team to show on their CRM dashboard when an existing member is up for renewal.
We renew a member every year (so yearly) - the month of their join date (aka startdate) is what triggers the invoice.

She also wishes to see it 3 months prior (quarterly) to the renewal date in order to send a quick reminder message to the member.

I'm hoping someone can direct me on how to create this report (search query) correctly.
I'm pulling on the Company Summary - I selected the necessary fields for the report.

However, I get confused on the search (join date field).

I'm not 100% sure what to use here - relative vs for any - and when I select for any, it wants to use a between.
I think I need to use relative - then choose quarter - but not sure about current vs next - I assume current is Jan though March - and next would be March through Jun.

Just curious if there is any "can" report I can use to create this for her.

Let me know if anyone has any other approach - maybe we should be using a different field to work this quarterly report better.

Thanks,
Stephane

Parents
  • +1
    verified answer

    Stephane

    Ah.... Sean got in ahead of me.

    A report in Sage CRM that uses a relative quarter is using standard SQL functions DatePart and DateAdd.  So for example Opportunities that are closing this quarter will generate a SQL that looks like

    SELECT oppo_stage,oppo_description,comp_name,oppo_forecast_CID,oppo_forecast,oppo_targetclose,pers_fullname,pers_phonefullnumber FROM vReportOpportunity WITH (NOLOCK)  WHERE ((oppo_secterr is null OR (oppo_secterr>=-1610612729 AND oppo_secterr<=-1342177275) OR (oppo_secterr>=-2147483639 AND oppo_secterr<=-1879048185) OR (oppo_AssignedUserId=4) OR (oppo_ChannelId=2) OR (oppo_ChannelId=3) OR (oppo_ChannelId=4) OR (oppo_ChannelId=5) OR (oppo_ChannelId=1) OR (oppo_CreatedBy=4)) AND (comp_secterr is null OR (comp_CreatedBy=4) OR (comp_ChannelId=2) OR (comp_ChannelId=3) OR (comp_ChannelId=4) OR (comp_ChannelId=5) OR (comp_ChannelId=1) OR (Comp_PrimaryUserId=4) OR (comp_secterr>=-2147483639 AND comp_secterr<=-1879048185) OR (comp_secterr>=-1610612729 AND comp_secterr<=-1342177275)) AND (pers_secterr is null OR (pers_CreatedBy=4) OR (pers_ChannelId=2) OR (pers_ChannelId=3) OR (pers_ChannelId=4) OR (pers_ChannelId=5) OR (pers_ChannelId=1) OR (Pers_PrimaryUserId=4) OR (pers_secterr>=-2147483639 AND pers_secterr<=-1879048185) OR (pers_secterr>=-1610612729 AND pers_secterr<=-1342177275))) AND ( (DATEPART(QQ, oppo_targetclose) = DATEPART(QQ, DATEADD(QQ, 0, GETDATE())) AND DATEPART(YY, oppo_targetclose) = DATEPART(YY, DATEADD(QQ, 0, GETDATE())))
    )ORDER BY oppo_stage,oppo_forecast,oppo_description ASC,comp_name ASC,oppo_targetclose ASC,pers_fullname ASC,pers_phonefullnumber ASC

    The DATEPART function returns an integer representing the specified datepart of the specified date.

    January-March returns 1
    April-June returns 2
    July-Sep returns 3
    Oct-Dec returns 4

    Does that make sense?

Reply
  • +1
    verified answer

    Stephane

    Ah.... Sean got in ahead of me.

    A report in Sage CRM that uses a relative quarter is using standard SQL functions DatePart and DateAdd.  So for example Opportunities that are closing this quarter will generate a SQL that looks like

    SELECT oppo_stage,oppo_description,comp_name,oppo_forecast_CID,oppo_forecast,oppo_targetclose,pers_fullname,pers_phonefullnumber FROM vReportOpportunity WITH (NOLOCK)  WHERE ((oppo_secterr is null OR (oppo_secterr>=-1610612729 AND oppo_secterr<=-1342177275) OR (oppo_secterr>=-2147483639 AND oppo_secterr<=-1879048185) OR (oppo_AssignedUserId=4) OR (oppo_ChannelId=2) OR (oppo_ChannelId=3) OR (oppo_ChannelId=4) OR (oppo_ChannelId=5) OR (oppo_ChannelId=1) OR (oppo_CreatedBy=4)) AND (comp_secterr is null OR (comp_CreatedBy=4) OR (comp_ChannelId=2) OR (comp_ChannelId=3) OR (comp_ChannelId=4) OR (comp_ChannelId=5) OR (comp_ChannelId=1) OR (Comp_PrimaryUserId=4) OR (comp_secterr>=-2147483639 AND comp_secterr<=-1879048185) OR (comp_secterr>=-1610612729 AND comp_secterr<=-1342177275)) AND (pers_secterr is null OR (pers_CreatedBy=4) OR (pers_ChannelId=2) OR (pers_ChannelId=3) OR (pers_ChannelId=4) OR (pers_ChannelId=5) OR (pers_ChannelId=1) OR (Pers_PrimaryUserId=4) OR (pers_secterr>=-2147483639 AND pers_secterr<=-1879048185) OR (pers_secterr>=-1610612729 AND pers_secterr<=-1342177275))) AND ( (DATEPART(QQ, oppo_targetclose) = DATEPART(QQ, DATEADD(QQ, 0, GETDATE())) AND DATEPART(YY, oppo_targetclose) = DATEPART(YY, DATEADD(QQ, 0, GETDATE())))
    )ORDER BY oppo_stage,oppo_forecast,oppo_description ASC,comp_name ASC,oppo_targetclose ASC,pers_fullname ASC,pers_phonefullnumber ASC

    The DATEPART function returns an integer representing the specified datepart of the specified date.

    January-March returns 1
    April-June returns 2
    July-Sep returns 3
    Oct-Dec returns 4

    Does that make sense?

Children
No Data

*Community Hub is the new name for Sage City