Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: get different columns from CDA query

  1. #1
    Join Date
    Dec 2012
    Posts
    121

    Default get different columns from CDA query

    Hi all,
    i have a BAR chart (CCC2) and i want to display on it only a subset of columns coming from mdx query.
    Is it possible to set it from code on barchart object?

    Thanks in advance.
    Yamas

  2. #2
    Join Date
    Dec 2012
    Posts
    121

    Default

    Hi guys,
    i solved myself the issue.
    I defined in CDA file an outputIndex.
    Then, from the chart, i change the output index to get the desidered columns.

    Yamas

  3. #3
    Join Date
    Dec 2012
    Posts
    121

    Default

    Hi,
    i ask if it possible to set the outputIndex not from cda file, but from any barchart options!

    Thanks in advance.
    Yamas

  4. #4
    Join Date
    Mar 2012
    Posts
    645

    Default

    Just wanna add that this is also possible to do with CCC2, by using the readers property.
    But to provide a concrete answer, I would need a concrete example - a resultset and its intended use :-).

  5. #5
    Join Date
    Dec 2012
    Posts
    121

    Default

    Hi duarte.leao,
    first, thanks for your response.

    I explain better my situation.
    I have a ccc2 barchart which use a MDX query to populate itself. However, the SERIES are dynamic, depending from a multi-selection control.
    I have the following query:

    set [filter_date] as '
    ANCESTOR([Tempo.default].[${p_start_year}].[${p_start_month}].[${p_start_day}],[Tempo.default].[day]):ANCESTOR([Tempo.default].[${p_end_year}].[${p_end_month}].[${p_end_day}],[Tempo.default].[day])'

    select


    UNION(
    UNION(
    UNION(
    Crossjoin({[Measures].[Thr. Down. Medio]}, { ${p_provincia} } ),
    Crossjoin({[Measures].[Thr. Down. Massimo]}, { ${p_provincia} })),
    Crossjoin({[Measures].[Numero Device]}, { ${p_provincia} })),
    Crossjoin([Measures].[Numero Test] , { ${p_provincia} }))
    ON COLUMNS,
    {[filter_date]} ON ROWS
    from [MyCube]


    where ${p_provincia} is the result of a multi-select component (please see the image on attachment).
    The output of this query is also displayed on the attachment.
    Now, the issue i have is the following. i want to display only a subset of this output query, i.e. only the [Measures].[Thr. Down. Medio]. However, i cannot define an Outputindex on CDA file because this Measure depends on ${p_provincia}. So, if i select only on province, there will be a single trend, if i select two province, there will be only two trends, etc.
    So, how can i extract in a dynamical way the columns on i'm interested to display on ccc2 barchart?


    Thanks.

    Yamas.



    Name:  img.jpg
Views: 103
Size:  23.5 KB

  6. #6
    Join Date
    Mar 2012
    Posts
    645

    Default

    Hi,

    the crosstab resultset format is, internally, transformed to an intermediate relational format.
    So the dynamic series x measure columns end up as multiple rows of a shared category.

    On row like this one:

    Code:
    C1 | S1M1 | S1M2 | S2M1| S2 M2 | ...
    is transformed into multiple rows in an intermediate (virtual item) format:

    Code:
    Ser  Cat Value
    S1M1 C1 V11
    S1M2 C1 V12
    S2M1 C1 V21
    S2M2 C1 V22
    ...
    Code:
    readers: [
        {
            // Special dimension used to split rows between multiple plots
            names: 'dataPart', 
            reader: function(vitem, output) {
                var seriesValueAndMeasureName = vitem[0];
                
                output.dataPart = 
                    // This should not happen, ignore row
                    !seriesValueAndMeasureName ? -1 :
                    
                    // Send "Thr. Down. Medio" rows to dataPart 1 - line plot
                    seriesValueAndMeasureName.indexOf("Thr. Down. Medio") >= 0 ? 1 :
    
                    // Send  "Numero Test" rows to dataPart 0 - main/bar plot
                    seriesValueAndMeasureName.indexOf("Numero Test") >= 0 ? 0 :
    
                     // Ignore all other rows, by sending them to an ignored dataPart
                    -1;
             }
            }
        ],
    Note that by specifying the special "dataPart" dimension, the related chart properties "plot2Series" and "plot2SeriesIndexes" are ignored.

    If you don't like the indexOf approach for detecting the measure that is present in each row you have another solution.
    Join the series value and the measure name with the "~" character. Or else, configure the property "dataSeparator" with the char you used to separate these "/".
    Then configure the option "isMultiValued" to true.
    An input row will then be transformed into the following virtual item structure:

    Code:
    Ser1 Ser2  Cat Value
    ------------------------
    S1    M1    C1  V11
    S1    M2    C1  V12
    S2    M1    C1  V21
    S2    M2    C1  V22
    Then, the reader function can instead be written as:

    Code:
    function(vitem, output) {
        var measureName = vitem[1];
                
        output.dataPart = 
            // This should not happen, ignore row
            !measureName ? -1 :
                    
            // Send "Thr. Down. Medio" rows to dataPart 1 - line plot
            measureName === "Thr. Down. Medio" ? 1 :
    
            // Send  "Numero Test" rows to dataPart 0 - main/bar plot
            measureName === "Numero Test" ? 0 :
    
            // Ignore all other rows, by sending them to an ignored dataPart
            -1;
    }
    Last edited by duarte.leao; 05-29-2013 at 07:16 AM.

  7. #7
    Join Date
    Dec 2012
    Posts
    121

    Default

    Thanks for your support!!!!

    ;-)

    Very great stuff!

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.