Hiding Empty Measure Columns in a Crosstab

Find more posts tagged with

Comments

mwilliams

Can you attach a sample report using a CSV file for the data and a design that uses this file so I can see how everything is set up? Thanks.

jgisler

I've attached a design and the CSV data file. You need to rename the .txt file to .cvs (the forum wouldn't let me upload the .csv file)

Thanks for your help

mwilliams

So, you just want to get rid of the column with no data? The row is ok because of the total score? Or get rid of both?

jgisler

We don't want any empty columns. Empty rows are fine.

mwilliams

Take a look at this modified report. I store the data from the last subSubject for the given subject into a persistent global variable in the onFetch of the dataSet and re-use this value in a computed column to replace the nulls so the blank column isn't made. I also added your subject field as an attribute to the student field so that you didn't have to display the subject in the list and in the crosstab. I just filtered using the attribute. Hope this helps.

jgisler

If I understand correctly, onFetch() gets called for each row of the result set and the computed column is set after the onFetch() method completes.

So, if the first record found contains a null sub-subject, then we still have the empty column. I suppose I can sort on that column in the query to ensure that I get non-null values first.

Thanks

mwilliams

Another way you could go about this would be to set the PGVs in the onFetch of the dataSet, put a hidden text box bound to your dataSet at the top of your report and then add the computed columns as bindings in your table. The hidden text box bound to the dataSet will ensure that the dataSet is ran prior to the table, so if a value exists for a sub subject for a subject, the PGV will be set regardless of order.

jgisler

Is there a way to conditionally set the column width to 0 if the value in the measure header is null?

What would that script look like?
To which event would I add that functionality?

mwilliams

In what scenario do you now need this? Let me know. Because of how a crosstab is made, some things like this are easier said than done.

jgisler

I have a script in the beforeFactory of the report that will "turn off" sub-subject details based on an input parameter. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if( params['goalPerformanceDisplay'] == false ) {
dropElementById( studentDetailHeaderGroupId );
hideColumnById( studentDetailCrosstabMeasureHeaderCellId );
hideColumnById( studentDetailCrosstabMeasureCellId );
}

function dropElementById( elementId ) {
var element = getElementById( elementId );
if( element != null ) {
element.drop();
}
}

function hideColumnById( columnId ) {
var column = getElementById( columnId );
if( column != null ) {
column.setProperty( "width", "0" );
}
}
</pre>
 
But what if I had a group where I knew there would be no sub-subjects. Could I attach a similar script to an onPrepare event for a group? 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if( row['test_type_description'] == 'SURVEY' ) {
hideColumnById( studentDetailCrosstabMeasureHeaderCellId );
hideColumnById( studentDetailCrosstabMeasureCellId );
}
</pre>

mwilliams

So, in the case that a subject has no sub-subjects, you wouldn't want the measure area of the crosstab to show? Am I understanding correctly? If so, one way to to this might be to have a regular table that shows the students and their scores and show this and hide the crosstab for that group when there are no sub-subjects.

jgisler

Ah, thanks! Great idea.