Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS Count Function

Posted on 2009-04-08
5
Medium Priority
?
191 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 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