Field Expressions In SSRS 2005 (Reporting Services)

I am preparing a report with trend analysis, I need to divide the total operating days/ days in operation for the month, in Excel 2005 I used = ie NETWORKDAYS(AB2,AB3)  What field expression would I use in SSRS 2005?
LVL 1
SPLadyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SPLadyAuthor Commented:
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))
Bhavesh ShahLead AnalystCommented:

Hi,

select datediff(day,cast('3/5/2008' as datetime),cast('3/10/2008' as datetime))*.69

it may works.

Source - http://www.forumtopics.com/busobj/viewtopic.php?p=473423
SPLadyAuthor Commented:
Thanks for your response this is in Report Server ;  not SQL Server so the query format doesnt work and I dont know how to translate it.

I am looking for an expression that basically will compute the number of work days in the month each month without having to change the the dates in the expression.. sorta like...

DateDiff("d",Today(),dateadd("s",-1,dateadd("m", datediff("m","1/1/1900",now())+1,"1/1/1900")))* (Fields!OrderAmount.Value)

this gives me the last day of the month but, I need the total number of workdays in the month. Thank you!
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Bhavesh ShahLead AnalystCommented:
Hi,

can we play with your query??

I mean, I'm good in manipulating queries, so i can achieve whatever u say.

If yes, please post your query.
SPLadyAuthor Commented:
I havn't added the date calculation part I was handling it as an expression on the report level

this is what I have so far and I need to add for prior year and trend % (which is part of the the date calculations I was asking about)


SELECT     recType, site, lineValue as 'OrderAmount', 0 AS 'InvoiceAmount', product, transYear
FROM         vRegionalData
WHERE     (recType = 'order') 
UNION
SELECT     recType, site,  0 as 'OrderAmount', lineValue as 'InvoiceAmount', product, transYear
FROM         vRegionalData_Sum
WHERE     (recType = 'invoice')

Open in new window

Bhavesh ShahLead AnalystCommented:
Sorry Miss,

Just for clarification,

I need the total number of workdays in the month

- You need number of workdays in given month, excluding weekends?
Bhavesh ShahLead AnalystCommented:
Hello Miss,

If above one is right then below will workout for you.



Apart from Q, I notice that you are from Pacific Daylight Time Zone.So Time will be between 7 to 8.
So, take care of health also.=)

SELECT *
FROM
(    
SELECT     recType, site, lineValue as 'OrderAmount', 0 AS 'InvoiceAmount', product, transYear
FROM         vRegionalData
WHERE     (recType = 'order') 
UNION
SELECT     recType, site,  0 as 'OrderAmount', lineValue as 'InvoiceAmount', product, transYear
FROM         vRegionalData_Sum
WHERE     (recType = 'invoice')
) A, (	SELECT  20 + COUNT(*)as WorkDayCount
		FROM    (
				SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate UNION
				SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29) UNION
				SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)
			) AS d
		WHERE   DATEPART(DAY, theDate) > 28
			AND DATEDIFF(DAY, 0, theDate) % 7 < 5
	)B

Open in new window

SPLadyAuthor Commented:
You are so awesome! I have the number of days :0)  but, all of the invoice amounts are now '0'
Bhavesh ShahLead AnalystCommented:

I'm not sure but still try below query.
SELECT *
FROM
(    
SELECT     recType, site, lineValue as 'OrderAmount', Cast(0 AS NUMERIC(30,2)) AS 'InvoiceAmount', product, transYear
FROM         vRegionalData
WHERE     (recType = 'order') 
UNION
SELECT     recType, site,  0 as 'OrderAmount', lineValue as 'InvoiceAmount', product, transYear
FROM         vRegionalData_Sum
WHERE     (recType = 'invoice')
) A, (  SELECT  20 + COUNT(*)as WorkDayCount
                FROM    (
                                SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate UNION
                                SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29) UNION
                                SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)
                        ) AS d
                WHERE   DATEPART(DAY, theDate) > 28
                        AND DATEDIFF(DAY, 0, theDate) % 7 < 5
        )B

Open in new window

SPLadyAuthor Commented:
Thank you! All invoices are still '0' , is there another way to format the query ?
Bhavesh ShahLead AnalystCommented:
Mem,

My given query should be create any problem, just can you please try your old query.

does it gives value??
SPLadyAuthor Commented:
the old query works
Bhavesh ShahLead AnalystCommented:
Hi,

Sorry for late reply.
can you call procedure?

if its ok, if we give you procedure?
Bhavesh ShahLead AnalystCommented:
Try this one
SELECT     recType, site, lineValue as 'OrderAmount', Cast(0 AS NUMERIC(30,2)) AS 'InvoiceAmount', product, transYear, WorkDayCount
FROM         vRegionalData A,( SELECT  20 + COUNT(*)as WorkDayCount
								FROM    (
												SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate UNION
												SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29) UNION
												SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)
										) AS d
								WHERE   DATEPART(DAY, theDate) > 28
								AND DATEDIFF(DAY, 0, theDate) % 7 < 5
										)B
WHERE     (recType = 'order') 
UNION
SELECT     recType, site,  0 as 'OrderAmount', lineValue as 'InvoiceAmount', product, transYear, WorkDayCount
FROM         vRegionalData_Sum,( SELECT  20 + COUNT(*)as WorkDayCount
							  	 FROM    (
												SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate UNION
												SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29) UNION
												SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)
										) AS d
								 WHERE   DATEPART(DAY, theDate) > 28
								 AND DATEDIFF(DAY, 0, theDate) % 7 < 5
										)B
WHERE     (recType = 'invoice')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SPLadyAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.