Forgive me if this is answered already but I can't seem to find a solution...
My Subject title is basically what I am trying to accomplish.
I need to enable the "__SelectAll__" function in my Slicer but I can't seem to update my code correctly.
Can someone please help me with that!
VDataCenter =
if
//check to see if the parameter is a list
Type.Is(
Value.Type(PCostCenter),
List.Type
) then
//if it is a list
let
//add single quotes around each value in the list
AddSingleQuotes = List.Transform(
PCostCenter,
each "'" & _ & "'"
),
//then turn it into a comma-delimited list
DelimitedList = Text.Combine(
AddSingleQuotes,
","
)
in
DelimitedList
else
//if the parameter isn't a list
//just add single quotes around the parameter value
"'"&PCostCenter&"'",
Source = Oracle.Database("RPTU", [HierarchicalNavigation=true, Query="select n_year,sum(total) total_amount,
sum(comp_amount) compensation_amount,
sum(op_exp_amount) operating_expense_amount,
sum(var_exp_amount) variable_expense_amount
from (
select n_year, sum(actual_amount) as total,
case when acc_level_1 = 'E7275' then sum(actual_amount) else 0 end comp_amount,
case when acc_level_1 = 'E7420' then sum(actual_amount) else 0 end op_exp_amount,
case when acc_level_1 = 'E7698' then sum(actual_amount) else 0 end var_exp_amount
from fcstexpense.TBL_GLVAR_QTR
where acc_level_0 = 'E7265'
and acc_level_1 in ('E7275','E7420','E7698')
and n_year= '"&PYEAR&"'
and vch_quarter in("&Quarters&")
and center_member IN (SELECT DISTINCT vchmembername
FROM XXPRUWH.tblHierpointAncestry
WHERE vchHierarchy='Current_C:Total Center - Balanced'
AND vchsumtomembername in ("&Vsummary&")
AND chmemberpointtype = 'D')
and center_member in ("&VDataCenter&")
and productgroup_member IN (select distinct B.vchMemberName vchMemberName
from xxpruwh.tblHierSumNav A, xxpruwh.tblHierDataNav B
where A.vchHierarchy = B.vchHierarchy
and A.vchSumToMemberName='G0001' --- Product Filter
and A.vchMemberName = B.vchSumToMemberName
and A.vchHierarchy = 'Current_P:Product Group')
and entity_member IN (select distinct B.vchMemberName vchMemberName
from xxpruwh.tblHierSumNav A, xxpruwh.tblHierDataNav B
where A.vchHierarchy = B.vchHierarchy
and A.vchSumToMemberName='P0000' --- Entity Filter
and A.vchMemberName = B.vchSumToMemberName
and A.vchHierarchy = 'Current_E:Profit Center')
group by n_year,acc_level_1)
group by n_year"])
in
Source
