Skip to Content

In this blog post I will demonstrate the possibility of hiding crosstab columns with the combination of css and scripting.

Use case:

The same 1 datasource is used in several different crosstabs, but in each crosstab different measures should be shown.

As of DS 1.3 this is not yet possible the desired way. The desired way would be a straightforward way with very little scripting involved similarly as it is currently possible to display different measures in different charts by using only 1 datasource by using the “setDataSelection(selection)” method (available only for the Chart component). Till DS 1.4 where this or a similar method will hopefully come for the Crosstab component too, I created a little scenario which enables the showing/hiding of table columns by using the combination of css and scripting to assign the desired css classes to the crosstab(s).

One could create several copies of the same datasource in Design Studio which use only the needed measures, but this is not good for the performance of your bi app.

First some gif image demonstration (click on the image to see the gif animation):

Using radio buttons to display 1 specific measure column or all columns:

Screencast_RadioButtons_5s_630x314_10fps.gif

Using checkboxes to display any combination of measure columns:

Screencast_Checkboxes_5s_628x314_10fps_v2.gif

I really liked the gif image demonstration that Mike used in his post, so I asked him (in one of the comments) how he created it. In the end I ended up with a different set of tools: first I used “Screencastify (Screen Video Recorder)” a Google Chrome extension to capture a screencast in the file format .webm (I captured it at 10fps, video length 5s, screen size of chrome browser ca. 630x320px) in order to get the final gif image fit into the max. 1MB size limitation in scn. And in the 2nd step I converted the .webm file into a .gif file using an online conversion tool.

CSS + Scripting behind my sample:

My bi app has the following components (outline):

/wp-content/uploads/2014/07/outline_507085.png

I’m using a custom css file “customCSS.css”. This is assigned under: Application Properties > Display > Custom CSS. You can of course name it however you want as long your operating system permits the file name. Just don’t forget that the file needs to have the extension .css

/wp-content/uploads/2014/07/custom_css_file_507140.png

APPLICATION > On Startup > contains the following 2 lines of code:


CROSSTAB_1.setCSSClass("showMeasure measure1");
CROSSTAB_2.setCSSClass("showMeasure measure1");




I have set in both the radiobuttongroup and checkboxes that the 1st one should be selected/checked by default and thus I also assigned the appropriate custom css classes to the crosstabs so that only the measure is shown that matches the selected/checked radio button/checkbox.

First let us look more closely how the RADIOBUTTONGROUP_1 works:

Properties:

/wp-content/uploads/2014/07/radiobuttons1_507098.png

Items:

/wp-content/uploads/2014/07/radiobuttons2_507099.png

Events > On Select > has 1 line of code:


/* assign css class to crosstab */
CROSSTAB_1.setCSSClass("showMeasure " + RADIOBUTTONGROUP_1.getSelectedValue());
/*
* delete css classes from crosstab if one of the css classes is "measureAll", no css class means nothing will be hidden
*
* but since the css definition for the measureAll css class is: ".showMeasure.measureAll {}"
* it is not needed to delete any css classes, because the css class doesn't hide anything = shows everything
*/
/* if (Convert.indexOf(CROSSTAB_1.getCSSClass(), "measureAll") > -1) {CROSSTAB_1.setCSSClass("");} */




With this code we assign 2 css classes at once to the crosstab component.

The following 4 combinations are possible in this example:

  • showMeasure measure1 (in the custom css file the definition becomes: .showMeasure.measure1)
  • showMeasure measure2 (in the custom css file the definition becomes: .showMeasure.measure2)
  • showMeasure measure3 (in the custom css file the definition becomes: .showMeasure.measure3)
  • showMeasure measureAll (in the custom css file the definition becomes: .showMeasure.measureAll)

So the “Value” of a radio button is the name of a custom css class.

So once a user clicks on the for example “Measure 2” radio button, the crosstab component gets the following 2 classes assigned:

/wp-content/uploads/2014/07/radiobuttons3_507100.png

And the last step is that the browser reads and interprets the css definitions that are defined in the custom css file, which tell the browser to hide some of the table columns: {display: none;}

The full CSS coding is at the end of this post and also included as attachment.

It is not necessary to assign 2 css classes at once, but it demonstrates at least that it is possible. It added a little bit of clarity for me at the time of creating it and I thought I might need it on the way to the solution so I just might incorporate this logic from the very beginning and see where it takes me. So I use the assignment of 2 css classes instead of just one but it is not a must.

Second let us look more closely how the CHECKBOXES work:

The first checkbox is set to Selected = true, the others are set to false (in my example):

/wp-content/uploads/2014/07/checkboxes1_507141.png

All 3 of them have the same 1 line of code in the Events > On Click event:


BUTTON_1.onClick();




I wrote some universal code so that it can be maintained in 1 place only and so I doesn’t have to repeat it in each checkbox again and again.

What I could have done to save me a probably unnecessary BUTTON component is: I could have written/moved the code from BUTTON_1 into CHECKBOX_1 and then from the other checkboxes I could have used the code: “CHECKBOX_1.onClick();” instead of “BUTTON_1.onClick();”.

Here is the code in the BUTTON_1 component which the checkboxes are using to determine which css classes need to be assigned to the crosstab:


// Determine which checkboxes are checked and assign a css class accordingly to show/hide measures in the crosstab
var checked = "";
if (CHECKBOX_1.isChecked()) {checked = "1";}
if (CHECKBOX_2.isChecked()) {checked = checked + "2";}
if (CHECKBOX_3.isChecked()) {checked = checked + "3";}
//if (checked == "") {checked = "0";} // show no measures at all
if (checked == "") {checked = "All";} // show all measures in the assigned data source
CROSSTAB_2.setCSSClass("showMeasure measure" + checked);




With this code we assign 2 css classes at once to the crosstab component.

The following 8 combinations are possible in this example:

  • showMeasure measure1 (in the custom css file the definition becomes: .showMeasure.measure1)
  • showMeasure measure2 (in the custom css file the definition becomes: .showMeasure.measure2)
  • showMeasure measure3 (in the custom css file the definition becomes: .showMeasure.measure3)
  • showMeasure measureAll (in the custom css file the definition becomes: .showMeasure.measureAll)
  • showMeasure measure12 (in the custom css file the definition becomes: .showMeasure.measure12)
  • showMeasure measure123 (in the custom css file the definition becomes: .showMeasure.measure123)
  • showMeasure measure13 (in the custom css file the definition becomes: .showMeasure.measure13)
  • showMeasure measure23 (in the custom css file the definition becomes: .showMeasure.measure23)

So actually all the scripting/coding is doing is just assigning some css classes to some crosstabs. The css is then doing/controlling the showing/hiding of table columns.

So here is the used css:

(this is an example that works in the tested simple scenario, in more complex scenarios you might have to adjust the logic behind the css and/or scripting, but you might have just such a simple scenario as this one, where it definitely might come handy as a possible solution)

For the css to work it is assumed that:

  • you have the same amount of measures in your datasource (the user doesn’t have the ability to select/filter for example through a filterpanel which measures to be filtered out from the datasource, if the user would for example choose to filter the whole datasource to exclude the 2nd measure, then the previously 3rd measure becomes the 2nd one (the order of the measures changes/shifts)… the css doesn’t check measure’s descriptions or technical names, it just shows/hides columns based on their index)
  • you do not change the order of the measures… the css doesn’t check measure’s descriptions or technical names, it just shows/hides columns based on their index

To summarize: you are showing/hiding table columns not based on the names/ids of the measures but only based upon their order in the datasource.


/* usage: hide button containing scripting code */
.customHidden {display: none;}
/* show all measures, nothing is hidden */
.showMeasure.measureAll {}
/* show 0 measures, hide all measure columns */
.showMeasure.measure0 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault
,.showMeasure.measure0 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault
{display: none;}
/* show only 1st measure, hide all measure columns except the 1st column with the 1st measure */
.showMeasure.measure1 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:not(:nth-child(1))
,.showMeasure.measure1 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:not(:nth-child(1))
{display: none;}
/* show only 2nd measure, hide all measure columns except the 2nd column with the 2nd measure */
.showMeasure.measure2 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:not(:nth-child(2))
,.showMeasure.measure2 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:not(:nth-child(2))
{display: none;}
/* show only 3rd measure, hide all measure columns except the 3rd column with the 3rd measure */
.showMeasure.measure3 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:not(:nth-child(3))
,.showMeasure.measure3 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:not(:nth-child(3))
{display: none;}
/* show measures: 1 & 2, hide all measures except the first 2 */
.showMeasure.measure12 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:not(:nth-child(-n+2))
,.showMeasure.measure12 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:not(:nth-child(-n+2))
{display: none;}
/* show measures: 1 & 2 & 3, hide all measures except the first 3 */
.showMeasure.measure123 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:not(:nth-child(-n+3))
,.showMeasure.measure123 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:not(:nth-child(-n+3))
{display: none;}
/* show measures: 1 & 3, hide measure in the 2nd column and measures from the 4th column onwards */
.showMeasure.measure13 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:nth-child(2)
,.showMeasure.measure13 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:nth-child(n+4)
,.showMeasure.measure13 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:nth-child(2)
,.showMeasure.measure13 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:nth-child(n+4)
{display: none;}
/* show measures: 2 & 3, hide measure in the 1st column and measures from the 4th column onwards */
.showMeasure.measure23 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:nth-child(1)
,.showMeasure.measure23 .sapzencrosstab-ColumnHeaderArea td.sapzencrosstab-HeaderCellDefault:nth-child(n+4)
,.showMeasure.measure23 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:nth-child(1)
,.showMeasure.measure23 .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:nth-child(n+4)
{display: none;}




Also see attached the contents of the css file (inserted in a txt file).

And the contents of my content.biapp file (inserted in a txt file) – you could create a new empty app first, then go into your own content.biapp file in your repository folder and copy the contents (all or the parts that you need) of my file into yours and you should have relatively quickly an example setup for yourself.

I hope someone learned something new from this post. 🙂

Cheers

David

To report this post you need to login first.

23 Comments

You must be Logged on to comment or reply to a post.

  1. Niels De Lathouwer

    An interesting way of doing this.

    I guess another way (without CSS) is to use the SetFilter command of your Datasource. But this could imply extra loading time.

    Nice work.

    (0) 
    1. David Gyurasz Post author

      Hi Niels,

      The goal was to avoid the use of SetFilter because that way you filter the whole datasource and thus you cannot display in 1 crosstab 3 measures and in another crosstab only 1 measure while using the same one datasource.

      (0) 
      1. Niels De Lathouwer

        Arguably you can create 2 datasources with the same BW query. this will not slow things down, since your first Datasource load will put the BW query in the BW cache.

        But I can understand why you would want to avoid the SetFilter command.

        I think it all depends on how you want to do things. But like I said, nice work. This definitely gave me some other ideas as well.

        (0) 
        1. David Gyurasz Post author

          Thanks! Your comments are much appreciated!

          I didn’t make a real performance comparison between an App with 1 datasource (1 BEx Query) and and App with lets say 5 datasources (1 BEx Query behind all 5 datasources) whether both reports take the same amount of time to load and further navigating/setting the same filters for all datasources by the user.

          I have heard customers (report designers/creators) talking about wanting to have a possibility for Crosstabs similar to the “setDataSelection(selection)” method of the Chart component to avoid creating multiple datasources with the same BW Query. So I thought I give it a try with css… 🙂

          But to be honest, I don’t know whether I would personally use this css method in a production environment for an important client… Would have to decide on a case by case basis.

          It’s always nice to hear input/opinions from others.

          (0) 
          1. Niels De Lathouwer

            setDataSelection would be awesome ofcourse 🙂

            Is there an idea for that in ideas.sap.com? Maybe they will implement it in a future version of Design Studio?

            (0) 
  2. Rahul Dhiman

    hi David,

    In your example you flipped between two different measure . Can we do something like this if we have 20 records displayed and I have to show 10 records in one view and there will be a button , when I click on that I can get the next 10 records in the same view and the button now will point to previous 10 records (like pagination feature )

    so in the same view you can flip between 2 sets of data for same measure.

    Thanks

    Rahul

    (0) 
  3. Oddmar Lid

    Hi David,

    I have used your solution in a Cross-tab where I use Exceptions (defined in BEx) to show different colors in Cells in the Key Figures.

    The Colors is based on some Key Figures in the Query that I don’t want to show in the Cross-Tab and I have used your solution to hide these Key Figures (otherwise the Exceptions defined in BEx will not work).

    What I experience when I run the Dashboard is that if the user sorts the Key Figures that is show in the Cross-tab (via sort button at the top of each Column in Cross-tab), this somehow makes the hidden Key Figures pop up again and this is not looking good in the Cross-tab.

    Is it anything I have done wrong in my definition of Cross-tab or CSS, or is this only a limitation with the solution to use the CSS to hide Key Figures in Cross-tab?

    regards

    Oddmar

    (0) 
  4. Robert Boneschansker

    Your solution works fine, but I’m running into a follow up issue. I.s.o. one header line with the key figures, I have two. In my case I want to display the key figures per calendar year.

    It looks like the screenshot below.

    Capture.PNGHi David,

    If I now slecte the checkbox for ‘Personen’, only 2005 and the KF ‘Personen’ is shown. If I select ‘FTE’, only 2006 and the KF ‘FTE’ is shown. I understand the logic, for both header columns the selected placeholder (first value, second value) is shown. But of course, it should show all years and only the selected KF. Is there a workaround for this or should I restrict the solution by creating a query with multiple views and select the specific view, depending on the selection like described above in one of the other remarks…

    (0) 
    1. David Gyurasz Post author

      Hi Robert,

      Sorry for the late response, but I was N/A the last 6 months.

      Unfortunately it looks like, this is not possible. My example was created based on a scenario containing only 1 dimension in the rows. As soon as you add another dimension into the columns, the html markup of the crosstab changes. In your case the Years contain the attribute colspan=”2″ in the generated html source code and this is incompatible with the css code in my post: one would have to also change/adjust this colspan attribute depending on how many key figures should be displayed, but this is not possible with css. Currently in your scenario the table layout/formatting breaks. I haven’t got a solution for this.

      I hope SAP will add the functionality of hiding crosstab columns via scripting without having to remove the measures from the underlying/assigned data source.

      Regards

      David

      (0) 
  5. dinesh kumar

    This works good if you have 3 measures so 3 factorial combinations which is 6 ,But what if you have say 6  keyfigures then should we write the code for 720 combinations to select multiple keyfigures in checkbox??

    (0) 
    1. David Gyurasz Post author

      Hi Dinesh,

      regarding the css code, you need to write code for:

      3 KFs = 8 combinations

      4 KFs = 16 combinations

      5 KFs = 32 combinations

      6 KFs = 64 combinations

      I have attached these combinations in a txt file (not the final css rules, just the analysis how many and which) into this blog post a minute ago.

      Regards

      David

      (0) 
  6. dinesh kumar

    Hi David,

    Thanq so much for ur detailed explanation about no of combinations..In my example am trying to use Checkbox group instead of individual checkboxes but while writing code in onselect am not getting value help for checkboxes of this group.

    For eg my checkbox group is checkboxgroup_1 and my values are measure 1,measure 2…

    but while writing code if i type the below code

    if (checkboxgroup_1. and pressing ctrl+space am unable to see measure in drop down.. enclosing screenshots please guide me on this.

    chkbxgrp.JPG

    chkbx_val.JPG

    chkbx_code.JPG

    (0) 
    1. David Gyurasz Post author

      Hi Dinesh,

      The coding when using the checkboxgroup component needs to be adjusted as different methods need to be used, see my example.

      The following should work when using the CHECKBOXGROUP component:

      Items in my example:

      /wp-content/uploads/2015/04/checkboxgroup_items_686055.png

      Note, that the Values are only numbers, starting from 1.

      In the On Select event of the checkboxgroup component I created the following code:

      // Determine which checkboxes are checked and assign a css class accordingly to show/hide measures in the crosstab

      var checked = “”;

      var selVals = CHECKBOXGROUP_1.getSelectedValues();

      if (selVals.length < 1 || selVals.length == 3 ) { // none or all checkboxes checked

        checked = “All”; // show all measures in the assigned data source

      }

      else {

        selVals.forEach(function(checkBoxValue, index) {

        checked = checked + checkBoxValue;

        });

      }

      CROSSTAB_3.setCSSClass(“showMeasure measure” + checked);

      TEXT_1.setText(selVals.length+” Var checked: “+checked); // debug, see what the variable contains

      In the On Startup event of the application I added the following so that by default the first measure is only shown in the crosstab, as this is also checked as default in the Edit Items of the checkboxgroup:

      CROSSTAB_3.setCSSClass(“showMeasure measure1”);

      Regards,

      David

      (0) 
  7. Ashok Vijayasundaram

    Dear David,

      

    I liked this technique very much. Thanks for sharing this innovative idea. I tried your sample files and they work perfect.

      

    However, i have a minor problem. When i upload them into the netweaver backend and run the application, the column hide happens but whenever the first column is hidden the complete layout of the crosstab collapses.

    Could you let me know if there is any small tweaking required when run in netweaver?.

    Regards,

    Ashok v

    (0) 
    1. David Gyurasz Post author

      Dear Ashok,

      I am really sorry but I am not able to assist you with this issue, because I would have to reproduce it first on NW, but I have no such access/possibility.

      I have only tested it locally and would also be able to test it on BIP.

      Regards,

      David

      (0) 
  8. Harish Nayak

    Hi David,

    Thanks for sharing the great work. This helped me a lot in my current project.

    Is it possible to hide Dimension in Crosstab? I have two Dimensions and 3 KF’s and wanted to hide 2nd Dimension Column.

    Thanks,

    Harish

    (0) 
  9. Lori Guymon

    Great article!  I needed a way to style just one of my measure columns (the 1st one) so that users could see visually know that they could select the value in that column to drill into details.  I wanted the values in this column to be underlined, blue and when hovering have the cursor turn to a pointer…and you article showed me the way.  Thank you!

    In case anyone else needs to do this, here is what I did in my css file:

    .myCustomClass .sapzencrosstab-DataArea td.sapzencrosstab-DataCellDefault:nth-child(1)

    ,.myCustomClass .sapzencrosstab-DataArea td:nth-child(1)

    {

      text-decoration: underline !important;

      color:blue;

    }

    .myCustomClass .sapzencrosstab-DataArea td:nth-child(1):hover

    {

      cursor: pointer;

    }

    (0) 
  10. Julie Welsk

    Hi David, your post is great. I have done a radio button and it works.

    My issue is that I have a chart below the crosstab, and I want this chart to show the same as the crosstab.

    Unfortunatelly, in the properties of the chart, in the Datasource you can only put a query, and not a crosstab as a DS.

    So I think I will have to create some code in the CSS file, to modify the crosstab and the chart.

    Any idea how is the code to update the chart also?

    Thanks

    Julie

    (0) 
  11. Marco Pagliotta

    Hi, i used SAP Design Studio 1.6 SP4 and i implemented the tutorial either with checkboxex and radio buttons and it works fine until i use the vertical or horizontal bar.
    Whehn i scroll or click on the bars of the crosstab the columns collapse and see only the dimensions.

    Anyone figure out why it happens?

    Thanks

    (0) 
  12. Luis Vila

    Hi Gyurasz,

    Great post. Thanks for sharing your experience.

    Have a question for you. Since this was developed in DS 1.3, is it possible in DS 1.6 to display selected columns using a single datasource in several crosstabs using a more straightforward method.

    Thanks,

    Luis

    (0) 

Leave a Reply