Is it possible to make a single report that will show a list of both incidents and service reque

You can make a single report that shows both IR and SR, but you can't combine that into one pivot table by default.

The way I've gotten around it is to add a pivot table to an Excel sheet linked to the Work Items Cube for IRs and then on the same page (generally a couple of cells to the right of the first pivot table to allow for expansion and contraction of the table) I add another pivot table linked to the Service Catalog Cube for SRs.

Or, if you are wanting to make a SSRS report, create a temp table in your stored proc, for example:

DECLARE @tableWorkItems TABLE

(

ID nvarchar(256),

Title nvarchar(256)

)

Then populate this table with incidents:

INSERT into @tableWorkItems

SELECT ID, Title FROM IncidentDimVw WHERE...

Add SRs:

INSERT into @tableWorkItems

SELECT ID, Title FROM ServiceRequestDimVw WHERE...

And simply return your results from the temp table to your report:

SELECT ID,

Title

FROM @tableWor

Thanks.  I just want to make a simple List All Incidents & Service Request report that can be ran from the console.  I'll try creating that with your instructions, Rob.  

Would you mind simplifying this for me? I'm not really experienced with SQL querying.

I am searching around in SQL report builder and am trying to create a report with the WorkItemCreatedByUserFactvw_bulkstg.  It contains the WorkItemCreatedBy_UserDimKey which I was hoping would output the CreatedByUsers name, but its just an ID number.  

If I could get that to reflect the users name, with the list of work items (incidents and service requests) and the date, that's really all I need. 

Thanks,

You should be using WorkItemCreatedByUserFactvw in DWDataMart, not WorkItemCreatedByUserFactvw_bulkstg.

The best way to learn how SCSM reports work is to look at current reports and stored procedures.

For example, take a look at "Programmability\Stored Procedures\dbo.ServiceManager_Report_IncidentManagement_SP_GetIncidentDetails". Right-click Modify.

Find "ICBUFact" and you will see this:

	Left Outer Join dbo.WorkItemCreatedByUserFactvw ICBUFact
		ON ICBUFact.WorkItemDimKey = WI.WorkItemDimKey
		AND ICBUFact.DeletedDate IS NULL

	Left Outer Join dbo.UserDimvw CreatedByUser 
		ON ICBUFact.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey

Then you can return the Created By User to your report by using CreatedByUser.DisplayName.