cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieveing totals and number of records BW qry

eddy_declercq
Active Contributor
0 Kudos

Hi,

I want to retrieve the totals and number of record of BW query in order to put in variable for further calculations, but I don't see how I can do this.

Any suggestions?

Cheers

Eddt

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

I am not BW guy though.

But I have a key figure in my BEx query as Number of Records (1ROWCOUNT). I am sure this column is not from base infocube or any calculation.

May be your BW team can easily relate it. Attached the screenshot

eddy_declercq
Active Contributor
0 Kudos

Hi,

If I have the 1ROWCOUNT, how could I retrieve it, since getdata() is for measures and not keyfigures, is it?

Cheers

Eddy

MustafaBensan
Active Contributor
0 Kudos

Hi Eddy,

Can you clarify why you need to do the calculations in Design Studio?  You should be able to perform all the necessary calculations directly in the BEx Query.

Regards,

Mustafa.

MustafaBensan
Active Contributor
0 Kudos

By the way "Key Figure" is the same as "Measure", so technically you should be able to use getData() to access the value.  However, I still question why the calculation needs to be performed in Design Studio rather than BW?

eddy_declercq
Active Contributor
0 Kudos

I give a fictive example on what it should be.

The end user selects a country. He sees a dashboard with 3 figures on it:

- total of inhabitants within that country

- calculated percentage per gender

- total of newborns

MustafaBensan
Active Contributor
0 Kudos

Hi Eddy,

You can and should perform all of those calculations in the BEx Query.  If you would like to provide a screenshot of the BW infocube that your query is based on then I could provide more specific guidance.

Regards,

Mustafa.

eddy_declercq
Active Contributor
0 Kudos

We'll manage what the Bex size matters. I'm trying the getdata out, but it doesn't seem to recognize any of the measures I'm specifying. Should I do something first with my datasource before I can use this? Do I need the description, technical name or something the else of the measure?

MustafaBensan
Active Contributor
0 Kudos

Hi Eddy,

You should specify the technical ID of the measure (key figure) which you can obtain from the properties panel of the BEx Query Designer.

Regards,

Mustafa.

MustafaBensan
Active Contributor
0 Kudos

The technical ID of the key figure should look something like this "006EIC2OSTY2YAK2X13HZB34Y".

eddy_declercq
Active Contributor
0 Kudos

Now that's very user friendly indeed

MustafaBensan
Active Contributor
0 Kudos

Did you manage to find it in the BEx Query Designer?

eddy_declercq
Active Contributor
0 Kudos

Yes, but unable to make it work.

I gave a filter panel, I put something simple in the on apply:

var totaal = DS_1.getDataAsString("518VMJ3XVEQAGE7DE23GMMVCD", "");

APPLICATION.alert(totaal);

and get

  • Error during script processing

    Error during script processing. Contact the Application Designer to resolve the issue

No clue what the problem might be. 518VMJ3XVEQAGE7DE23GMMVCD is a key figure.

I didn't specify any filter, since I assume that the filter panel will filter it automatically. If not; how can I specify that the filter should the selected filter in the filter panel.

MustafaBensan
Active Contributor
0 Kudos

You should replace the second parameter of getData to read {} instead of "".

eddy_declercq
Active Contributor
0 Kudos

That works indeed. But the filter from the filter panel doesn't seem to be applied. How can I achieve that?

MustafaBensan
Active Contributor
0 Kudos

Hi Eddy,

To allow me to provide further guidance, can you please provide screenshots of the following?:

1)  Your Design Studio Application launched in the browser and showing the filter panel;

2)  Your data source Edit Initial View window;

3)  The Properties panel of your Filter Panel component.

Thanks,

Mustafa.

MustafaBensan
Active Contributor
0 Kudos

Hi Eddy,

Normally the recommended approach is to perform all of the calculations in the backend if possible.  With scripting you can obtain the total for a particular measure with the data source getData() method, by specifying the measure and excluding the dimension for which you want the total.  However, I'm not aware of any good method whereby you can get the number of records for the data set via scripting.  The getMembers().length method might help with small data sets of less than 100 rows which do not include hierarchies.

My recommendation is to push the calculations to the backend.  If you can be a little more specific about the measures, dimensions and type of calculation you'd like to do then we can try provide suggestions accordingly.

Regards,

Mustafa.