asked on

YTD/PTD Groupings

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

ASKER CERTIFIED SOLUTION

Hwkranger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DEN_Jimbo

ASKER

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