How to use the same logic from a formula in Crystal Reports in SSRS

[tkaz]

Using Visual Studio 2010 and SQL Server 2008 Database.

I'm very new to SSRS and in the process of moving years worth of Crystal Reports to Reporting Services. I have formulas that create fields in many of my Crystal Reports that use begin and end date parameters to provide Daily, Weekly, MTD, and YTD columns on my CR. Works great, but I can't make that happen in SSRS.

Here is my WTD Field in CR:::::

if {TICKET.TKTDATE} >= {?WTD Start Date} and {TICKET.TKTDATE} <= {?Report Date}
then {TICKET.PRODQTY}

I tried using the same logic in SSRS ....here's the expression =IIf(Fields!TKTDATE) BETWEEN (Parameters!WTDSTARTDATE.Value) and (Parameters!REPORTDATE.Value),Fields!PRODQTY)....but keep getting an error. I assumed that I would have to add an expression field for WTDQTY??? Am I doing this all wrong? The daily column works fine but my other columns are totally blown up!

Thanks for any help or direction you can provide. I'm sure this is basic for most people but I'm struggling!

 

[IanWaterman]

I think you need a second argument in that IIF = if..Then.. else

Your formula does not have an else

Try

=IIf(Fields!TKTDATE) BETWEEN (Parameters!WTDSTARTDATE.Value) and (Parameters!REPORTDATE.Value),Fields!PRODQTY, 0)

I do not envy your task, I have converted a few Crystal Reports to RS and it seems to take ages!!

Ian

 

[tkaz]

I tried that and I'm still getting an error. I'm going to keep working with it, but it's totally frustrating! I never really worked much in Access or VB so I'm starting over. It seems to me that it should be easier than what I've done with Oracle and Crystal Reports, but it hasn't been so far. The only nice thing about it is being able to schedule and distribute than the CR's. Thanks for trying to help me an encourage me along I don't envy me either!!

 

[IanWaterman]

I have not used BETWEEN in RS

Try

=IIF(Fields!TKTDATE) >=(Parameters!WTDSTARTDATE.Value) and Fields!TKTDATE) <=((Parameters!REPORTDATE.Value),Fields!PRODQTY, 0)

Ian

 

[tkaz]

This is the error message that I'm getting.

Error 1 [rsCompilerErrorInExpression] The Value expression for the field ‘=IIF(Fields!TKTDATE) >=(Parameters!WTDSTARTDATE.Value) and Fields!TKTDATE) <=((Parameters!REPORTDATE.Value),Fields!PRODQTY, 0)’ contains an error: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments. 0

This is a SIMPLE Crystal Report. I can't imagine what I'm going to face with my more complicated ones. I've spent three days trying to figure this out...I'm feeling pretty dense about now Thanks for trying to help me!

 

[IanWaterman]

Not sure if this is relevant but not all your fields have the .value extension, and the Parentheses () were not matched

try

=IIF((Fields!TKTDATE.Value >=Parameters!WTDSTARTDATE.Value and Fields!TKTDATE.Value <= Parameters!REPORTDATE.Value),Fields!PRODQTY.Value, 0)

Whilst I love Tek-tips I have found that help for Reporting Services is better here

http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/threads?prof=required

Ian

 

[IanWaterman]

Also this blog is quite good too

http://blogs.msdn.com/b/robertbruck...at-header-and-visible-fixed-header-table.aspx

Ian

 

[tkaz]

Tah-Dah!!! This finally worked!!! I like CR MUCH, MUCH better! Of course, after almost 20 years of working with Crystal...it should be easier Again, thanks for all of your help and support. I liked the links you sent me and that is where I actually found this idea.

=IIf(Parameters!WTDSTARTDATE.Value<= Fields!TKTDATE.Value,Fields!PRODQTY.Value,
IIf(Parameters!REPORTDATE.Value >=Fields!TKTDATE.Value,Fields!PRODQTY.Value,0)
)

 

[DaveInIowa]

Your condition is equivalent to 'If startDate <= ticketDate [red]OR[/red] reportDate >= ticketDate then prodQty else 0. I think what you really want is

=IIf(Parameters!WTDSTARTDATE.Value <= Fields!TKTDATE.Value [blue]AndAlso[/blue] Fields!TKTDATE.Value <= Parameters!REPORTDATE.Value, Fields!PRODQTY.Value, 0)

 

[tkaz]

Thanks, Dave. I was just thrilled that it didn't error out on me! I simply want to provide calculated fields on my report for Daily, WTD, MTD and YTD amounts. This is a breeze for me in Crystal Reports. I simply write a formula using the date parameter values and ask to return the PRODQTY if the dates fall within that criteria.

I didn't realize that it was putting an "OR" in the IIf statement. I thought it was working fine because it was returning the right data. I appreciate you pointing that out. I changed out the code and yours works fine...and makes more sense. I really want BETWEEN but that is obviously not an option.

Changing up that same code for MTD does NOT work.

=IIf(Parameters!MTDSTARTDATE.Value <= Fields!TKTDATE.Value AndAlso Fields!TKTDATE.Value <= Parameters!REPORTDATE.Value, Fields!PRODQTY.Value,0)

My SQL works fine and returns exactly what I need. It's these expressions that I am just not familiar enough with to get them to work.

Thanks for your reply.

 

[xlbo]

don;t think you need andalso

=IIf(Fields!TKTDATE.Value >= Parameters!MTDSTARTDATE.Value And Fields!TKTDATE.Value <= Parameters!REPORTDATE.Value, Fields!PRODQTY.Value,0)

however if these are aggregates then you would need:

=SUM(IIf(Fields!TKTDATE.Value >= Parameters!MTDSTARTDATE.Value And Fields!TKTDATE.Value <= Parameters!REPORTDATE.Value, Fields!PRODQTY.Value,0))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question