How to dynamically display data labels in area chart

Question

Hello, everyone,

I have an stacked area chart with the data set as follows:

Values: sales

Category Group: date

Series Group: products

Now I have an requirement to display the data labels in the chart for the top 3 products with the highest sales within a certain time period (based on the date parameter). E.g. product1 in the product 1 area, product 2 in the product 2 area, etc.

Is this possible? If so how do I go about doing it?

Your help is much appreciated

Kind regards

Answer 1

If you right-click the chart area, you can choose to display data labels.

I am not sure that completely answers your question though. Do you mean that you want to limit the chart to only show the top products and for a specified time period. Or you only want to show the labels for the top 3 but show the area for all returned data?

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 2

Thanks Tim for replying.

I want to show areas for all products in the chart, but only display the data labels for the top 3 products in its corresponding area, presumably only once, say "product 1" in product 1 area, "product 2" in product 2 area.

Answer 3

Hi QQFA,

According to your description, you have a stacked area chart. Values is sales, Category  Group is date, and Series Group is product. Now you want to show only top 3 products which has the highest sales data labels for each date.

In SQL SERVER Reporting Service, we can achieve this goal by using expression in Series Label Properties setting. I have tested it on my local environment, you can follow these steps below:

1. Create a new DataSet(name is “top3”).
2. Select use a embedded dataset in my report and put
         “ select * from(
         select Product,Sales,[Date],
         ROW_NUMBER() over (partition by [Date] order by Sales desc) as rn
         from #temp)
         where rn<4 ”
       into Query.
3. Right Click on your chart, select “show Data Labels”.
   
4. Right Click on any label that just appeared, select Series Labels Properties .
5. Click the “fx” next to Label data textbox.
6. Type the expression below into Set expression for Label :
        “=IIF(InStr(Join(
        lookupset(Fields!Date.Value,Fields!Date.Value,Fields!Product.Value,
        "top3"),","),Fields!Product.Value)>0,Fields!Sales.Value,"")”
        (Ps: the “lookupset” function is only supported  in SQL SERVER 2008 R2 or later) 
7. Click “OK”.
8. Save and preview the chart ,as shown here:
   

Now your chart will show you the top 3 highest sales product data labels in corresponding area.

Reference:
LookupSet Function
Expression and Functions

If you have any question ,please feel free to ask.

Best Regards,
Simon

Answer 4

Hi, Simon,

Thanks for your detailed solution. I appreciate your taking the time to do it. I moved on to something else after posting this question. Now I can come back to it to implement it.

Thanks again.

Regards