May 15, 2019 at 10:16 am
Is there any way I can make the following sum dynamic so it only collates data for the current month with out the need for using any parameters?
=Sum(Fields!SplitBillsMasterSplitAmount.Value)
I have spent a while search the internet and here for answers and I can't see if this is even possible
Thanks
Chris
May 15, 2019 at 12:57 pm
What do you mean by :-
with out the need for using any parameters?
current month?
what other field do you have to signify what month the data belongs to?
Far away is close at hand in the images of elsewhere.
Anon.
May 15, 2019 at 1:07 pm
Hi David,
Sorry I didnt explain it very well. My background is more excel than sql
I have a field that is called PostingDetailsDate I know the following would give me the beginning of the current month =DateSerial(Year(Date.Now), Month(Date.Now), 1) and this would give me the end of the month =Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)
If this was excel I would write an if(and()) statement to incorporate this something along the lines of
=If(and(PostingDetailsDate>DateSerial(Year(Date.Now), Month(Date.Now), 1),PostingDetailsDate,Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)),Sum(Fields!SplitBillsMasterSplitAmount.Value),0)
Im just not sure of how to write a similar statement on SSRS
May 15, 2019 at 1:19 pm
This should give you what you want
=Sum(
IIF(Year(Fields!PostingDetailsDate.Value)=Year(ToDay)
AND Month(Fields!PostingDetailsDate.Value)=Month(ToDay),
Fields!SplitBillsMasterSplitAmount.Value,
0)
)
Far away is close at hand in the images of elsewhere.
Anon.
May 15, 2019 at 2:52 pm
Thanks David I got the following error
The Value expression for the textrun ‘SplitBillsMasterSplitAmount.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] Name 'PostingDetailsDate' is not declared.
May 15, 2019 at 3:56 pm
Thanks David I got the following error The Value expression for the textrun ‘SplitBillsMasterSplitAmount.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] Name 'PostingDetailsDate' is not declared.
Sorry my bad, missed of Fields reference 🙁
Fixed my post above
Far away is close at hand in the images of elsewhere.
Anon.
May 15, 2019 at 4:29 pm
Thank you again David. Textbox is now showing #Error 🙁
May 16, 2019 at 7:52 am
If you are not getting a syntax error then the problem is almost certainly to be the data.
Is PostingDetailsDate a date or is it varchar containing a date?
I have also found that SSRS sometimes baulks at summing results from IIf
You could try to use CDbl to convert the result of the IIf
Far away is close at hand in the images of elsewhere.
Anon.
May 16, 2019 at 8:25 am
PostingDetailsDate returns a date and time, not sure if thats the issue. Not even sure how to use the CDbl expression
May 16, 2019 at 9:48 am
PostingDetailsDate returns a date and time, not sure if thats the issue. Not even sure how to use the CDbl expression
=Sum(
CDbl(
IIF(Year(Fields!PostingDetailsDate.Value)=Year(ToDay)
AND Month(Fields!PostingDetailsDate.Value)=Month(ToDay),
Fields!SplitBillsMasterSplitAmount.Value,
0)
)
)
Far away is close at hand in the images of elsewhere.
Anon.
May 16, 2019 at 9:57 am
David you are an absolute legend. That worked perfectly
Thank you 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply