Hello Community
I have a report which I have built to use the current selected account. The issue is that the report still selects all records instead of the current selected one. I have created a Parameter named "CRM_FilteredAccount" and set the default value to:
Select * from FilteredAccount
Then I create the query like this:
declare @sql as nVarchar(max)
set @sql = '
SELECT
CRMAF_org.bsv_targetgroupattribute1,
pos.bsv_address_multiline,
pos.bsv_memberlist_memberlistcode,
pos.owneridname,
mbl_bsv_memberlist.bsv_memberlistcode,
pos.bsv_count_german,
pos.bsv_count_french,
pos.bsv_count_italian,
pos.bsv_count_rr,
pos.bsv_count_english
FROM Filteredbsv_memberlist as mbl_bsv_memberlist LEFT JOIN
Filteredbsv_memberrelation as pos ON pos.bsv_memberlist = mbl_bsv_memberlist.bsv_memberlistid LEFT JOIN
(' @CRM_FilteredAccount ') as CRMAF_org ON CRMAF_org.accountid = pos.bsv_organization'
exec(@sql)
To enable the PreFiltering I've set the CRMAV_ Prefix for the FilteredAccount alias and added the parameter to the query like shown above.
So the XML of the Parameter looks like this:
String
true
Select * from FilteredAccount
true
CRM_FilteredAccount
true
And the dataset:
BSV =Parameters!CRM_FilteredAccount.Value declare @sql as nVarchar(max) set @sql = ' SELECT CRMAF_org.bsv_targetgroupattribute1, pos.bsv_address_multiline, pos.bsv_memberlist_memberlistcode, pos.owneridname, mbl_bsv_memberlist.bsv_memberlistcode, pos.bsv_count_german, pos.bsv_count_french, pos.bsv_count_italian, pos.bsv_count_rr, pos.bsv_count_english FROM Filteredbsv_memberlist as mbl_bsv_memberlist LEFT JOIN Filteredbsv_memberrelation as pos ON pos.bsv_memberlist = mbl_bsv_memberlist.bsv_memberlistid LEFT JOIN (' @CRM_FilteredAccount ') as CRMAF_org ON CRMAF_org.accountid = pos.bsv_organization' exec(@sql)
What I dont know is if the CRMAF_ prefix will only be evaluated by dynamics if it is placed right after the FROM in the query. Maybe this construct does not work if the prefilter prefix is part of a join?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,522 Super User 2024 Season 2
Martin Dráb 228,441 Most Valuable Professional
nmaenpaa 101,148