none
SSRS 2008 bar chart measures as groups RRS feed

  • Question

  • I have an SSRS 2008 bar chart with 3 measures and one dimension containing a year restricted to 2008-2009.  With the fields configured as follows:
    Drop data fields here - Charges Refunds NetProfits
    Drop series fields here -
    Drop category fields here - Year

    ...the chart shows the legend with Charges, Refunds, NetProfits and the Years across the bottom.  What I want is the years listed in the legend and the measures across the bottom.  but when I change the configuration to this:
    Drop data fields here - Charges Refunds NetProfits
    Drop series fields here - Year
    Drop category fields here -

    The report lists Charges, Refunds, NetProfits, Charges, Refunds, NetProfits making 6 colors in the chart.  The bottom then has just "Charges" listed on the bottom.  When I change the configuration as follows:
    Drop data fields here - Charges Refunds NetProfits
    Drop series fields here - Year
    Drop category fields here - Year

    ...the chart gets the years on the bottom and the legend still shows two sets of measures one for each year on the chart. 

    Is there a way to get the measures (Charges, Refunds, NetProfits) displayed as 3 groups across the bottom each of which would have only two bars, one for each year?  So the legend would have only two items listed 2008 and 2009.  Is there a way to do this?
    Friday, August 21, 2009 2:48 PM

Answers

  • You mean something like this?

     

     

    Hmmm…, this really depends on your dataset structure, I modify the dataset query.  

    And the chart design is:

    Category: type

    Series: year

    Data: value

     

    This is the original dataset query:

    select  ordr.docentry, cast(ordr.docdate as varchar(11)) as docdate,rdr1.quantity/100 as quantity,year(ordr.docdate) as year

     from ordr inner join rdr1 on ordr.docentry =rdr1.docentry

     

    This the new dataset query:

     

    select  ordr.docentry as value, cast(ordr.docdate as varchar(11)) as docdate,year(ordr.docdate) as year, 'DocEntry' as type

     from ordr inner join rdr1 on ordr.docentry =rdr1.docentry

    union

    select   rdr1.quantity/100  as value, cast(ordr.docdate as varchar(11)) as docdate, year(ordr.docdate) as year, 'Quantity' as type

     from ordr inner join rdr1 on ordr.docentry =rdr1.docentry

     

    Since you are using cube, you can try to delete the two measures, and create calculated measure to combine the two measure values with prefixed name -type (‘DocEntry’, 'Quantity') . After that:

    Category: group on cast the prefixed name

    Series: group on year

    Data: group on cast the value from the calculated measure.

     

    This is just my thought, if you need help to achieve that with MDX statements, you can refer to SSAS forum:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads

     

    Hope this helps.

    Raymond

    Friday, August 28, 2009 3:17 AM
    Moderator

All replies

  • I think you have to create 3 data fields: Charges, Refunds, and NetProfits. The value for each need to be just for them, i.e. write expression like: sum(Fields!Charges.Value) and so on. Do category by Year. And see what comes up. good luck. hth.
    Chicagoan ...
    Friday, August 21, 2009 3:04 PM
    Answerer
  • That is already what I have actually.  The configuration I detailed above is actually like so:
    Drop data fields here - sum(Fields!Charges.Value) sum(Fields!Refunds.Value) sum(Fields!NetProfits.Value)
    Drop series fields here -
    Drop category fields here - Year
    There are definitely 3 summed measures in the data fields slot and 1 dimension in the category fields slot.  The problem is getting the measures to list across the bottom of the chart and the 2 years to appear under the legend area.  So you would have 3 catageries across the bottom named for each of the 3 measures.  Those 3 catagories would each have two bars, one for each year.  I don't want 6 different colored bars though (2 X 3 = 6).  I just want 2 bar colors corresponding to the two years listed in the legend (2008 and 2009). 
    It seems like a simple thing but I can't see how to get it working. 
    Friday, August 21, 2009 3:35 PM
  • Hi PoleB,

     

    Are you using cube as datasource? Anyway, It seems there is something wrong in your bar chart or I didn’t get your problem.

     

    In report designer:

    Drop data fields here – sum(DocEntry) sum(quantity)

    Drop series fields here - nothing
    Drop category fields here – Docdate

     

    RS 2008:


     

    RS 2005:


     

    After that, I got this:

     

    RS 2008



     

    RS 2005


     

     

    Note, by default, the time listed in the y-axis and the measures across the bottom.

     

    Or you want this? This is a column chart:


     


    the time across the bottom and the measures in the y-axis.

    I guess you need to change the chart type.

    Hope this helps.

    Raymond

    Tuesday, August 25, 2009 2:50 AM
    Moderator
  • I am using a cube.

    Your last chart (the "column chart") is the type I want, but I need to swap the dates with the measures. 

    Your chart has the measures "DocEntry" and "quantity" represented by different colored bars in the chart and the columns are dates across the bottom. 
    What I would want is two COLUMNS labeled "DocEntry" and "quantity" and the different colored bars would represent years (you have indvidual dates but I just need two bars for two years). 

    So say we restrict the report to 2008 and 2009.  We would have 2 columns, but they would be called "DocEntry" and "quantity". 
    There would still be 2 bars but in the legend green would represent 2008 and blue would be 2009. 
    The left side of the chart would still have numbers as you show. 
    In the "DocEntry" column, the green bar would show the amount for "DocEntry" in 2008 and the blue bar would show the amount for "DocEntry" in 2009. 
    In the "quantity" column, the green bar would show the amount for "quantity" in 2008 and the blue bar would show the amount for "quantity" in 2009. 

    Can you make your chart look like that? 

    Thanks

    Wednesday, August 26, 2009 4:41 PM
  • Hi PoleB,

     

    You can try to add a series group with expression =fields!year.value.

     

     

    After that, you will get this:



     

    After that, format the label to meet your requirement.

     

    Hope this helps.

    Raymond

    Thursday, August 27, 2009 3:59 AM
    Moderator
  • That was one configuration I had already tried but the measure names still don't appear across the bottom as column names. 

    It looks like Reporting Services just can't do exactly what I want in this case unless there are any other ideas out there. 
    Thursday, August 27, 2009 8:06 PM
  • You mean something like this?

     

     

    Hmmm…, this really depends on your dataset structure, I modify the dataset query.  

    And the chart design is:

    Category: type

    Series: year

    Data: value

     

    This is the original dataset query:

    select  ordr.docentry, cast(ordr.docdate as varchar(11)) as docdate,rdr1.quantity/100 as quantity,year(ordr.docdate) as year

     from ordr inner join rdr1 on ordr.docentry =rdr1.docentry

     

    This the new dataset query:

     

    select  ordr.docentry as value, cast(ordr.docdate as varchar(11)) as docdate,year(ordr.docdate) as year, 'DocEntry' as type

     from ordr inner join rdr1 on ordr.docentry =rdr1.docentry

    union

    select   rdr1.quantity/100  as value, cast(ordr.docdate as varchar(11)) as docdate, year(ordr.docdate) as year, 'Quantity' as type

     from ordr inner join rdr1 on ordr.docentry =rdr1.docentry

     

    Since you are using cube, you can try to delete the two measures, and create calculated measure to combine the two measure values with prefixed name -type (‘DocEntry’, 'Quantity') . After that:

    Category: group on cast the prefixed name

    Series: group on year

    Data: group on cast the value from the calculated measure.

     

    This is just my thought, if you need help to achieve that with MDX statements, you can refer to SSAS forum:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads

     

    Hope this helps.

    Raymond

    Friday, August 28, 2009 3:17 AM
    Moderator