Solved

SSRS Count Function

Posted on 2009-04-08
5
185 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 26

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 26

Expert Comment

by:Chris Luttrell
ID: 24102436
0
 
LVL 26

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
Viewers will learn how the fundamental information of how to create a table.

752 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