Query in DirectQuery

[miltenburger]

Hi all,

Hope you guys are alright. I have a question about the DirectQuery method since my database is to big to import.

Now we are using a sql statement, by using the sql report editor to show some facts about last weeks employees.

My question is,

Can i use following query directly in DirectQuery to import the right data and how does it work?

Spoiler

SET DATEFIRST 1 ;

WITH temp ( Account , DayOfWeek , Duration , TickID , WeekNumber , Year) AS

(

SELECT [User]

      ,DatePart(dw,[CreatedDateTime])

      ,[Minutes]

      ,[TicketNumber]

      ,DatePart("ww",[CreatedDateTime]) AS WeekNumber

      ,DatePart("yyyy",[CreatedDateTime]) AS Year

  FROM [ksubscribers].[kasadmin].[vSDTicketNotes]

),

temp1 (Account_Closed , WKNr_Closed , Y_Closed , Amount_Closed) AS

(

SELECT [Assignee]

      ,DatePart("ww",[Closed]) AS WeekNumber

      ,DatePart("yyyy",[Closed]) AS Year

      ,Count(*)

  FROM [ksubscribers].[kasadmin].[vSDTicket]

  GROUP BY [Assignee] , DatePart("ww",[Closed]) , DatePart("yyyy",[Closed])

)

Select Account , DayOfWeek , SUM(Duration) AS Duration , TickID , COUNT(*) AS Amount , WeekNumber , Year , temp1.Amount_Closed from temp

INNER JOIN temp1 ON temp1.WKNr_Closed = WeekNumber and temp1.Y_Closed = Year and temp1.Account_Closed = Account

WHERE Duration > 0 and Account Like 'itsn%' and TickID like 'CH%' and Year = 2017

GROUP BY Account , WeekNumber , Year , DayOfWeek , TickID , temp1.Amount_Closed

Do you have any ideas?

[miltenburger]

Maybe to make it more clear for you guys.

What i actually want is:

Get data from my Kaseya database (SQL), but i don't want all the tables with all the rows,

i only want data from last 7 days from several tables (This is what the query above does)

[Eric_Zhang · Accepted solution]

---

@miltenburger wrote:

Maybe to make it more clear for you guys.

What i actually want is:

Get data from my Kaseya database (SQL), but i don't want all the tables with all the rows,

i only want data from last 7 days from several tables (This is what the query above does)

---

@miltenburger

I don't have Kaseya DB at hand, per my knowledge on SQL Server, in Power BI, when using DirectQuery mode, the leading SET statement and CTE expressions are not supported, you'll have remove the SET statement and replace CTEs with derived tables.