I'm trying to use "New Named Query" to add a table in Data Source View in SSAS. The script is shown as follows:
Declare @AvgInvestment float;
SELECT @AvgInvestment=SUM(Investment)/COUNT(distinct meta_ID)
FROM AAAA
SELECT Player, Investment,
InvestmentRange=
Case When Investment >=0 AND Investment <(@AvgInvestment/3) THEN 1
When Investment >=(@AvgInvestment/3) AND Investment <(4*@AvgInvestment/3) THEN 2
When Investment >=(4*@AvgInvestment/3) AND Investment <(6*@AvgInvestment/3) THEN 3
When Investment >=(2*@AvgInvestment) THEN 4
END
FROM AAAA
However, SSAS does not allow declare variables in SQL Query for DVS. Is there any possible way to modify the SQL statement to have no variables? I tried to replace @AvgInvestment as "SELECT SUM(Investment)/COUNT(distinct meta_ID) FROM AAAA" , but it's not working.
Thanks for any possible solutions!
You can join with your average instead using variable. For example:
SELECT Player, Investment,
InvestmentRange=
Case When Investment >=0 AND Investment <(a.avg/3) THEN 1
When Investment >=(a.avg/3) AND Investment <(4*a.avg/3) THEN 2
When Investment >=(4*a.avg/3) AND Investment <(6*a.avg/3) THEN 3
When Investment >=(2*a.avg) THEN 4
END
FROM AAAA cross join
(select SUM(Investment)/COUNT(distinct meta_ID) as avg from aaaa) a
Works perfectly in Data Source view. Stored procedures won't work there.