need expression help/guidance

Do all expressions have to be on one line?
Can you declare variables in them like in Crystal Reports Formulas?
Can you use other formulas as values? As in you set up a textbox that
will give you a value with an expression, then in another expression
you reference the value of that text box?
Is there a decent guide out there for expressions? Why
can'texpressions just behave similar to a function?
The answers to this post will certainly help determine whether or not
we stick with Crystal or go with this.You can use GlobalVariables in RS :
In Report Properties, go in Custom Code and Declare typed Variables
Exemple : Public Test As String = "100"
You can reference this variables in any expression in your report :
=Code.Test
In any expression formula you can call Custom Code variables or functions.
As well as reference other Report Items:
=ReportItems!textbox4.Value (to use a textbox named textbox4 value).
Let me know if this helps.
Regards,
Davy Ramirez
Winsight - Paris.
"RickB" <rbinns@.nationaldiabetic.com> wrote in message
news:7f3528da.0410150525.60ae7975@.posting.google.com...
> Do all expressions have to be on one line?
> Can you declare variables in them like in Crystal Reports Formulas?
> Can you use other formulas as values? As in you set up a textbox that
> will give you a value with an expression, then in another expression
> you reference the value of that text box?
> Is there a decent guide out there for expressions? Why
> can'texpressions just behave similar to a function?
> The answers to this post will certainly help determine whether or not
> we stick with Crystal or go with this.|||You may want to read this MSDN article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RickB" <rbinns@.nationaldiabetic.com> wrote in message
news:7f3528da.0410150525.60ae7975@.posting.google.com...
> Do all expressions have to be on one line?
> Can you declare variables in them like in Crystal Reports Formulas?
> Can you use other formulas as values? As in you set up a textbox that
> will give you a value with an expression, then in another expression
> you reference the value of that text box?
> Is there a decent guide out there for expressions? Why
> can'texpressions just behave similar to a function?
> The answers to this post will certainly help determine whether or not
> we stick with Crystal or go with this.

Need Expression Help with DateTime Variable

Hello,

I have a DateTime variable called CurrentDate that needs to reflect the current date, but the date portion only. I checked several functions in the Expression Builder to use with GETDATE() so that I could just get the date portion, but I didn't see anything that really fit. In a SQL query I would normally use CONVERT to do this.

Any ideas?

Thank you for your help!

cdun2

Why do you care if it has a time portion on it?

How are you populating the variable? It'll have to be a string type in order to eliminate the time component.|||

Sorry for the confusion. Actually, the date does need to be cast to a string. I need to append the date to another package variable to complete the 'name' of a flat file. The idea is that when the package runs, the flat file destination file name will contain the current date.

I know how to set up an exression for the 'Name' of the flat file, but what would be the best way to take the result of GETDATE(), cast it to a string, and parse out just the date portion? Since I don't want the non alpha numeric characters in the string, maybe I should just put a date string together using YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE()).

If I go this route, how do I used type casting with these date functions?

Thanks again.

|||

Here is my first idea. This would be the expression for the Name property of the flat file Connection Manager;

@.[User::FilePath] + @.[User::FilePrefix] + (DT_STR, 4, 1252) YEAR(GETDATE()) + (DT_STR,2, 1252)MONTH(GETDATE()) + (DT_STR, 2,1252)DAY(GETDATE())

|||

cdun2 wrote:

Here is my first idea. This would be the expression for the Name property of the flat file Connection Manager;

@.[User::FilePath] + @.[User::FilePrefix] + (DT_STR, 4, 1252) YEAR(GETDATE()) + (DT_STR,2, 1252)MONTH(GETDATE()) + (DT_STR, 2,1252)DAY(GETDATE())

I like this idea.

Need expression help ASAP

I have a report in ssrs where it has a colum with dates in it I has and other colum with either a $0.00 amount or and amount paid. I need a colum which which calculates only the number of days from the date colum that when the other colum =0 it gives me the total number of days. VS.net SQL 2005 SSRS C#

example of what needed:

Invoice amount | Date of Invoice| Amount received | number days past due

$100.00 06/01/2007 0.00 18 days ( this is what I need)

can some one explain.

Here are my expressions

FormatCurrency(Fields!Grossamtbilled.value)

FormatDateTime(Fields!EXPR1.Value,2)

FormatCurrency(Fields!RCVAMOUNT.Value)

I'm not sure what expression you would use when creating a report, but it would be fairly easy to do it in the original SQL Statement (by using a mixture of the CASE and DateDiff functions).

sql

Need expression help

Can someone one tell me the correct expression to take a figure like 24.09876 and make it show 24% I have tried using the following expression

=FormatPercent(Avg(Fieilds!avgPercentDiscL)) and I keep getting 2409.876% I need it to show 24% please help asap. Thanks

How about this:

= Convert.tostring(convert.toint(Fieilds!avgPercentDiscL)) & "%"

|||

Hello,

It does not reconize the toint I am using C# vs.net 2005 sql report

|||

Yes its all VB/VB.NET syntax irrespective of what you are using..

try toint16 or toinr32..

|||

try this

= Round(Avg(Fieilds!avgPercentDiscL)),0).ToString() + "%"

Bye

Vaibhav

|||

=FormatPercent(Fieilds!avgPercentDiscL.Value,0)

The second parameter specifies the number of digits after the decimal, in this case none.

Need Date in Days Expression

I have an expression in a ssrs report that is a date can some one tell me how to formati the date to show days

example my expression =FormatDate Time(Fields!BillDate.Value,2)

This shows 02/02/2007 as the date

What i need it to say 33 days how can I format my exprsion in my ssrs report to reflect this..

HI,cpowers:

What is the meaning of 'What i need it to say 33 days how can I format my exprsion in my ssrs report to reflect this..'

Would you please clarify more about your requirement?

Thanks in advance!

sql

Need current date in ssrs expression

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

Need an expression format to put decimals .00

I have the following field(Fields!SequenceNO.Value), coming form database.

i would like to have an expression IIF...

if the value coming from database is just 1, then make it 1.00 or

if the value coming from database has a period or point say 1.01 then show as it is.

thank you very much for the help / information....

Hello,

You can do this a couple different ways...

1. Select your textbox that will hold Fields!SequenceNO.Value, then change the Format property to #.00

or

2. Replace Fields!SequenceNO.Value with the expression: =Format(Fields!SequenceNO.Value, "#.00")

Both of these will make all of your numbers in the column have 2 decimals.

Hope this helps.

Jarret