Avatar of Steve Jebson
Steve JebsonFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Matrix columns (week numbers) and rows with no data but want to display ?

Hi,

I'm trying to build a Matrix that shows week numbers across the top with a list of Sites that contain Lufts on the left. I want to show how many Callouts/Jobs have been created in each week. I've got that working but if there were no Callouts/Jobs in a week I'd still like to show that week column, similarly if there were no Callouts/Jobs for a particular Site/Lift i would still like to show that blank row.

I think i need to somehow select all Weeknumbers in a date range and Join that in some way to my main query and also include Sites/Lifts with no data in my main query ??

I've been trying all sorts of joins/sub queries but can't get it, can anybody help me please ?

here's the sql for my main query...


 
SELECT 
	s.id sId,
    l.id lId,
	count(c.ID) cCount,
    DATEPART (week,c.datetimereported) cReportedWeek,
	CASE
	 WHEN l.liftshortname is Null THEN ' ** Site Level ** '
	 ELSE l.liftshortname
	END lLift

FROM   site s
       LEFT JOIN job j
         ON s.id = j.siteid
       INNER JOIN callout c
         ON j.id = c.jobid
       INNER JOIN jobline jl
         ON j.id = jl.jobid
       LEFT JOIN lift l
         ON jl.liftid = l.id
WHERE  ( c.datetimereported BETWEEN '01/01/2010' AND '12/12/2010' )
       AND ( s.id IN (SELECT id
                         FROM   site s
                         WHERE  ( contractid = 3005 )) )

group by DATEPART (week,c.datetimereported), s.id, l.id, l.liftshortname

Open in new window

SSRS

Avatar of undefined
Last Comment
Steve Jebson
Avatar of jasonduan
jasonduan
Flag of United States of America image

use LEFT JOIN instead:

...
LEFT JOIN callout c
         ON j.id = c.jobid
LEFT JOIN jobline jl
...
Avatar of Steve Jebson
Steve Jebson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

doesn't work, Lifts belong to Sites and a Callout/Job/Jobline is either at Site or Lift level. I want to count the Callout/Job/Jobline's that have been created against a Site/Lift but it's not returning me Site/Lifts that have a count of 0
Avatar of G Godwin
G Godwin
Flag of United States of America image

Create a permanent or a temp table with the week numbers (1, 2, 3, ... 52).

Outer join this table in your query so that all the weeks are represented (right outer or left outer depending on placement in query).

Say you call the table 'WeekNumbers', and the week number column 'WeekNumber'; add WeekNumbers.WeekNumber to the select statement.  You will need this because if there are no calls (c.datetimereported) for the week the cReportedWeek will be null.

Let me know if you have questions from there.
-G
Avatar of Steve Jebson
Steve Jebson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi GDG,

Thanks for the pointer on the weeks, I've managed to get what i'm looking for in 2 seperate queries, just not sure how to join them !

here's the 1st query that gets me  the weeks for each Lift i require... (Obviously just limiting the weeks to 4 at the moment)

which returns rows of (SiteID, LiftID, Week) i.e.
2994, 4409, 30
2994, 4409, 31
2994, 4409, 32
2994, 4409, 33
2994, 4418, 30
2994, 4418, 31
2994, 4418, 32
2994, 4418, 33
2994, 4419, 30
2994, 4419, 31
2994, 4419, 32
2994, 4419, 33
 
with  Weeks (week) AS 
(
 SELECT 30 AS week    UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 
)
select  s.id, s.addressline1, l.id, l.liftshortname, week 
from site s  
inner join lift l on l.siteid= s.id 
cross join weeks 


WHERE s.id=2994

Open in new window


and here's the query that gets me the count of Callouts for each Lift by Week

which returns rows of (SiteID, LiftID, Week, Count) i.e.
2994,NULL,33,1
2994,4409,30,1
2994,4409,32,1
 
SELECT 
	   s.id sId,
       s.addressline1 sAddress, l.id as lID,
       CASE
         WHEN l.liftshortname is Null THEN 'At Site Level'
         ELSE l.liftshortname
       END lLift,
 DATEPART (week,c.datetimereported) cReportedWeek, count (c.id) as cCount

FROM   site s
       right OUTER JOIN job j
         ON s.id = j.siteid
       LEFT OUTER JOIN callout c
         ON j.id = c.jobid
       INNER JOIN jobline jl
         ON j.id = jl.jobid
       LEFT OUTER JOIN lift l
         ON jl.liftid = l.id
WHERE  ( c.datetimereported BETWEEN '01/01/2010' AND '12/12/2010' )
       AND (j.LookupJobStatusID <> 7)
       AND ( s.id IN (SELECT id
                         FROM   site s
                         WHERE  ( s.id = 2994 )) )

