Reporting with parameters-queries on Urgency and Impact issue
I am building a report for Helpdesk and I get the following strange behaviour (as I do not understand)
I use the following code to generate a pulldown for Priority and Status:
SELECT value, value [value1], 1
FROM dbo.HD_workitem_priority_lookup (or FROM dbo.workitem_status_lookup)
WHERE status='a'
UNION ALL
SELECT isnull(dbo.fnLocalizeString('report.--any--', '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), '--Any--'), '%' , 0
ORDER BY 3,2
This seems to work as I can use these as parameters.
When I do the same for Impact and Urgency, I get:
! Failed to generate the report. This report is based upun data that is not present in your database.....
??????
So I translate the Altiris SQL to normal SQL:
SELECT value, value [value1], 1
FROM workitem_urgency_lookup (and workitem_impact_lookup and workitem_priority_lookup and workitem_status_lookup)
WHERE status='a'
ORDER BY 3,2
and try it in the QueryBuilder... runs like a charm!
So two questions:
- What is the difference between "Priority"+"Status" in comparison to "Impact"+"Urgency"
- What code do I need to create a pulldown query Parameter for "Impact" and for "Urgency"
Thx!
Comments
SELECT value, value [value1],
SELECT value, value [value1], 1
FROM altiris_incidents.dbo.workitem_urgency_lookup
WHERE status='a'
ORDER BY 3,2
I tested the query above and it seems to work fine. If there are parameters being used for this query I would guess it has something to do with the values you are sending the query or the query being used to pull the parameters. Also I do not see where you are inserting your parameters into your query.
Thx for your reaction,
Thx for your reaction, mccarthy.
Did you test this as a Global Parameter in a Report to get a DropDown filed from where you can choose the "value"?
I already had tried your suggestion myself and as I mentioned before, it works like a charm when you do this in SQL Query Builder... But not as a Global Parameter for Reporting.
For all four tables (Priority, Status, Urgency, Impact) with the "same" SQL_code you get the "same" predictive results when you do it in SQL Query Analyser.
But as Global Parameter this ONLY works for 'Status' and 'Priority' (and 'Category' and others). Not for 'Impact' and 'Urgency'... Why!!!
I also did a recheck in the Out-Of-The-Box reports. NON has a parameter for Urgency or Impact!!!
So there is something tricky here.. Who knows?
Guido
Guido Langendorff
Arrow ECS Netherlands
Netherlands
Select "Mark as Solution" when it has resolved your problem.
Give a "Thumps up" when you like the comment or "Thumps down" when n
When running reports they
When running reports they come from the "Altiris" database not the "Altiris_incidents" database. For some reason there is no Lookup View brought over for Impact or Urgency like there is for Priority, status, and type. I think that is what's getting you.
If you enter your parameter as this:
Select Distinct workitem_urgency_lookup_value From HD_workitem_current_view
It will give you a list of all the values that are in use. Now, that does drop off those that aren't selected, but really if you want valid results that might not be such a bad thing.
Test it out and hit me back if you have issues.
- Matt
Thx for your reply. I tried
Thx for your reply.
I tried the following as "value" for a "DropDown" Global Parameter in my report:
USE [Altiris_Incidents]
SELECT DISTINCT workitem_urgency_lookup_value, workitem_urgency_lookup_value [value1], 1
FROM dbo.HD_workitem_current_view
WHERE status='a'
UNION ALL
SELECT isnull(dbo.fnLocalizeString('report.--any--', '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), '--Any--'), '%' , 0
ORDER BY 3,2
I get the same error: Failed to generate report. blablabla.. You must load the associated data into your database..
Then I removed the USE command (was my own input) and that also did not work: Parameter Query failed
Any other suggestions? All are welcome!
I got another tip to find the proper GUID to replace 77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba. As it could be for Impact and Urgency another GUID has to be used...
Guido Langendorff
Arrow ECS Netherlands
Netherlands
Select "Mark as Solution" when it has resolved your problem.
Give a "Thumps up" when you like the comment or "Thumps down" when n
I have a feeling it's failing
I have a feeling it's failing on the "Where status=a" line, because there isn't a "status" column on the hd_Workitem_current_view table.
- Matt
LogViewer told me so
LogViewer told me so already
But removing the WHERE command does not give me the result I want: the same message: Failed to generate report...
Also taking the information from the workitem_current_view will never work 100% save. As some levels can be inactive or not yet used in the workitem_current_view both values are not stored.
Guido
Guido Langendorff
Arrow ECS Netherlands
Netherlands
Select "Mark as Solution" when it has resolved your problem.
Give a "Thumps up" when you like the comment or "Thumps down" when n
create a view?
You could make a view for Urgency and Impact table.
Just model it after the info in the view for Priority.
- Matt
Will it work?
That wil ldo the trick I guess. Now I see what you mean. Have to discuss this change.
I will come back on this issue wether or no I can do it in this project.
Thx!
Guido Langendorff
Arrow ECS Netherlands
Netherlands
Select "Mark as Solution" when it has resolved your problem.
Give a "Thumps up" when you like the comment or "Thumps down" when n
This works!!!
Putting it all together:
For making a dropdown Global Parameter for Priority, Status and Category you need the following code as "Value":
SELECT value, value [value1], 1
FROM dbo.HD_workitem_XXXXXX_lookup (where XXXXXX is equal to Status, Priority or Category)
WHERE status='a'
UNION ALL
SELECT isnull(dbo.fnLocalizeString('report.--any--', '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), '--Any--'), '%' , 0
ORDER BY 3,2
This is a copy/past from some out-of-the-box reports. Works fine but if you also want to make dropdown's for Urgency and Impact, you need to prepare some things first.
For Status, Priority and Category, views exist in the Altiris-database, for reporting purposes for instance. These views do not exist for Urgency and Impact.
If you want to make Dropdowns Global Parameters for Impact/Urgency, first create the needed views in the Altiris-database using following code:
USE [ALTIRIS] (enter the name you use)
CREATE VIEW dbo.HD_workitem_urgency_lookup
AS
SELECT * FROM [SAP-Altiris_Incidents].dbo.workitem_urgency_lookup
CREATE VIEW dbo.HD_workitem_impact_lookup
AS
SELECT * FROM [SAP-Altiris_Incidents].dbo.workitem_impact_lookup
When this is done, you can use also the Impact- and Urgency lookup values.
!!! Please reward MBHarmon for putting me on this track !!!
This is the solution for it
Guido Langendorff
Arrow ECS Netherlands
Netherlands
Select "Mark as Solution" when it has resolved your problem.
Give a "Thumps up" when you like the comment or "Thumps down" when n
Also your parameter query
Also your parameter query must pull only one value. You have multiple fields being pulled in your parameter query.
Would you like to reply?
Login or Register to post your comment.