Skip to Content

SAP Design Studio - Counting the Number of Rows in Crosstab not working

Hello dear community,

I have a problem with counting the records/members in a crosstab. I have 5 filter criteriums:

Project, OEsG, Costcenter, Group and VOS.

Example:

I filter project = '12345' or project = '56789' works fine, also the count works fine. (I made the count records in a TextBox, where I can see the number of rows). For example here I get a count of 1000 members. Now I want to filter a numeric range with the filter criterium 'VOS' between 300 and 400.

The members in the crosstab change to for example 500 records, but the count in the textbox is still showing 1000. And this only happens when I try to filter a range.

When I make the filter criterium VOS = 300, 310, 320, 320 ... 400, then it works fine.

-> The rows in the crosstab change to 500 AND also the count in the textbox changes to 500.

I only have the count problem, when I apply a range.

Can you help me please?

My code for the record count is like:

DS_1.reloadData();

DS_1.loadDataSource();

var getarray = DS_1.getMembers("_wHEBMOoLEeaB-JtworBC7A", 20000);

var membercount = getarray.length;

DS_1.setText("Number of Rows: " + membercount);

DS_1.setVisible(true);

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Mar 03, 2017 at 12:01 PM

    Hi Vu,

    Using getMembers() is not a reliable way to count the records in a data result set because getMembers() actually obtains members from the master data and not the data result set. An alternative approach you can try is as follows:

    1) In the data source initial view, pick one of the measures and set the Calculate Totals As option to "Total Number of Detailed Values";

    2) In the "On Result Set Changed" event script of the data source, specify code similar to the following:

    var rowCount = DS_1.getData("<measure technical ID>", {});
    TEXT_1.setText(rowCount.formattedValue);
    

    Regards,

    Mustafa.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mustafa,

      my problem is that I can't apply a range to a setVariableValueExt().

      Do you have an idea, how I can apply a range to a setVariableValueExt() ? In my "Edit Query Specification" I have the Query Filter "In List" for all filter criteriums (Project, OEsG, VOS, Group, Costcenter).

      The first thing I have to do with the filter is ALWAYS setVariableValueExt() because in Initial View there is too much data, so it can't show. And the normal filter doesn't apply to a defect Initial View. That's why at first I have to use the setVariableValueExt().

  • Mar 13, 2017 at 07:25 AM

    Hi Mustafa,

    sorry, I will be mindful for that in the future. By the way it's really nice that you answer so quick, it's very helpful.

    Yes my data source is a Universe, built up in SAP Information Design Tool. The universe deals with an Oracle Database.

    My filterpanel and my crosstab, where I want to display my key performance indicators, have different data sources.

    Hereafter I will describe the data source of my filterpanel as "FP_DS" and the data source of my crosstab as "CT_DS".

    Why do we have to use two different data sources?

    -> Because the key performance indicators have to be calculated on the basis of over 300.000 datasets. And within these 300.000 datasets we have about 8 columns we want to display in the crosstab, but only 5 columns of them should be able to be filtered in the filter panel (and therefore have to bring all 8 columns into the "Edit Query Specification") and so there are more than 20.000 possible combinations -> The filter wouldn't even show data to filter. (and so does the crosstab).

    So we made two different data sources.

    FP_DS contains a list of values of the distinct values (otherwise it would be a cross join of all possble combinations and that would be over 20.000 allowed combinations) from CT_DS. In the "On Apply"-Event of the Filter Panel we made something like this:

    CT_DS.setVariableValueExt("pmProject", FP_DS.getFilterExt("ext_key_of_project"));

    We have to use setVariableValueExt instead of setFilterExt because we need to reduce the amount of data from CT_DS first, otherwise the filter wouldn't apply. But with setVariableValueExt applying a range doesn't work anymore.

    I hope you can help me with this problem, too.

    Regards,

    Vu

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 14, 2017 at 05:37 AM

    Hi Mustafa,

    my problem is that I can't apply a range to a setVariableValueExt().

    Do you have an idea, how I can apply a range to a setVariableValueExt() ? In my "Edit Query Specification" I have the Query Filter "In List" for all filter criteriums (Project, OEsG, VOS, Group, Costcenter).

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Mustafa,

      finally I got it working with a little array workaround:

      var filter = Convert.replaceAll(DS_BZD_FILTER_DERIV.getFilterText("_6wq-cPNyEeam2IYRVom_Uw"),"; ", ",");
      var counter = 0;
      var filterArray = filter.split(",");
      var filterString = "";
      var min = "";
      var max= "";
      var loopMark = 0;

      filterArray.forEach(function(element,index) {

      if (element.indexOf("-") == -1) {
      if (counter == 0)
      {
      filterString = element;
      counter = counter+1;
      }
      else
      {
      filterString = filterString + "; " + element;
      }
      }
      else {
      min = element.substring(0,element.indexOf("-") - 1);
      max = element.substring(element.indexOf("-")+ 2);
      var loopArray = DS_BZD_FILTER_DERIV.getMembers("_6wq-cPNyEeam2IYRVom_Uw", 100);

      loopArray.forEach(function(element2,index2) {
      if (Convert.stringToInt(element2.text) >= Convert.stringToInt(min) && Convert.stringToInt(element2.text) <= Convert.stringToInt(max)) {
      if (counter == 0) {
      filterString = ""+element2.text;
      counter = counter+1;
      }
      else {
      filterString = filterString + "; " + element2.text;
      }
      }
      });
      }
      });

      U_FZG_LIST.setVariableValueExt("pmVOS", filterString);

  • Mar 17, 2017 at 07:21 AM

    Hi Vu,

    Thanks for sharing your filtering solution. It's unfortunate that you've had to jump through those hoops though. So if I understand correctly, it looks like the script converts the ranges into a string of individual delimited member values which is then compatible with the input prompt definition. Is that right?

    Regards,

    Mustafa.

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 06 at 01:03 PM

    Worked like a charm. Thanks!

    Add comment
    10|10000 characters needed characters exceeded