Create pie chart using count / percentage function

[former_member207587]

Hi,

i have a larger raw-data table, from which I want to display a single measure in a pie chart. This measure is a kind of category and can have three values. I want to create a pie chart, which shows the summarized amount and/or percentage of all lines in this table.

How can I create this?

[TammyPowlas]

Hi Sascha - can you share screen shots of how you have tried this yourself and what/how is not working? Thank you

[former_member207587]

Here are the samples...

It is not the issue, that something is wrong. I just don't know how to use the charts in Design Studio to put out, what I need

Message was edited by: Sascha Landowski

[TammyPowlas]

Sascha - can you use the Data Selection property of the pie chart to select your desired measure/key figure?

See screen shot below:

[MustafaBensan]

Hi Sascha,

Since you have assigned the raw data source to the pie chart, it will display based on all the dimensions and members (rows) in the data source.  This is why you are seeing such a dense pie chart.  You need to define the base query or initial view to summarize the data to the level you need to display.

Can you be a bit more specific about what you mean by "I want to create a pie chart, which shows the summarized amount and/or percentage of all lines in this table."?  What level do you want to summarize amounts and percentages at?

Regards,

Mustafa.

[former_member207587]

Yes and I have tried it before. The chart doesnt change.

As I would say, the chart shows every single value oft the whole table. But I just want to show, how the values of the measure are spread.

[former_member207587]

Hi Mustafa,

my datasource is a specified query. In the query we attached an additional row which depends on another. We decided not to attach it in the cube, but use the query to display it.

As I said, it is a kind of categorization of measure. There are just 3 different values available (<=0; <100.000 and >=100.000).

But this measure just make sense in one view with specific filters. It is an amount of steps, that were taken for a specified user, a specified system and a specified user type. Once I change the view for the amount of steps in (e.g.) all systems, the measure does not make sense anymore (most of them are then >100.000).

And for this view I have a total amount of 665 rows. Now I want to create a chart, which shows the amount of category 1, 2, and 3.

Like this one: http://www.blutspende.ch/uploads/picture/translation/image/2295/blutgruppen.png

[MustafaBensan]

Hi Sascha,

Okay, I think I understand what you're trying to achieve now.  In order to display your data as required, the pie chart will need an initial view with either one of the following layouts:

1) A dimension for the category and a measure for the category count.  The category dimension should simply specify a code that corresponds to each grouping (e.g. 1, 2, 3), determined at the lowest level of dimensions user, system and user type.  The category count measure should simply define a constant value of 1;

OR

2) A separate category count measure for each category grouping (eg. Category1, Category2, Category3).  For each row in your 665 rows of data, only one of the category count measures should be populated with a value of 1 and the remaining two should be zero (again determined at the lowest level of dimensions user, system and user type). 

The way you implement the above will depend on your backend system.  What is your data source: a BW Query, a HANA View or a BusinessObjects Universe?

Once you have implemented the data source, depending on which of the two options above you chose, you can setup the Initial View in Design Studio as follows:

1) Row Axis: Category dimension

    Column Axis: Category Count measure

OR

2) Row Axis: No dimensions

    Column Axis: Each of the three Category Count measures.

With the above setup you'll get the correct rollup of data for the pie chart.  But as I mentioned, the exact implementation will depend on the backend platform.  If you can confirm what this is I can try to provide further guidance.

Regards,

Mustafa.

[former_member207587]

Hi Mustafa,

thanks for your detailed answer. But I'm not sure, wether I have understand what do you meant with

Mustafa Bensan wrote:

[...]the pie chart will need an initial view[...]

In the window for edit the initial view of a data source, I can't find the possibility to add a dimension and measure. Do I have to edit this in the backend or QueryDesigner?

[MustafaBensan]

Hi Sascha,

Can you provide a complete screenshot of your Edit Initial View window?

Thanks,

Mustafa.

[former_member207587]

Here is my - already edited - initial view (window).

[MustafaBensan]

Hi Sascha,

It looks like your data source is BW.  After giving your requirement some further thought I think the only way to achieve the desired result is as follows:

1.  Create a BEx Query with the same structure as the Initial View in your screenshot.  To this query you should add 3 formula columns, one for each possible value of Formel 1.  The calculation in each of the 3 calculated columns should define the following boolean logic:

1st Formula Column: Formel 1 = 1

2nd Formula Column: Formel 1 = 2

3rd Formula Column: Formel 1 = 3

The above formula columns will act as counters, which will later be aggregated.

2.  Create an InfoCube with characteristics "Benutzer Sysubergr", "SAP Benutzertyp", "SAP System" and key figures Category 1 , Category 2, Category 3;

3.  Load the results of the query in Step 1 into the new InfoCube;

4.  Create another query over the new InfoCube, which has nothing in the row axis and only the three key figures in the column axis;

5.  The query in Step 4 should then be used as the Design Studio data source for the pie chart.

To derive your required calculations at the right level of aggregation, I don't think it can be done without creating a new InfoCube and loading as described above.  Perhaps others might have alternative ideas.

Regards,

Mustafa.