October 21, 2011 at 3:12 pm
I am doing an ssrs report and I have employee hours transactions in one table and the forecast hours in another table
Forecast Table has
ProjectId HoursQTY Category
1 15 C1
1 20 C2
1 30 C3
Employee Trans Table
Proj Empl Category Hours QTY
1 123 C1 5
1 124 C1 10
1 125 C2 15
1 126 C3 10
I have linked EmployeeTrans table with ForecastTable with OuterLeft. I need tin the report to show total hours actuals from Employee Trans (total 40) and from Forecast (total 65). I need some help on how to make the expression so it SUM's up only Quantity of Forecasted QTY hours.
Result should be
Category ------------- HoursActual-----Hours Forecast
C1------------ -------------15----------------15
C2--------------------------15----------------20
C3--------------------------10----------------30
Total------------------------40----------------65
This may be simple but just started on this...
October 22, 2011 at 12:26 pm
You can try the query below:
WITH comparisonCTE(Category, HoursActual,HoursForecast) as(
select a.Category,a.TotalHoursQTY as HoursActual,ISNULL(forecast.HoursQTY,0) as HoursForecast
from
(select Category, sum(HoursQTY) as TotalHoursQTY from Empl_Trans group by Category) a
left outer join forecast
on forecast.category=a.Category
)
select Category, HoursActual,HoursForecast from comparisonCTE
UNION
select 'Total' as Category,sum(HoursActual),sum(HoursForecast) from comparisonCTE
October 24, 2011 at 7:39 am
Thanks a lot for your input. I am using Report builder 3.0 and not sur how your suggestion fits to my query. Below I am including my query where result is groupted by first Dimension (all projects falling in that dimension category) than by project and under the project I get all the categories that actuall hours are posted, for each category I also get the forecasted hours. Up to this point I am ok, only when i add expression to add forecasted hours I get incorrect number becasue on my Forecast hours table I only have forecast by category so the result has lots of duplications. This way when I join these table with outer I cannot sum up the categories as I get lots of duplications. Your query may be the solution but dont know how it will be included on my query. I hope you can help me and in meantime I need a good book on this...
SELECT
DIMENSIONS.DESCRIPTION
,DIMENSIONS.NUM
,PROJFORECASTEMPL.PROJID AS [PROJFORECASTEMPL PROJID]
,PROJFORECASTEMPL.CATEGORYID AS [PROJFORECASTEMPL CATEGORYID]
,PROJFORECASTEMPL.QTY AS [PROJFORECASTEMPL QTY]
,PROJFORECASTEMPL.COSTPRICE AS [PROJFORECASTEMPL COSTPRICE]
,PROJEMPLTRANS.PROJID AS [PROJEMPLTRANS PROJID]
,PROJEMPLTRANS.EMPLID
,PROJEMPLTRANS.CATEGORYID AS [PROJEMPLTRANS CATEGORYID]
,PROJEMPLTRANS.TRANSDATE
,PROJEMPLTRANS.QTY AS [PROJEMPLTRANS QTY]
,PROJEMPLTRANS.COSTPRICE AS [PROJEMPLTRANS COSTPRICE]
,PROJEMPLTRANS.DIMENSION AS [PROJEMPLTRANS DIMENSION]
,PROJTABLE.PROJID AS [PROJTABLE PROJID]
,PROJTABLE.NAME
,PROJTABLE.DIMENSION AS [PROJTABLE DIMENSION]
FROM
PROJEMPLTRANS
INNER JOIN PROJTABLE
ON PROJEMPLTRANS.PROJID = PROJTABLE.PROJID
INNER JOIN DIMENSIONS
ON PROJEMPLTRANS.DIMENSION = DIMENSIONS.NUM
LEFT OUTER JOIN PROJFORECASTEMPL
ON PROJEMPLTRANS.PROJID = PROJFORECASTEMPL.PROJID AND PROJEMPLTRANS.CATEGORYID = PROJFORECASTEMPL.CATEGORYID
WHERE
PROJEMPLTRANS.DATAAREAID = @DATAAREAID
AND (PROJEMPLTRANS.TRANSDATE BETWEEN (@StartDate) AND (@EndDate))
October 24, 2011 at 8:04 am
Did you try modifying and running the query that I have given?
The query I have given below is based on the input data and output given by you.
If your data is somewhat different, you may attach the sample input tables and required output. I will try to re-modify the query to your requirement.
October 24, 2011 at 8:29 am
Thanks for prompt reply and yes you are right that my initial query was a simplified version of this below
Tables:
Dimensions - used for Categories, like Servicing, Engineerring etc
ProjEmplTrans -this holds all hour transactions of employees to differnt projects for various time categories
ProjForecastEmpl - this table holds forecast for each category that is used to post time (i.e Commissioning, Training etc)
ProjTable - This is table that I am using to get the Project Name
Result
Dimension --------ProjectID-------Project Name------Category-----------PROJEMPLTRANS.QTY------PROJFORECASTEMPL.QTY
Servicing.........100001.................Test1....................Commissioning.......50.....................................60
................................................................................TESTING................40......................................70
.................................................................................Total.....................90......................................130
.......................100002.................Test2....................TECH SUPPORT......30.......................................40
.................................................................................Training.................20.......................................30
..................................................................................Total....................50.......................................70
So I am grouping by dimension and then Project ID and for each project I am listing Categories that employee transactions
are posted. Along that I am showing forecasted Hours. On my expressions in product builder I am getting correct Sum for
actual hour using Sum(Fields!PROJEMPLTRANS_QTY.Value). But not for the forecast if I use =Sum(Fields!PROJFORECASTEMPL.QTY.Value)
I get very high number as is adding all the rows that is getting from combination of table PROJEMPLTRANS and PROJFORECASTEMPL
My query at this point as follows
SELECT
DIMENSIONS.DESCRIPTION
,DIMENSIONS.NUM
,PROJFORECASTEMPL.PROJID AS [PROJFORECASTEMPL PROJID]
,PROJFORECASTEMPL.CATEGORYID AS [PROJFORECASTEMPL CATEGORYID]
,PROJFORECASTEMPL.QTY AS [PROJFORECASTEMPL QTY]
,PROJFORECASTEMPL.COSTPRICE AS [PROJFORECASTEMPL COSTPRICE]
,PROJEMPLTRANS.PROJID AS [PROJEMPLTRANS PROJID]
,PROJEMPLTRANS.EMPLID
,PROJEMPLTRANS.CATEGORYID AS [PROJEMPLTRANS CATEGORYID]
,PROJEMPLTRANS.TRANSDATE
,PROJEMPLTRANS.QTY AS [PROJEMPLTRANS QTY]
,PROJEMPLTRANS.COSTPRICE AS [PROJEMPLTRANS COSTPRICE]
,PROJEMPLTRANS.DIMENSION AS [PROJEMPLTRANS DIMENSION]
,PROJTABLE.PROJID AS [PROJTABLE PROJID]
,PROJTABLE.NAME
,PROJTABLE.DIMENSION AS [PROJTABLE DIMENSION]
FROM
PROJEMPLTRANS
INNER JOIN PROJTABLE
ON PROJEMPLTRANS.PROJID = PROJTABLE.PROJID
INNER JOIN DIMENSIONS
ON PROJEMPLTRANS.DIMENSION = DIMENSIONS.NUM
LEFT OUTER JOIN PROJFORECASTEMPL
ON PROJEMPLTRANS.PROJID = PROJFORECASTEMPL.PROJID AND PROJEMPLTRANS.CATEGORYID = PROJFORECASTEMPL.CATEGORYID
WHERE
PROJEMPLTRANS.DATAAREAID = @DATAAREAID
AND (PROJEMPLTRANS.TRANSDATE BETWEEN (@StartDate) AND (@EndDate))
October 26, 2011 at 7:41 am
Hello Grasshopper
I am not having any luck with this...pls let me know if you can help me
October 26, 2011 at 12:08 pm
Lon2012 (10/26/2011)
Hello GrasshopperI am not having any luck with this...pls let me know if you can help me
You still have not answered if you tried the query suggested. The logic to follow is to sum your actual hours BEFORE joining with projected. Using a CTE to do this will give you the proper results:
;WITH EmpTransSum AS (SELECT Project, Category, SUM(Hours) AS HoursActual FROM [Employee Trans] GROUP BY Project, Category)
SELECT Forecast.ProjectId, Forecast.Category, EmpTransSum.HoursActual, Forecast.HoursQTY
FROM Forecast
LEFT JOIN EmpTransSum
ON Forecast.ProjectId = EmpTransSum.ProjectId
AND Forecast.Category = EmpTransSum.Category
--untested
This is untested and the syntax may be off since I wrote it quickly, but it should give ou the main idea?
Thanks,
Jared
Jared
CE - Microsoft
October 26, 2011 at 4:05 pm
LogicWonder: I did try but could not complete it with results for my scenario.
Jared: Thanks for your reply, I have tried your version with my scenario below. I added some more for my internal cost calculations and it is working GREAT with one exception
1. When there is a forecast for one category and there is no actual transactions for that project in PROJEMPLTRANS table I get a line against that project with estimated hours. This is a good thing as it is ok to show estimation even if there are no transactions for that project for that category.
HOWEVER, if on the forecast table PROJFORECASTEMPL there is no forecast line for a particular category i.e. TESTING then I don't get any actual hours data for that category. If I cant get both ways I rather have the actuals all trans and forecast when there is actual
2. This is second part that if I need to add now DIMMENSION TABLE and group projects by dimension. what modifications I need. I have tried few things but as I was writing about item 1 i thought I ask for this too
WITH EmpTransSum AS (SELECT PROJID, CATEGORYID, SUM(QTY) AS HoursActual, SUM(QTY*COSTPrice) AS CostActual FROM [PROJEMPLTRANS] GROUP BY PROJID, CATEGORYID)
SELECT PROJFORECASTEMPL.PROJID, PROJFORECASTEMPL.categoryid, EmpTransSum.HoursActual, EmpTransSum.CostActual, PROJFORECASTEMPL.QTY, PROJFORECASTEMPL.COSTPRICE
FROM PROJFORECASTEMPL
LEFT JOIN EmpTransSum
ON PROJFORECASTEMPL.PROJID = EmpTransSum.PROJID
AND PROJFORECASTEMPL.categoryID = EmpTransSum.CATEGORYID
October 26, 2011 at 5:43 pm
Change the LEFT JOIN to FULL JOIN. For the dimensions, I guess it depends on what your dimensions are... What would you be joining the dimensions to this on?
Thanks,
Jared
Jared
CE - Microsoft
October 27, 2011 at 8:17 am
Thanks Jared for reply. I had tried before FULL JOIN, also FULL OUTER JOIN.
When I run this query I see two issues
1. If the project has forecast for some categories but is missing one category that there are transactions in PROJEMPLTRANS it wont be listed on that project group on the report I.e if we have transactions for category TESTING and there is no forecast but the same project has some other
2. For other projects that have no forecast for any category I just get blank fields for ProJ id, CategoryID and will list on the report only the total values for HoursActual and CostActual. IF I just run the query without report I see all the rows for other projects with blank PROJ no a and category and just see values for HoursActual and CostActual.
I would like to list the projects and categories that have transactions regardless if there is forecast or not. For now I am getting the numbers but no other data.
Second part that would like to implement is include PROJTABLE that is linked with PROJEMPLTRANS using PROJID so I can do two things
1. Show the Project name PROJTABLE.NAME
2. Group Projects by Department PROJTABLE.DEPNO
and insert final condition so I can use some parameters for Dates and DATAAREAID that is basically records of a particular company.
WHERE (PROJEMPLTRANS.TRANSDATE BETWEEN (@StartDate) AND (@EndDate))
AND PROJEMPLTRANS.DATAAREAID = @DATAAREAID
AND PROJFORECASTEMPL.DATAAREAID = @DATAAREAID
I appreciate your help in advance
October 27, 2011 at 8:29 am
Lon2012 (10/27/2011)
Thanks Jared for reply. I had tried before FULL JOIN, also FULL OUTER JOIN.When I run this query I see two issues
1. If the project has forecast for some categories but is missing one category that there are transactions in PROJEMPLTRANS it wont be listed on that project group on the report I.e if we have transactions for category TESTING and there is no forecast but the same project has some other
2. For other projects that have no forecast for any category I just get blank fields for ProJ id, CategoryID and will list on the report only the total values for HoursActual and CostActual. IF I just run the query without report I see all the rows for other projects with blank PROJ no a and category and just see values for HoursActual and CostActual.
I would like to list the projects and categories that have transactions regardless if there is forecast or not. For now I am getting the numbers but no other data.
Second part that would like to implement is include PROJTABLE that is linked with PROJEMPLTRANS using PROJID so I can do two things
1. Show the Project name PROJTABLE.NAME
2. Group Projects by Department PROJTABLE.DEPNO
and insert final condition so I can use some parameters for Dates and DATAAREAID that is basically records of a particular company.
WHERE (PROJEMPLTRANS.TRANSDATE BETWEEN (@StartDate) AND (@EndDate))
AND PROJEMPLTRANS.DATAAREAID = @DATAAREAID
AND PROJFORECASTEMPL.DATAAREAID = @DATAAREAID
I appreciate your help in advance
Why are people entering numbers for categories that don't exist? Why don't categories exist that should in the forecast?
That is a problem that should be addressed... However, to fix this you can use a case statement:
SELECT CASE WHEN forecast.category IS NULL THEN actual.category ELSE forecast.category END AS category, nextColumn, nextColumn2
FROM ... You can use this for any of your blanks, but I would still address the issue at the source.
If you are using the dimensions as the base for your results, I would start with that table and then join the other 2 as needed.
Thanks,
Jared
Jared
CE - Microsoft
January 30, 2012 at 2:46 pm
I am back and need some more help to resolve this. From above issue I had I sorted out by making some changes on the Select statement. Results now are fine I just need Project Name that need to be joined from another table.
Query below works ok just need to include PROJTABLE.NAME that can be joined base on PROJTABLE.PROJID = PROJEMPLTRANS.PROJID
WITH EmpTransSum AS
(
SELECT PROJID, CATEGORYID, DATAAREAID, DIMENSION, SUM(QTY) AS HoursActual, SUM(QTY*COSTPrice) AS CostActual
FROM [PROJEMPLTRANS]
GROUP BY PROJID, CATEGORYID, DATAAREAID, DIMENSION
)
SELECT EmpTransSum.PROJID, EmpTransSum.categoryid, EmpTransSum.HoursActual, EmpTransSum.CostActual, EmpTransSum.DATAAREAID, EmpTransSum.DIMENSION, PROJFORECASTEMPL.QTY, (PROJFORECASTEMPL.QTY * PROJFORECASTEMPL.COSTPrice) AS CostEstimate
FROM PROJFORECASTEMPL
FULL OUTER JOIN EmpTransSum
ON PROJFORECASTEMPL.PROJID = EmpTransSum.PROJID
AND PROJFORECASTEMPL.categoryID = EmpTransSum.CATEGORYID
WHERE EmpTransSum.DATAAREAID = 'ibs'
January 30, 2012 at 2:55 pm
I don't understand what the question is. It sounds like you already know what you need to do.
Jared
CE - Microsoft
January 30, 2012 at 3:01 pm
Jared,
Thanks for reply. I am having difficulty to join another table, in this case PROJTABLE so on my query results I get the field PROJTABLE.NAME.
PRORJTABLE is linked with PROJEMPLTRANS tablw with PROJID field
Query below works ok just need to include PROJTABLE.NAME that can be joined base on PROJTABLE.PROJID = PROJEMPLTRANS.PROJID
WITH EmpTransSum AS
(
SELECT PROJID, CATEGORYID, DATAAREAID, DIMENSION, SUM(QTY) AS HoursActual, SUM(QTY*COSTPrice) AS CostActual
FROM [b]PROJEMPLTRANS[/b]
GROUP BY PROJID, CATEGORYID, DATAAREAID, DIMENSION
)
SELECT EmpTransSum.PROJID, EmpTransSum.categoryid, EmpTransSum.HoursActual, EmpTransSum.CostActual, EmpTransSum.DATAAREAID, EmpTransSum.DIMENSION, PROJFORECASTEMPL.QTY, (PROJFORECASTEMPL.QTY * PROJFORECASTEMPL.COSTPrice) AS CostEstimate
FROM PROJFORECASTEMPL
FULL OUTER JOIN EmpTransSum
ON PROJFORECASTEMPL.PROJID = EmpTransSum.PROJID
AND PROJFORECASTEMPL.categoryID = EmpTransSum.CATEGORYID
WHERE EmpTransSum.DATAAREAID = 'ibs'
January 30, 2012 at 3:06 pm
Ok... So add it. I assume you know how to add joins and what the different ones are. It is indicated in previous posts that you do. If not, go here: http://www.sqlservercentral.com/articles/T-SQL/67941/
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply