October 9, 2014 at 8:27 am
Hi frnds,
Could u pls help me solving my issue:
My req.:
present in a line chart the number of active employed consultants and the number of available consultants per week.
I have build a Chart in SSRS where I'm trying to show available consultants vs. number of employees (consultants) per week. (see attachments)
So I have found the code for exploring the number of employees per week,
The code for week numbers:
=DatePart(DateInterval.WeekOfYear,Fields!stex_startdate.Value)
But when I use this one, it only shows the amount of employees for each week and doesn't count the amount of employees week before for example.
I think it should shows the amount between my startdate and endate
The fields from my database are:
Filteredstex_consultantcontract.stex_startdate,
Filteredstex_consultantcontract.stex_enddate,
I need an solution like
SUM(DatePart(DateInterval.WeekOfYear,Fields!stex_startdate.Value)
or something like:
=DatePart(DateInterval.WeekOfYear, @StartDate between @EndDate)
Can this be done?
Pls help me out.
Thanks in Advance
October 9, 2014 at 12:23 pm
Without table definitions and maybe some sample data, it's hard to tell, but I would guess that if you created a Calendar table and outer joined that to the source of your chart (looks like a table of employment?), then it should be easy, because you would end up with all the dates in a range, and you would get zeroes for counts anytime nobody was working. Then your AvailableStaff - EmployedStaff calculation would work.
October 10, 2014 at 2:24 am
Thanks @SSC Eights! for the reply.
This is the query which I use for my dataset and I think this one meets my requirements.
But my issue is how to present the data the right way in my chart.
The requirements description:
As a manager I want to be able to see the number of available consultants set against the number of active employed consultants
so I can see the impact on the availability.
- present in a line chart the number of active employed consultants and the number of available consultants per week (for the running year)
- I want to be able to select the required BU's manually
Logic behind the lines:
Number of available consultants (V)
- a consultant contract with a start date before TODAY() and an end date after TODAY() or BLANC
- no active assignment details with a end date after TODAY() or BLANC
Number of employed consultants:
- a consultant contract with a start date before TODAY() and an end date after TODAY() or BLANC
My query for my Dataset:
--------------------------------------------------------------------------------------------------------------
SELECT Distinct
Filteredstex_Consultant.stex_consultantid,
Filteredstex_consultantcontract.stex_consultantname,
Filteredstex_consultantcontract.stex_startdate,
Filteredstex_consultantcontract.stex_enddate,
Case When
(CAST(Filteredstex_consultantcontract.stex_startdate AS date) < CAST(GETDATE() AS DATE)
AND(CAST(Filteredstex_consultantcontract.stex_enddate AS date) > CAST(GETDATE() AS DATE)OR Filteredstex_consultantcontract.stex_enddate is NULL))
THEN 1
ELSE NULL
End as Employed_consultants,
Case When
(CAST(Filteredstex_consultantcontract.stex_startdate AS date) < CAST(GETDATE() AS DATE)
AND(CAST(Filteredstex_consultantcontract.stex_enddate AS date) > CAST(GETDATE() AS DATE)OR Filteredstex_consultantcontract.stex_enddate is NULL)
AND (CAST(Filteredstex_assignmentdetail.stex_enddate AS date) < CAST(GETDATE()AS DATE)
OR (Filteredstex_assignmentdetail.stex_startdate is NULL and Filteredstex_assignmentdetail.stex_enddate IS NULL ))
AND NOT EXISTS(Select 1
FROM Filteredstex_assignmentdetail
where Filteredstex_Consultant.stex_consultantid = Filteredstex_assignmentdetail.stex_consultant
AND CAST(Filteredstex_assignmentdetail.stex_enddate AS date) > CAST(GETDATE() AS DATE)
))
THEN 1
ELSE NULL
End as Available_Counsultants
FROM Filteredstex_Consultant
INNER JOIN Filteredstex_consultantcontract
ON Filteredstex_Consultant.stex_consultantid = Filteredstex_consultantcontract.stex_consultant
LEFT JOIN Filteredstex_assignmentdetail
ON Filteredstex_Consultant.stex_consultantid = Filteredstex_assignmentdetail.stex_consultant
WHERE 1=1
AndFilteredstex_consultantcontract.stex_businessunitname IN (@BUname)
Order by Filteredstex_consultantcontract.stex_consultantname
--------------------------------------------------------------------------------------------------------------------
In my Dataset properties, I have created a calculated field for my Week Nr. (see attachment)
I have selected this calc.field in my Chart Data (see attachment).
The calculated field is builed with this expression:
=DatePart(DateInterval.WeekOfYear, Fields!stex_startdate.Value)
This present the data each week and the data is always different.
One week I have 11 employees and other week I have 3 employees.
It's not logical if you work for large department.
I think my troubleshooter is the calculated field. It only checks my startdate.value, I think it should check the period between my StartDate and EndDate.
And I cannot find a solution for this issue.
I have also tried with two Parameters (@StartDate & @EndDate) in combination with DataDiFF().
Expression: =iif(IsNothing(Fields!stex_startdate.Value),"N/A",DateDiff(dateinterval.WeekOfYear,Parameters!StartDate.Value,Parameters!EndDate.Value))
But this shows only the total amount between the two parameters and doesn't present the data in chart by WeekNr like I want it to do.
Anyone????
Thanks
October 10, 2014 at 10:51 pm
The query you posted had me confused. Once I took a step backward and slapped up a totally fake dataset, this was a snap. I created a bar chart instead of a line chart, so I would have a bar for Employed and another for Available.
I attached the report I did. It's somewhat simplified, just so you could see how it works.
Here's my dummy dataset:
SELECT 1 AS WeekNo, 3 AS EmployedConsultants, 5 AS AvailableConsultants
UNION ALL
SELECT 2, 4, 8
UNION ALL
SELECT 3, 5 , 7
UNION ALL
SELECT 4, 9, 5
Then the graph is stupid easy:
Employed = MIN(EmployedConsultants)
Available = MIN(AvailableConsultants)
Category Groups: WeekNo
And you're done! You can add a BusinessUnit to the dataset if you want... you would just add a parameter and filter...
SELECT Weeknumber, Employed, Available
FROM ...
WHERE BusinessUnit IN (@BusinessUnit);
then @BusinessUnit would be multi-valued parameter.
This issue:
I need an solution like
SUM(DatePart(DateInterval.WeekOfYear,Fields!stex_startdate.Value)
or something like:
=DatePart(DateInterval.WeekOfYear, @StartDate between @EndDate)
is the part I would deal with in the stored procedure the report is based on. If you use a stored procedure, you can easily include a Calendar table... but one issue at a time.
October 13, 2014 at 1:35 am
Thanks for your reply, I fully understand your way of approach and to give me an idea, but I have to deal with table views of this department and I am not database administrator. So I was thinking that I could somehow resolve this by trying to write a code in the report instead of trying S.P. functions or other ways.
Is there not a way to resolve this in the report only or must I build Stored Procedure Functions for this matter? If so, do you have an example for stored procedure?
Thanks for your help..
October 13, 2014 at 10:15 am
I guess you don't have to build stored procedures, but it makes things a lot easier.
Any chance you could post some dummy data, so we can see what you're dealing with and have a dataset to model with? I don't need hundreds of records - just enough to model what you're dealing with. Then I can be sure that my answer will work for you.
See these two web pages, because they discuss it in detail.
One of them mentions using a Numbers or Tally table to force the existence of records for each week. If you have WeekNumber in your stored procedure, then you can outer join that to a Tally table, and you can force the existence of the records in the Tally table.
e.g.
SELECT Tally.N, OtherTable.WeekNumber, OtherTable.Field....
FROM Tally LEFT JOIN OtherTable ON Tally.N = OtherTable.WeekNumber
...
hope this sorts it out.
October 14, 2014 at 2:43 am
Thanks for the reply.
I have made some data export from my db (see attachment)
I have also adjusted some rows.
Maybe this gives you an idea...
The rows are from 3 tables which I use for my dataset query.
Filteredstex_Consultant
Filteredstex_consultantcontract
Filteredstex_assignmentdetail
ps. I deleted some rows, they were not relevant for this matter.
I was testing with some query's, trying to just show Employed Consultants in one week (see attachment). But if you take a look at this image you will see that the amount of Empl_Consultants are different by the week and I understand that my query is asking for this view.
But what I need is something like: amount of weeknr 3 should maybe be 11 and not 1.
It should also consider the weeks from the past to defined the amount of Employed Consultants, I think. If I show this view to my manager he will say: we had 10 Consultants in week 1 and in week 3 we had 1, so we fired 9 consultants. I think this is not correct.
So I need some solution for this matter.
I hope somehow that I've given you a clear view of I want I want.
Thanks.
October 14, 2014 at 10:35 am
Any chance you can post the create table scripts for the 3 tables? (Are these part of an external system? the table and field names are mind boggling!)
October 14, 2014 at 1:21 pm
Check out this article [/url].
It shows one of the reasons to have a Calendar table. It makes a job like the one you are attempting really easy.
The problem you are having is caused by the fact that you cannot guarantee that there are records for every time period (month, week, year, quarter... whatever) that you're trying to summarize. As a result, your result set is missing the months where there are no records. To force the existence of records for each week, you need a Calendar table containing these dates that you can outer join to. Something like
SELECT CT.TheDate, A.EmployeeID, A.StartDate, A.EndDate
FROM CalendarTable CT LEFT JOIN Assignment A ON (CT.TheDate>=A.StartDate AND CT.TheDate<=A.EndDate)
WHERE...
Once you have this, the issues you're having with your graph missing weeks etc is solved, because the Calendar table forces those dates to exist in your query results. I would suggest pointing your DB admins to the article and seeing if you can't convince them to at least let you test it in a non-production database. That would make your life infinitely easier and this problem really easy to solve. If that's not possible, then I am not sure I know a way of accomplishing what you are trying to do.
October 16, 2014 at 10:46 pm
I think I sorted it out. The problem is that it requires a Calendar table/ table valued function in order to work (because you have to have all the dates in a range to make the graph work correctly.)
Here's the stored procedure I wrote:
CREATE PROC uspContractorAvailability
@StartDate DATE,
@EndDate DATE
AS
SELECT ContractorID
, CalendarDate
, CASE WHEN CalendarDate BETWEEN StartDate AND EndDate THEN 'Employed' ELSE 'Available' END ContractorStatus
FROM EmpContract ec CROSS JOIN Calendar c
WHERE c.CalendarDate BETWEEN @StartDate AND @EndDate;
I added a line chart and a stacked bar chart (because then you can see how many consultants you have available) just so you could see the difference.
If you want, I can upload the report with a dummy dataset so you can see it in action.
Pieter
October 17, 2014 at 1:19 am
Thanks Pieter, yes would you please upload that report?
October 17, 2014 at 1:20 am
I will discuss this with the department and tell them about it.
I'm so curious to see if it works but thanks anyways.
October 17, 2014 at 11:41 am
Here is the report... Hopefully my table/field names match yours. If not, I can share the table scripts too.
Just realized... I'm using 2012, so I may have to redo it in 2008. =(
It's pretty simple, though. So if you're stuck post back and I'll give you quick instructions. I basically created one dataset that filtered by date (based on the stored procedure), and then created two matrices and filtered them - one for ContractorStatus = "Available" and the other for ContractorStatus = "Employed". (ContractorStatus or whatever it's called is a column in the stored procedure, so there's pretty much nothing to do when you drop the columns into the matrix.
October 21, 2014 at 3:34 am
Thanks for your replay and your solutions.
I had invited a colleague to see how we can build like you suggested that meets our requirements, so we build it like this query.
Only when I try to select more than 1 value for my parameter @BUname in my report, then I'm getting the following error:
"column name or number of supplied values does not match table definition"
Do you have any idea what's wrong with it?
Or do you have an solution for this?
declare @tijdvakstart datetime
declare @tijdvakeind datetime
--declare @BUname varchar(250)
set @tijdvakstart= @StartDate
set @tijdvakeind= getdate()
declare @inassignement table ( consultant uniqueidentifier, dag datetime, available bit)
declare @bu_consultants table( consultant uniqueidentifier primary key, bu varchar(100) )
insert into @bu_consultants
select stex_consultant, @BUname from Filteredstex_consultantcontract
where stex_businessunitname IN (@BUname)
group by stex_consultant;
with mycte as
(
select cast(@tijdvakstart as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < @tijdvakeind)
insert into @inassignement
select CS_A.stex_consultant,DateValue,1 AS INASSIGNMENT
--CS_A.stex_startdate , CS_A.stex_enddate
--CS_A.stex_startdate,CS_A.stex_enddate
from mycte
LEFT JOIN Filteredstex_assignmentdetail CS_A
ON
-- the day of DateValue in Assignement ?
( DATEVALUE BETWEEN CS_A.stex_startdate AND CS_A.stex_enddate OR ( CS_A.stex_enddate IS NULL AND CS_A.stex_startdate <= DateValue ))
WHERE CS_A.stex_businessunitname IN (@BUname)
and CS_A.statuscode = 1
group by DateValue,CS_A.stex_consultant
OPTION (MAXRECURSION 0);
with mycte as
(select cast(@tijdvakstart as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < @tijdvakeind)
select
DATEPART(YEAR, DATEVALUE) AS Jaar,
SUM((( CASE WHEN ( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR ( CS.stex_enddate IS NULL AND CS.stex_startdate <= DateValue) ) THEN 1 ELSE 0 END )))
AS InDienst,
SUM(((CASE WHEN nb.dag is null
AND
-- in Employed?
( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR ( CS.stex_enddate IS NULL AND CS.stex_startdate <= DateValue) )
THEN 1 ELSE 0 END) )) AS Available,
DATEPART(WEEK, DateValue ) AS Weeknummer, 0
from mycte
LEFT OUTER JOIN Filteredstex_consultantcontract CS
ON ( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR CS.stex_enddate IS NULL)
LEFT OUTER JOIN @inassignement nb ON nb.consultant = CS.stex_consultant AND dag=DateValue
WHERE CS.stex_consultant IN ( select consultant from @bu_consultants )
GROUP BY DATEPART(YEAR, DATEVALUE) , DATEPART(WEEK, DateValue )
ORDER BY DATEPART(WEEK, DateValue ) ASC
OPTION (MAXRECURSION 0);
October 21, 2014 at 7:21 am
I had to change my (insert into) for previous error, but now having this error:
"The select list for the INSERT statement contains more items than the insert list.
The number of SELECT values must match the number of INSERT columns. "
I'm probably missing something and I don't know what...
my query:
________________________________________________________________
declare @tijdvakstart datetime
declare @tijdvakeind datetime
--declare @BUname varchar(100)
set @tijdvakstart=@StartDate
set @tijdvakeind= getdate();
declare @inassignement table ( consultant uniqueidentifier, dag datetime, available bit)
declare @bu_consultants table( consultant uniqueidentifier primary key, bu varchar(100) )
insert into @bu_consultants (consultant, bu)
select stex_consultant, @BUname from Filteredstex_consultantcontract
where stex_businessunitname IN (@BUname)
group by stex_consultant;
with mycte as
(
select cast(@tijdvakstart as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < @tijdvakeind)
insert into @inassignement (consultant, dag, available)
select CS_A.stex_consultant,DateValue,1 AS INASSIGNMENT
--CS_A.stex_startdate , CS_A.stex_enddate
--CS_A.stex_startdate,CS_A.stex_enddate
from mycte
LEFT JOIN Filteredstex_assignmentdetail CS_A
ON
-- day of DateValue in Assignement ?
( DATEVALUE BETWEEN CS_A.stex_startdate AND CS_A.stex_enddate OR ( CS_A.stex_enddate IS NULL AND CS_A.stex_startdate <= DateValue ))
WHERE CS_A.stex_businessunitname IN (@BUname)
and CS_A.statuscode = 1
group by DateValue,CS_A.stex_consultant--, CS_A.stex_startdate, CS_A.stex_enddate
--order by DateValue, CS_A.stex_consultant, CS_A.stex_startdate, CS_A.stex_enddate
OPTION (MAXRECURSION 0);
with mycte as
(select cast(@tijdvakstart as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < @tijdvakeind)
select
DATEPART(YEAR, DATEVALUE) AS Jaar,
SUM((( CASE WHEN ( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR ( CS.stex_enddate IS NULL AND CS.stex_startdate <= DateValue) ) THEN 1 ELSE 0 END )))
AS InDienst,
SUM(((CASE WHEN nb.dag is null
AND
-- in Employed?
( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR ( CS.stex_enddate IS NULL AND CS.stex_startdate <= DateValue) )
THEN 1 ELSE 0 END) )) AS Available,
DATEPART(WEEK, DateValue ) AS Weeknummer, 0
from mycte
LEFT OUTER JOIN Filteredstex_consultantcontract CS
ON ( DATEVALUE BETWEEN CS.stex_startdate AND DATEADD(D,-1,CS.stex_enddate) OR CS.stex_enddate IS NULL)
LEFT OUTER JOIN @inassignement nb ON nb.consultant = CS.stex_consultant AND dag=DateValue
WHERE CS.stex_consultant IN ( select consultant from @bu_consultants )
GROUP BY DATEPART(YEAR, DATEVALUE) , DATEPART(WEEK, DateValue )
ORDER BY DATEPART(WEEK, DateValue ) ASC
OPTION (MAXRECURSION 0);
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply