CODEWITHSUNDEEP
reporting-servicesssrs-2008reportbuilder3.0
moirK
moirK

Reputation: 681

How to sort category group by another column in SSRS line chart?

I am creating line chart in a paginated SSRS report and my data consists of three columns and looks something like this:

Date       |   MonthYear   |   Percentage
20.09.2018 |   Sep 2018    |   29.8
29.09.2018 |   Sep 2018    |   87.5
30.01.2019 |   Jan 2019    |   18.3

For creating my line chart I need Percentage values per month so my dataset consists of only Percentage and MonthYear.

But I only need to visualize data for the last 10 months. What I have done so far is that I have created another dataset called DateSource containing the Date and MonthYear and filtered the rows such that I get data for the last 10 months.

And in the Category Groups of my line chart I am fetching data as =Lookup(Fields!MonthYear.Value, Fields!MonthYear.Value, Fields!MonthYear.Value, "DateSoure"). Here is what my chart looks like.

enter image description here

I am wondering how to write a lookup query to fetch only those data points whose corresponding MonthYear exist. Or if there's a better way to view data for only last 10 months.

I am new to SSRS and would really appreciate some help.

Upvotes: 0

Views: 638

Answers (2)

moirK
moirK

Reputation: 681

I figured it out by a workaround. In the original dataset I filtered the rows using IN operator and checked if MonthYear is one from the last 10 months. I used a Split method: enter image description here

In the code snippet below I concatenate two strings, one containing month's name and another with the respective year. And then check if MonthYear exists IN any of these values. 

=Split(
    StrConv(Format(DateAdd("M",-1,Now()),"MMM"), VbStrConv.ProperCase) & " " & Year(DateAdd("M",-1,Now())) 
    & "," & 
    StrConv(Format(DateAdd("M",-2,Now()),"MMM"), VbStrConv.ProperCase) & " " & Year(DateAdd("M",-2,Now())) 
    ...
    StrConv(Format(DateAdd("M",-10,Now()),"MMM"), VbStrConv.ProperCase) & " " & Year(DateAdd("M",-10,Now()))
    , ",")

Upvotes: 0

Shekar Kola
Shekar Kola

Reputation: 1297

How can I filter the rows to get data for the last 10 months but get percentage only as if I were aggregating on MonthYear? So far my Category Group Properties include the following group expression:

I would suggest do the aggregation within data-set query, so the Group by [MonthYear] would be taken care outside report, for the report it's already processed data, when you apply filter Months aggregation won't effected since the processed data going to have per month one-row.

In case if there is other element within report that required date-wise data, then you can crate new data-set that shows without group by and per date one-row.

P.S: If any help needed on doing aggregation within data-set, you may post your data-set query part of the question.

Screenshot reference for adding additional data-set:
enter image description here

Upvotes: 0

Related Questions