I have a report which has a date like 01/05/2007 for a bill date. I need to create and expression which will take the current date and subtract the number of days from todays date and give a total number of days. How do I get thew current date in the expression.
the billed date expression is =FormatDateTime(Today()) - FormatDateTime(Fields!BILLDATE.value,2) I can not get this to work
Any help would be appreciated
You can execute code within a Report. You may need to do this although I think the reporting expressions should support what you are trying to accomplish. This is a good resource for you anyway though:http://support.microsoft.com/kb/920769
Hope this helps!
Matt
|||Thanks for the link but my expression isnt working in the report can you tell me what is wrong with it.
|||HI,cpowers:
You can try this expression:
= DateDiff("d",Fields!ShippedDate.Value ,Today() )
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
|||Rex Lin,
That works great thanks.. What I am tring to accomplish is a 1-30 31-60 61-90 days aging
I tried to di the following expression-
=DateDiff("d",Fields!ShippedDate.Value,Today()) < 30 for the 1-30 day
=DateDiff('d".Fields!ShippedDate.Value, Today()) >30<61 for the 31-60 day I am getting a true or false instead of the days is there away to modify the expression to give only the number of days that fall within the range
|||
HI,cpowers:
Hope this will meet your requirement:
=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range")
|||I am tring to get a total for the amount owed that is over 30days but under 60days. My expression just gives me the word True does not give me the total MASRCVAMOUNT $$ in that date range. Can come one explain or help with expression...=FormatCurrency(Sum(Fields!MASRCVAMOUNT.Value))=DateDiff("d",Fields!Expr1.Value ,Today())>31<60
I also need to no how to get total MASRCVAMOUNT over 120 days
=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range") How would I sum the MASCVAMOUNT in this date range and how can I select a between 61-91days these are my two real issues. I have the dates working.
Thanks for all the help I do appreciate it
|||HI，cpowers：
It seems that the goal you need to achieve is not easily for expression. How about deal with it in the sql statement?
|||Thanks for the help Rex Lin,
Ok I added the daging date to my sql script. Every thing saves ok and in my parameters there is now an new entry filter. Before I only had a StartDate, Enddate and Company. Now I have the Startdate , Enddate Company and dAging. I have tried to enter a date in to the dAging but it keeps giving me and error.This script was added in the creation of the ssrs report in the ssrs query builder. Can you explain to me what I may be missing or need to do to get my 1-30 , 31 60, 61-90 and 91 and over amounts.... The field I am collecting the amounts from is MASRECEIVL.MASRCVAMOUNT I am really stuck with this and need any help availible. Thanks
SELECT ' Language Detail by Company' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,PAYER.PAY_GROUPNAME,PAYER.PAY_COMPANY,PAYER.PAY_CITY,PAYER.PAY_STATE,PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,JOB.PATIENTID,JOB.JOBOUTCOMEID,JOB.SERVICEOUTCOME,INVOICE_AR.INVOICE_NO,INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,INVOICE_AR.CLAIMNUMBER,PATIENT.LASTNAME,PATIENT.FIRSTNAME,PATIENT.EMPLOYERNAME,JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,PAT_SERVICES_HISTORY.LANG_TYPE,MASRECEIVL.MASRVDATE,MASRECEIVL.MASRCVAMOUNT,REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,REFERRAL_SOURCE.REF_PHONE,REFERRAL_SOURCE.REF_PHONE_EXT,REFERRAL_SOURCE.REF_FAX,REFERRAL_SOURCE.REF_EMAIL,JOB.INJURYDATE,JOB.APPT_DATE,
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') THEN 1 ELSE 0 END) AS 'CompletedWithcomplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') THEN 1 ELSE 0 END) AS 'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') THEN 1 ELSE 0 END) AS 'CompletedwithNoChargeItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') THEN 1 ELSE 0 END) AS 'CompletedwithNoShowItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') THEN 1 ELSE 0 END) AS 'CompletedWithSituationItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Not Completed') THEN 1 ELSE 0 END) AS 'NotCompletedItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') THEN 1 ELSE 0 END) AS 'CancelledPriortoserviceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') THEN 1 ELSE 0 END) AS 'CancelledDuringServiceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Awaiting for completion') THEN 1 ELSE 0 END) AS 'AwaitingforcpmpletionItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Pending for review') THEN 1 ELSE 0 END) AS 'PendingforreviewItems',
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-30 and @.dAgingDate THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age30,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-60 and @.dAgingDate-31 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age60,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-90 and @.dAgingDate-61 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age90,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-120 and @.dAgingDate-91 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age120
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN MASRECEIVL ON MASRECEIVL.INVOICE_NO = INVOICE_AR.INVOICE_NO
LEFT OUTER JOIN REFERRAL_SOURCE ON REFERRAL_SOURCE.REFERRAL_ID = JOB.ADJUSTERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE
(INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_Company like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%L') AND (MASRECEIVL.MASRCVAMOUNT > 0)
Group By
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.LANG_TYPE,
MASRECEIVL.MASRVDATE,
MASRECEIVL.MASRCVAMOUNT,
REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,
REFERRAL_SOURCE.REF_PHONE,
REFERRAL_SOURCE.REF_PHONE_EXT,
REFERRAL_SOURCE.REF_FAX,
REFERRAL_SOURCE.REF_EMAIL,
JOB.INJURYDATE,
JOB.APPT_DATE
Order By 'QTR' asc
|||HI,cpowers:
Your sql statement is quite complicated than i expected. I have created a simple one by using Orders table in the Northwind. It brings out the sum value of 30-60 and 60-90 date intervals
select
sum(case when timespan between 0 and 29 then shipvia end )as zeroToThirty,
sum(case when timespan between 30 and 59 then shipvia end )as ThirtyToSixty
from
(select (datediff(dd,getdate(),orderdate)+4000)as timespan,*
from dbo.orders) as newtable
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
|||Rex Lin,
I am having similar problems, except all I want is to substract current date from the promised date (date that is was suppose to ship) and be able to view only the negetive data. I tried =GETDATE()-Fields!PROMISE.Value
but it would give me an error, then I tried the format you gave cpowers
=DateDiff("d",fields!Promise.Value, Today()) but all it did was give me one number. Am I doing it wrong? What do you recomend? Please advice
Thanks
