Solved

YTD/PTD Groupings

Posted on 2009-05-08
2
530 Views
Last Modified: 2012-05-06
Hi- I need to run a report that would summarize Project to date and Year to date items.  These currently are all retrieved in 1 data set.

How do I display them to they are group together eg.

Project name, Period, Start date, hours
Project1          PTD   Max(date)eg 2/2/2009    100
project1          YTD   1/1/2009       40

0
Comment
Question by:DEN_Jimbo
2 Comments
 
LVL 9

Accepted Solution

by:
Hwkranger earned 500 total points
ID: 24337443
If your line data looks something like:

Project, Item Date, Hours  

You can do

SELECT
 Project, YEAR(Item Date), SUM(Hours)
FROM
 <Source>
GROUP BY Project, Year(Item Date)

Example


DECLARE @Table TABLE (Project nvarchar(10), ItemDate datetime, Hours float)
 

INSERT INTO @Table VALUES ('Project 1', '01/01/05', '5.5')

INSERT INTO @Table VALUES ('Project 1', '02/01/05', '1.5')

INSERT INTO @Table VALUES ('Project 1', '03/01/05', '2.5')

INSERT INTO @Table VALUES ('Project 1', '04/01/05', '3.5')

INSERT INTO @Table VALUES ('Project 1', '01/01/06', '4.5')

INSERT INTO @Table VALUES ('Project 1', '02/01/07', '6.5')

INSERT INTO @Table VALUES ('Project 2', '01/01/05', '5.5')

INSERT INTO @Table VALUES ('Project 2', '02/01/05', '1.5')

INSERT INTO @Table VALUES ('Project 2', '03/01/05', '2.5')

INSERT INTO @Table VALUES ('Project 2', '04/01/05', '3.5')

INSERT INTO @Table VALUES ('Project 2', '01/01/06', '4.5')

INSERT INTO @Table VALUES ('Project 2', '02/01/07', '6.5')
 

SELECT Project, SUM(Hours), Year(ItemDate)

FROM @Table

GROUP BY Project, Year(ItemDate)

Open in new window

0
 

Author Comment

by:DEN_Jimbo
ID: 24340288
hum.  I am tring to do this with reporting serices not plain sql.  I think its the way I do the layouts.  For example my dataset is included below.  Can I combine a table with two datasets that are grouped on the projectname or Title? because then I could do a full query for PTD(eg the code below) then do a query from the start of the year to present.

<multiList title="Projects" relativeSiteUrl="@URL!" tableName="Projects" type="List">

<fields>Title,State,Site,Rstlabel,ProjectType,Start,ActualStart,BaselineFinish,BaselineWork,BaselineCost,Finish,Cost,Work,ExecSponsor,Owner,PM</fields>

<query>

<Where>

<Eq>

<FieldRef Name="State" /><Value Type="Text">Active</Value>

</Eq>

</Where>

</query>

</multiList>

<sqlOp op="distinct">

<sortOrder>ASC</sortOrder>

<dstTableName>SIP Projects</dstTableName>

<tableName>Projects</tableName>

<fieldName>Title,State,Site,Rstlabel,ProjectType,Start,ActualStart,BaselineFinish,BaselineWork,BaselineCost,Finish,Cost,Work,ExecSponsor,Owner,PM</fieldName>

</sqlOp>

<resultSet>Projects</resultSet>

</root>

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now