Skip to Content
0

InfoChart - column & line chart, how to structure data source?

Feb 23, 2017 at 05:43 PM

288

avatar image

Hello,

I have been struggling with this for weeks now. Can anyone please advise how I need to build my BW query so that I can achieve the following chart?

Design Studio places the key figures (Sales & Revenue) and dimensions incorrectly for me (swaps them round putting Sales & Revenue in the legend instead of x-axis).

Currently, my BW query has the following structure:

I would be grateful for any clues, please.

AL

fhaxm.png (33.4 kB)
hpewq.png (78.6 kB)
10 |10000 characters needed characters left characters exceeded

How about using Analysis Office for this requirement?

It works!

See below

1agataao.jpg (52.3 kB)
0

Tammy,

Unfortunately, I am trying to build a individualised DS dashboard that will be used by each user in my organisation. So Analysis is not the right tool. Of course, it will do what I want, since it's just Excel and Excel is clever ;-) DS is not...

A.

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Mustafa Bensan Feb 24, 2017 at 12:17 AM
0

Hi Agata,

I am guessing that with your current query structure, what you are seeing is "John Doe" and "% of all sales" as x-axis labels, with Sales and Revenue in the legend (as you've indicated). This is exactly the behaviour I would expect because key figures are always assigned to the chart legend.

Basically, you need to come up with a structure whereby "John Doe" and "% of all sales" are treated as key figures and "Sales" and "Revenue" are treated as dimension members. This is why Tammy's Analysis Office example works since the data table has been structured in this way. If you are able to use Analysis Office to setup a data table as such then perhaps a smartcopy to Design Studio might work.

Otherwise, the solution could turn out to be a backend data modelling issue. However, before we go down that route, can you provide the following so that I can make further suggestions accordingly?

1) BEx Query screenshots of the detailed definitions of the key figures Sales and Revenue as well as the two dimensions in the rows;

2) A screenshot of the Initial View in Design Studio of the data source that is based on your BEx Query.

Regards,

Mustafa.

Show 9 Share
10 |10000 characters needed characters left characters exceeded

Mustafa,

Thank you. Yes, I know now that DS binds key figures with the legend and I can't swap axises, and I struggle to understand why DS would do such a thing to their users...

I have tried to structure my data source so that key figures are in the "John Doe" & "% of all sales" rather than Sales & Revenue but I always ended up with something like "John Doe Sales", "John Doe Revenue", "% of all sales Sales", "% of all sales Revenue", but it would only make things worse. I did do this approach when I had just one key figure in my query and managed to achieve what I needed. But if there are more than one key figures I simply fail.

Here's what is sitting underneath my key figures - Sales & Revenue (in real life they are called WIP & Debt):

And here's what sitting in "John Doe" & "% of all sales":

plus I add a formula which calculates % of the former to the latter (Matter manager %A MM office):

Just to add, this exact structure is correct when I use for example pie charts (for just John Doe vs his office), but doesn't work for the InfoChart column & line.

Here's what DS is doing with my data source:

Any ideas? Thank you!

A.

t9c4u.png (70.5 kB)
hzkoy.png (63.4 kB)
n4gx1.png (29.0 kB)
djspz.png (46.1 kB)
3frki.png (35.5 kB)
hrlcd.png (19.5 kB)
0

Here's what was my first attempt at placing key figures in the right places, so that DS would show them in the right place:

This is my BW query structure:

Debt & WIP in the columns are just empty labels to help me with orientation. The actual key figures are sitting in line 2,3, 6 & 7 alongside with variables getting user name and their office/practice. Line 4 & 8 are formulae calculating %. At InfoChart level i will only display lines 2,4,6&8 and this is what I want to display in the legend (although only as two lines, not four).

In the rows, Debt & WIP are labels that I want to show on the x-axis.

Here's how line 2 & 3 (Debt/Sales data) are structured:

plus the formulae (line 4).

Accordingly, Revenue/WIP key figures (6&7) are structured:

plus % formula, line 8.

In DS I select line 2,4, 6 & 8 to appear on the chart (which would be John Doe Debt/Sales, % of all sales Debt/Sales, John Doe WIP/Revenue, % of all sales WIP/Revenue):

And the InfoChart looks like this:

I have Debt(Sales) & WIP(Revenue) on the x-axis at last, and my John Doe (Marcin) & % of sales are in the legend. But that's not exactly perfect, as everything is doubled... I also, can't see an option to indicate what's on primary and what's on secondary axis. Notice how the first % is on the wrong axis (you can't really see it as it's close to x-axis).

I will try to define further contents in the structure at cell level and hide/change some of the entries and will let you know how I come along.

jx1ub.png (155.4 kB)
cryuh.png (93.5 kB)
yazma.png (100.9 kB)
jdbwk.png (74.4 kB)
wnqqv.png (88.3 kB)
a7cpe.png (81.9 kB)
7xut3.png (22.9 kB)
0

Eureka!

I have managed this at last!

I have defined my BW query cells further, moving them to the right places:

In DS I am selecting the following measures to display on the InfoChart:

DS InfoChart looks like this:

Done. Working! Shame, shame, shame Design Studio that I have to go through this to build a simple chart. Something that takes literally 5 seconds to build in Excel! Please bring back swap axis option and disconnect measures from the legend - let the users decide how they want to display their data, please. You got it wrong.

oetba.png (43.8 kB)
swrgd.png (48.2 kB)
zxslr.png (21.5 kB)
1

Hi Agata,

Thanks for sharing your solution. Your persistence has certainly paid off, even though the workaround has forced you to jump through a few hoops in the process :) Let's hope future enhancements make implementing this type of scenario simpler out-of-the-box.

Regards,

Mustafa.

0

Thanks Mustafa,

I really do hope SAP will re-introduce axis swapping in their future DS releases. The above was just a very simple scenario I was trying out. In real life, the requirements tend to be much more complex and building BW query for what I am trying to do will probably take ages :-)

Thank you again for taking your time reading this topic.

A.

1

Hi Agata,

You're welcome. I was happy to read the topic and follow the interesting journey to a solution :)

Regarding the axis swapping, I noticed on Slide 49 of the latest Lumira/Design Studio Roadmap that there will be a "Use Measures as a Dimension" option which might address the scenario you've described in this post. Let's wait and see.

Mustafa.

1

Hi Agata,

Thanks for the very detailed information. The issue is now perfectly clear. Although I think it is an inflexibility resulting from having a non-standardised KPI framework in the modelling of the data warehouse, I will give it some further thought to see if I can come up with any ideas to structure the BEx Query in such a way as to achieve your desired result.

I'll get back to you with my findings,

Mustafa.

0

Mustafa, thank you for your time. I do not feel like there is anything non-standard in the way we show our KPIs - for many of our reports we show the users' or groups' results compared with other groups/peers/country/world etc. And we sometimes use a number of (similar but different) key figures to display on a given chart, for different time periods (current month, last month, previous quarter, year) sometimes broken down by month/quarter further etc. I think it's pretty standard and very simple charts I am talking about :-)

And Excel or WAD don't seem to have a slighest problem with it, they show the data correctly from the start - rows end up on x-axis, columns end up in the legend. It's the other way, the one preferred by DS, that is awkward for Excel/WAD and I would need to force them to display data the other way round, by swapping axises.

I really struggle to understand why DS decided to rigidly bind key figures to legend, and not letting users decide - it's just counterintuitive and restrictive in my opinion. And looking at what I need to go through to structure my data source, I think it's just a deal breaker. I think this is a massive flaw in the product that should be corrected asap. I have been struggling for a couple of months now to build a very simple dashboard in DS, something that would take me max 1-2 days to do in Excel (and that includes building BW queries) and I am finding it harder and harder to explain to my boss what is taking so long :-)

0

Hi Agata,

Actually, I didn't mean the display/reporting of your KPIs was non-standard. Your examples are perfectly legitimate. I was more referring to a standardised data model where KPIs are differentiated by a KPI dimension and Key Figures are generic, with one for each data type (eg amount, qty, number etc) instead of a key figure for each KPI, like "ASR WIP". This just provides more flexibility when you structure queries such that you can support the scenarios such as your example.

That being said, curiosity got the better of me so I replicated your query structure and will now eat humble pie after seeing and understanding the issue more clearly :) The screenshots below show the data source and chart:

I see that technically, Design Studio will not let you define what should be considered a "Data Series" for the purposes of a chart (like Excel/WAD does as you stated). In this case, we would like the data series to be the "John Doe" and "% of All Sales" columns but as you have shown, Design Studio forces the series to be based on the measures/key figures rather than what I will call "data columns".

I'm not sure if there's going to be a straightforward way to workaround this limitation but will let you know if I come up with anything.

Of course you could evaluate the 3rd party charts available on the Analytics Extensions Directory to check if any can support your scenario.

Regards,

Mustafa.

datasource.png (60.9 kB)
chart.png (68.0 kB)
1