Export custom reports (SQL Server only)

[Filip_V]

Generate SQL queries to recreate your custom reports on another database. This code is designed for SQL Server and will not work on SQL Compact.

SELECT 'INSERT INTO [tsysreports]
      ([Reportquery]
      ,[Reporttitle]
      ,[Sendmail]
      ,[Mailgroup]
      ,[Created]
      ,[LastChanged]
      ,[CreatedBy]
      ,[ChangedBy]
      ,[LastRun]
      ,[Permissions]
      ,[Total])
VALUES
      ('''+Reportquery+''',
      '''+Reporttitle+''',
      '+coalesce(''''+Convert(NVARCHAR, Sendmail)+'''', 'null')+',
      '+coalesce(''''+Mailgroup+'''', 'null')+',
      '+coalesce(''''+Convert(NVARCHAR, Created)+'''', 'null')+',
      '+coalesce(''''+Convert(NVARCHAR, LastChanged)+'''', 'null')+',
      '+coalesce(''''+CreatedBy+'''', 'null')+',
      '+coalesce(''''+ChangedBy+'''', 'null')+',
      '+coalesce(''''+Convert(NVARCHAR, LastRun)+'''', 'null')+',
      '+coalesce(''''+Permissions+'''', 'null')+',
      '+coalesce(''''+Convert(NVARCHAR, Total)+'''', 'null')+'
)' TsysReports,
OBJECT_DEFINITION (OBJECT_ID(Reportquery)) [View] 
FROM tsysreports
WHERE Created IS NOT NULL

[rader]

I recently had to run @Susan_A 's custom report extraction to repopulate another installation and I have one recommendation to the process.

If you run the report and export it via excel, you can do a search/replace on the "CREATE VIEW dbo.web..." script in column B to get just the SQL code you need to import back into another install of Lansweeper. Of course this is a good way to have a backup of the custom reports you've accumulated over time.

My search/replace string is as follows: Search for "CREATE VIEW dbo.web* AS " and Replace is "" (blank). This gets most or all of the beginning of the script statement and leaves you with the starting "Select Top 1000000" for each report.

Then it's as simple as copy/pasta into new reports. Bada Bing - Bada Boom.

[Alig26]

How would we import this into the new Database?

[Mercedes_O]

Hello, due to the age of this post I recommend searching through our articles here  Reporting - Lansweeper Community   or request help with a specific report here  in a forum post 

[esr]

Thank you very much-
This short bit of code is worth it's weight in gold!

I'd go so far as to suggest that it should be a default report in Lansweeper.

[Susan_A]

A sample report can be seen below. It lists the display names of custom reports, as well as the scripts used to create the report views.

Select Top 1000000 tsysreports.Reporttitle As Title,
  OBJECT_DEFINITION(Object_Id(tsysreports.Reportquery)) ViewScript
From tsysreports
Where tsysreports.Created Is Not Null
Order By Title

[jmje]

Susan.A wrote:
A sample report can be seen below. It lists the display names of custom reports, as well as the scripts used to create the report views.

Select Top 1000000 tsysreports.Reporttitle As Title,
  OBJECT_DEFINITION(Object_Id(tsysreports.Reportquery)) ViewScript
From tsysreports
Where tsysreports.Created Is Not Null
Order By Title

Best report ever made. This has saved me so much time. 🙂

[friedbad]

Is there a way to query just the Report Title and Query off in a report? I'm looking for the title and actual sql query (not the webxxxx name) text as a backup for all reports.