Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: How to do query to obtain multiple pie charts

  1. #1
    Join Date
    Dec 2012
    Posts
    121

    Default How to do query to obtain multiple pie charts

    Hi all,
    can someone post how can i format a query to plot a multi pie chart (in mdx and sql)?

    I didn't find any documentation...

    Thanks,
    Yamas

  2. #2
    Join Date
    Jan 2013
    Posts
    135

    Default

    Hi marcolino,

    I'm using SQL queries and it works:

    SELECT
    table.var1, table.var2, sum(table.shownvalue)
    FROM
    table
    GROUP BY
    table.var1,
    table.var2
    ... as output I have on y-axis var1 values and var2 is for multiple charts. I got it working only that first graph is for all and others are with detail per value var2.

    Regards,
    timfu83

  3. #3
    Join Date
    Mar 2012
    Posts
    645

    Default

    The simplest way to setup multi-charting is to use the multiChartIndexes option, available as a CDE property.
    Most of the time, specifying a value of 0 and supplying an additional categorical column in the resultset will correspond to what you want.

    From the documentation, these indexes are:
    The indexes of the data source's virtual item columns that are to feed the default multiChart,multiChart2, ... dimensions.
    Ok, so what's that virtual item thing?


    Relational data-set

    For a relational data set (crosstabMode=false), the virtual item structure coincides with that of the resultset structure.
    So, for a resultset like:

    Code:
      0              1              2              3
    | Categorical1 | Categorical2 | Categorical3 | Measure1 |
    specifying multiChartIndexes = 0, and nothing more, will result in the following mapping into CCC dimensions:

    Code:
    CCC Dimension <-- CDA ColumnName
    --------------------------------
    multiChart    <-- Categorical1
    series        <-- Categorical2 (default mapping)
    category      <-- Categorical3 (idem)
    value         <-- Measure1     (idem)

    Crosstab data-set


    For a crosstab data set (crosstabMode=true), the virtual item structure is built by relationalizing the cross table.
    For a resultset with a single MEAsure column, a single COL column, but multiple ROW columns:

    Code:
    | ROW1 | ROW2 | COL1_value1/MEA1 | COL1_value2/MEA1 | COL1_value3/MEA1 |...
    the virtual item takes the following relational structure:

    Code:
      0      1      2      3
    | COL1 | ROW1 | ROW2 | MEA1 |
    So, specifying multiChartIndexes = 0, and nothing more, will result in the following mapping into CCC dimensions:

    Code:
    CCC Dimension  <-- CDA ColumnName
    ---------------------------------
    multiChart     <-- COL1
    series         <-- ROW1 (default mapping)
    category       <-- ROW2 (idem)
    value          <-- MEA1(idem)
    If you prefer to map the COL1 column to the series dimension, you would specify multiChartIndexes = 1, resulting in:

    Code:
    CCC Dimension  <-- CDA ColumnName
    ---------------------------------
    multiChart     <-- ROW1
    series         <-- COL1 (default mapping)
    category       <-- ROW2 (idem)
    value          <-- MEA1 (idem)
    If you're curious to know why the multiChartIndexes property has a plural name, then try to specify additional indexes, like [0, 1].
    CCC will create dimensions multiChart and multiChart2 and combine then into a single visual role (multiChart) - they will show up like this: "multiChartValue ~ multiChart2Value".
    Last edited by duarte.leao; 04-30-2013 at 08:20 AM.

  4. #4
    Join Date
    Dec 2012
    Posts
    121

    Default

    Hi guys,
    thanks for the answers. I appreciated it. However i was not able to obtain a multi chart.

    I post my MDXquery and the output, so someone might help me.

    The query:
    SELECT
    NON EMPTY CrossJoin([Operatore.default].[operatore].Members, {[Measures].[Latenza Massima]}) ON COLUMNS,
    NON EMPTY {Hierarchize({{[Localita.default].[CENTRO]}, Filter({{[Localita.default].[Regione].Members}}, (Exists(Ancestor([Localita.default].CurrentMember, [Localita.default].[AreaTerritoriale]), {[Localita.default].[CENTRO]}).Count > 0))})} ON ROWS
    FROM [MyCube]


    On the attachment there is the output of the query.
    Name:  Immagine.jpg
Views: 491
Size:  21.0 KB

  5. #5
    Join Date
    Mar 2012
    Posts
    645

    Default

    Hi marcolino,

    This is not my area, but try specifying CCC chart option «dataMeasuresInColumns: true».

  6. #6
    Join Date
    Jan 2013
    Posts
    135

    Default

    Hi Marcolino, did you find the answer and are your multiple charts working? I can not get it to work and don't know what to do. As I see is your query crosstab format but how to name that categories when I don't know how many there are?
    Thanks a lot for ideas how to get cube dataset working in multiple charts.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.