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