BusinessObjects Board

Footer Calculations in a Crosstab not working as expected

building reports
#1

Hi,

I am really hoping someone can assist me with what I assumed would be a really simple count calculation, but has become a real pain - probably because I am a bit of a noob. I have hunted through this forum and all over google and I cannot find an answer to my problem.

So, I have a Crosstab that has been created using two queries and ultimately the report is tracking pupil progress for each course. I have attached a screenshot to give an idea of what I am working with. I’ve had to fill it with fake data for data protection reasons.

WEBI
WEBI1563×449 20.1 KB

So in the screenshot attached, the rows contain the pupil level data and the columns contain course level data. The columns are broken down by Course AND Level. In the body of the Crosstab, is a measure with the formula as follows:

=If(IsNull([Working Grade]) And ([Level] InList (“NAT3”; “NAT4”;“NAT5”;“HIGH”;“ADVH”));0;[Working Grade])

The Y/N columns (I know they aren’t officially columns, but actually rows) are each calculated using a bunch of measures (I don’t know if you need this info, but I’m including it just in case)

Column1 (after the first ‘columns’ Forename, Surname and ID)
=If(Sum([vPassAC] Where ([Level] InList (“NAT3”;“NAT4”;“NAT5”;“HIGH”;“ADVH”)))>4;“Y”;“N”)

Column2
=If(Sum([vPassAC] Where ([Level] InList (“NAT4”;“NAT5”;“HIGH”;“ADVH”)))>4;“Y”;“N”)

Column3
=If(Sum([vPassAC] Where ([Level] InList (“NAT5”;“HIGH”;“ADVH”)))>4;“Y”;“N”)

Column4
=If(Min([Working Grade]Where(Left([Course];4) InList (“C724”; “C824”)))<7;“Y”;“N”)

Column5
=If(Min([Working Grade]Where(Left([Course];4) InList(“C744”;“C844”)))<7;“Y”;If(Min([Working Grade]Where(Left([Course];4) InList(“C747”;“C847”)))<7;“Y”;“N”))

Column6
=If(Concatenation([vEnglish@3A-C];[vMaths@3A-C])=“YY”;“Y”;“N”)

Now, what I want to do, is have 3 footer calculations for each of those Y/N ‘Columns’. The first footer calculation just counts how many rows there are in the report - that one is easy, I can just do a count of the ID’s. The second footer calculation is to show how many “Y”'s there are in each ‘Column’. The third footer calculation is just the second calculation/first calculation to work out the percentage. (the cells highlighted in green would be where I would start the footer calculations)

I figured it would be simple:

=Count([ID] Where(Column1 = “Y”))

But nope - I have tried about 50 different calculations, using a variety of syntaxes, but I get one of the following values: 0, 1, 199 (which is the total number of rows) or 8000 (not exactly 8000, but some crazy high number). There should be 199 rows total and the number of "Y"s should be lower than 199, but I cannot for the life of me get it to work. I don’t know if its to do with Calculation Contexts or if it’s something else entirely, but please, someone put me out of my misery :joy:

#2

I think you need calculation context, have you tried using the “in” context with (student id number or something that makes the row unique)?

#3

I did try that - I’ve had three people look at it too, and none of us can figure out the syntax…none of us are experts of course, but I have tried using a footer calculation, I’ve also created a separate table underneath the cross tab to see if that would help, but I get the same values returned regardless of where I have the calculation.

Do you have any suggestions for the syntax? I am stumped.

#4

I do generally solve this kind of issues in a way like this:
=Count([ID] Where(Column1 in([ID])= “Y”))
or
=Count([ID]) Where(Column1 in([ID])= “Y”)
or
=Count([ID] Where(Column1 in([ID]))= “Y”)