Design Studio: Multi Dimension selection or filter on InfoChart

[Former Member]

Hi All,

I have just started working on the SAP Design Studio and requires help from the experts in this tool. I have a simple DS based on BEx query with 1 measure - (% stock) and 2 dimensions - (Year) and structure based (Week - 53 weeks). I am trying to create a simple line graph to show all 53 weeks on X axis, % stock on y axis and 2 lines representing each year (eg, 2017 & 2018).

This graph should work as YTW graph such that the 1st line for year 2017 should show %stock for all 53 weeks and the 2nd line should show %stock till current week.

I am not able to figure out how to put filter or data selection on "week" and "year" together to filter out data which is greater than current year and current week.

The help I need is to know what "filter" or "Data Selection" expression / string (setDataSelection) I can use to see the below expected graph?

This is what I am expecting the final output...

cusersin8ajesipicturesexpected.png(5.7 kB)

Below is the initial view...

cusersin8ajesipicturesinitialview.png(17.1 kB)

This is what I see as of now...

Please let me know in case you need any additional information?

[MustafaBensan]

Hi Ajeet,

As Tammy has pointed out, your requirement cannot be implemented via front-end manipulation. It is best to model the BEx Query to output the needed restricted data set. Your requirement is very similar to that discussed in the following post, where the solution applies a combination of BEx Variables and Formula Variables:

https://answers.sap.com/questions/190549/display-of-cumulated-figures-in-design-studio.html

I suggest you review the solution in the above post with your BW Team colleagues.

Let me know if you have any questions.

Regards,

Mustafa.

[MustafaBensan]

Hi Ajeet,

By way of further clarification, can you provide screenshots showing how the Week structure has been defined in the BEx Query?

Thanks,

Mustafa.

[howsy]

Dear Ajeet,

if I understood your problem correctly, you can achieve set, by setting a filter in the "on Startup Routine".

Here is my Scripting:

1. Get the actual Date out of the Design Studio Application
2. Convert that date to "int" format (so that you can work with it)
3. Do your desired filtering for the Dimension (if you need the actual date as a filter, you can leave it as it is, with the function "substring" you can split the value, so that you have month, year and day in different variables within Design Studio)
4. Set the Filter to the DataSource (in my Case DS_TABLE).

Initial View of the DataSource:

Filtered View of the DataSource:

Regards,
Chris

[Former Member]

Thanks Tammy for providing your inputs. I always find your answers and tips very useful and informative. You are doing a great help to people like us!

I don't have any good skill set in BEx queries as usually my major focus always remained on front end reporting tools. You have provided the suggestion to make changes in the query but what I understand from my team who handles BW / BEx that we can't apply any other filters because the query is using a custom week structure (for 53 weeks) which don't use the standard calendar. So we want to have filtering done at design studio level.

Isn't there any way to put a filter having the following condition:

Exclude all rows WHERE year='current-year' AND week>'current-week'

I understand that there is an 'data selection' method to achieve this kind of results but I haven't any success on data selection on InfoChart. Would it be possible for you to give me some inputs on same?

Also, it would be of great help if you can provide me some documentation on building data selection expressions (JSON based) which can be passed to CHART.setDataSelection() function. I want to know what are the keywords which can be used (like, MEASURES_DIMENSION, etc) and the syntax to use it. Looking forward for your help!

[TammyPowlas]

Hi - I am not a JSON person; I recommend first asking the BEx query design team to look at creating an offset variable for the 53 weeks - see https://wiki.scn.sap.com/wiki/display/BI/Offset+Variables+Example or https://blogs.sap.com/2013/11/17/easy-trend-analysis-with-replacement-path/ - it is very typical to do this; your application's performance will be faster if you do it on the BEx back end.

Are you using 0CALWEEK in the Bex query? That is what I am doing. You can also try using the filter that comes with the Basic Analysis Template to see if it meets your needs.

Good luck

[TammyPowlas]

Hello - what is your data source? If it's the BEx query, I would do the filtering back there.

In this, my BEx query selects last 12 months automatically using a BEx variable:

Then I use the standard Basic Analysis template to show the line graph:

This template has built-in filters. Perhaps try this with your data to see if it meets your needs?