cross tab : suppressing fields

[Former Member]

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,

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

[Former Member]

k this is the result of the your query

of course the difference with my query is the inclusion of columns with null reason_not_present values, but i dont think its significant here because those columns were entered before i added reason_not_null column.i still dont get why you gave me this query.

this is the result of my query:

In my case also i have been able to insert -1 in case marks value is null.

My original question still remains. How can i suppress status_name when marks value is more than -1 now since we have -1 in the datatable? Btw, what i am trying to make is a marksheet for student. In the front end if the user enters some value then status is obliviously 'Present' for that particular subject. If user has not entered any marks then AB gets inserted which means that the student is Absent.

[Former Member]

hello? any one there?

[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