A while ago I published an article on how to create custom Service Manager reports –> see here. This post is a follow up article that explains how to use report parameters to filter the displayed results. This can be useful if you want to have filters to only display specific records in your report. Read on to see how this works.
I will again use SQL Report Builder V3 for this. First create a new report that displays some information. In this example I have a very simple reports that displays Domain User information directly from the CMDB. One column shows the domain name where the user accounts lives.
I created a simple query directly for the table where the domain users are stored and get all the records with all the attributes. Normally you would optimize the query a little bit and user views instead of tables, but for a quick example this approach is fine. The records are then displayed in a simple table.
Now let’s add a new parameter to the report, define a name for it, define the data type and some other options. In this case I want to allow the report user to select multiple values if needed.
For the selectable values we have two options. Either we can specify manual values or use a query that gives us back available values. I will show how to define values manually and come back to the query-based approach later. So I just enter some static values that will be selectable when executing the report. If needed, a default value can also be set.
After the completion of the wizard, the new parameter is displayed.
Now we have to reconfigure the table in the report to use the parameter as a filter. By right clicking on the table I can select the table properties.
For defining the filter, I add a new entry and configure the expression. I select the domain field, define the operator (in this case “in” because I defined that multiple values can be selected) and then define the value. The value will be the user selection. Using the button, the parameter can be selected (double click).
When the report is run, the parameters are available and can be selected as needed.
The available report parameter values are working great, but the values are static. There may be times where you want to have dynamic values available to select. This is indeed possible by using queries to populate the selectable values. To make this work, we need to add another data set that gets the available values from somewhere. In this example I want to get all the available domains directly from the CMDB. So I prepared a query that gets those.
By using a “Select Distinct” I make sure that every value is only returned once.
When the data set is ready, it’s now time to reconfigure the Parameter. Instead of using static values, I now change the configuration so that the data set records are available as selectable values.
When now running the report again, the returned values from the query are available and can be used to filter the returned records. But this time it’s dynamic and therefore (most of the time) more useful.
When the report design is complete, deploy the report as explained in the previous post.
cheers
Marcel
marcelzehner.ch 
Pingback: Creating Custom Service Manager Reports | SCSMfaq.ch
Hi Marcel,
Your tutorial works out great in our environment. When you get a chance, could you please provide suggestions on how to resolve the FONT size issue. When I deploy the report in SM, the font size gets seems to get really big unless I change the font size to 100% from the drop-down. Is there a way to set the default font size somewhere so that we don’t encounter such issue? Thank you,
Vinh
Dear Marcel,
Thanks for the effort in explaining this. It helped us a lot in figuring out many other things. I have a situation and wonder if you came across such a scenario. I am trying to create a new report of extended work item where that report should be the same as the related original OOTB work item report with addition of the extenstion reflected as a new column. When I try to copy the orginial .rdl file and then try to run it in SQL reports builder I can see that the list of parameters is way more than what I have seen in the original report. Why I do not just get the list of the same parameters during run time. If you go on this gentleman blog http://social.technet.microsoft.com/wiki/pt-br/contents/articles/13305.customizing-scsm-reports-using-extended-fields-on-an-management-pack.aspx
you could see on step 5 the extra parameters that I was talking about. I just wanted to add one more parameter but I am ended with many more. I wonder if copying would not copy the parameters properties set already which made those parameters hidden or captioned differently in the original reports.
Any help would be much appreciated
Cheers
Mark
Hi Marcel,
How we can can put Start and End Date parameters to a form so that we can easily filter according to the created date of incidents?
Hey
I dont have an article for that, but check out this TechNet article, that should explain the trick. http://technet.microsoft.com/en-us/library/dd255194(v=sql.100).aspx
Cheers
Marcel
Ok I found it. Bu this time in my report there are duplicate entries for the same work item because it has been assigned twice. How to eliminate and show the latest assignment entry in the custom reports?
Pingback: Create a report for “Support Group Changes” | marcelzehner.ch
ALTER PROCEDURE [dbo].[ServiceManager_Report_Incidents]
@Classification nvarchar(max) = ‘-1’,@SupportGroup nvarchar(max) = ‘-1’,@ID nvarchar(max) = null,@IncludeDeleted bit = 0,@LanguageCode nvarchar(max)= ‘ENU’
AS
DECLARE @tableClassification TABLE (value nvarchar(256))
INSERT @tableClassification (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Classification)
DECLARE @tableSupportGroup TABLE (value nvarchar(256))
INSERT @tableSupportGroup (value)
SELECT * FROM dbo.fn_CSVToTableInt(@SupportGroup)
DECLARE @tableID TABLE(value nvarchar(256))
INSERT @tableID (value)
Select * FROM dbo.fn_CSVToTableString(ISNULL(@ID, ”))
SELECT DISTINCT
I.Id,
I.Title,
Classification = ISNULL(ClassDS.DisplayName,ClassEnum.IncidentClassificationValue),
ClassEnum.IncidentClassificationId AS ClassificationId,
ClassEnum.ParentId,
Support = ISNULL(SupportDS.DisplayName,SupportEnum.IncidentTierQueuesValue),
SupportEnum.IncidentTierQueuesId AS SupportId,
Status = ISNULL(StatusDS.DisplayName, StatusEnum.IncidentStatusValue) ,
StatusEnum.IncidentStatusId AS StatusId,
AssignedTo.DisplayName AssignedToUserName,
AssignedTo.UserDimKey AssignedToUserId
FROM dbo.IncidentDimvw I
INNER JOIN dbo.WorkItemDimvw WI ON I.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN dbo.WorkItemAboutConfigItemFactvw ON dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.WorkItemAboutConfigItemFactvw CIFctForFilter ON CIFctForFilter.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR CIFctForFilter.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.IncidentClassificationvw AS ClassEnum ON I.Classification_IncidentClassificationId = ClassEnum.IncidentClassificationId
LEFT OUTER JOIN dbo.DisplayStringDimvw ClassDS ON ClassEnum.EnumTypeId=ClassDS.BaseManagedEntityId AND ClassDS.LanguageCode = @LanguageCode
LEFT OUTER JOIN dbo.IncidentTierQueuesvw AS SupportEnum ON I.TierQueue_IncidentTierQueuesId = SupportEnum.IncidentTierQueuesId
LEFT OUTER JOIN dbo.DisplayStringDimvw SupportDS ON SupportEnum.EnumTypeId=SupportDS.BaseManagedEntityId AND ClassDS.LanguageCode = @LanguageCode
LEFT OUTER JOIN dbo.IncidentStatusvw AS StatusEnum ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN dbo.DisplayStringDimvw StatusDS ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId AND StatusDS.LanguageCode = @LanguageCode
LEFT OUTER JOIN dbo.WorkItemAssignedToUserFactvw ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN dbo.UserDimvw AS AssignedTo ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
WHERE
((-1 IN (Select value from @tableClassification)) OR (I.Classification_IncidentClassificationId IN (Select value from @tableClassification)))
AND
((-1 IN (Select value from @tableSupportGroup)) OR (I.TierQueue_IncidentTierQueuesId IN (Select value from @tableSupportGroup)))
AND
((@ID IS NULL) OR (I.Id IN (Select value from @tableID)))
— exec ServiceManager_Report_Incidents @Classification=N’281,415,9,135′,@SupportGroup=N’72,-1,69,43′,@ID=N’IR55145′
Can you share my how to track first response against incident or ticket
?
In Sql Or Query through ?