cross tab : suppressing fields

[#1]

i have this cross tab as shown below.

And this is what is get when i generate the report.

However what i actually need is this:

1)show the value of status_name i.e 'Present' or AB' only when the value of marks is null.in other words i want to suppress status_name only if marks is null. I also think my method above is wrong since there are two summarized fields and the summary type for status_name is 'mode'. But that is the closest i could come to getting what i need.

2) the total in the right hand side should be calculated only if all the marks values are not null. else it must show a string like 'Compartmental'. In other words if there is a single 'AB' then total must not be calculated and it must show the string.

[abhilash_kumar]

Hi,

Formulas that evaluate against true 'NULL' values, unfortunately, do not work in a crosstab.

The reason the first summary appears 'blank' for 'Multimedia Technologies' is that no record exists. You'd need to fix this part first so that it returns 'some' value. A zero might not be a good idea, however, a negative number should be enough. You should be able to handle this with a Custom SQL query as the report's data source.

We can then suppress the 'status' summary using the following formula:

GridValueAt(CurrentRowIndex, CurrentColumnIndex, 0) > 0

For the 'Departmental' text, you'd need to insert a Calculated Member column, however, this can only be done when you fix the first part I mentioned above.

-Abhilash

[Former Member]

thanks for the reply.

this is the SQL query to return -1 if marks value is null. but i am still cant suppress status_name. i m sure i ammissing something here. And is it still ok to use two summarized fields?

SELECT S2.student_rollno, IsNull(SSJ.marks, -1) as marks,
SSJ.reason_not_present,
S.subject_name, S.subject_id, GT.Grade_id,  
TP.ThPr_name,EP.ETPA_name,SGTJ.maximum_marks,
 SGTJ.pass_marks  ,ExamStatus .Status_name 
FROM Students_Subjects_junction SSJ                                    
JOIN Subjects S ON SSJ.subject_id = S.subject_id                                    
JOIN Students S2 ON SSJ.student_pk = S2.student_pk                                  
JOIN Gradetype GT ON SSJ.grade_id = GT.grade_id                                
JOIN TheoryPractical TP ON TP.ThPr_id = GT.ThPr_id                                    
JOIN EndtermProgressive EP ON EP.ETPA_id = GT.ETPA_id                            
JOIN Subjects_Gradetype_junction SGTJ ON S.subject_id = SGTJ.subject_id AND GT.Grade_id = SGTJ.Grade_id
join ExamStatus  on SSJ .reason_not_present = ExamStatus .Status_id  
   WHERE S2.semester_id = 7 And S2.department_id = 14 And SSJ.year = 2017 and SSJ.student_pk =131