Retrieving multiple values from one field in SQL Server for use in multiple columsn in Reports

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.

Thanks.

I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.

--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE ca.name = 'Service Opportunity'

--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Num of Hours'

--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Grade'

--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')

There are a number of ways to solve this. Here are a few options:

If each value is in its own row, like this:

Name Value

Hours 100

Type AAA

Grade C

the SQL Pivot statement can be used in your query to pivot the rows to columns, so the result looks like:

Hours Type Grade

100 AAA C

If the values are concatenated in a single column, like:

Column

100;AAA;C

then you can use the Split VB function in the report to split the string into its component parts, and put them into fields. To get the values, you can use the following:

=Split(Fields!Column.Value, ";")(0)

=Split(Fields!Column.Value, ";")(1)

=Split(Fields!Column.Value, ";")(2)

|||

It is quite clear from your post that you have all the data in one column in a database table. If you have access to the query or the stored procedure, try to accomplish this in the SQL query level itself because calculating it in reporting service is costlier than doing it in the query level (better if there is a stored procedure becasue it is compiled and faster). Try using this sql query (assuming that your field separator is "," and the order is hours, type and then grade):

CASE WHEN CHARINDEX(',', Column1) > 0

SUBSTRING(Column1, 1, CHARINDEX(',', Column1)-1) AS 'Hours'

END,

CASE WHEN CHARINDEX(',', Column1) > 0

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

SUBSTRING(Column1, CHARINDEX(',', Column1)+1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))-1) AS 'Type'

END

END,

CASE WHEN CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1))) > 0

RIGHT(Column1, CHARINDEX(',', SUBSTRING(Column1, CHARINDEX(',', Column1)+1, LEN(Column1)-CHARINDEX(',', Column1)))+1) AS 'Grade'

END

If your still want to use reporting services, create 3 calculated dataset fields as follows:

Hours: IIf(Split(Fields!Column1.Value, ",").UpperBound>=0, Split(Fields!Column1.Value, ",")(0), "")

Type: IIf(Split(Fields!Column1.Value, ",").UpperBound>=1, Split(Fields!Column1.Value, ",")(1), "")

Grade: IIf(Split(Fields!Column1.Value, ",").UpperBound>=2, Split(Fields!Column1.Value, ",")(2), "")

Shyam

|||

John,

Thanks for the suggestion. I guess I didn't make myself very clear on to what I was attempting. I modified my thread and enclosed a sample of what I am attempting to do.

Thanks again for the help

|||

Shyam,

Thanks for the advice. I guess I didn't make myself clear enough on what I was attempting to do. I have modifed my thread and enclosed a sample of the code I am trying to make work.

Thanks again

|||

Use the following query to get all values at one shot:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM Cstudent cs1

INNER JOIN cattribute ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

INNER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

INNER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Thanks,

Shyam

|||

Shyam,

I've been working with what you suggested but so far with no luck. It pulls the column headings but there is no information in the columns. I'm looking to see if I'm missing something somewhere.

Thanks for the advice though, I'll keep working at it and see what I can come up with.

Thanks

Wayne

|||

Maybe not all records are available in Cstudent, so use this query:

SELECT cs1.value AS 'Service Opportunity', cs2.value AS 'Number of Hours', cs3.value AS 'Grade'

FROM cattribute ca

LEFT OUTER JOIN Cstudent cs1

ca ON ca.attributeid = cs1.attributeid

AND ca.name = 'Service Opportunity'

LEFT OUTER JOIN Cstudent cs2 ON ca.attributeid = cs2.attributeid

AND ca.name = 'Num of Hours'

LEFT OUTER JOIN Cstudent cs3 ON ca.attributeid = cs3.attributeid

AND ca.name = 'Grade'

Shyam

|||

Shyam

Thanks for the help, Just have to do some more tweaking on my end but looks like it might give me what I'm looking for.

Thanks Again for the Help

Wayne

|||

So, can you mark the post as answer?

Shyam

|||

Shyam

Thanks for all the help. This was the first post I had done one here so I apoligize for that. It's taken care of and I posted it answered.