group by DATEPART (week,c.datetimereported), s.id, s.addressline1, l.id, l.liftshortname

Open in new window


there's a gotcha in that a Callout might not be linked to the Lift, so I suppose i need it to join on the SiteID ??

Sorry to be a pain, i'm going round and round trying to get them together. so i can end up with  rows like  (SiteID, LiftID, Week, Count) i.e.
2994,NULL,33,1
2994, 4409, 30,1
2994, 4409, 31,0
2994, 4409, 32,1
2994, 4409, 33,0
2994, 4418, 30,0
2994, 4418, 31,0
2994, 4418, 32,0
2994, 4418, 33,0
2994, 4419, 30,0
2994, 4419, 31,0
2994, 4419, 32,0
2994, 4419, 33,0
Avatar of G Godwin
G Godwin
Flag of United States of America image

Try this instead.

I created a temp table, populated it, and then used it in your original query.

Let me know if you have questions.

-G
declare @weeks table (week int)
insert into @weeks (week) values 
(1), (2) , (3) , (4) , (5) , (6) , (7) , (8) , (9) 
, (10) , (11) , (12) , (13) , (14) , (15) , (16) , (17) , (18) , (19) 
, (20) , (21) , (22) , (23), (24) , (25) , (26) , (27) , (28) , (29) 
, (30) , (31) , (32) , (33), (34) , (35) , (36) , (37) , (38) , (39) 
, (40) , (41) , (42) , (43), (44) , (45) , (46) , (47) , (48) , (49) 
, (50) , (51) , (52) 

-- select * from @weeks


SELECT 
	s.id sId,
    l.id lId,
	count(c.ID) cCount,
--    DATEPART (week,c.datetimereported) cReportedWeek,
	weeks.week 'cReportedWeek',
	CASE
	 WHEN l.liftshortname is Null THEN ' ** Site Level ** '
	 ELSE l.liftshortname
	END lLift

FROM   site s
       LEFT JOIN job j
         ON s.id = j.siteid
       INNER JOIN callout c
         ON j.id = c.jobid
       INNER JOIN jobline jl
         ON j.id = jl.jobid
       LEFT JOIN lift l
         ON jl.liftid = l.id
       right outer join @weeks weeks on weeks.week = DATEPART(week,c.datetimereported)
WHERE  ( c.datetimereported BETWEEN '01/01/2010' AND '12/12/2010' )
       AND ( s.id IN (SELECT id
                         FROM   site s
                         WHERE  ( contractid = 3005 )) )

group by --DATEPART (week,c.datetimereported)
weeks.week, s.id, l.id, l.liftshortname

Open in new window

Avatar of Steve Jebson
Steve Jebson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

didn't realise you could use temp tables in srss, however it doesn;t seem to like this, i've tried all combinations for the values but the only one that works is when i put a single values (1) so i can;t seem to get more valeus in ?? I'm using 2005 if that makes any difference ??

declare @weeks table (week int)
insert into @weeks (week) values (1),(2)

select * from @weeks
Avatar of Steve Jebson
Steve Jebson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

oh, complains about invlaid syntax near ','
Avatar of Steve Jebson
Steve Jebson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

looks like i need to code it so...
declare @weeks table (weeknum int)
insert into @weeks (weeknum)  values (1)
insert into @weeks (weeknum)  values (2)
insert into @weeks (weeknum)  values (3)
insert into @weeks (weeknum)  values (4)
select * from @weeks

Open in new window

Avatar of Steve Jebson
Steve Jebson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

nope, still only returning a small set of data, is it because of the COUNT ? i.e. it appears i'm only getting rows back where the count > 0
Avatar of G Godwin
G Godwin
Flag of United States of America image

The where clause is eliminating weeks that do not have results.

Add
"or c.datetimereported is null"
to your where clause.  

-G
Avatar of G Godwin
G Godwin
Flag of United States of America image

To be specific, replace your where clause with this one...
WHERE  (( c.datetimereported BETWEEN '01/01/2010' AND '12/12/2010' )
		or c.datetimereported is null)
       AND (j.LookupJobStatusID <> 7)
       AND ( s.id IN (SELECT id
                         FROM   site s
                         WHERE  ( s.id = 2994 )) )

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of G Godwin
G Godwin
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of G Godwin
G Godwin
Flag of United States of America image

How are you doing with this?
-G
Avatar of Steve Jebson
Steve Jebson
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks GDG, took some tweaking, ended up with a couple of temp tables and took me most of yesterday cleaning it up and reformatting. Looking good now though. Thanks again for your help, definately learnt a few things here.

Steve
SSRS
SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

10K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo