Solved

SSRS Count Function

Posted on 2009-04-08
5
182 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Re-appearing SQL Server Agent jobs 7 30
In sql, how to roll up multiple rows to only one row. 4 39
xml files 7 29
MS SQL Conditional WHERE clause 3 19
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

820 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