cancel
Showing results for 
Search instead for 
Did you mean: 

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

former_member358236
Participant
0 Kudos

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);

Accepted Solutions (1)

Accepted Solutions (1)

MustafaBensan
Active Contributor

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.

former_member358236
Participant
0 Kudos

Hi Mustafa,

first at all thanks for your very helpful answer!

The row count works very fine.

But there's still the problem with the filtering of a numeric (also not numeric) range.

If I try to apply a range from 200 - 300, the application throws an error message with the content "Dimension VOS does not support interval selection". It would be really nice if you could also help me with this problem.

If you need more information in detail about this problem, just write an answer.

I'm sitting in front of this problem since a few days now and it's just really annoying.

Regards,

Vu

MustafaBensan
Active Contributor
0 Kudos

Hi Vu,

For future reference, as per the SCN Rules of Engagement, since this question is now closed and you have a different issue, you should post a new question so that it can be addressed accordingly.

That being said, for your range filter, can you post the script code you have applied? Are you using the setFilterExt() method? Looks like your data source is a Universe, is that right?

Regards,

Mustafa.

former_member358236
Participant
0 Kudos

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

MustafaBensan
Active Contributor
0 Kudos

Hi Vu,

Can you provide an example of the string value that is returned by getFilterExt() in your case?

Thanks,

Mustafa.

former_member358236
Participant
0 Kudos

Hi Mustafa,

if I do something like "TEXT_1.setText(FP_DS.getFilterExt("dimension")) and apply a filter like AU383 - AU390, it returns AU383 - AU390.

former_member358236
Participant
0 Kudos

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().

Answers (4)

Answers (4)

former_member805792
Discoverer
0 Kudos

Worked like a charm. Thanks!

MustafaBensan
Active Contributor
0 Kudos

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.

former_member358236
Participant

Hi Mustafa,

yes that's correct. At first I wanted to find a less complicated solution for my problem but unfortunately I couldn't.

Regards,

Vu

MustafaBensan
Active Contributor
0 Kudos

It seems the only other option would have been to redefine the input prompt in the Universe IDT.

former_member358236
Participant
0 Kudos

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).

MustafaBensan
Active Contributor
0 Kudos

Hi Vu,

setVariableValueExt() uses the SAP Business Explorer Selection Syntax which is probably not applicable to Universe data sources. You will need to define the correct logic in the Universe prompt definition to support ranges. setVariableValueExt() simply sets the variable value as an input string. The way the input string is handled, including ranges, must be defined in the Universe itself.

However, I am not clear on why you are trying to use setVariableValueExt()? You should be able to achieve the desired result of reducing the data set by loading the data source in script and immediately after, executing a setFilter() or setFilterExt(). The range should then work.

Regards,

Mustafa.

former_member358236
Participant
0 Kudos

My FP_DS (Datasource for the Crosstab) has over 300.000 Datasets in the Initial View.

The setFilterExt() method doesn't work, if in the Initial View is too much data so it can't display them.

What do you mean with:

"You should be able to achieve the desired result of reducing the data set by loading the data source in script and immediately after, executing a setFilter() or setFilterExt(). The range should then work."

How do I reduce the data set, if I don't want to loose any of the data (300.000 datasets).

And what do you mean with:

"The way the input string is handled, including ranges, must be defined in the Universe itself."

Do you mean in SAP Design Studio or in the tool I use for designing the Universe (SAP Information Design Tool).

Regards,

Vu

former_member358236
Participant
0 Kudos

My FP_DS (Datasource for the Crosstab) has over 300.000 Datasets in the Initial View.

The setFilterExt() method doesn't work, if in the Initial View is too much data so it can't display them.

What do you mean with:

"You should be able to achieve the desired result of reducing the data set by loading the data source in script and immediately after, executing a setFilter() or setFilterExt(). The range should then work."

How do I reduce the data set, if I don't want to loose any of the data (300.000 datasets).

And what do you mean with:

"The way the input string is handled, including ranges, must be defined in the Universe itself."

Do you mean in SAP Design Studio or in the tool I use for designing the Universe (SAP Information Design Tool).

Regards,

Vu

MustafaBensan
Active Contributor
0 Kudos

Hi Vu,

See my comments below:

What do you mean with:

"You should be able to achieve the desired result of reducing the data set by loading the data source in script and immediately after, executing a setFilter() or setFilterExt(). The range should then work."

How do I reduce the data set, if I don't want to loose any of the data (300.000 datasets).

1. Set the Load in Script property of data source CT_DS to true;

2. In the "On Apply" event script of the filter panel include code like this:

if (!CT_DS.isInitialized()) {

CT_DS.loadDatasource();

}

CT_DS.setFilterExt(FP_DS.getFilterExt("dimension"));

And what do you mean with:

"The way the input string is handled, including ranges, must be defined in the Universe itself."

Do you mean in SAP Design Studio or in the tool I use for designing the Universe (SAP Information Design Tool).

I mean in the SAP Information Design Tool.

Regards,

Mustafa.

former_member358236
Participant
0 Kudos

Hi Mustafa,

Thanks for that good advice, but unfortunately it didn't work.

The filter still doesn't work.

Do you have any other idea?

Regards,

Vu

former_member358236
Participant
0 Kudos

Maybe I do have to remove my Query Filters in the "Edit Query Specification" of CT_DS?

I have 5 Query Filters with "In List" in it, do you think it has something to do with that?

former_member358236
Participant
0 Kudos

By the way this is my "On Apply" - Code of the Filter Panel:

if (!U_FZG_LIST.isInitialized()) {
U_FZG_LIST.setFilterExt("_wHmMsOoLEeaB-JtworBC7A",DS_BZD_FILTER_DERIV.getFilterExt("_6wmtAPNyEeam2IYRVom_Uw"));
U_FZG_LIST.loadDataSource();

}

MustafaBensan
Active Contributor
0 Kudos

Vu,

Your code is not correct. You need to apply it in the EXACT sequence I have specified in my example. So it should look like this:

if (!U_FZG_LIST.isInitialized()) {
U_FZG_LIST.loadDataSource();
}
U_FZG_LIST.setFilterExt("_wHmMsOoLEeaB-JtworBC7A",DS_BZD_FILTER_DERIV.getFilterExt("_6wmtAPNyEeam2IYRVom_Uw"));

Also, make sure the Load in Script property of data source CT_DS is set to true;

Regards,

Mustafa.

former_member358236
Participant
0 Kudos

I know that my sequence was wrong, but I tried yours before that and it didn't work both.

Now I tried your code again and it still doesn't work.

No matter what I filter, the result set is always too large.

That's why somehow I need to reduce the data source itself with setVariableValueExt(), but with that method a range doesn't work, because in Edit Query Specification I choose "In List". I can't choose "In List OR Between".

MustafaBensan
Active Contributor
0 Kudos

Can you confirm that you also set the Load in Script property of data source CT_DS to true?

former_member358236
Participant
0 Kudos

Hi Mustafa,

yes I can confirm that.

former_member358236
Participant
0 Kudos

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);

former_member358236
Participant
0 Kudos

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