Gantt charts?

I have a table with start and end dates for useage of an asset and would
like to generate a sort of gantt chart for a date range. ie a horizontal
bar graph like Microsoft Project with the assests down the left-hand-side
and dates along the top.
Is this possible with SSRS? I feel like I have read about RS features that
would assist with this but can not now find an example.
Any ideas would be much appreciated.
cheers,
Paul.On Apr 28, 9:08 pm, Paul Ritchie <REMOVEpritc...@.xtraREMOVE.co.nz>
wrote:
> I have a table with start and end dates for useage of an asset and would
> like to generate a sort of gantt chart for a date range. ie a horizontal
> bar graph like Microsoft Project with the assests down the left-hand-side
> and dates along the top.
> Is this possible with SSRS? I feel like I have read about RS features that
> would assist with this but can not now find an example.
> Any ideas would be much appreciated.
> cheers,
> Paul.
I myself haven't done Gantt charts via SSRS: it seems to be a rare
topic. This company seems to have experience in this area:
http://www.randsmanagement.com/
I would suggest using a stacked bar chart to accomplish this.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Function to return all days in a month

Hi,

What I need to do is to join a table with all days in a range that the users inform with another table that has the values so I can have a montly report separated day by day...I don't want to create the table manually cause the range is different...

I tried to do with cursor but it's to slow..

Any better solution?

Thanks

You don't say what version of SQL Server you are using. If you are using SQL Server 2005 then you can use a recursive common table expression to do what you want. Here is a sample:

Code Snippet

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

-- Use a common table expression to loop over a set of years

;WITH DayList (DayDate) AS

(

SELECT @.RepMonth

UNION ALL

SELECT DATEADD(d, 1, DayDate)

FROM DayList

WHERE (DayDate < DATEADD(d, -1, DATEADD(m, 1, @.RepMonth)))

)

SELECT *

FROM DayList

|||

OK thanks. It solves for my other program....But I still need it to sql2000... =)

Thanks

|||

Been thinking about it since posting previously and I came up with the following code which works if you don't have SQL Server 2005. It depends upon having a table which simply contains the values from 0 to at least 30 (giving 31 records for 31 days). I put up to 32 in to show check the logic worked for any month - and that it can have any number of records in it. In the sample this is a table variable - but in a real system this would probably just be a little standard table.

Code Snippet

DECLARE @.ValueList table

(

ValueID int

)

SET NOCOUNT ON

INSERT INTO @.ValueList Values ( 0)

INSERT INTO @.ValueList Values ( 1)

INSERT INTO @.ValueList Values ( 2)

INSERT INTO @.ValueList Values ( 3)

INSERT INTO @.ValueList Values ( 4)

INSERT INTO @.ValueList Values ( 5)

INSERT INTO @.ValueList Values ( 6)

INSERT INTO @.ValueList Values ( 7)

INSERT INTO @.ValueList Values ( 8)

INSERT INTO @.ValueList Values ( 9)

INSERT INTO @.ValueList Values (10)

INSERT INTO @.ValueList Values (11)

INSERT INTO @.ValueList Values (12)

INSERT INTO @.ValueList Values (13)

INSERT INTO @.ValueList Values (14)

INSERT INTO @.ValueList Values (15)

INSERT INTO @.ValueList Values (16)

INSERT INTO @.ValueList Values (17)

INSERT INTO @.ValueList Values (18)

INSERT INTO @.ValueList Values (19)

INSERT INTO @.ValueList Values (20)

INSERT INTO @.ValueList Values (21)

INSERT INTO @.ValueList Values (22)

INSERT INTO @.ValueList Values (23)

INSERT INTO @.ValueList Values (24)

INSERT INTO @.ValueList Values (25)

INSERT INTO @.ValueList Values (26)

INSERT INTO @.ValueList Values (27)

INSERT INTO @.ValueList Values (28)

INSERT INTO @.ValueList Values (29)

INSERT INTO @.ValueList Values (30)

INSERT INTO @.ValueList Values (31)

INSERT INTO @.ValueList Values (32)

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

SELECT DATEADD(d, ValueID, @.RepMonth)

FROM @.ValueList

WHERE (DATEADD(d, ValueID, @.RepMonth) < DATEADD(m, 1, @.RepMonth))

Put an order by on the selects if you need the records in a particular order.

|||

You might want to give consideration to using a calendar table; you can find an article that describes this type of table here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

GREAT !!! Thanks..you're the master....

Do you have msn....I'll add you to never forget !(or add me...it's my nickname @.hot....

|||

You may find having a 'Calendar' table to be constantly useful.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

create function udf_MakeDatesTable(

@.dtInput smalldatetime

,@.Months tinyint = 1

)

/*

this function will allow you get a table of dates

for any range of months you like,

starting at the input date.

See below for the usage

*/

returns @.TableOfDates table(dtTemp smalldatetime)

as

begin

declare @.dtEnd smalldatetime

--declare @.dtInput smalldatetime

--set @.dtInput = '1/5/2005 05:12'

--This line will strip out the Time component

set @.dtinput = convert(varchar,@.dtInput,106)

--Find our end date. Always @.Months after @.dtInput

set @.dtEnd = dateadd(m, @.Months, @.dtInput)

--declare @.TableOfDates table(dtTemp smalldatetime)

while @.dtInput < @.dtend

begin

insert into @.TableOfDates select @.dtInput

set @.dtInput = @.dtInput + 1

end

return

end

go

select * from dbo.udf_MakeDatesTable('1/15/2005',1)

Function to convert a 'date range' to table of starting and ending

I have a table that contains two fields: effectiveFrom, effectiveTo.
The time elapsed between these day could be greater than one year.
I would like to develop a UDF that would convert these dates into a
table of values. For example, if the dates are 7/1/03 and 4/1/06 the
resulting table would look like:
startingDate endingDate
07/01/03 12/31/03
01/01/04 12/31/04
01/01/05 12/31/05
01/01/06 04/01/06
I am confident that I can do this in a stored procedure, but I'm having
difficulty in a table-set UDF.
Suggestions appreciated.
Craig BuchananAdi-
Not exactly what I was looking for, but useful in other situations.
Thanks a lot for your response.
Craig
Adi wrote:
> You can do it this way:
> create function ShowDates (@.StartDate smalldatetime, @.EndDate
> smalldatetime)
> returns @.DatesTable table (DateCol smalldatetime)
> as
> begin
> while @.startDate <= @.EndDate
> BEGIN
> insert into @.DatesTable (DateCol) values (@.StartDate)
> set @.StartDate = dateadd(dd,1, @.StartDate)
> END
> return
> END
> If you are using SQL Server 2005, then you can use recursive CTE to do
> the same thing
>|||Razvan-
Perfect! Thanks a lot.
Craig Buchanan
Razvan Socol wrote:
> Hello, Craig
> You can use this function (after you give it a better name):
> CREATE FUNCTION YourFunctionName(
> @.EffectiveFrom datetime,
> @.EffectiveTo datetime
> )
> RETURNS @.Result TABLE (
> StartingDate datetime NOT NULL PRIMARY KEY,
> EndingDate datetime NOT NULL UNIQUE,
> CHECK (startingDate<=endingDate)
> ) AS BEGIN
> WHILE YEAR(@.EffectiveFrom)<YEAR(@.EffectiveTo) BEGIN
> DECLARE @.EndOfYear datetime
> SET @.EndOfYear=DATEADD(year,DATEDIFF(year,0,
@.EffectiveFrom)+1,0)-1
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EndOfYear)
> SET @.EffectiveFrom=@.EndOfYear+1
> END
> IF @.EffectiveFrom<=@.EffectiveTo BEGIN
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EffectiveTo)
> END
> RETURN
> END
> Razvan
>|||Uri-
Thanks for the reply. Not exactly what I needed, but I can use this in
another situation.
Thanks,
Craig
Uri Dimant wrote:
> Try this one
> CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> DECLARE @.rc AS INT
> SET @.rc = 1
> INSERT INTO @.Dates VALUES(@.from)
> WHILE @.from + @.rc * 2 - 1 <= @.to
> BEGIN
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> SET @.rc = @.rc * 2
> END
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> WHERE dt + @.rc <= @.to
> RETURN
> END
> GO
> SELECT dt FROM fn_dates('20060101', '20060130')
>
> "Craig Buchanan" <user@.example.net> wrote in message
> news:OAAuwYKMGHA.2992@.tk2msftngp13.phx.gbl...
>