Solved

SSRS Count Function

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now