Worker Report not Running
hermant
April 15th, 2009
Howdy All,
I came across an article on a great helpdesk report. I can't get it working and hope ya'll can help.
https://www-secure.symantec.com/connect/articles/advanced-report-helpdesk
I have made some changes so I can get the report to pull my groups.
Paramaters:
Team paramater:
Name: team
Parameter type: Dropdown
List Type: Query Results
value Type: String
Value:
USE ALTIRIS_INCIDENTS
select distinct queue_name
from dbo.worker_view
where queue_name IS NOT NULL
Parameter type: Dropdown
List Type: Query Results
value Type: String
Value:
USE ALTIRIS_INCIDENTS
select distinct queue_name
from dbo.worker_view
where queue_name IS NOT NULL
Name: begindate
Parameter type: Date/Time
Type: Short Date
Prompt: begindate
Name: enddate
Parameter type: Date/Time
Type: Short Date
Type: Short Date
Prompt: enddate
I have also made changes to the code trying to get it to work the error I get is
Heres the Code:
/*SET dateformat mdy
DECLARE @begindate DATETIME,
@enddate DATETIME
SET @begindate = CAST('%begindate%' AS DATETIME)
SET @enddate = CAST('%enddate%' AS DATETIME)*/
USE ALTIRIS_INCIDENTS
SELECT
wrkr.queue_name AS 'Team',
wrkr.worker_name AS 'Worker Name',
abc.TeamAverage,
abc.TeamIncidents,
COUNT (*) AS 'Indiv. # of incidents',
COUNT (*) - abc.TeamAverage AS 'Difference',
(cast(COUNT (*) as money) / cast(abc.TeamIncidents as money)) * 100 as '% for individual'
FROM dbo.workitem_current_view hd1
INNER JOIN dbo.worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
INNER JOIN
(
SELECT
queue_name,
count(queue_name) AS 'number_in_queue'
FROM dbo.worker_view wrkr
WHERE
[queue_name] IS NOT NULL
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY [queue_name]
) X
ON X.[queue_name] = wrkr.[queue_name]
INNER JOIN
(
SELECT
wrkr.queue_name as 'queue',
COUNT(wrkr.queue_name) / X.number_in_queue AS 'TeamAverage',
COUNT (*) AS 'TeamIncidents'
FROM dbo.workitem_current_view hd1
INNER JOIN dbo.worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
INNER JOIN
(
SELECT
queue_name,
count(queue_name) AS 'number_in_queue'
FROM dbo.worker_view wrkr
WHERE [queue_name] IS NOT NULL
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY [queue_name]
) X
ON X.[queue_name] = wrkr.[queue_name]
WHERE (hd1.[workitem_created_on] BETWEEN '%begindate%' AND '%enddate%')
AND hd1.[workitem_status_lookup_value] = 'Closed'
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY
wrkr.queue_name,
X.number_in_queue
) abc
ON abc.queue = wrkr.queue_name
WHERE (hd1.[workitem_created_on] BETWEEN '%begindate%' AND '%enddate%')
AND hd1.[workitem_status_lookup_value] = 'Closed'
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY
wrkr.queue_name,
X.number_in_queue,
wrkr.worker_name,
abc.TeamAverage,
abc.TeamIncidents
ORDER BY wrkr.queue_name, count(*) desc
SELECT
wrkr.queue_name AS 'Team',
wrkr.worker_name AS 'Worker Name',
abc.TeamAverage,
abc.TeamIncidents,
COUNT (*) AS 'Indiv. # of incidents',
COUNT (*) - abc.TeamAverage AS 'Difference',
(cast(COUNT (*) as money) / cast(abc.TeamIncidents as money)) * 100 as '% for individual'
FROM dbo.workitem_current_view hd1
INNER JOIN dbo.worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
INNER JOIN
(
SELECT
queue_name,
count(queue_name) AS 'number_in_queue'
FROM dbo.worker_view wrkr
WHERE
[queue_name] IS NOT NULL
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY [queue_name]
) X
ON X.[queue_name] = wrkr.[queue_name]
INNER JOIN
(
SELECT
wrkr.queue_name as 'queue',
COUNT(wrkr.queue_name) / X.number_in_queue AS 'TeamAverage',
COUNT (*) AS 'TeamIncidents'
FROM dbo.workitem_current_view hd1
INNER JOIN dbo.worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
INNER JOIN
(
SELECT
queue_name,
count(queue_name) AS 'number_in_queue'
FROM dbo.worker_view wrkr
WHERE [queue_name] IS NOT NULL
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY [queue_name]
) X
ON X.[queue_name] = wrkr.[queue_name]
WHERE (hd1.[workitem_created_on] BETWEEN '%begindate%' AND '%enddate%')
AND hd1.[workitem_status_lookup_value] = 'Closed'
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY
wrkr.queue_name,
X.number_in_queue
) abc
ON abc.queue = wrkr.queue_name
WHERE (hd1.[workitem_created_on] BETWEEN '%begindate%' AND '%enddate%')
AND hd1.[workitem_status_lookup_value] = 'Closed'
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY
wrkr.queue_name,
X.number_in_queue,
wrkr.worker_name,
abc.TeamAverage,
abc.TeamIncidents
ORDER BY wrkr.queue_name, count(*) desc
Any help would be greatly apreciated.
Try This
Hi Hermant,
I think it's not liking these two lines:
SET @begindate = CAST('%begindate%' AS DATETIME)
SET @enddate = CAST('%enddate%' AS DATETIME)
The error message saying that it can't convert from string to DATETIME. Try this instead:
SET @begindate = %begindate%
SET @enddate = %enddate%
Let me know if this works
thanks
thanks for the help. I went ahead and changed the code and I am still getting an error.
Sql error in query. Error: System.Data.SqlClient.SqlError: Conversion failed when converting datetime from character string.Sql CommandText:
Any other Ideas would be greatly appreciated. thanks again.
RE: Thanks
Hermant,
Sorry it took me a while to get back to you. I tried your query and it works just fine in my environment! I did copy and paste exactly of what you have. I'm attaching the XML version of the report. Try just importing it to your NS and see if it works. You may need to change the NS GUID in the XML file.
Let me know if it works for you...
<item guid="{a097d316-9681-44a5-8937-8ea0b62c25d5}" classGuid="{67033a4e-a848-4a0b-b037-ab02854a315a}">
<!-- Type: Altiris.NS.StandardItems.Query.Report -->
<!-- Assembly: Altiris.NS.StandardItems, Version=6.0.6074.70, Culture=neutral, PublicKeyToken=d516cb311cfb6e4f -->
<name>Incident History</name>
<alias />
<ownerNSGuid>{12bee4c0-951a-4830-8ba5-38325244e4d6}</ownerNSGuid>
<productGuid>{d0e33520-c160-11d2-8612-00104b74a9df}</productGuid>
<itemAttributes>Normal</itemAttributes>
<itemLocalizations>
<culture name="">
<description>Please enter the report description</description>
<name>Incident History</name>
</culture>
<culture name="en">
<description>Please enter the report description</description>
</culture>
</itemLocalizations>
<scheduling>
<enabled>False</enabled>
<sharedSchedule>{00000000-0000-0000-0000-000000000000}</sharedSchedule>
</scheduling>
<version>6.0</version>
<manufacturer />
<alwaysPromptParameters>True</alwaysPromptParameters>
<queries>
<parameter type="dropdown" listType="queryresults" prompt="True" name="team" substituted="true">
<valueType><![CDATA[NVarChar]]></valueType>
<prompt><![CDATA[Team]]></prompt>
<query><![CDATA[USE ALTIRIS_INCIDENTS
select distinct queue_name
from dbo.worker_view
where queue_name IS NOT NULL ]]></query>
</parameter>
<parameter type="custom" assemblyName="Altiris.NS.StandardItems, Version=6.0.6074.70, Culture=neutral, PublicKeyToken=d516cb311cfb6e4f" typeName="Altiris.NS.StandardItems.Query.DateTimeParameter" prompt="True" name="begindate">
<default><![CDATA[]]></default>
<valueType><![CDATA[DateTime]]></valueType>
<prompt><![CDATA[begindate]]></prompt>
<format>ShortDate</format>
<useUpDown>true</useUpDown>
</parameter>
<parameter type="custom" assemblyName="Altiris.NS.StandardItems, Version=6.0.6074.70, Culture=neutral, PublicKeyToken=d516cb311cfb6e4f" typeName="Altiris.NS.StandardItems.Query.DateTimeParameter" prompt="True" name="enddate">
<default><![CDATA[]]></default>
<valueType><![CDATA[DateTime]]></valueType>
<prompt><![CDATA[enddate]]></prompt>
<format>ShortDate</format>
<useUpDown>true</useUpDown>
</parameter>
<query type="rawSqlQuery">
<view type="grid">
<enabled>True</enabled>
<default>True</default>
<hide><![CDATA[Guid]]></hide>
</view>
<query><![CDATA[/*SET dateformat mdy
DECLARE @begindate DATETIME,
@enddate DATETIME
SET @begindate = CAST('%begindate%' AS DATETIME)
SET @enddate = CAST('%enddate%' AS DATETIME)*/
USE ALTIRIS_INCIDENTS
SELECT
wrkr.queue_name AS 'Team',
wrkr.worker_name AS 'Worker Name',
abc.TeamAverage,
abc.TeamIncidents,
COUNT (*) AS 'Indiv. # of incidents',
COUNT (*) - abc.TeamAverage AS 'Difference',
(cast(COUNT (*) as money) / cast(abc.TeamIncidents as money)) * 100 as '% for individual'
FROM dbo.workitem_current_view hd1
INNER JOIN dbo.worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
INNER JOIN
(
SELECT
queue_name,
count(queue_name) AS 'number_in_queue'
FROM dbo.worker_view wrkr
WHERE
[queue_name] IS NOT NULL
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY [queue_name]
) X
ON X.[queue_name] = wrkr.[queue_name]
INNER JOIN
(
SELECT
wrkr.queue_name as 'queue',
COUNT(wrkr.queue_name) / X.number_in_queue AS 'TeamAverage',
COUNT (*) AS 'TeamIncidents'
FROM dbo.workitem_current_view hd1
INNER JOIN dbo.worker_view wrkr
ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]
INNER JOIN
(
SELECT
queue_name,
count(queue_name) AS 'number_in_queue'
FROM dbo.worker_view wrkr
WHERE [queue_name] IS NOT NULL
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY [queue_name]
) X
ON X.[queue_name] = wrkr.[queue_name]
WHERE (hd1.[workitem_created_on] BETWEEN '%begindate%' AND '%enddate%')
AND hd1.[workitem_status_lookup_value] = 'Closed'
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY
wrkr.queue_name,
X.number_in_queue
) abc
ON abc.queue = wrkr.queue_name
WHERE (hd1.[workitem_created_on] BETWEEN '%begindate%' AND '%enddate%')
AND hd1.[workitem_status_lookup_value] = 'Closed'
AND (wrkr.[queue_name] LIKE %team%)
GROUP BY
wrkr.queue_name,
X.number_in_queue,
wrkr.worker_name,
abc.TeamAverage,
abc.TeamIncidents
ORDER BY wrkr.queue_name, count(*) desc]]></query>
</query>
</queries>
<parentFolderGuid>3a854cd5-05c6-44ce-b18f-2dd8ca75377a</parentFolderGuid>
<security owner="S-1-5-21-1299907705-492924282-1846952604-15202" inherit="True">
<aces>
<ace type="trustee" sid="S-1-5-21-1299907705-492924282-1846952604-15202" name="WHP\Mabdelnabi">
<permissionGrants>
<permissionGrant guid="{f57658a3-865e-47d8-8a77-0210ade119b0}" name="Run Report" />
<permissionGrant guid="{0bef78d6-13f0-4f9e-9a56-1ba2c7a5dbe1}" name="Save Reports" />
<permissionGrant guid="{ac296df1-eb40-4592-899f-25d5c07d45f6}" name="Write" />
<permissionGrant guid="{819dae1e-b1a5-4643-81a1-26ef95feb8a8}" name="Change Permissions" />
<permissionGrant guid="{93d33ee3-5703-4a24-ac4f-4dbe460b3b83}" name="Run Reports" />
<permissionGrant guid="{983a2d22-7a82-4db0-a707-52c7d6b1441e}" name="Read" />
<permissionGrant guid="{eca6254f-5017-4730-9b3f-5add230829b7}" name="Delete" />
<permissionGrant guid="{726b1c09-7108-450d-ae24-5f8e93135ed6}" name="Clone" />
<permissionGrant guid="{4ddc04c3-f0a5-4e88-84aa-c44c8c5ebcc4}" name="Read Permissions" />
</permissionGrants>
</ace>
</aces>
</security>
<itemReferences />
</item>
trying your report
Ok For some reason importing yours works. I can run it. Some times Altiris is just nuts.
Thanks for all your help.
RE:trying your report
I call that job security :)
Glad I can help...
Would you like to reply?
Login or Register to post your comment.