If I need to write a report utilising temp tables e.g.
SELECT IncidentNumber,RecId, CreatedDateTime INTO #mytable FROM Incident;
will this work in HEAT Cloud reports?
As long as a user can connect to a SQL Server instance, the user is able to create temporary tables. There are no special permissions. A login with the most basic permissions has full rights to create and use temporary tables.