Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I received the DECLARE error message stating I can't use declare in a Direct Query. I found out in this forum that a user defined function (UDF) is necessary to make this work: https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/td-p/40983
I then found out from the error message that I can't have dynamic SQL in a UDF.
"Invalid use of a side-effecting operator 'EXECUTE STRING' within a function."
Are there any other options for getting my query to work in a direct query in Power BI? My query is below:
DECLARE @SQL as VARCHAR(MAX) DECLARE @Columns AS VARCHAR(MAX) SELECT @Columns = COALESCE(@Columns + ', ','')+QUOTENAME([MonthYear]) FROM( SELECT distinct cast([MonthName] as varchar) + ' ''' + right(cast([Year] as varchar),2) AS MonthYear, [Year],[Month] FROM gpo.dbo.DateDimension A WHERE [Date] between getdate()-365 AND dateadd(month,-1,getdate())) A order by [Year],[Month] SET @SQL = ' WITH PivotData AS ( SELECT distinct A.[Client Designated Loc Nm] as Client, sum(A.TotalInvoice) as [Total Admin Fee], cast([MonthName] as varchar) + '' '''''' + right(cast([Year] as varchar),2) AS MonthYear FROM GPO.DBO.SALES A INNER JOIN GPO.DBO.datedimension B ON A.InvoiceDate=B.[Date] INNER JOIN RXSS.DBO.SupplierContractNbrs C ON A.SupplierID=C.SupplierID WHERE A.[Status] = ''A'' and C.ActiveReporting = 1 AND B.[Date] >= getdate()-365 GROUP BY A.SupplierID, A.[Client Designated Loc Nm], A.InvoiceDate, B.[MonthName], B.[Year], B.[MonthYear] ) SELECT * FROM (SELECT Client, ' + @Columns + ' FROM PivotData PIVOT ( sum([Total Admin Fee]) FOR [MonthYear] IN ( ' + @Columns + ' ) ) AS pvt ) D ' EXEC (@SQL)
Hi,
Have someone has a workarround for this issue. I am also struggling with this. Thank you in advance
@Anonymous wrote:
I received the DECLARE error message stating I can't use declare in a Direct Query. I found out in this forum that a user defined function (UDF) is necessary to make this work: https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/td-p/40983
I then found out from the error message that I can't have dynamic SQL in a UDF.
"Invalid use of a side-effecting operator 'EXECUTE STRING' within a function."
Are there any other options for getting my query to work in a direct query in Power BI? My query is below:
May I know why dynamical PIVOT in your case? What is the final goal?
Hi @Anonymous
What about calling the sp_executeSQL proc and passing your dynamic query via that?
Worked for me using Direct Query
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |