on 03-03-2017 8:35 AM
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);
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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().
Worked like a charm. Thanks!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
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.
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.
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".
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);
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.