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.
