Hello,

The report check box missing is a bug I think, there was an issue on earlier versions where if certain tables were added to the view, the check-box was not visible. I don't think this is the case with your view however, I have tested your view on 7.2 and it is working as expected, when I remove your custom fields so it runs on a vanilla DB. What version of CRM are you using?

The view you have specified above should do what you want, you can then group by the 'Day' column you have created to differentiate the data.

Personally I would use a CASE statement in the view to display 'Today', 'Tomorrow' or '2 days time' based on the value of the oppo_proposaldue in comparison to the system date. Unions are best avoided where possible due the the processing that comes with them, there is some analysis here:

http://www.sswug.org/articles/viewarticle.aspx?id=19349

e.g.

SELECT CASE

WHEN CONVERT(date,oppo_proposaldue,103) = CONVERT(date,GETDATE(),103) THEN 'Today'

WHEN CONVERT(date,oppo_proposaldue,103) = CONVERT(date,DATEADD(dd,1,GETDATE()),103) THEN 'Tomorrow'

WHEN CONVERT(date,oppo_proposaldue,103) = CONVERT(date,DATEADD(dd,2,GETDATE()),103) THEN '2 Days Time'

END AS oppo_Proposalday

FROM opportunity

You can then add a where clause to only find oppos where the dates are in the range you are looking for, and you can group by this new field.

Hope that helps

Ok, I've got it created, using the case now, and it does take, but the reports view checkbox is gone again.

I have 7.2b

This is what I'm dropping in - I was planning on using SummaryOpportunity view so I can get all those goodies too..

CREATE VIEW vOppUpcomingPropDue

AS

SELECT CASE WHEN CONVERT(date,oppo_proposalduedate,103) = CONVERT(date,GETDATE(),103) THEN 'Today' WHEN CONVERT(date,oppo_proposalduedate,103) = CONVERT(date,DATEADD(dd,1,GETDATE()),103) THEN 'Tomorrow' WHEN CONVERT(date,oppo_proposalduedate,103) = CONVERT(date,DATEADD(dd,2,GETDATE()),103) THEN '2 Days Time' END as DayDue, epd_pers.epd_EmailAddress as Pers_EmailAddress, epd_pers.epd_PhoneCountryCode as Pers_PhoneCountryCode, epd_pers.epd_PhoneAreaCode as Pers_PhoneAreaCode, epd_pers.epd_PhoneNumber as Pers_PhoneNumber, epd_pers.epd_FaxCountryCode as Pers_FaxCountryCode, epd_pers.epd_FaxAreaCode as Pers_FaxAreaCode, epd_pers.epd_FaxNumber as Pers_FaxNumber, epd_comp.epd_EmailAddress as Comp_EmailAddress, epd_comp.epd_PhoneCountryCode as Comp_PhoneCountryCode, epd_comp.epd_PhoneAreaCode as Comp_PhoneAreaCode, epd_comp.epd_PhoneNumber as Comp_PhoneNumber, epd_comp.epd_FaxCountryCode as Comp_FaxCountryCode, epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode, epd_comp.epd_FaxNumber as Comp_FaxNumber, Person.*, Company.*, Opportunity.*, Account.* FROM Opportunity LEFT JOIN Person ON Oppo_PrimaryPersonId = Pers_PersonId LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID LEFT JOIN Company ON Oppo_PrimaryCompanyId = Comp_CompanyId AND Comp_Deleted IS NULL LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID LEFT JOIN Account ON Oppo_PrimaryAccountId = Acc_AccountId WHERE Oppo_Deleted IS NULL AND oppo_status = 'In Progress'

This is what I'm seeing on edit.

I ended up making a simpler view, not based on the opp summary view, but a cut down version, with specifically what I wanted. For whatever reason, it showed up fine on reports.

CREATE VIEW vOppPropDueSoon

AS

select

CASE WHEN CONVERT(date,oppo_proposalduedate,103) = CONVERT(date,GETDATE(),103) THEN 'Today' WHEN CONVERT(date,oppo_proposalduedate,103) = CONVERT(date,DATEADD(dd,1,GETDATE()),103) THEN 'Tomorrow' WHEN CONVERT(date,oppo_proposalduedate,103) = CONVERT(date,DATEADD(dd,2,GETDATE()),103) THEN '2 Days Time' END as DayDue,

oppo_salesperson,

oppo_quoteid,

Oppo_OpportunityId,

Oppo_PrimaryCompanyId,

Oppo_PrimaryPersonId,

Oppo_ChannelId,

Oppo_Description,

Oppo_Stage,

Oppo_Status,

Oppo_CreatedBy,

Oppo_Deleted,

Oppo_SecTerr,

oppo_forecast,

oppo_forecast_CID,

oppo_opened,

oppo_proposalduedate,

pers_personid,

pers_secterr,

pers_createdby,

pers_channelid,

pers_primaryuserid,

comp_companyid,

comp_secterr,

comp_createdby,

comp_channelid,

comp_primaryuserid

FROM OPPORTUNITY

LEFT JOIN Company on oppo_PrimaryCompanyId = comp_companyid

LEFT JOIN Person on oppo_primarypersonid = pers_personid

WHERE oppo_deleted is null AND CONVERT(date,oppo_proposalduedate,103) = CONVERT(date,getdate(),103) AND oppo_status = 'In Progress'

Any clue on why reports disappears so I can avoid it in the future?

Also thanks for the help deadmau5 :)