Tag: System Centre

Hi All,

I know it has been a little while.

The reports built into Service Manager are great, however when you try to get a bit creative and automate some of the reports, you are going to run into some issues. So to work around theses issues, I did the following.

Create a custom report from SQL Reporting Services and modified for my needs, I was focusing more on “Incident Analyst” and I was looking for a breakdown of the number of Incidents per Analysts in a week period. This becomes tricky as the Analyst wanted this to be automated for reporting purposes.

1. If you using SQL 2008 R2, Report Builder 3 will be available to you from the SSRS webpage.

2. Select File, Open, drill down to System Center\ServiceManager\ServiceManager.Console.Reporting.IncidentManagement and select

ServiceManager.Report.IncidentManagement.IncidentAnalystReport

This time it opens OK – let’s try and do a “Save As”, just to see what we can do with a report.

The first error is complaining about a mismatch between allowed values and the default for the TimeZone parameter:

To “correct this” go to Parameters, TimeZone – double-click it.

Select the Default Values and change the option to No default Value:

Click OK and select Save As (Don’t forget to change the report name again as it would appear that when a failed save occurs then the original name is used – and thus you may make a change to the default report).

The next error again concerns a defaultvalue for the EndDate_OffsetValue parameter this time.

Again change the Default Value of this parameter to No Default Value

Again attempt the Save As (again check the name).  This time it should succeed.

So now we effectively have a report that we can manipulate to utilise a variable parameter for the StartDate and EndDate values.

Double-click the StartDate_BaseValue parameter. Click the Default Values option and select “Specify values”

Click Add and strike the (Null) that appears in the Value form.

Click the function button

In the Category chooser, select “Common Functions” “Date & Time”

Locate the “Today” item and double-click it.

Close the bracket in the “Set expression for Value option:

Click OK.

Repeat this step for the EndDate_BaseValue Parameter.

Now make the change to the StartDate_OffsetType to switch the interval from the past month to the past week.

Double-click StartDate_OffsetType, select Default Values and change the Value option from Month to Week.

So back to Report Manager – drill down to the new report and open it up.  Run the report using the View Report button – no results returned.

Check the NULL option for Incident IDs

And now re-run – data as expected.  The Challenge now is to select the NULL option for a scheduled report as opposed to the interactive one.

Click “New Subscription”

Populate the Report Delivery Options and Subscription Processing Options as required  – I used a File Share (NB it needs a UNC \\server\share) and just set up a schedule in 5 mins time to use an HTML 4 report.  (NB You will need to repeat the password for the account that will write the report after you have clicked the schedule button as it loses the password and saving the subscription will fail)

Then in the Report Parameter Values ensure the following two options are selected.  NB Do not set these in the Properties, Parameters option of Report Manager as they have a different impact.

Save the changes and wait for the report to run – this should now pick up the correct “current” date and provide a week’s worth of data!

3. Now, you try to run the report and under “Assigned To User”, you try to use UPN names or Display Names and nothing seems to work! This is because the Report is actually looking for a UserDimkey, this can be seen in this post. So how do you get this information. SQL is your friend.

4. Open SQL Management Studio on the Data Warehouse Server and run the following command substituting the User Display name you want to find the UserDimKey for.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [AssetStatus]
      ,[AssetStatus_ConfigItemAssetStatusId]
      ,[BaseManagedEntityId]
      ,[BusinessPhone]
      ,[BusinessPhone2]
      ,[City]
      ,[Company]
      ,[Country]
      ,[Department]
      ,[DisplayName]
      ,[DistinguishedName]
      ,[Domain]
      ,[EmployeeId]
      ,[EntityDimKey]
      ,[Fax]
      ,[FirstName]
      ,[FQDN]
      ,[HomePhone]
      ,[HomePhone2]
      ,[Initials]
      ,[InsertedBatchId]
      ,[IsDeleted]
      ,[LastName]
      ,[Mobile]
      ,[Notes]
      ,[ObjectStatus]
      ,[ObjectStatus_ConfigItemObjectStatusId]
      ,[Office]
      ,[Pager]
      ,[SID]
      ,[SourceId]
      ,[State]
      ,[StreetAddress]
      ,[Title]
      ,[UpdatedBatchId]
      ,[UPN]
      ,[UserDimKey]
      ,[UserName]
      ,[Zip]
  FROM [DWDataMartR2].[dbo].[UserDimvw] where [DisplayName] = ‘DisplayNameofUserInActiveDirectory

5. Once you have this value(s), you can add these value(s) to your report and schedule them successfully.

Hope this helps,

Follow me