September 16, 2012 at 9:19 am
Hi,
I am trying to work with data from the ProjectServer_Reporting database, which is a part of the Microsoft Project Server 2010 infrastructure. I have now done enough with this data and with SQL Queries in particular, to be dangerous.
Up to this point, I have not worked with multi-value fields in the ProjectServer_Reporting database. I found in a search the information I need to make those work in reports (listing them in a comma separated value column, leveraging a Function).
However, this is where my rusty SQL query skills come into play. I basically don’t know how to exact one column from the separate Function assisted query that I found to include it in the code of my main report dataset (called "dsProject_Detail").
So below, I have listed the code in question, with explantions thoughout on where I'm at.
I have a hunch that I need to somehow do a JOIN to incorporate the 'Business Owner' mutli-value column from my separate query into my overall SSRS primary dataset. If someone is able to show me how that can be done with the code below, it would be a huge help for me.
Any assistance in combining these two queries into one would be most appreciated.
Cheers,
Wayne
1.I found the following Function that will handle the process of listing the multi-value field values in a column:
CREATE FUNCTION [dbo].[GetMultipleValues]
( @EntityUID varchar(255)
)
RETURNS varchar(max)
AS
BEGIN
declare @output varchar(max)
Select @output = COALESCE(@output + ', ', '') + MemberValue
from [MSPLT_Business Owner_Userview] mmu
inner join [MSPCFPRJ_Business Owner_AssociationView] mmuv on (mmu.LookupMemberUID = mmuv.LookupMemberUID)
inner join MSP_EpmProject_UserView mep on (mmuv.EntityUID = mep.ProjectUID)
where mmuv.EntityUID = @EntityUID
return @output
END
GO
2.The accompanying SQL query allows me to pull the detail I need about the ‘Business Owner’ multi-value field that I am using.
SELECT DISTINCT projectname, mmuv.EntityUID,
dbo.GetMultipleValues(mmuv.EntityUID) AS [Business Owner]
FROM msp_epmproject_userview mep
INNER JOIN [MSPCFPRJ_Business Owner_AssociationView] mmuv
ON (mep.ProjectUID = mmuv.EntityUID)
3.This displays fine in a table in SQL Server Management Studio.
4.However, what I would really like is for the column to display in my main Dataset called "dsProject_Detail". The query for that Dataset is:
SELECT p.ProjectUID, p.Project_Status,
p.ProjectName AS [Project Name], p.ProjectModifiedDate AS [Last Modified],
po.ResourceName AS [Owner], p.[Project Category],
p.ProjectStartDate AS [Start], p.ProjectFinishDate AS [Finish],
p.ProjectBaseline0StartDate AS [Baseline Start],
p.ProjectBaseline0FinishDate AS [Baseline Finish],
p.ProjectPercentCompleted AS [Percent Completed],
FROM dbo.MSP_EpmProject_UserView p LEFT OUTER JOIN
dbo.MSP_EpmProjectOwner_OlapView po
WHERE (p.ProjectType = 0 OR p.ProjectType = 5 OR p.ProjectType = 6)
5. I've trimed down the main "dsProject_Detail" dataset query, as it was really long, so I hope I didn't miss out anything important.
September 17, 2012 at 5:53 am
Is this what you want?
SELECT p.ProjectUID, p.Project_Status,
p.ProjectName AS [Project Name], p.ProjectModifiedDate AS [Last Modified],
po.ResourceName AS [Owner], p.[Project Category],
p.ProjectStartDate AS [Start], p.ProjectFinishDate AS [Finish],
p.ProjectBaseline0StartDate AS [Baseline Start],
p.ProjectBaseline0FinishDate AS [Baseline Finish],
p.ProjectPercentCompleted AS [Percent Completed],
mmuv.EntityUID,
dbo.GetMultipleValues(mmuv.EntityUID) AS [Business Owner]
FROM dbo.MSP_EpmProject_UserView p
INNER JOIN [MSPCFPRJ_Business Owner_AssociationView] mmuv
ON (p.ProjectUID = mmuv.EntityUID)
LEFT OUTER JOIN
dbo.MSP_EpmProjectOwner_OlapView po -- THERE's NO JOIN CONDITION HERE!
WHERE (p.ProjectType = 0 OR p.ProjectType = 5 OR p.ProjectType = 6)
I've added an inner join to 'dbo.MSP_EpmProject_UserView ' which is the base table for both queries.
The two fields are added at the end, but you can place them in any position.
I assume the missing join condition exists in the real query...
September 17, 2012 at 7:52 am
Hi Laurie,
Thanks for responding to my post! I think that might be what I'm after, but will try it out in my Virtual Envirnment to make sure. You can tell I'm a rookie, as I can't even tell if things will work unitl I try them out.
It looks like I did leave out the JOIN from the original query.
I'll let you know if I'm successful.
Cheers,
Wayne
September 17, 2012 at 8:27 am
Hi
Something to bear in mind:
I used an inner join from dbo.MSP_EpmProject_UserView to [MSPCFPRJ_Business Owner_AssociationView] because that's what's in the code above. If [MSPCFPRJ_Business Owner_AssociationView] hasn't got a match for every record in dbo.MSP_EpmProject_UserView & you don't want to lose any data you may need a left outer join instead.
September 17, 2012 at 8:51 am
Hi,
I can see my priority reading after 15 years of SQL Dev neglect, is to make sure I have a thorough knowledge of the various types of joins again. Problem is, I don't think I did enough 15 years ago to get a good understanding, so I've got some homework to do.
Can't do a whole lot during my work hours, but I'll certainly be trying this out during my evenings. Thanks again.
Cheers,
Wayne
September 17, 2012 at 10:17 am
Here's a solution that seems to be what you are trying to do...joining one table to another table where the source table has a delimited list of key values in a single column. If this is not what you need, can you post some sample tables and data?
/* Create a test table with a column of delimited values */
CREATE TABLE [dbo].[TestStringInput](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DelimitedStringInput] [VARCHAR](max) NULL,
CONSTRAINT [PK_TestStringInput] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* Insert some test values */
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('234,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,456,789')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,234,456,789,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('890,567,234')
GO
/* Create a test table with dummy data and keys */
CREATE TABLE [dbo].[TestJoinOutPutAlt](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [VARCHAR](max) NULL,
CONSTRAINT [PK_TestJoinOutPut] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* Insert some test values */
SET IDENTITY_INSERT dbo.TestJoinOutPut ON
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (123,'George Washington')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (234,'John Adams')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (456,'Thomas Jefferson')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (567,'James Madison')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (789,'James Monroe')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (890,'Andrew Jackson')
SET IDENTITY_INSERT dbo.TestJoinOutPut OFF
GO
/* Create a function to join the delimited values */
/* to the second table's keys as a table */
CREATE FUNCTION [dbo].[TestJoinTable]
(
@strList VARCHAR(50)
)
RETURNS @RtnValue TABLE
(
[ID] INT IDENTITY(1,1)
,[KeyValue] INT
,[TestValue] VARCHAR(MAX)
,PRIMARY KEY (ID)
,UNIQUE (ID)
)
AS
BEGIN
DECLARE
@TestValues VARCHAR(MAX)
,@strXML XML
SET@strXML = CONVERT(XML,'<root><item>' + REPLACE(@strList,',','</item><item>')+'</item></root>')
INSERT INTO @RtnValue (KeyValue, TestValue)
SELECT
Nodes.VALUE
,testjoin.TestValue
FROM
(
SELECT
X.t.value('.','VARCHAR(MAX)') AS Value
FROM
@strXML.nodes('/root/item') X (t)
) AS Nodes
INNER JOIN
dbo.TestJoinOutPut AS testjoin
ON [Value] = testjoin.ID
ORDER BY
testjoin.TestValue--optional order by
RETURN
END
GO
SELECT * FROM dbo.TestStringInput
SELECT * FROM dbo.TestJoinOutPut
/* Output to a table from a single value input */
SELECT * FROM dbo.TestJoinTable('890,567,234')
/* Output to a table by joining to the input table */
SELECT * FROM dbo.TestStringInput AS i
CROSS APPLY dbo.TestJoinTable(i.DelimitedStringInput)
September 17, 2012 at 11:31 am
Hi Steven,
Wow, thanks very much for the sample code! I'll try this out in my Virtual Environment this evening as well.
It's great, from my perspective, to get sample code that I can try out and then work back through the code to see how it works. So I very much appreciate you sharing this.
Cheers,
Wayne
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply