Hide Column In Tabular Report

[GoodBoy]

Hello guys! Just want to share this other solution on how to hide a column in a tabular report using CSS. Just paste the code below in the Header.

<style>
form[action*='[@cbAppKey]'] tr.cbResultSetDataRow td:nth-child(5),
form[action*='[@cbAppKey]'] tr.cbResultSetTableHeader th:nth-child(5) /* replace 5 with the position order of column to be hidden */
{
display:none !important;
}
</style>

 

The form[action*='[@cbAppKey]'] is used here so this CSS will only be applied to this specific DataPage. It is good to use if you will embed it in a webpage with many DataPages. 

[GoodBoy]

Additionally, if your tabular report has an aggregation or grouping, the code will be changed slightly as we will add another 2 lines (For TotalsRow & Group) after the first 2 lines in the code above. The new code will be like this: 

<style>
form[action*='[@cbAppKey]'] tr.cbResultSetDataRow td:nth-child(5),
form[action*='[@cbAppKey]'] tr.cbResultSetTableHeader th:nth-child(5), /* replace 5 with the position order of column to be hidden */
form[action*='[@cbAppKey]'] tr.cbResultSetTotalsRow td:nth-child(2),
form[action*='[@cbAppKey]'] tr.cbResultSetGroup1Row td:nth-child(2) /* replace 2 with the position order of td class of column you want to hide using F12 key*/
{
display:none !important;
}
</style>

 

[Kurumi]

Hi - Just to add, if you want to hide a column in a Pivot Report - you can use this code:

 

[DesiLogi]

On 11/4/2021 at 5:32 AM, KlisaN137 said:

Hi @kpcollier

Great spot by you, the proposed solution does not work if we have search form, and we are experiencing some strange behavior - hiding some other fields as you described it during consecutive search.

The reason for this is because it appears that when 'Search' button is clicked, the code from footer is not executed only once, but a couple of times. In order to execute the code only once, when page is first reloaded, we can add an IF statement before the actual code, which will check how many columns are still there:

<script>
document.addEventListener('DataPageReady', function (event) {

    const check = document.querySelectorAll('table[data-cb-name="cbTable"] th');
    
    /* Just set the number in IF below to the number of columns you have BEFORE hiding anything */
    if(check.length===9){
       // code goes here
    }
});
</script>

 

Additionally, I refactored whole code to be able to hide any number of columns without adding redundant code, just add or edit arguments in function deleting :

<script>
document.addEventListener('DataPageReady', function (event) {

    const check = document.querySelectorAll('table[data-cb-name="cbTable"] th');
    if(check.length===9){
        const deleting = (...args) =>{
               let label, values
               for(let a of args){
                      label = document.querySelector(`table[data-cb-name="cbTable"] th:nth-of-type(${a})`);
                      values = document.querySelectorAll(`table[data-cb-name="cbTable"] td:nth-of-type(${a})`);
                      label.parentElement.removeChild(label);

                      values.forEach(el => {
                            el.parentElement.removeChild(el);
                      });
               }
        }
        
        /* In below function call, just put any number of column you want to hide, but in reverse order - from highest number to the lowest */
        deleting(8,5);
    }
});
</script>

 

Hi @KlisaN137, 

This is a really great solution. My only question is if you have 2 or more datapages on the same html page, using this code. How do you delineate the datapages in the js so each one runs correct to the page. I imagine it's putting the appkey in (`table[data-cb-name="cbTable"] but I can't figure out the exact syntax for that. Any suggestions? Thanks!

[kpcollier]

@DesiLogi

You might be able to wrap everything in an if statement like seen on this page:

https://howto.caspio.com/datapages/ajax-loading/

document.addEventListener('DataPageReady', function (event) {
       	if (event.detail.appKey == 'MY_DATAPAGE_APPKEY') {
              //do something 
       	}
});

 

[DesiLogi]

Hi @kpcollier,

Thanks for the suggestion--it makes sense to me but I couldn't get it to work. Strangely, adding the appkey worked fine on the second datapage (there are two on the html page) but not on the first--and the first datapage worked (hid the correct columns) in Preview so I know the code was accurate. It's possible some stuff in the datapage (a lot of complex calculated fields and also css in header) is interfering when it's not alone. 

That said, I ended up using the responsive css in the header/footer to accomplish this particular task. I really, really like the javascript approach, though, and will use that where there's only one datapage deployed. Thanks again for posting this info!

[GoodBoy]

Hello~ Just sharing this more efficient way of hiding multiple columns in a report.  Thank you for this, @futurist!

 

[Kurumi]

Hi - Just an update, here are the other ways for you to hide column in the Tabular Report:

Without Download/Sort Options:

table[id*='cbTable'] > tbody > tr[class*='cbResultSetTableHeader'] > th:nth-child(1), td[class*='cbResultSetData']:nth-child(1){

    display:none !important;
  
}

With Download/Sort Options:

table[id*='FreezeTabularHeaderObj'] > tr[data-cb-name="header"] > th:nth-child(1), td[class*='cbResultSetData']:nth-child(1), #target table:nth-of-type(1) th:nth-of-type(1), #target table:nth-of-type(2) th:nth-of-type(1){

    display:none !important;
  
}

Replace 1 with the position order of column to be hidden.

[Javier]

On 10/23/2020 at 12:43 AM, CoopperBackpack said:

Hello @rush360,

Please add this code to the Footer and test. It works on my end.

<script>
document.addEventListener('DataPageReady', changeColSpan);

  function changeColSpan() {
  var elem = document.querySelector('.cbResultSetTotalsLabelCell');
  var currentColSpan = elem.colSpan;
    
  elem.colSpan = currentColSpan - 4; // 4 is a number of hidden columns, you may change the value if needed

  document.removeEventListener('DataPageReady', changeColSpan);

}

</script>

Hope this helps!

Hi @CoopperBackpack,

This code works fine until I enable the "bulk edit" option in the "Result page Editing option",  my tabular report create a space before the total result:

 

<td class="cbResultSetTotalsLabel cbResultSetTotalsLabelCell" colspan="1"></td>

<td class="cbResultSetTotalsLabel cbResultSetTotalsLabelCell" colspan="20">Total Invoices</td>

 

Any idea on how to make reference to the 2nd cell area with the spancol=20. your code point to the first cbResultSetTotalsLabelCell

 

Thank you

 

 

 

 

[CoopperBackpack]

Hello @Javier,

I suggested that code for a particular case that was shown on the screenshot.

You are correct, the bulk edit adds one more cell (one more field actually) and we need to reference the second cell with the 'cbResultSetTotalsLabelCell' CSS class.
For example: 

I changed the 'elem' variable in this code to reference the second cell.

<script>
document.addEventListener('DataPageReady', changeColSpan);

  function changeColSpan() {
  const elem = document.getElementsByClassName('cbResultSetTotalsLabelCell')[1];
  const currentColSpan = elem.colSpan;
    
  elem.colSpan = currentColSpan - 4; // 4 is a number of hidden columns, you may change the value if needed

  document.removeEventListener('DataPageReady', changeColSpan);
}
</script>

Please test this solution.

Also, please note that when you hide the fields by CSS, for example: 
td:nth-child(4)  {display: none;}
th:nth-child(4)  {display: none;}

and Bulk edit is enabled, it is considered as the first field, so you need to define the order number of the field to hide accordingly.  

[Javier]

14 hours ago, CoopperBackpack said:

Hello @Javier,

I suggested that code for a particular case that was shown on the screenshot.

You are correct, the bulk edit adds one more cell (one more field actually) and we need to reference the second cell with the 'cbResultSetTotalsLabelCell' CSS class.
For example: 

I changed the 'elem' variable in this code to reference the second cell.

<script>
document.addEventListener('DataPageReady', changeColSpan);

  function changeColSpan() {
  const elem = document.getElementsByClassName('cbResultSetTotalsLabelCell')[1];
  const currentColSpan = elem.colSpan;
    
  elem.colSpan = currentColSpan - 4; // 4 is a number of hidden columns, you may change the value if needed

  document.removeEventListener('DataPageReady', changeColSpan);
}
</script>

Please test this solution.

Also, please note that when you hide the fields by CSS, for example: 
td:nth-child(4)  {display: none;}
th:nth-child(4)  {display: none;}

and Bulk edit is enabled, it is considered as the first field, so you need to define the order number of the field to hide accordingly.  

Hello @CoopperBackpack

 

Thank you very much for taking the time to review my problem, your solution worked perfectly

[APTUS]

@KlisaN137, thank you so much for your post and the code for deleting tabular report columns using JS. I was using CSS to do this for the longest time and this JS approach is much, much better. While a user who knows what they are doing could technically get at the removed columns, it is much more difficult and will take a much more of a tech-savvy user to do so as compared with the CSS approach.

FYI, and for others who may come across this post and find it useful, I have tweaked the code as shown below so the argument for the "deleting" function can be entered as one or more ranges, as well as one or more individual columns, or any combination thereof. Again, I have just modified things and the original idea and credit goes to KlisaN137; thanks again.

I also have a somewhat related challenge that I cannot figure out for the life of me. I would like to be able to use this same approach with a Grid tabular report and while I have tried many, many variations of the script, I just cannot make it work. When I say variations, it mainly includes changing the querySelector lines to get to the correct elements/columns in the Grid scenario since I think the rest of the code should be ok regardless of the report type. If it make a difference, my grid report is already running a script to automatically open it in Grid Edit mode, which is working fine. It is just the removing columns part (using JS) that I cannot make work after having spend 1.5 days on it.

Any help or guidance from KlisaN137, or anyone else, on the Grid report issue will be very much appreciated.

 

<script>

document.addEventListener('DataPageReady', function(event) {
  const check = document.querySelectorAll('table[data-cb-name="cbTable"] th');

  const deleting = (...args) => {
    for (let arg of args) {
      if (Array.isArray(arg)) {
        // Column range
        let start = arg[0];
        let end = arg[1];
        deleteColumnsInRange(start, end);
      } else {
        // Single column number
        deleteColumn(arg);
      }
    }
  };

  const deleteColumnsInRange = (start, end) => {
    for (let i = end; i >= start; i--) {
      deleteColumn(i);
    }
  };

  const deleteColumn = (column) => {
    let label = document.querySelector(`table[data-cb-name="cbTable"] th:nth-of-type(${column})`);
    let values = document.querySelectorAll(`table[data-cb-name="cbTable"] td:nth-of-type(${column})`);

    label.parentElement.removeChild(label);
    values.forEach(el => {
      el.parentElement.removeChild(el);
    });
  };

  // Example showing use of multiple individual columns and multiple ranges
  let columnsToDelete = [5, [8, 12], 15, [18, 20]];
  deleting(...columnsToDelete);

});

</script>

 

[KlisaN137]

Hi @APTUS,

Thank you for adding the range, great option to use!

For your challenge to hide the fields in Grid Edit mode (if I understood that correctly), I found out that if we just remove the element as I suggested with JS, it will stay removed until the entire Report is refreshed, so this is leading to each time when you click on 'Grid Edit', more and more elements are removed. I tried adding an additional variable to check if the actual Report is refreshed, or if we are just going in and out of the Grid Edit mode, but that failed because the 'DataPageReady' event is fired in both cases.

Instead, we can use JavaScript to manipulate the CSS property, together with Mutation Observer to check whenever Grid Edit mode is accessed, and it looks like this:

<script>
document.addEventListener('DataPageReady', function(event) {

    function hideColums(...args) {
        for (let a of args) {
            values = document.querySelectorAll(`tbody:nth-of-type(1) td:nth-of-type(${a})`);
            values.forEach(el => {
                el.style.display = 'none';
            });
        }
    }

    const target = document.querySelectorAll('a[data-cb-name="GridEditButton"]')[1];
    const observer = new MutationObserver(mutations => {
        // WHICH COLUMNS TO HIDE WHEN ENTERING GRID EDIT MODE
        hideColums(7, 4, 3, 2);
    });
    const config = {
        subtree: true,
        childList: true
    };
    observer.observe(target, config)
});
</script>

 

[APTUS]

@KlisaN137, WOW: (1) thank you so much for your attention to my query and your fast response; and (2) the script for hiding columns in grid edit mode works flawlessly. This is awesome and I really appreciate it.

[APTUS]

@KlisaN137, thanks again and for your reference, or anyone else interested, I have a tweaked the grid edit version of your script with the same approach as before to be able to insert individual columns and/or ranges of columns to be removed as shown below:

<script>
  document.addEventListener('DataPageReady', function(event) {

    function hideColumns(...args) {
        for (let arg of args) {
            if (typeof arg === 'number') {
                hideColumn(arg);
            } else if (typeof arg === 'object' && arg.length === 2 && typeof arg[0] === 'number' && typeof arg[1] === 'number') {
                hideColumnsInRange(arg[0], arg[1]);
            }
        }
    }
    
    function hideColumn(columnNumber) {
        const values = document.querySelectorAll(`tbody:nth-of-type(1) td:nth-of-type(${columnNumber})`);
        values.forEach(el => {
            el.style.display = 'none';
        });
    }
    
    function hideColumnsInRange(startColumn, endColumn) {
        for (let i = startColumn; i <= endColumn; i++) {
            hideColumn(i);
        }
    }

    const target = document.querySelectorAll('a[data-cb-name="GridEditButton"]')[1];
    const observer = new MutationObserver(mutations => {
        // WHICH COLUMNS TO HIDE WHEN ENTERING GRID EDIT MODE
        hideColumns(7, [4, 6], 3, [2, 3]);
    });
    const config = {
        subtree: true,
        childList: true
    };
    observer.observe(target, config);
});
</script>

 

[EileenMG]

KlisaN137

Your solution that you posted on 11/4/21 worked perfectly for what I needed to do... except one small detail.  The column was gone but the Agg Total for that column was still showing.  Any suggestions?

 

[Kurumi]

Hi @EileenMG - do you have the code/script and sample screenshot?

[EileenMG]

Meekee,

I didn't post the code because I'm a bit embarrassed by it, lol.  I don't understand much of what is in the code but I needed to base the "hide" upon an authfield, so I inserted the "if" for that instead of the if that was there (since I'm not using any edit feature on that page).  Anyway, here's the code:

 

<script>
document.addEventListener('DataPageReady', function (event) {

    const check = document.querySelectorAll('table[data-cb-name="cbTable"] th');
   if(Number([@authfield:t1_b2]) == 1){
        const deleting = (...args) =>{
               let label, values
               for(let a of args){
                      label = document.querySelector(`table[data-cb-name="cbTable"] th:nth-of-type(${a})`);
                      values = document.querySelectorAll(`table[data-cb-name="cbTable"] td:nth-of-type(${a})`);
                      label.parentElement.removeChild(label);

                      values.forEach(el => {
                            el.parentElement.removeChild(el);
                      });
               }
        }
        
        /* In below function call, just put any number of column you want to hide, but in reverse order - from highest number to the lowest */
        deleting(7);
    }
});
</script>

The remaining aggregate total is the 400.00 in the upper right.  It's a total that certain users don't need to see.  Also, when viewed in portrait on a cellphone, it shows the Field Name as well.  

Any help would be so appreciated!

PS How do you get that Hi EileenMG in your post with a link to me?  I looked for forum instructions but didn't see any.

Here also is the full result with column 7 intact.

 

[EileenMG]

A quick update...

I took a completely different route to arrive at the same ultimate outcome.  It took me a couple of hours (reading articles & posts) to figure out how to pull aggregate totals from one table to another but eventually it worked.

So as for the table above, I deleted the whole column that I was trying to hide.  I would have liked to know how to fix what I had, but for right now I don't need it.

Anyway, thanks to all the previous posters for leaving breadcrumbs (and expertise!) as I keep trying to find my way.

 

[Lynda]

I took the Java logic and took it one step further. Because I use rather large reports several places across my app and I allow my members to set their Preferences for each of the areas (what they want to see) and I only wanted to maintain one foundation code...

The problem I am having is that when I go to sort on any of the columns, in the 7th - 14th columns, the sort does not work and the sort header refers to current header - 1. Any ideas?

Lynda

<script>

 

// UPDATE HERE: Update the parameters to match the report type

var Pref_1 = [@S_1];

var Pref_2 = [@S_2];

var Pref_3 = [@S_3];

var Pref_4 = [@S_4];

var Pref_5 = [@S_5];

var Pref_6 = [@S_6];

var Pref_7 = [@S_7];

var Pref_8 = [@S_8];

var Pref_9 = [@S_9];

 

document.addEventListener('DataPageReady', function (event) {

 

// The first array is preferences. preferences are entered in reverse order - from highest to lowest and must align with columns

// UPDATE HERE: The second array is mycolumns. mycolumns must be entered in reverse order - from highest number to the lowest  

 

const preferences = [Pref_9,Pref_8,Pref_7,Pref_6,Pref_5,Pref_4,Pref_3,Pref_2,Pref_1];

 

const mycolumns = [14,13,12,11,10,9,8,7,6];

 

//This sets the the number of mycolumns to loop - it varies depending on where script is used

let fLen = mycolumns.length;

 

const check = document.querySelectorAll('table[data-cb-name="cbTable"] th');

   

// UPDATE HERE: Set the number in IF below to the TOTAL number of columns you have BEFORE hiding anything.

    if(check.length===15){

        //This loops the mycolumns for each column

        for (let i = 0; i < fLen; i++) {

            //This gets the associated preference for the column. If value 0 (Hide) then process

            if (preferences[i] == 0) {

                let label, values;

                // Remove the Headers

                label = document.querySelector(`table[data-cb-name="cbTable"] th:nth-of-type(${mycolumns[i]})`);

                label.parentElement.removeChild(label);

                // Remove the Values

                values = document.querySelectorAll(`table[data-cb-name="cbTable"] td:nth-of-type(${mycolumns[i]})`);

                    values.forEach(el => {

                        el.parentElement.removeChild(el);

                    });

            }  

        }

    }

});

</script>

[Lynda]

I have updated the script (found a bug in the group and Totals). It now works perfectly! I hope you like. Still having problems with the sort.

<script>

// UPDATE HERE: Update the TABLE FIELDS/external params to match the report

var Pref_1 = 1;

var Pref_2 = [@field:ProfilePreference_F_2];

var Pref_3 = [@field:ProfilePreference_F_3];

var Pref_4 = [@field:ProfilePreference_F_4];

var Pref_5 = [@field:ProfilePreference_F_5];

var Pref_6 = [@field:ProfilePreference_F_6];

var Pref_7 = [@field:ProfilePreference_F_7];

var Pref_8 = [@field:ProfilePreference_F_8];

var Pref_9 = [@field:ProfilePreference_F_9];

 

document.addEventListener('DataPageReady', function (event) {

 

// UPDATE HERE: preferences are entered in reverse order

const preferences = [Pref_9,Pref_8,Pref_7,Pref_6,Pref_5,Pref_4,Pref_3,Pref_2,Pref_1];

 

// UPDATE HERE: mycolumns must be entered in reverse order - from highest number to the lowest, are positional, do not have to be sequential  

const mycolumns = [15,14,13,12,11,10,9,8,7];

 

//This sets the the number of mycolumns to loop

let fLen = mycolumns.length;

 

const elem = document.querySelector('.cbResultSetTotalsLabelCell');

let TotSpan = elem.colSpan;

 

const check = document.querySelectorAll('table[data-cb-name="cbTable"] th');

   

// UPDATE HERE: Set the number in IF below to the TOTAL number of columns you have BEFORE hiding anything.

    if(check.length===18){

       

        // For this report - Always Hide Column 16 Foodlog ID <== It doesnt have any totals to worry about

        let label  = document.querySelector('table[data-cb-name="cbTable"] th:nth-of-type(16)');

        let values  = document.querySelectorAll('table[data-cb-name="cbTable"] td:nth-of-type(16)');

 

        label.parentElement.removeChild(label)

        values.forEach(el => {

                el.parentElement.removeChild(el)

        });  

 

        //This loops the mycolumns for each column

        for (let i = 0; i < fLen; i++) {

            //This gets the associated preference for the column

            if (preferences[i] == 0) {

                let label, values, group1, agg1;

                // Remove the Headers

                label = document.querySelector(`table[data-cb-name="cbTable"] th:nth-of-type(${mycolumns[i]})`);

                label.parentElement.removeChild(label);

                // Remove the Values

                values = document.querySelectorAll(`table[data-cb-name="cbTable"] td:nth-of-type(${mycolumns[i]})`);

                    values.forEach(el => {

                        el.parentElement.removeChild(el);

                    });

                //Get the column position for the Group and Total position using the span

                newCol = (mycolumns[i] - TotSpan) + 1;              

                // Remove the Group Totals

                group1 = document.querySelectorAll(`.cbResultSetGroup1Row td:nth-of-type(${newCol})`);

                    group1.forEach(el => {

                        el.parentElement.removeChild(el);

                    });

                // Remove the Report Totals

                aag1 = document.querySelectorAll(`.cbResultSetTotalsRow td:nth-of-type(${newCol})`);

                    aag1.forEach(el => {

                        el.parentElement.removeChild(el);

                    });

            }  

        }

    }

});

</script>

Lynda

 

[Hiperf]

I have tried this solution but have two (2) Aggregate rows.

• I have a total of 24 columns. I have hidden rows 8, 21, and 22.
• These have been successfully hidden! However, my two Aggregate rows are off to the right.
• Aggregate 1: Rows SRO through 7BDR are SUMS.
• Aggregate 2: SLI and ELI columns have a COUNT BLANK formula.

 

Here's what I have added to the header:

<style>
 #target table:nth-of-type(1) td:nth-of-type(8)  {display: none;}
#target table:nth-of-type(1) th:nth-of-type(8)  {display: none;}

#target table:nth-of-type(1) td:nth-of-type(21)  {display: none;}
#target table:nth-of-type(1) th:nth-of-type(21)  {display: none;}

#target table:nth-of-type(1) td:nth-of-type(22)  {display: none;}
#target table:nth-of-type(1) th:nth-of-type(22)  {display: none;}

tr[data-cb-name='grand_total'] td:nth-of-type(3),
tr[data-cb-name='grand_total'] td:nth-of-type(4) {display: none;}

</style>
</header>
<div id="target">
<header>

 

ADDED to Footer:

<script>
document.addEventListener('DataPageReady', changeColSpan);

  function changeColSpan() {
  var elem = document.querySelector('.cbResultSetTotalsLabelCell');
  var currentColSpan = elem.colSpan;
    
  elem.colSpan = currentColSpan - 3; // 3 is a number of hidden columns; you may change the value if needed

  document.removeEventListener('DataPageReady', changeColSpan);

}

</script>
</footer>
</div>
<footer>