April 13, 2012 at 11:49 am
Hi,
I've made a report from a report model supplied by our software providers and want to be able to do what is a simple job in excel.
I have summarised the results per school using the count function in a texbox in the tablix. The idea was that I add 2 columns for the results field 1 with a total count of results per school and the other with an expression to count only those results over a certain level. This is the same sort of way we do it in Excel so would please the end users if we could get the layout the same. Only problem is that I can't work out how to do the expression. Don't even know if this is the best way to do it.
The other potential problem is that the field is a text field as a mixture of numbers and letters are used for a variety of different tests.
For the report I'm trying to do I need to count all values above a 4. (I convert all to values before I start in Excel and then use a Sumproduct formula).
I'm not a coder - doing through a graphical interface but my expression for count all is =Count(Fields!Result.Value).
Hope someone can help but don't forget it has to be at the dummy level
Many thanks
April 13, 2012 at 12:27 pm
April 13, 2012 at 3:59 pm
Hi,
Year StudentNo SchoolNo Subject Result
2011 3196 4125 English A
2011 3197 4125 English 2
2011 3198 4125 English 3
2011 3199 4125 English 4
2011 3200 4126 English 5
2011 3201 4125 English 6
2011 3197 4125 CSI Y
2011 3198 4125 CSI N
2011 3197 4125 Math 6
2011 3198 4125 Math 4
So results in Eng can be A , B , C , 1 through to 8.
Other fields hold student details
Is this of any use?
Cheers
Yvonne
April 13, 2012 at 6:26 pm
Hi Yvonne, I hope this is helpful. Unfortunately, I am very much a SQL coder, which may be a detriment in this scenario, given that you are not, but I've done my best given what you provided me to set you on the right path. If you can run something like this in SSMS or in your report building tool, this should get you to where you need the data to be. Here is a setup of what I understood your table to be
--So you dont get a bunch of useless strings output
set nocount on
--Make temp table with your data
if object_id('tempdb.dbo.#Data') > 0 drop table #Data
create table #Data
(
TheYear int,
StudentID int,
SchoolID int,
Subject varchar(30),
Result char(1),
ctResult tinyint --The added column which determines whether or not you count it in your aggregation
)
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3196, 4125, 'English', 'A'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3197, 4125, 'English', '2'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3198, 4125, 'English', '3'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3199, 4125, 'English', '4'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3200, 4126, 'English', '5'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3201, 4125, 'English', '6'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3197, 4125, 'CSI', 'Y'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3198, 4125, 'CSI', 'N'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3197, 4125, 'Math', '6'
insert into #Data (TheYear, StudentID, SchoolID, Subject, Result) select 2011, 3198, 4125, 'Math', '4'
So it sounds like you need various aggregates of the information here based on what the student's [Result] was, with the wrench in the gears being that there are numbers and letters in the column. There's a couple ways you can approach this, but I just added another column and told it "When result is either A, Y, 4, 5, 6, 7 or 8, put a 1, and otherwise put a 0"
--Assign a 1 or 0 value based on whether a Result counts or not. I've made the assumption here that A, 4, 5, 6, 7, 8, Y
-- count, but you can change that. Just edit the string below.
update #Data
setctResult = case when Result like '[A45678Y]' then 1
else 0
end
from #data
Finally you need to count up the rows for each combination of Year, School and Subject, plus the total counts for the whole school. I've written a query, which draws from another query here
--The inner query (aliased as "a") is a grouping of each subject by year and school, and
select
TheYear,
SchoolID,
Subject,
TotScore,
SubScore,
Ratio = SubScore / (TotScore * 1.0)
from (select
TheYear,
SchoolID,
[Subject],
TotScore = (select COUNT(1)
from #Data), --The total count of all studend scores over all subjects
SubScore = count(ctResult) --the count of qualifying results per subject
from #Data
where ctResult = 1
group by TheYear, SchoolID, [Subject]) as a
If I understand your issue right, that should get you the data you need. Let me know if I missed something or can help you out more.
April 15, 2012 at 12:58 pm
Hi Gabe,
Looks scary but I'll take a look at this tomorrow when I'm back in work. I may not be back in touch straight away as it looks like there's a lot to get my head round.
Thank you so much for this 🙂
Cheers
Yvonne
April 15, 2012 at 4:12 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply