none
Combining Column Data under new Column in stacked graph

    Question

  • Hi

    I'm trying to do two graphs on a report done with SQL report Builder

    The one graph needs to combine values from the second graph in a stacked graph

    For example:

    I have the following data:

    Order Area Color Units 

    1 A1 Blue 2

    2 A2 Green 5

    A3 Red 4

    4 A1 Green 4

    5 B1 orange 2

    6 B3 red 4

    C2 yellow 3

    C1 red 2

    9 B3 orange    2

    Now the one graph will just show stacks based on area on the x-asis with units on y series

    The other graph needs to combine all the A areas into one area called "A" all b areas as "B" etc

    The the stacked graph will now only have 3 areas on x asis and all the values from different A areas that made red will be summed together and so on for all colors and then displayed in one stack

    Is this possible or will i need to create a store procedure that go do these calcs etc?

    Thanks in advance


    • Edited by Henko Venter Friday, September 13, 2013 12:55 PM
    Friday, September 13, 2013 8:53 AM

Answers

  • Hi Henko,
     
    If I understand correctly, you want to create a Stacked Column Chart which with only three areas (A, B, C) on X-Axis. Display all color with different areas.
    We can use expression add group in the Category Groups to get the Category values in the chart.
     
    You can refer to the following steps:
    1. Click the arrow below Category Groups, select Category Group Properties.
    2. Click Add icon below “Group expression:”, click expression icon after “Group on:”.
    3. Type the below expression in textbox:
    =IIF(InStr(Fields!Area.Value,"A")>0,"A",IIF(InStr(Fields!Area.Value,"B")>0,"B","C"))
    4. Add Area, Color in Series Groups.
    5. Right click the bar in the chart. Click Series Properties.
    6. Click Fill in the left pane in the Series Properties dialog box.
    7. Click the expression icon below “Color:”.
    8. Type the below expression in the textbox:
    =Fields!Color.Value
     
    You can refer to the screen shots below:
     
    Preview the report:
     
    Hope this helps. If there are any misunderstanding, please feel free to let me know.
     
    Regards,
    Alisa Tang
    Tuesday, September 17, 2013 8:53 AM
    Moderator

All replies

  • Hi Henko,
     
    If I understand correctly, you want to create a Stacked Column Chart which with only three areas (A, B, C) on X-Axis. Display all color with different areas.
    We can use expression add group in the Category Groups to get the Category values in the chart.
     
    You can refer to the following steps:
    1. Click the arrow below Category Groups, select Category Group Properties.
    2. Click Add icon below “Group expression:”, click expression icon after “Group on:”.
    3. Type the below expression in textbox:
    =IIF(InStr(Fields!Area.Value,"A")>0,"A",IIF(InStr(Fields!Area.Value,"B")>0,"B","C"))
    4. Add Area, Color in Series Groups.
    5. Right click the bar in the chart. Click Series Properties.
    6. Click Fill in the left pane in the Series Properties dialog box.
    7. Click the expression icon below “Color:”.
    8. Type the below expression in the textbox:
    =Fields!Color.Value
     
    You can refer to the screen shots below:
     
    Preview the report:
     
    Hope this helps. If there are any misunderstanding, please feel free to let me know.
     
    Regards,
    Alisa Tang
    Tuesday, September 17, 2013 8:53 AM
    Moderator
  • THanks a lot
    Tuesday, October 01, 2013 5:13 PM