Presentation of a query in different crosstabs

[rahulvilas_chaudhari]

I have a requirement to present the query in different formats. E.g. Query returns with 12 key figure values and I need to separate these 12 KFs in separate cross tabs.

E.g.

Query output in DS 1.

KF1	KF2	KF3	KF4	KF5	KF6	KF7	KF8	KF9	KF10	KF11	KF12
100	120	140	160	180	200	220	245	265	285	305	315

Output expected in Crosstabs.

Crosstab 1:

KF1	KF2	KF3
100	120	140

Crosstab 2:

KF4	KF5	KF6
160	180	200

and so on..

- Prime requirement is to execute the query only once but distribute to multiple crosstabs (with different fields in visible).

- Query execution time is considerable, hence we need to reduce the execution of the same multiple times.

- Query views has been attempted but no performance gain.

Thanks,
Rahul

[IngoH · Accepted solution]

Hello Rahul,

do you need the tables to be visible at the same time ?

In case the answer is yes, then you would create BEx Query views in BW and leverage the different views in Design Studio.

In case the answer is No, then you could change the view of the data in the application on the fly.

regards

Ingo Hilgefort, SAP

[rahulvilas_chaudhari]

Hi Ingo,

I used another syntax available for cell usage.

I wrote below code for On Startup event:

TEXT_1.setText(DS_1.getDataAsString(

"009ZVKYB3L9Q2CAH0ZN2B6KG1", { "0FISCVARNT": "Z1"}));

This populated text field with value from table cell & I could re-arrange the single executed datasource.

Sincerely appreciate your guidance. Thanks.

Rahul

[rahulvilas_chaudhari]

Hi Ingo,

Yes, I need these tables to be visible at same time.

We have a query will all key figures.

We tried following approach:

- Single query

- Multiple queries

- Multiple views based on single query

- Multiple datasources based on single query

Execution time for single query is 2.47 sec (in RSRT) and when we create multiple datasources or multiple views or multiple queries approach in design studio, it exponentially increases to 1.5 - 2 min.

We are attempting to troubleshoot reason for delay and at the same time trying to see possible option if we can use single datasource (multiple crosstabs based on single execution).

Thanks,

Rahul

[IngoH]

Hello Rahul,

- single query would not work because as you said you need different views visible at the same time.

- multiple queries will be more expensive than query views

- multiple views - you can define them in Design Studio or in BW, recommended is to create the query view in BW already.

- Multiple data sources on the same query and I assume you used the initial view editor then, that will be more expensive than having the query view in BW.

I would go with having the query views defined in BW already and then troubleshoot step by step to see where that increase is coming from

regards

Ingo Hilgefort, SAP

[rahulvilas_chaudhari]

Hi Ingo,

We have tried multiple views i.e. for above example 4 views were created. Still time taken by design studio increased to approx 2 mins.

Any particular way to use these views in DS.

Thanks,

Rahul

[IngoH]

Hello Rahul,

you mentioned that you tried the views but then you seem to ask how to use the views.

You would leverage the Query Views directly in Design Studio.

Ingo

[rahulvilas_chaudhari]

Hi Ingo,

You are very correct, we have used query views in creation of DS's.

First approach was (long execution time):

- Created 9 views on single query

- Created DS for each query view

Now that we have divided query into 3 separate queries.

- Created 3 DS's on each query

- Separated execution of DS by Tab

Shall we try to create 3 views (on each query) instead ?

Thanks for your guidance.

[IngoH]

Hello Rahul,

you should define the Query Views in BW already and then point Design Studio to those Query Views

regards

Ingo

[Former Member]

Hi Rahul,

I think what you have asked is not possible yet in Design Studio, there is no particular way of filtering the data in different crosstabs from a single Data Source, you can use the same Query three times in Different Aliases and then edit it accordingly.

Thanks,

Fazith.