SSRS 2005 - Expression Results
The table being used for my report contains 2 months worth of data. When viewing the report, the report shows results for both months even though I have the following expression. What am I doing wrong? =IIF(Fields!DateEntered.Value>=Parameters!CurrentMonth.Value,sum(Fields!PDAmt.Value),0)
September 23rd, 2010 8:19pm

Do you want to filter the results to show only Current Month data or try to show summary of field by Month? Your expression is summarizing the data. Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2010 8:56pm

Arbi Thank you for your assistance. I'm pretty new at SSRS so I will try to be as detailed as I possibly can. Here's my objective: Client ClientID Current Total Current AVG Last Month Total Last Month Avg % / Prior Month ABC 123 $25,000 $1,136.30 $23,000 $1,045.45 1.08% I can get the current months total and avg but only if I set the report filter to >=current month. But, then I can't get last months total and avg. If I remove the report filter and set the current month total and avg field to =IIF(Fields!DateEntered.Value>=Parameters!CurrentMonth.Value,sum(Fields!PDAmt.Value),0) then I get both months in the current month field. Hope this makes sense and thank you again for any help you give me.
September 23rd, 2010 9:11pm

BTW... This is a matrix, not table.
Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2010 9:17pm

In your Current Month's Expression do some thing like this: =IIF(Fields!DateEntered.Value=Parameters!CurrentMonth.Value,sum(Fields!PDAmt.Value),0) What is the PDAmt amount showing up? Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
September 23rd, 2010 9:18pm

I get $0 because my parameter is a calendar where I choose the first day of the month which is why I had the expression set to >=
Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2010 9:28pm

What about this: =IIF(DatePart(Year, Fields!DateEntered.Value)=DatePart(Year, Parameters!CurrentMonth.Value) And DatePart(Month, Fields!DateEntered.Value)=DatePart(Month, Parameters!CurrentMonth.Value),sum(Fields!PDAmt.Value),0)Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
September 23rd, 2010 9:31pm

Even if I don't use the parameter and just use the following, I still get the total for both August and Sept =IIF(Fields!DateEntered.Value>= "9/1/2010",sum(Fields!PDAmt.Value),0)
Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2010 9:31pm

Now I get the following error - Argument not specified for parameter 'DateValue' of 'Public Function Year(DateValue As Date) As Integer'.
September 23rd, 2010 9:44pm

What is the PDAmt amount showing up? Can you post in here your query?Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2010 10:38pm

Second reply: Here's my objective: Client ClientID Current Total Current AVG Last Month Total Last Month Avg % / Prior Month ABC 123 $25,000 $1,136.30 $23,000 $1,045.45 1.08% I can get the current months total and avg but only if I set the report filter to >=current month. But, then I can't get last months total and avg First reply: The table being used for my report contains 2 months worth of data. When viewing the report, the report shows results for both months even though I have the following expression. What am I doing wrong? =IIF(Fields!DateEntered.Value>=Parameters!CurrentMonth.Value,sum(Fields!PDAmt.Value),0) Hi Missa-Lissa, In your first post, I notice that you don't want to return both monthes's data, however based on your second reply, I find that you may want to get both monthes' data. So I am a little confused about your requirement. Could you please give some clarification about this. Are below columns static columns? Client ClientID Current Total Current AVG Last Month Total Last Month Avg % / Prior Month Actually, I would recommend you utilize the filter to filter the dataset, then you will display the value based on your parameters. Thanks, Challen Fu
September 30th, 2010 12:05pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics