Home > Home > SSRS :-Show Dates Data in Timescale view using SSRS

SSRS :-Show Dates Data in Timescale view using SSRS

I got this question from MSDN, Click here for actual question.

In Brief:-

I have a requirement to display project start date and finish date in timescale view.

 

Create Sample SQL query as given below:-

declare @MINYear INT,@MAXYear INT

select

@MINYear=Year(MIN(cast(ProjectStartDate as date))over()),@MAXYear=Year(MAX(cast(ProjectEndDate as date))over())

from

(select ‘Project A’ as ProjectName,’10/14/2014′ ProjectStartDate,’06/30/2015′ ProjectEndDate

UNION

select ‘Project B’ as ProjectName,’07/15/2015′ ProjectStartDate,’06/30/2016′ ProjectEndDate

UNION

select ‘Project C’ as ProjectName,’06/20/2015′ ProjectStartDate,’06/30/2016′ ProjectEndDate

UNION

select ‘Project D’ as ProjectName,’1/01/2015′ ProjectStartDate,’11/30/2015′ ProjectEndDate

UNION

select ‘Project E’ as ProjectName,’10/14/2014′ ProjectStartDate,’05/31/2015′ ProjectEndDate

UNION

select ‘Project F’ as ProjectName,’11/26/2015′ ProjectStartDate,’07/06/2016′ ProjectEndDate

)t

;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y),

t3 AS (SELECT 1 N FROM t2 x, t2 y),

t4 AS (SELECT 1 N FROM t3 x, t3 y),

tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS betweendata

FROM t4 x, t4 y)

–Actual query

select *

, Case when keyCol>=keyStartCol and keyCol<=keyEndCol

then ‘Yes’ else’No’ end

from

(

SELECT DATENAME(MONTH,betweendata) AS months,month(betweendata) AS monthNumber,Year(betweendata)Year

, LEFT(CONVERT(varchar, betweendata,112),6)keyCol

FROM tally

WHERE betweendata >= DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@MINYear as varchar)+’-01-01′), 0)

AND betweendata <= DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@MAXYear as varchar)+’-12-31′), 0)

)T

cross join

(

select ProjectName      ,cast(ProjectStartDate as date)ProjectStartDate,      cast(ProjectEndDate as date) ProjectEndDate,

Year(MIN(cast(ProjectStartDate as date))over())MINYear,Year(MAX(cast(ProjectEndDate as date))over())MAXYear,

month(cast(ProjectStartDate as date))MonthStart,MONTH(cast(ProjectEndDate as date)) MonthEND,

LEFT(CONVERT(varchar, cast(ProjectStartDate as date),112),6)keyStartCol,

LEFT(CONVERT(varchar, cast(ProjectEndDate as date),112),6)keyEndCol

from

(select ‘Project A’ as ProjectName,’10/14/2014′ ProjectStartDate,’06/30/2015′ ProjectEndDate

UNION

select ‘Project B’ as ProjectName,’07/15/2015′ ProjectStartDate,’06/30/2016′ ProjectEndDate

UNION

select ‘Project C’ as ProjectName,’06/20/2015′ ProjectStartDate,’06/30/2016′ ProjectEndDate

UNION

select ‘Project D’ as ProjectName,’1/01/2015′ ProjectStartDate,’11/30/2015′ ProjectEndDate

UNION

select ‘Project E’ as ProjectName,’10/14/2014′ ProjectStartDate,’05/31/2015′ ProjectEndDate

UNION

select ‘Project F’ as ProjectName,’11/26/2015′ ProjectStartDate,’07/06/2016′ ProjectEndDate

)t

)T1

Create a report using above SQL query.

Use Matrix,

Create row groups on Project Name, Project StartDate , Project End Date

Create column group on Year

Create column child group on Month

Give value field as =””

Change background color of the value Field with below expression:-

=IIF(Fields!ID.Value=”Yes”,”Green”,”No Color”)

Your report design will look like below:-

1 2

Run the report, Report look like below:-

2

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: Home
  1. Badal Ratra
    December 15, 2015 at 11:35 am

    Hi Prasad,
    I am facing one issue in SQL query provided by you, it’s not filling up the color in timescale in following case. For Example,

    When Project Start Date = 16/03/2015 and Project End Date = 15/07/2016

    In above case, below SQL logic code doesn’t fill in the color in timescale for 2015, Q1 and 2016,Q3. It starts filling from 2015, Q2 to 2016 Q2 for a project.

    Could you please help and advise what is the issue?

    declare @MINYear INT,@MAXYear INT

    set @MAXYear= Year(DATEADD(year,2,getdate()))
    set @MINYear= Year(DATEADD(year,-1,getdate()))

    ;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
    t2 AS (SELECT 1 N FROM t1 x, t1 y),
    t3 AS (SELECT 1 N FROM t2 x, t2 y),
    t4 AS (SELECT 1 N FROM t3 x, t3 y),
    tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS betweendata
    FROM t4 x, t4 y)
    –Actual query
    select distinct *
    , Case when keyCol>=keyStartCol and keyCol= DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@MINYear as varchar)+’-01-01′), 0)
    AND betweendata <= DATEADD(MONTH, DATEDIFF(MONTH, 0, cast(@MAXYear as varchar)+'-12-31'), 0)
    )T

    cross join

    (

    select ProjectName ,cast(ProjectStartDate as date)ProjectStartDate, cast(ProjectEndDate as date) ProjectEndDate,ProjectBudget,
    Year(MIN(cast(ProjectStartDate as date))over())MINYear,Year(MAX(cast(ProjectEndDate as date))over())MAXYear,
    month(cast(ProjectStartDate as date))MonthStart,MONTH(cast(ProjectEndDate as date)) MonthEND,
    LEFT(CONVERT(varchar, cast(ProjectStartDate as date),112),6)keyStartCol,
    LEFT(CONVERT(varchar, cast(ProjectEndDate as date),112),6)keyEndCol

    from
    (select 'Project A' as ProjectName,'10/14/2014' ProjectStartDate,'06/30/2015' ProjectEndDate, 10000 as ProjectBudget
    UNION
    select 'Project B' as ProjectName,'07/15/2015' ProjectStartDate,'06/30/2016' ProjectEndDate, 20000 as ProjectBudget
    UNION
    select 'Project C' as ProjectName,'06/20/2015' ProjectStartDate,'06/30/2016' ProjectEndDate, 20000 as ProjectBudget
    UNION
    select 'Project D' as ProjectName,'1/01/2015' ProjectStartDate,'11/30/2015' ProjectEndDate, 20000 as ProjectBudget
    UNION
    select 'Project E' as ProjectName,'10/14/2014' ProjectStartDate,'05/31/2015' ProjectEndDate, 20000 as ProjectBudget
    UNION
    select 'Project F' as ProjectName,'11/26/2015' ProjectStartDate,'07/06/2016' ProjectEndDate, 20000 as ProjectBudget
    )t
    )T1
    order by ProjectName

    Thanks for your help

    • December 15, 2015 at 12:33 pm

      I will check once I got time on this.

    • December 15, 2015 at 4:06 pm

      Hi Badal,
      I can see its coloring, Just check the date format, enter MM/DD/YYYY

      Check my screenshot:-
      https://msbitips.wordpress.com/wp-admin/upload.php?item=1038

      Just send your screenshot in my gmail id:-
      prasadv.msbi@gmail.com

      Thanks
      Prasad

      • December 16, 2015 at 9:36 am

        Hi Badal,

        yes that expression needs to me modified,

        expression :-
        ,Case
        when keyCol>=KeyStartCol AND keyCol=keyStartCol and keyCol<=keyEndCol
        then 'Yes' else case when Quarter='Q'+datename(qq,ProjectStartDate) and (Year=datepart(YEAR,ProjectStartDate))then 'Yes' else case when Quarter='Q'+datename(qq,ProjectEndDate) and (Year=datepart(YEAR,ProjectEndDate))then 'Yes' else 'No' end end end ShowINTimescale

        Thanks
        Prasad

  2. Badal Ratra
    December 15, 2015 at 1:07 pm

    Thanks Prasad for your note. I really appreciate it. I have also raised another issue in the same forum thread regarding summation of project budget. Please check it mate when you get a chance.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/86f8ae60-dddf-4c04-ae30-db13c52d48c8/show-dates-data-in-timescale-view-using-ssrs?forum=sqlreportingservices#2bb9abd7-488c-40b9-afbd-6f6e089934fa

    Thanks for your help

  3. December 16, 2015 at 11:10 am

    Thank you so much Prasad. It works perfectly fine. Just have removed the following condition from the modified expression shared by you –

    AND keyCol=keyStartCol

    expression after update –
    ,Case
    when keyCol>=KeyStartCol and keyCol<=keyEndCol
    then 'Yes' else case when Quarter='Q'+datename(qq,ProjectStartDate) and (Year=datepart(YEAR,ProjectStartDate))then 'Yes' else case when Quarter='Q'+datename(qq,ProjectEndDate) and (Year=datepart(YEAR,ProjectEndDate))then 'Yes' else 'No' end end end ShowINTimescale

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: