The customer had the requirement of being able to filter by user select fields on ID list gadgets.
Example: Create a new dasboard gadget based on the Team Opportunities report. You will not have the option of filtering by Opportunity -> Assigned To or Company -> Account Manager.
This has been raised to product management as an enhancement request.
It's possible to make this work by customising a view. This is a little hacky, so I wouldn't recommend doing this unless it's absolutely required.
The way we'll do this is by adding 2 dummy fields – one for Opportunity -> Assigned To, another for Company -> Account Manager.
These fields won't contain any data themselves, they'll just be used to provide metadata for values aliased off the Users table.
Here's the properties for the 2 fields. I'd suggest giving them different captions from Assigned To and Account Manager so that you can tell the difference between these and the regular fields.
Entry type: Text
Column name: oppo_AssignedUserIdText
Caption: Assigned User
Max Length: 56
Entry Width: 40
Entry type: Text
Column name: comp_PrimaryUserIdText
Caption: Company Primary User
Max Length: 56
Entry Width: 40
Once these are created, we can edit the view used in the report on which the dashboard gadget is based. I used the Team Opportunities report for my gadget – this is based on vReportOpportunity.
Editing a system view isn't really recommended, but it's possible. So long as we don't hugely change the structure of the view we should be all right.
Please note that changes to a system view may be wiped out on upgrade.
Here's the new view text, showing the aliased fields.
CREATE VIEW vReportOpportunity AS
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,
Oppo_OpportunityId, Oppo_PrimaryCompanyId, Oppo_PrimaryPersonId, Oppo_AssignedUserId,
Oppo_ChannelId, Oppo_Description, Oppo_Type, Oppo_Product, Oppo_Source,
Oppo_Note, Oppo_CustomerRef, Oppo_Opened, Oppo_Closed, Oppo_Status,
Oppo_Stage, Oppo_Forecast, Oppo_Certainty, Oppo_Priority, Oppo_TargetClose,
Oppo_CreatedBy, Oppo_CreatedDate, Oppo_UpdatedBy, Oppo_UpdatedDate,
Oppo_TimeStamp, Oppo_Deleted, Oppo_Total, Oppo_NotifyTime, Oppo_SMSSent,
Oppo_WaveItemId, Oppo_SecTerr, Oppo_WorkflowId, Oppo_LeadID,
Oppo_Forecast_CID, Oppo_Total_CID, oppo_scenario, oppo_decisiontimeframe,
oppo_Currency, oppo_TotalOrders_CID, oppo_TotalOrders, oppo_totalQuotes_CID,
oppo_totalQuotes, oppo_NoDiscAmtSum, oppo_NoDiscAmtSum_CID, Oppo_PrimaryAccountId,
Comp_Name, Comp_Territory, Comp_EmailAddress, Comp_CompanyId, Comp_SecTerr, Comp_CreatedBy, Comp_PrimaryUserID,
Pers_Title, Pers_EmailAddress, Pers_SecTerr, Pers_CreatedBy, Pers_PersonId, Pers_PrimaryUserID,
(Oppo_Base_Currency.Curr_CurrencyID) AS Oppo_WeightedForecast_CID,
((Oppo_Forecast / Oppo_Forecast_Currency.Curr_Rate) * Oppo_Certainty / 100) AS Oppo_WeightedForecast,
Oppo_PrimaryAccountId AS Acc_AccountId,
RTRIM(ISNULL(U1.User_FirstName, '')) + ' ' + RTRIM(ISNULL(U1.User_LastName, '')) as Oppo_AssignedUserIdText,
RTRIM(ISNULL(U2.User_FirstName, '')) + ' ' + RTRIM(ISNULL(U2.User_LastName, '')) as Comp_PrimaryUserIdText
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
LEFT JOIN Currency Oppo_Base_Currency ON Oppo_Base_Currency.Curr_CurrencyID = (
SELECT CAST(CAST(Parm_Value AS NCHAR) AS INTEGER)
WHERE Parm_Name = 'BaseCurrency')
LEFT JOIN Users U1 ON Oppo_AssignedUserId = U1.User_UserId
LEFT JOIN Users U2 ON Comp_PrimaryUserId = U2.User_UserId
WHERE Oppo_Deleted IS NULL
I've enumerated the fields on the Opportunity table rather than using Opportunity.*. If we were to use Opportunity.*, then it would not work when we tried to filter by Oppo_AssignedUserId. I've included all fields on the Opportunity table, except the dummy field we created earlier.
Once this is done, the new fields will be available for selection on the report and dashboard gadget, and the dashboard gadget can be filtered by the new fields.