CODEWITHSUNDEEP
sql-serverreporting-servicesdynamics-crm
Dremor
Dremor

Reputation: 831

SSRS : How to show all month of a year as X axis of a graph

I'm working on a graph that shows data for a whole year, but as I'm a newbie, I can't figure out how to show all month on the X axis (including those without data)

Here is the SELECT part of my SQL request :

SELECT DISTINCT
    AB.Id AS ID,
    MONTH(AP.Date) AS Date

Resulting in the following data:

|   ID   |  Date  |
|--------|--------|
|   1    |   2    |
|   2    |   3    |
|   3    |   2    |
|   4    |   2    |
|   5    |   3    |
|   6    |   3    |
|   7    |   3    |
|--------|--------|

Currently I'm using =MonthName(Fields!Date.Value)) as category group, resulting in the following graph :

4|            X
3|    X
2|
1|
0|_________________
   February  March

What I want to have is something like this :

4|                      X
3|              X
2|
1|
0|______________________________________
   January  February  March  April  ...

I've found some workaround on the net using a custom table in the database, but I can't in this case, as it is a MS Dynamics project.

Thank you for your time.

Upvotes: 2

Views: 2607

Answers (2)

Jayvee
Jayvee

Reputation: 10875

You can add a right join to your query with all the month and return its column to the output:

SELECT DISTINCT
    AB.Id AS ID,
    m.n AS Date
   right join (select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m(n) ) m
   on MONTH(AP.Date)=m.n

Upvotes: 1

iamdave
iamdave

Reputation: 12243

You need to return the right data to your chart so that there is something to include on the axis. To do this, you need to generate every single x-axis value and then find the total that relates to it.

In the script below, I am creating a table of dates that represent the start of the month for each month of the current year. It then goes and finds all the relevant values in your table for that month and does a count. This can then be used as your chart dataset with minimal processing on the client side, which will help with report performance.

declare @t table(ID int,DateValue Date)
insert into @t values (1,'20170204'),(2,'20170307'),(3,'20170203'),(4,'20170207'),(5,'20170304'),(6,'20170302'),(7,'20170309');

with d(d) as(select cast(dateadd(mm,n,dateadd(yyyy,datediff(yyyy,0,getdate()),0)) as date) from(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) n(n))
select d.d
        ,count(t.ID) as c
from d
    left join @t t
        on(d.d = dateadd(mm,datediff(mm,0,t.DateValue),0))
group by d.d
order by d.d;

Output:

+------------+---+
|     d      | c |
+------------+---+
| 2017-01-01 | 0 |
| 2017-02-01 | 3 |
| 2017-03-01 | 4 |
| 2017-04-01 | 0 |
| 2017-05-01 | 0 |
| 2017-06-01 | 0 |
| 2017-07-01 | 0 |
| 2017-08-01 | 0 |
| 2017-09-01 | 0 |
| 2017-10-01 | 0 |
| 2017-11-01 | 0 |
| 2017-12-01 | 0 |
+------------+---+

Upvotes: 3

Related Questions