Solved

SSRS Count Function

Posted on 2009-04-08
5
189 Views
Last Modified: 2012-08-13
In SQL Server Reporting Services, I have a column "Status" in my report. Somewhere in the footer of the report, I would like to Count how many "New" jobs are there.

In the Status column, the possible values are (New, Cancelled, Rescheduled).

Somewhere in the footer I want to see:
NEW: ##
RESCHEDULED: ##

Is there a way to do this with the help of "Expressions"?
0
Comment
Question by:pzozulka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Author Comment

by:pzozulka
ID: 24102162
Just in case, I wanted to insert the SQL code used for this report. Also, here are all the options for the Status column.

CASE
WHEN Jobs.Status = 800 THEN 'Request New'
WHEN Jobs.Status = 801 AND JSL1.R IS NULL THEN 'New'
WHEN Jobs.Status = 803 THEN 'Confirmed'
WHEN Jobs.Status = 806 THEN 'Turned In'
WHEN Jobs.Status = 812 THEN 'Cancelled'
WHEN Jobs.Status = 807 THEN 'Billed'
WHEN JSL1.R IS NOT NULL THEN 'Rescheduled'
END AS 'Status'
IF DatePart(Weekday,Getdate()) = 2
BEGIN
SELECT Jobs.JobNo,
Convert(Varchar,Jobs.JobDate,101) as JobDate,
Convert(Varchar,Jobs.Entered,101)  AS 'Entered',
CASE
WHEN Jobs.Status = 800 THEN 'Request New'
WHEN Jobs.Status = 801 AND JSL1.R IS NULL THEN 'New'
WHEN Jobs.Status = 803 THEN 'Confirmed'
WHEN Jobs.Status = 806 THEN 'Turned In'
WHEN Jobs.Status = 812 THEN 'Cancelled'
WHEN Jobs.Status = 807 THEN 'Billed'
WHEN JSL1.R IS NOT NULL THEN 'Rescheduled'
END AS 'Status',
Jobs.OrderedBy AS 'Ordered By',
"Contacts with Firm".Contact_DisplayName AS 'Ordering Contact',
"Contacts with Firm".Contact_MainPhone AS 'Contact Phone',
"Contacts with Firm".Contact_Email AS 'Contact Email',
"Contacts with Firm".Firm_FirmName AS 'Firm',
"Contacts with Firm".Firm_City AS 'Firm City',
CASE WHEN Cases.CaseName IS NULL THEN 'Case Name NOT in RB'
ELSE Cases.CaseName END AS 'Case Name',
CASE WHEN R.DisplayName IS NULL THEN '.Bluebird - (New Client)'
ELSE R.DisplayName END AS 'SalesRep',
"Contacts with Firm".Firm_Source AS 'Firm Source',
Case 
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_FirmName
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then JobsLoc.LocName
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_FirmName
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_FirmName)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select Locations.LocName
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY Locations.LocName)
END AS Location,
Case
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_Address
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then JobsLoc.LocAddr
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_Address
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_Address)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select Locations.Address
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY Locations.Address)
END AS Address,
Case
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_City
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then JobsLoc.LocCity
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_City
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_City)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select Locations.City
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY Locations.City)
END AS City,
Case
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_State
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then (Select State.StateName
        From RB8.dbo.JobsLoc
        JOIN RB8.dbo.State ON State.StateNo = JobsLoc.LocStateNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
        GROUP BY State.StateName)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_State
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_State)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select State.StateName
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        JOIN RB8.dbo.State ON State.StateNo = Locations.StateNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY State.StateName)
END AS State,
U.DisplayName AS 'EnteredBy'
FROM 
(RB8.dbo.Jobs Jobs
JOIN RB8.dbo."Contacts with Firm" "Contacts with Firm" ON
Jobs.OrdContactNo = "Contacts with Firm".Contact_ContactNo
LEFT OUTER JOIN RB8.dbo.Cases Cases ON Jobs.CaseNo = Cases.CaseNo
JOIN RB8.dbo.JobsLoc JobsLoc ON Jobs.JobNo = JobsLoc.JobNo
LEFT OUTER JOIN RB8.dbo.Resources R ON Jobs.SalesRep = R.RsrcNo
LEFT OUTER JOIN RB8.dbo.Users U ON U.UserNo = Jobs.EnteredBy)
LEFT OUTER JOIN (
SELECT DISTINCT JSL.JobNo, 'Resch' as 'R'
FROM JobsStatusLog JSL
WHERE JSL.Remarks LIKE 'Rescheduled%') JSL1 ON JSL1.JobNo = Jobs.JobNo
WHERE 
Jobs.Entered>=Convert(Varchar,Getdate()-3,101)
And Jobs.Entered<=Convert(Varchar,Getdate(),101)
ORDER BY R.DisplayName
END
 
 
 
									ELSE
 
 
 
BEGIN
SELECT Jobs.JobNo, 
Convert(Varchar,Jobs.JobDate,101) as JobDate,
Convert(Varchar,Jobs.Entered,101)  AS 'Entered',
CASE
WHEN Jobs.Status = 800 THEN 'Request New'
WHEN Jobs.Status = 801 AND JSL1.R IS NULL THEN 'New'
WHEN Jobs.Status = 803 THEN 'Confirmed'
WHEN Jobs.Status = 806 THEN 'Turned In'
WHEN Jobs.Status = 812 THEN 'Cancelled'
WHEN Jobs.Status = 807 THEN 'Billed'
WHEN JSL1.R IS NOT NULL THEN 'Rescheduled'
END AS 'Status',
Jobs.OrderedBy AS 'Ordered By',
"Contacts with Firm".Contact_DisplayName AS 'Ordering Contact',
"Contacts with Firm".Contact_MainPhone AS 'Contact Phone',
"Contacts with Firm".Contact_Email AS 'Contact Email',
"Contacts with Firm".Firm_FirmName AS 'Firm',
"Contacts with Firm".Firm_City AS 'Firm City',
CASE WHEN Cases.CaseName IS NULL THEN 'Case Name NOT in RB'
ELSE Cases.CaseName END AS 'Case Name',
CASE WHEN R.DisplayName IS NULL THEN '.Bluebird - (New Client)'
ELSE R.DisplayName END AS 'SalesRep',
"Contacts with Firm".Firm_Source AS 'Firm Source',
Case 
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_FirmName
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then JobsLoc.LocName
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_FirmName
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_FirmName)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select Locations.LocName
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY Locations.LocName)
END AS Location,
Case
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_Address
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then JobsLoc.LocAddr
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_Address
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_Address)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select Locations.Address
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY Locations.Address)
END AS Address,
Case
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_City
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then JobsLoc.LocCity
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_City
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_City)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select Locations.City
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY Locations.City)
END AS City,
Case
 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_State
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
 Then (Select State.StateName
        From RB8.dbo.JobsLoc
        JOIN RB8.dbo.State ON State.StateNo = JobsLoc.LocStateNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0
        GROUP BY State.StateName)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 
 Then (Select "Contacts with Firm".Firm_State
        From RB8.dbo."Contacts with Firm" "Contacts with Firm",
        RB8.dbo.JobsLoc
        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1
        GROUP BY Firm_State)
 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
 Then (Select State.StateName
        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc
		ON JobsLoc.LocNo = Locations.LocNo
        JOIN RB8.dbo.State ON State.StateNo = Locations.StateNo
        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2
        GROUP BY State.StateName)
END AS State,
U.DisplayName AS 'EnteredBy'
FROM 
(RB8.dbo.Jobs Jobs
JOIN RB8.dbo."Contacts with Firm" "Contacts with Firm" ON
Jobs.OrdContactNo = "Contacts with Firm".Contact_ContactNo
LEFT OUTER JOIN RB8.dbo.Cases Cases ON Jobs.CaseNo = Cases.CaseNo
JOIN RB8.dbo.JobsLoc JobsLoc ON Jobs.JobNo = JobsLoc.JobNo
LEFT OUTER JOIN RB8.dbo.Resources R ON Jobs.SalesRep = R.RsrcNo
LEFT OUTER JOIN RB8.dbo.Users U ON U.UserNo = Jobs.EnteredBy)
LEFT OUTER JOIN (
SELECT DISTINCT JSL.JobNo, 'Resch' as 'R'
FROM JobsStatusLog JSL
WHERE JSL.Remarks LIKE 'Rescheduled%') JSL1 ON JSL1.JobNo = Jobs.JobNo
WHERE
Jobs.Entered>=Convert(Varchar,Getdate()-1,101)
And Jobs.Entered<=Convert(Varchar,Getdate(),101)
ORDER BY R.DisplayName
END

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24102308
try
=Sum(IIF(Fields!Status.Value = "New", 1, 0))
 etc.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 24102362
That worked, but how do I put into that box along with that expression the literal word "New" in front of the expression?
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24102436
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24102464
this should work
don't know what happend that last post
="New: " & Sum(IIF(Fields!Status.Value = "New", 1, 0))

Open in new window

0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

636 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