Skip to Content
avatar image
Former Member

cross tab : suppressing fields

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.

1.png (31.7 kB)
1.png (12.3 kB)
2.png (12.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 25, 2017 at 08:13 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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
  • Sep 27, 2017 at 09:41 AM

    Hi,

    Could you please try this query:

    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                                    
    LEFT JOIN Subjects S ON SSJ.subject_id = S.subject_id                                    
    LEFT JOIN Students S2 ON SSJ.student_pk = S2.student_pk                                  
    LEFT JOIN Gradetype GT ON SSJ.grade_id = GT.grade_id                                
    LEFT JOIN TheoryPractical TP ON TP.ThPr_id = GT.ThPr_id                                    
    LEFT JOIN EndtermProgressive EP ON EP.ETPA_id = GT.ETPA_id                            
    LEFT JOIN Subjects_Gradetype_junction SGTJ ON S.subject_id = SGTJ.subject_id AND GT.Grade_id = SGTJ.Grade_id
    LEFT 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  

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded