none
HOW TO ADD MORE THAN ONE CATEGORY FIELDS IN SSRS CHART

    Question

  • trying to create an SSRS chart with multiple category fields on x-axis. when i drag and drop more than one column on the x-axis ,the 2nd column becomes the child of the 1st column. i want each columns to be stand alone. sample expected chart below

    enter image description here 

    enter image description here

    Friday, November 15, 2013 4:28 PM

Answers

  • Hi BenEsec,

    Based on my test, we can use UNPIVOT function which performs the operation by rotating columns of a table-valued expression into column values to achieve your goal. For UNPIVOT function, you can see the link: Using PIVOT and UNPIVOT. To resolve the issue, please follow the steps:

    1. Create a dataset which type the similar commands below in Query Designer:

    SELECT Company, TimeRange, Val
    FROM 
      (SELECT Company, [ID8AM_10AM],[ID10AM_12PM],[ID12PM_2PM],[ID2PM_4PM],[ID4PM_6PM],[ID6PM_8PM],[ID8PM_10PM],[ID10PM_12AM],[ID12AM_2AM] 
    
     FROM YourTableName)
     pUNPIVOT 
    
    (Val FOR TimeRange IN  
    ([ID8AM_10AM],[ID10AM_12PM],[ID12PM_2PM],[ID2PM_4PM],[ID4PM_6PM],[ID6PM_8PM],[ID8PM_10PM],[ID10PM_12AM],[ID12AM_2AM])) AS unpvt;

    2. Create a 3-D stacked column chart, and select TimeRange field as Category Groups, select Company field as Series Groups, and then select Val field as Values.

    3. Right click the chart and select Horizontal Axis Properties, and select Labels in left pane, and then uncheck the option of “Labels can be rotated”.

    4. Now, please see the following result screenshot:
     
    Regards,
    Heidi Duan

    If you have any feedback on our support, please click here.


    Heidi Duan
    TechNet Community Support



    Monday, November 18, 2013 9:59 AM
    Moderator

All replies

  • Hi,

    I test the issue and think your gaol isn't achieved because the behavior is by design.

    Regards,

    Alisa Zhang

    Sunday, November 17, 2013 9:05 AM
  • Hi BenEsec,

    Based on my test, we can use UNPIVOT function which performs the operation by rotating columns of a table-valued expression into column values to achieve your goal. For UNPIVOT function, you can see the link: Using PIVOT and UNPIVOT. To resolve the issue, please follow the steps:

    1. Create a dataset which type the similar commands below in Query Designer:

    SELECT Company, TimeRange, Val
    FROM 
      (SELECT Company, [ID8AM_10AM],[ID10AM_12PM],[ID12PM_2PM],[ID2PM_4PM],[ID4PM_6PM],[ID6PM_8PM],[ID8PM_10PM],[ID10PM_12AM],[ID12AM_2AM] 
    
     FROM YourTableName)
     pUNPIVOT 
    
    (Val FOR TimeRange IN  
    ([ID8AM_10AM],[ID10AM_12PM],[ID12PM_2PM],[ID2PM_4PM],[ID4PM_6PM],[ID6PM_8PM],[ID8PM_10PM],[ID10PM_12AM],[ID12AM_2AM])) AS unpvt;

    2. Create a 3-D stacked column chart, and select TimeRange field as Category Groups, select Company field as Series Groups, and then select Val field as Values.

    3. Right click the chart and select Horizontal Axis Properties, and select Labels in left pane, and then uncheck the option of “Labels can be rotated”.

    4. Now, please see the following result screenshot:
     
    Regards,
    Heidi Duan

    If you have any feedback on our support, please click here.


    Heidi Duan
    TechNet Community Support



    Monday, November 18, 2013 9:59 AM
    Moderator