Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

SSRS Count Function

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
pzozulka
Asked:
pzozulka
  • 3
  • 2
1 Solution
 
pzozulkaAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
try
=Sum(IIF(Fields!Status.Value = "New", 1, 0))
 etc.
0
 
pzozulkaAuthor Commented:
That worked, but how do I put into that box along with that expression the literal word "New" in front of the expression?
0
 
Chris LuttrellSenior Database ArchitectCommented:
0
 
Chris LuttrellSenior Database ArchitectCommented:
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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now