locked
Multiple Pie charts grouping RRS feed

  • Question

  • Hello All,

    I have a requirement where user want to see all of his project amounts on a Pie chart on its own page and multiple pie charts for subgroup i.e. Finance structure belongs to that project under the project pie chart ..there could be any number of structures in a Project.

    Please find screen print below . The projects (Bigger Pie charts ) are going on their own page but the finance structures under that project also going on new page.. how can I avoid it.

    I want to see a bigger Pie chart for Project  and under that pie chart I want to see smaller pie charts in same box  for finance structures in that project...

    please suggest if that is possible. 

    Best Regards,

    Pragati




    Monday, September 19, 2016 2:23 PM

Answers

  • So... in the Matrix solution the SQL would be something like this... a little ugly but it works.  Maybe you can figure out something a little more elegent.

    DECLARE  @mytable TABLE(
       Customer VARCHAR(8) NOT NULL 
      ,Pct      NUMERIC(2,1) NOT NULL
    );
    INSERT INTO @mytable(Customer,Pct) VALUES
     ('Client1',.5)
    ,('Client2',.6)
    ,('Client3',.7)
    ,('Client4',.2)
    ,('Client5',.5)
    ,('Client6',.3)
    ,('Client7',.8)
    ,('Client8',.9)
    ,('Client9',.1)
    ,('Client10',.2);
    
    
    select 
            Customer
            ,Pct
            ,RowId 
            ,Remainder
            ,ColumnGroup
            ,ROW_NUMBER() over (partition by ColumnGroup order by RowId) RowGroup
    
    from (
            select
            Customer
            ,Pct
            ,RowId 
            ,RowId % 3 Remainder
            ,case 
                when RowId % 3 = 1 then 1
                when RowId % 3 = 2 then 2
                when RowId % 3 = 0 then 3
                else 0 end as ColumnGroup
    from ( 
    select 
            Customer
            ,Pct
            ,ROW_NUMBER() over (order by cast(replace(Customer,'Client','') as int)) RowId
    from @mytable 
    ) x
    ) y
    order by RowId 

    Your Matrix would look something like this... (I used a bar graph to keep it simple).

    And final render would be like this... 

    Edit... I should have added the Customer Column in the Chart Title, but I think you get the idea.
    • Edited by Tim Konecky Tuesday, September 20, 2016 3:33 PM Customer Column thing.
    • Proposed as answer by Tim Konecky Tuesday, September 20, 2016 3:35 PM
    • Marked as answer by Pragati Sharma Friday, September 30, 2016 9:33 AM
    Tuesday, September 20, 2016 3:28 PM

All replies

  • To be more clear ..i want something like below

    Tuesday, September 20, 2016 9:35 AM
  • Hi Pragati Sharma,

    According to your description, you would like to create a Project pie chart in one box, then generate the other Finance structure pie charts automatic under the box by grouping, right ?

    As you mentioned, finance structure charts display on the different pages from the project pie chart, do you mean this happens when you preview the report, or after exporting to other files? Would you please share more information about this?

    In your scenario, if you want to generate multiple finance structure charts from one chart by group, you can place the pie chart into the column group within a table or matrix control, see:

    1.Sample data.

    2.Creat a bigger pie chart and then place it in a rectangle.

    3.Create a pie chart for your small chart and place it in a table column group which groups on Item.

    4.Preview the report.

    By the way, you can also try to use the Chart Area for a series.

    If you still have any questions, please feel free to ask.

    Best Regards,
    Pirlo Zhang

    Tuesday, September 20, 2016 12:24 PM
  • Thank you Pirlo,

    I have tried column grouping but it doesn't look very good as there could be quite  a lot structures inside a project and if 20 pie charts comes under  a project in single line report looks weird:(

     I want the chart's to move on the new line according to the number of Pie charts getting generated.

    if 2 pie charts then show in one line 

    if 4 pie charts show in 2 line (2 pie charts in each)

    if 8 show in 2 line (4 and 4) etc...

    regarding your query: As you mentioned, finance structure charts display on the different pages from the project pie chart, do you mean this happens when you preview the report, or after exporting to other files? 

    --This happens when I preview the report 

    Thank you,

    Pragati


    Tuesday, September 20, 2016 12:52 PM
  • You could do this in a matrix table.  It's easier if there are natural column and row groupings.  But that doesn't mean you can't create your own artificial groupings using ranking fuctions.  I'll try to get you can example
    Tuesday, September 20, 2016 3:02 PM
  • So... in the Matrix solution the SQL would be something like this... a little ugly but it works.  Maybe you can figure out something a little more elegent.

    DECLARE  @mytable TABLE(
       Customer VARCHAR(8) NOT NULL 
      ,Pct      NUMERIC(2,1) NOT NULL
    );
    INSERT INTO @mytable(Customer,Pct) VALUES
     ('Client1',.5)
    ,('Client2',.6)
    ,('Client3',.7)
    ,('Client4',.2)
    ,('Client5',.5)
    ,('Client6',.3)
    ,('Client7',.8)
    ,('Client8',.9)
    ,('Client9',.1)
    ,('Client10',.2);
    
    
    select 
            Customer
            ,Pct
            ,RowId 
            ,Remainder
            ,ColumnGroup
            ,ROW_NUMBER() over (partition by ColumnGroup order by RowId) RowGroup
    
    from (
            select
            Customer
            ,Pct
            ,RowId 
            ,RowId % 3 Remainder
            ,case 
                when RowId % 3 = 1 then 1
                when RowId % 3 = 2 then 2
                when RowId % 3 = 0 then 3
                else 0 end as ColumnGroup
    from ( 
    select 
            Customer
            ,Pct
            ,ROW_NUMBER() over (order by cast(replace(Customer,'Client','') as int)) RowId
    from @mytable 
    ) x
    ) y
    order by RowId 

    Your Matrix would look something like this... (I used a bar graph to keep it simple).

    And final render would be like this... 

    Edit... I should have added the Customer Column in the Chart Title, but I think you get the idea.
    • Edited by Tim Konecky Tuesday, September 20, 2016 3:33 PM Customer Column thing.
    • Proposed as answer by Tim Konecky Tuesday, September 20, 2016 3:35 PM
    • Marked as answer by Pragati Sharma Friday, September 30, 2016 9:33 AM
    Tuesday, September 20, 2016 3:28 PM
  • Thanks you Tim, this seems like what I want. I will try it Tomorrow and get back to you ..



    Tuesday, September 20, 2016 3:33 PM
  • If you find that it works for you, feel free to Mark it as an answer and give me an upvote. Much appreciated friend!
    Tuesday, September 20, 2016 3:36 PM
  • Sorry I caught up with a Sev 1 issue today so couldn't try it..

    Yes i will definitely:)

    Thanks!


    Wednesday, September 21, 2016 2:00 PM
  • Thanks Tim..It did help
    Friday, September 30, 2016 9:34 AM
  • I Know i already replied on it ..but today i have fully tested it and it did works exactly the way i wanted.

    Thank yo so very much for helping me out Tim..you are the star :):)

    Monday, October 3, 2016 12:50 PM