Henrov
Henrov

Reputation: 1610

0-value not showing in line graph

I have a dataset that contains a lot of records.

Employee (varchar) | month (int) | Sales (money)
John | jan | 15
John | jan | 6
Hank | jan | 20
Mike | jan | 0
Hank | jan | 12
Pete | jan | 0
Pete | jan | 7
John | feb | 4
John | feb | 9
Hank | feb | 0
Pete | feb | 11
Mike | feb | 15
John | mar | 0
Hank | mar | 20
Mike | feb | 0
Pete | mar | 10
Pete | mar | 12

Each name exists for each month and has a value (albeit 0)

I have a tablix grouping this and it correctly shows:

     | jan | feb | mar
John |  21 |  13 |  0 
Hank |  32 |   0 | 20
Pete |   7 |  11 |  0
Mike |   0 |  15 |  0

(actually, there is also an Mike, Edward, Michelle, Blake and so on)

A line graph based on the dame dataset however omits all the values '0'. The lines are broken, there are gaps. Except for the first two that have no '0' in their sequence. So John and Pete are shown for jan - feb - mar and Hank's line shows gaps.... If I set Hank on 11 in feb and Pete for 0 in feb, than John and Hank are shown with no gaps in their lines. So, if there are '0' at the end, the line continues as it should, as soon as there is a 0 in the middle there are gaps in the line where there are 0's. Any employee after that (Mike, Blake, Michelle, etc) has gaps wherever the line should touch zero. I toyed around with emptypoints and it does fill the gaps (e.g. with a red line) but why are John and Pete shown as a normal line that goes all the way to the end, filling up with 0's while others have a red line (emptypoint)?

And strictly spoken, there are no empty/null's, they are '0'. Or am I mistaken?

Why Can't I get the chart to simply show the same that the tablix does?

Upvotes: 0

Views: 1793

Answers (2)

Henrov
Henrov

Reputation: 1610

I have no clue what happened. Somehow the graph concluded that, where the dataset returned the value '0', the value was NOTHING. So I solved it by using an expression for the aggregate instead of using the default sum:

IIF(isnothing(Fields!sales.Value),0,Sum(Fields!sales.Value))

Upvotes: 0

Ian Preston
Ian Preston

Reputation: 39586

Not sure what's happening with your report, but there's nothing wrong with your data or getting it to display in a Chart.

Data (slightly modified to better align with your Tablix):

select employee = 'John' ,month = 1, monthName = 'Jan' ,sales =  15
union all select employee = 'John' ,month = 1, monthName = 'Jan' ,sales =  6
union all select employee = 'Hank' ,month = 1, monthName = 'Jan' ,sales =  20
union all select employee = 'Mike' ,month = 1, monthName = 'Jan' ,sales =  0
union all select employee = 'Hank' ,month = 1, monthName = 'Jan' ,sales =  12
union all select employee = 'Pete' ,month = 1, monthName = 'Jan' ,sales =  0
union all select employee = 'Pete' ,month = 1, monthName = 'Jan' ,sales =  7
union all select employee = 'John' ,month = 2, monthName = 'Feb' ,sales =  4
union all select employee = 'John' ,month = 2, monthName = 'Feb' ,sales =  9
union all select employee = 'Hank' ,month = 2, monthName = 'Feb' ,sales =  0
union all select employee = 'Pete' ,month = 2, monthName = 'Feb' ,sales =  11
union all select employee = 'Mike' ,month = 2, monthName = 'Feb' ,sales =  15
union all select employee = 'John' ,month = 3, monthName = 'Mar' ,sales =  0
union all select employee = 'Hank' ,month = 3, monthName = 'Mar' ,sales =  20
union all select employee = 'Mike' ,month = 3, monthName = 'Mar' ,sales =  0
union all select employee = 'Pete' ,month = 3, monthName = 'Mar' ,sales =  10
union all select employee = 'Pete' ,month = 3, monthName = 'Mar' ,sales =  12

A basic chart, just created by adding fields to the respective areas:

enter image description here

Only other action was to change the label to monthName.

Which looks like:

enter image description here

All names are showing points for all months. I understand this doesn't help your specific issue but it does show that it can work as intended. In your case I would create a new chart entirely, add in the basic fields as above and see how it looks; once this is going you can add more formatting, etc, checking that it still works at each step.

Upvotes: 2

Related Questions