POWER BI and Declare

  • Hi Guys,

    I have the following code but for some reason Power BI doesn't seem to accept declare.  Does anyone know a way around this?  The error message I get in Power BI is:

    Query1 Microsoft SQL: Incorrect syntax near the keyword 'DECLARE'

    Many thanks

    DECLARE @sd DATETIME, @ed DATETIME;

    -- set the start date to the first day of this month
    SET @sd = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

    -- if it's the first, we want last month instead
    IF DATEPART(DAY, GETDATE()) = 1
    BEGIN
     SET @sd = DATEADD(MONTH, -1, @sd);
    END
    ELSE

     -- set end dates
    IF DATEPART(DAY, GETDATE()) = 1
    SET @ed = DATEADD(MONTH, 1, @sd)
    ELSE
    SET @ed = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

    SELECT logfile.Dealer,Dealers.Name
    ,COUNT(*) AS Booked
    , SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END) AS SER
    , SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END) AS MST
    , SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END) AS MOT
    , SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS WAR
    , SUM(CASE PayType WHEN 'INTERNAL' THEN 1 ELSE 0 END) AS [INT]
    ,COUNT(DISTINCT RegNo) AS Vehs

    ,COUNT(*)
    -SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END)
    -SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END)
    -SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END)
    -SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS Other

    , SUM(TotalValue) AS [Value]

    FROM LogFile
    LEFT OUTER JOIN RepairCodes
    ON RepairCode = ServCode
    AND LogFile.Dealer = RepairCodes.Dealer

    JOIN Dealers ON Dealers.Dealer=LogFile.Dealer

    AND dbo.LogFile.Created >= @sd
    AND dbo.LogFile.Created < @ed
    AND DBO.LogFile.Tran1 in ( 'IBB','W3B')
    GROUP BY LogFile.Dealer, Dealers.Name
    order by Dealers.Name

  • I tried to reproduce your problem by using DECLARE inside the query window in PowerBI (after you specify the database etc), and mine worked. Granted, my SQL was a lot simpler... so it's not the DECLARE that's causing the problem. I executed this inside that query window in PowerBi:

    DECLARE @NumRecords INT
    SET @NumRecords = 1+ABS(CHECKSUM(NEWID())%10);

    SELECT TOP (@NumRecords) *
    FROM dbo.SymptomData;

    and mine worked, so there's something else going on in there.  What happens if you execute your T-SQL inside SSMS? It works there, right?.

  • Thanks for coming back to me. Yeah, its works fine in SSMS and also I import the query into excel and it also works fine.

  • Quick question, have you tried separating the declare into two lines?
    😎


    DECLARE @sd DATETIME;
    DECLARE @ed DATETIME;

  • Amazing Eirikur Eiriksson, it now works fine thank you so much

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply