none
How can I SUM data from a multi-value field RRS feed

  • Question

  • I've been looking in this discussion group and elsewhere for a way to do the following with no success.

    I hope someone will have an idea to help.

    -------------------------------------

    I have a Datasource filled with "Chair" items.

    "Chair" has a field "Colors" which is a comma-seperated field (ex:  "red, black, silver")

    Is it possible to create a bar chart showing the count of "Chairs" grouped by "Color"?

     

    Basically, the Chair in my example would be accounted for in the "red", "black" and "silver" grouping.

    The only way I was able to do that is to create a brand new dataset and pre-process the data in C# to generate this new DataSet.

    I'm pretty sure there is a way I could do this directly in Rdlc expressions.

    Thanks in advance.

    Wednesday, November 10, 2010 3:04 PM

Answers

  • Hi Eric,

     

    As the following picture shows, do you mean you have a dataset like the left table and need to generate a chart like the right one?

     

     

    In order to achieve this, the better approach is to change the dataset in the query level or in the application level as you tried. However, in order to achieve this in the report based on original dataset, we need to know all color when designing report. Please refer to the steps below:

    1.    Add a chart the report.

    2.    Add the Chair field to the top data area of the chart, and then right click the field and select Series Properties...

    3.    In the Series Properties window, specify the Value field to the expression like

    =Count(IIf(InStr(Fields!Colors.Value,"red")>0,Fields!Chair.Value,nothing))

     

    4.    Repeat the step 2 and step 3 to add the Chair field to the top data area 2 more times and specify the Value field to the expression like

    =Count(IIf(InStr(Fields!Colors.Value,"black")>0,Fields!Chair.Value,nothing))

    and

    =Count(IIf(InStr(Fields!Colors.Value,"silver")>0,Fields!Chair.Value,nothing))

     

    As you see, the limitation is we need to add series to the chart according to the number of colors. If a new color added to the dataset, we also need to modify the report chart timely. So I would suggest you still use the current approach to generate the chart report.

     

    Thanks,

    Tony Chain

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    • Marked as answer by Eric-Novabrain Thursday, November 11, 2010 3:53 AM
    Thursday, November 11, 2010 3:32 AM
    Moderator

All replies

  • Hi Eric,

     

    As the following picture shows, do you mean you have a dataset like the left table and need to generate a chart like the right one?

     

     

    In order to achieve this, the better approach is to change the dataset in the query level or in the application level as you tried. However, in order to achieve this in the report based on original dataset, we need to know all color when designing report. Please refer to the steps below:

    1.    Add a chart the report.

    2.    Add the Chair field to the top data area of the chart, and then right click the field and select Series Properties...

    3.    In the Series Properties window, specify the Value field to the expression like

    =Count(IIf(InStr(Fields!Colors.Value,"red")>0,Fields!Chair.Value,nothing))

     

    4.    Repeat the step 2 and step 3 to add the Chair field to the top data area 2 more times and specify the Value field to the expression like

    =Count(IIf(InStr(Fields!Colors.Value,"black")>0,Fields!Chair.Value,nothing))

    and

    =Count(IIf(InStr(Fields!Colors.Value,"silver")>0,Fields!Chair.Value,nothing))

     

    As you see, the limitation is we need to add series to the chart according to the number of colors. If a new color added to the dataset, we also need to modify the report chart timely. So I would suggest you still use the current approach to generate the chart report.

     

    Thanks,

    Tony Chain

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    • Marked as answer by Eric-Novabrain Thursday, November 11, 2010 3:53 AM
    Thursday, November 11, 2010 3:32 AM
    Moderator
  • Tony, thank you for taking the time to reply.

    Your example is exactly what I am talking about.

    Unfortunately, I cannot know all the colors in advance so hardcoding colors in IIf statements is not an option.

    I will continue pre-processing data as I do now. But I always prefer to manipulate the data in the Report directly instead of creating custom datasets (I work with Business Objects and not a DB SQL).

     

    Thanks

    Eric

    Thursday, November 11, 2010 3:59 AM