Use Microsoft SSRS for reporting?

[OptusIT]

Is it possible to use an alternate report writing tool to write help desk reports? We use Microsoft SSRS throughout our company for other reporting needs and would like to write some of our help desk reports using this tool as well.

[AZHockeyNut]

I use SQL Management studio to make most of my reports only when I get the output close do I paste it into LS's report tool. Generally this is because my SQL is terrible and I have to lean on my DBA or DR. Google to figure it out and it is a lot easier for me in that tool then the LS one.

[jacob_bks]

ha - sorry, that's a snippet of my report I included as an example...

[jacob_bks]

For more complicated reports, I use SSMS query analyzer on the server, make my queries, and from there, insert it in to the database - as the report writer/GUI does not work with some SQL syntax/functions.

Here's what they told me for my support ticket




• Make a backup of your database
• Run the first query below and insert a Reportquery name and Reporttitle:

Insert Into tsysreports ( [Reportquery],[Reporttitle]) Values('MyReportQueryName', 'MyReportTitle')


• Once the first query has been run we can run a second query to insert your report query to the database. Replace the green highlighted query with your query do note that you may have to replace any single quotes in the report query with double quotes to avoid any issues to insert it. Finally replace the yellow highlighted code with the Reportquery name that you gave in previously:

Insert Into tsysDBobjects(Query, DBobjName) Values('Select distinct assetid,backedupto = STUFF((Select distinct tblassets.assetid,'', '' + tblAssets4.assetname as backedupto
from tblAssets
left Join (Select distinct tsysAssetRElationTypes.Name, tsysAssetRelationTypes.RelationTypeID,tblassets.assetname, tblAssetRelations.ParentAssetID,tblAssetRelations.ChildAssetID from tblAssets
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID = tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID = tblAssetRelations.Type
Where tsysAssetRelationTypes.Name = ''backed up to'') As RR
On RR.ChildAssetId = tblassets.assetid
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID = tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID = tblAssetRelations.Type
Left Join tblAssets tblAssets4 On tblAssets4.AssetID = rr.ParentAssetID
Where tsysAssetRelationTypes.Name = ''backed up to'' and tblassets.assetid=ttt.assetid
group by '', '' + tblassets4.assetname, tblassets.assetid
FOR XML PATH(''''), TYPE).value(''.[1]'', ''nvarchar(max)''), 1, 4, '''')
from
tblassets as TTT
Group by assetid', 'MyReportQueryName')



• The report can now be found in the Lansweeper Reports section and be run.

[jkot]

Ok, Explain how you have reported HelpDesk Ticket Notes from SSRS. I don't see that this is even stored in the DB tables. At least I cannot find it if it is.

[endyk]

jkot wrote:
Ok, Explain how you have reported HelpDesk Ticket Notes from SSRS. I don't see that this is even stored in the DB tables. At least I cannot find it if it is.

This query should get you started. If you need an SSRS report, let me know.

select
t.ticketid
,t.[subject]
,t.[date] as ticket_date
,t.[priority]
,t.deadline
,n.[date] as note_date
,n.note
,case n.notetype when 1 then 'Public Note' when 2 then 'Internal Note' when 3 then 'Initial Ticket' end as note_type
,case n.servicechange when 'true' then 'Closed' when 'false' then 'Not Closed' end as service_change
,u.[name] as first_last_name
,u.email as user_email
,u.username
,u.[description]
from htblnotes n
left join htblticket t on t.ticketid = n.ticketid
left join htblusers u on u.userid = n.userid
order by ticketid,note_date

[endyk]

Hello,

SSRS is a great reporting tool and yes, it is indeed possible to write reports using an external tool such as SSRS. I currently create SSRS reports from my Lansweeper installation.

One thing to remember is to specify no locks on your query to eliminate any chances of a deadlock on any other query running against the Lansweeper database.

If you need further help with setting up SSRS reports, let me know.

Regards,

Endy

[OptusIT]

Can we get any further detail on this request?

[jkot]

Is it possible to see Helpdesk Ticket Notes in SSRS?

[OptusIT]

Just to clarify. I want to actually run reports from a different tool, like Microsoft Reporting Services or perhaps Microsoft Access. Is it possible to directly access the SQL database that Lansweeper uses from outside the Lansweeper application to do reporting?