First, a caveat: Sage does not recommend altering system views. You might want to consider creating your own view for a custom report.

Here is the select statement that SQL output. Try it in a test system first:

SELECT RTRIM(ISNULL(vPersonPE.Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(vPersonPE.Pers_LastName, '')) AS Pers_FullName,

RTRIM(ISNULL(vCompanyPE.Comp_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(vCompanyPE.Comp_PhoneAreaCode, ''))

+ ' ' + RTRIM(ISNULL(vCompanyPE.Comp_PhoneNumber, '')) AS Comp_PhoneFullNumber, RTRIM(ISNULL(vCompanyPE.Comp_FaxCountryCode, ''))

+ ' ' + RTRIM(ISNULL(vCompanyPE.Comp_FaxAreaCode, '')) + ' ' + RTRIM(ISNULL(vCompanyPE.Comp_FaxNumber, '')) AS Comp_FaxFullNumber,

RTRIM(ISNULL(vPersonPE.Pers_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(vPersonPE.Pers_PhoneAreaCode, ''))

+ ' ' + RTRIM(ISNULL(vPersonPE.Pers_PhoneNumber, '')) AS Pers_PhoneFullNumber, RTRIM(ISNULL(vPersonPE.Pers_FaxCountryCode, ''))

+ ' ' + RTRIM(ISNULL(vPersonPE.Pers_FaxAreaCode, '')) + ' ' + RTRIM(ISNULL(vPersonPE.Pers_FaxNumber, '')) AS Pers_FaxFullNumber,

Opportunity.Oppo_OpportunityId, Opportunity.Oppo_PrimaryCompanyId, Opportunity.Oppo_PrimaryPersonId, Opportunity.Oppo_AssignedUserId,

Opportunity.Oppo_ChannelId, Opportunity.Oppo_Description, Opportunity.Oppo_Type, Opportunity.Oppo_Product, Opportunity.Oppo_Source, Opportunity.Oppo_Note,

Opportunity.Oppo_CustomerRef, Opportunity.Oppo_Opened, Opportunity.Oppo_Closed, Opportunity.Oppo_Status, Opportunity.Oppo_Stage,

Opportunity.Oppo_Forecast, Opportunity.Oppo_Certainty, Opportunity.Oppo_Priority, Opportunity.Oppo_TargetClose, Opportunity.Oppo_CreatedBy,

Opportunity.Oppo_CreatedDate, Opportunity.Oppo_UpdatedBy, Opportunity.Oppo_UpdatedDate, Opportunity.Oppo_TimeStamp, Opportunity.Oppo_Deleted,

Opportunity.Oppo_Total, Opportunity.Oppo_NotifyTime, Opportunity.Oppo_SMSSent, Opportunity.Oppo_WaveItemId, Opportunity.Oppo_SegmentID,

Opportunity.Oppo_SecTerr, Opportunity.Oppo_WorkflowId, Opportunity.Oppo_LeadID, Opportunity.Oppo_Forecast_CID, Opportunity.Oppo_Total_CID,

Opportunity.oppo_scenario, Opportunity.oppo_decisiontimeframe, Opportunity.oppo_Currency, Opportunity.oppo_TotalOrders_CID, Opportunity.oppo_TotalOrders,

Opportunity.oppo_totalQuotes_CID, Opportunity.oppo_totalQuotes, Opportunity.oppo_NoDiscAmtSum, Opportunity.oppo_NoDiscAmtSum_CID,

Opportunity.oppo_classification, Opportunity.oppo_PlanAssets, Opportunity.oppo_PlanAssets_CID, Opportunity.oppo_AnnualPrem,

Opportunity.oppo_AnnualPrem_CID, Opportunity.oppo_PlanType, Opportunity.oppo_TransferType, Opportunity.oppo_OutcomeReason,

Opportunity.oppo_WinningCarrier, Opportunity.oppo_ReasonObtained, Opportunity.oppo_ReasonLost, Opportunity.oppo_TransferDate,

Opportunity.oppo_FinRecBoardResolution, Opportunity.oppo_FinRecLetterOfIntent, Opportunity.oppo_FinNotificationSent, Opportunity.oppo_FinFundsTransferred,

Opportunity.oppo_CurrentCarrier, Opportunity.oppo_403bAssets, Opportunity.oppo_403bAssets_CID, Opportunity.oppo_Descriptor, Opportunity.oppo_AssetSource,

Opportunity.oppo_FundingVehicle, Opportunity.oppo_ReasonLost2, Opportunity.oppo_ReasonLost3, Opportunity.oppo_ReasonObtained2,

Opportunity.oppo_ReasonObtained3, Opportunity.oppo_ActualTransferAmount, Opportunity.oppo_ActualTransferAmount_CID, Opportunity.oppo_ActualTransferDate,

Opportunity.oppo_ssnein, Opportunity.oppo_rfpstatus, Opportunity.Oppo_PrimaryAccountId, Opportunity.oppo_campaigncode, Opportunity.oppo_annuitydate,

Opportunity.oppo_assetsource_ee, Opportunity.oppo_benefitslost, Opportunity.oppo_blackoutdate, Opportunity.oppo_caisdate, Opportunity.oppo_salescredit,

Opportunity.oppo_contractnum, Opportunity.oppo_datepolicycreated, Opportunity.oppo_reg60letterdate, Opportunity.oppo_enrollmenttype,

Opportunity.oppo_exemptreason, Opportunity.oppo_finradate, Opportunity.oppo_finrastart, Opportunity.oppo_assetlocation, Opportunity.oppo_issuingstate,

Opportunity.oppo_missedfinra, Opportunity.oppo_missedturnaround, Opportunity.oppo_missingdocsmulti, Opportunity.oppo_missingformsmulti,

Opportunity.oppo_notefromfield, Opportunity.oppo_notetofield, Opportunity.oppo_notetobilling, Opportunity.oppo_oldworkflowstateid,

Opportunity.oppo_descriptior_ee, Opportunity.oppo_opportunitytype, Opportunity.oppo_policynumber, Opportunity.oppo_policynumaddedby,

Opportunity.oppo_priorstage, Opportunity.oppo_prioruser, Opportunity.oppo_reasonlost_indiv, Opportunity.oppo_reasonwon_indiv, Opportunity.oppo_reg60,

Opportunity.oppo_replacement, Opportunity.oppo_caisscannedby, Opportunity.oppo_sourceinfo, Opportunity.oppo_submittedtouw,

Opportunity.oppo_uwturnarounddate, Opportunity.oppo_reg60workflowstateid, Opportunity.oppo_UWExempt, Opportunity.oppo_UWExemptBy, Opportunity.oppo_ern,

Opportunity.oppo_nameofdecedent, Opportunity.oppo_balanceyearend, Opportunity.oppo_balanceyearend_CID, Opportunity.oppo_relation,

Opportunity.oppo_checklasd, Opportunity.oppo_failedsuitability, Opportunity.oppo_followupdue, Opportunity.oppo_Caption1, Opportunity.oppo_CaptionOppoID,

Opportunity.oppo_reg60letterdue, vCompanyPE.Comp_Name, vCompanyPE.Comp_Territory, vCompanyPE.Comp_EmailAddress, vCompanyPE.Comp_CompanyId,

vCompanyPE.Comp_SecTerr, vCompanyPE.Comp_CreatedBy, vCompanyPE.Comp_PrimaryUserId, Territories.Terr_Caption, vPersonPE.Pers_Title,

vPersonPE.Pers_EmailAddress, vPersonPE.pers_SecTerr, vPersonPE.Pers_CreatedBy, vPersonPE.Pers_PersonId, vPersonPE.Pers_PrimaryUserId,

Channel.Chan_Description, vCompanyPE.Comp_ChannelID,

(SELECT CAST(CAST(Parm_Value AS NCHAR) AS INTEGER) AS Expr1

FROM Custom_SysParams

WHERE (Parm_Name = 'BaseCurrency')) AS Oppo_WeightedForecast_CID, vPersonPE.Pers_ChannelID,

Opportunity.Oppo_Forecast / Oppo_Forecast_Currency.Curr_Rate * Opportunity.Oppo_Certainty / 100 AS Oppo_WeightedForecast,

Opportunity.Oppo_PrimaryAccountId AS Acc_AccountId, vCompanyPE.Comp_Sector

FROM Opportunity LEFT OUTER JOIN

vPersonPE ON Opportunity.Oppo_PrimaryPersonId = vPersonPE.Pers_PersonId LEFT OUTER JOIN

vCompanyPE ON Opportunity.Oppo_PrimaryCompanyId = vCompanyPE.Comp_CompanyId LEFT OUTER JOIN

Territories ON Opportunity.Oppo_SecTerr = Territories.Terr_TerritoryID LEFT OUTER JOIN

Channel ON Channel.Chan_ChannelId = Opportunity.Oppo_ChannelId LEFT OUTER JOIN

Currency AS Oppo_Forecast_Currency ON Opportunity.Oppo_Forecast_CID = Oppo_Forecast_Currency.Curr_CurrencyID

WHERE (Opportunity.Oppo_Deleted IS NULL)

- Matt -

HI Debbie,

Matt is correct, really you should not change the vReportOpportunity view because it is a system view.

You should create a new view use the script from your post but change the name and add your field.

That view query should already have access to comp_sector so you should just be able to add it to the selection list like so:

CREATE VIEW vCustomReportOpportunity AS
SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName,
'')) AS Pers_FullName, RTRIM(ISNULL(Comp_PhoneCountryCode, '')) + ' ' +
RTRIM(ISNULL(Comp_PhoneAreaCode, '')) + ' ' + RTRIM(ISNULL(Comp_PhoneNumber,
'')) AS Comp_PhoneFullNumber, RTRIM(ISNULL(Comp_FaxCountryCode, '')) + ' ' +
RTRIM(ISNULL(Comp_FaxAreaCode, '')) + ' ' + RTRIM(ISNULL(Comp_FaxNumber, '')) AS
Comp_FaxFullNumber, RTRIM(ISNULL(Pers_PhoneCountryCode, '')) + ' ' +
RTRIM(ISNULL(Pers_PhoneAreaCode, '')) + ' ' + RTRIM(ISNULL(Pers_PhoneNumber,
'')) AS Pers_PhoneFullNumber, RTRIM(ISNULL(Pers_FaxCountryCode, '')) + ' ' +
RTRIM(ISNULL(Pers_FaxAreaCode, '')) + ' ' + RTRIM(ISNULL(Pers_FaxNumber, '')) AS
Pers_FaxFullNumber, Opportunity.*, Comp_Name, Comp_Territory, Comp_EmailAddress,
Comp_CompanyId, Comp_SecTerr, Comp_CreatedBy, Comp_PrimaryUserID, Terr_Caption, Comp_Sector,
Pers_Title, Pers_EmailAddress, Pers_SecTerr, Pers_CreatedBy, Pers_PersonId,
Pers_PrimaryUserID, Chan_Description, Comp_ChannelID,(SELECT
CAST(CAST(Parm_Value AS NCHAR) AS INTEGER) FROM Custom_SysParams WHERE Parm_Name
= 'BaseCurrency') AS Oppo_WeightedForecast_CID, Pers_ChannelID, ((Oppo_Forecast
/ Oppo_Forecast_Currency.Curr_Rate) * Oppo_Certainty / 100) AS
Oppo_WeightedForecast, Oppo_PrimaryAccountId AS Acc_AccountId FROM Opportunity
LEFT JOIN vPersonPE ON Oppo_PrimaryPersonID = Pers_PersonID LEFT JOIN vCompanyPE
ON Oppo_PrimaryCompanyID = Comp_CompanyId LEFT JOIN Territories ON Oppo_SecTerr
= Terr_TerritoryId LEFT JOIN Channel ON Chan_ChannelId = Oppo_ChannelId LEFT
JOIN Currency Oppo_Forecast_Currency ON Oppo_Forecast_CID =
Oppo_Forecast_Currency.Curr_CurrencyID WHERE Oppo_Deleted IS NULL

So the new view should look something like this in the system:

Then make your new view the source of your report like so:

Let me know if you have any problems with this.

Thanks,

Robert

Thank you to all! Works like a charm. :-D