Dynamic Summing on Textbox

  • cmw 66135

    Ten Centuries

    Points: 1205

    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

  • David Burrows

    SSC Guru

    Points: 64392

    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.

  • cmw 66135

    Ten Centuries

    Points: 1205

    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

  • David Burrows

    SSC Guru

    Points: 64392

    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)
    )

    • This reply was modified 3 days, 15 hours ago by  David Burrows. Reason: Fix missing Fields reference

    Far away is close at hand in the images of elsewhere.
    Anon.

  • cmw 66135

    Ten Centuries

    Points: 1205

    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.

     

  • David Burrows

    SSC Guru

    Points: 64392

    cmw 66135 wrote:

    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.

  • cmw 66135

    Ten Centuries

    Points: 1205

    Thank you again David. Textbox is now showing #Error 🙁

  • David Burrows

    SSC Guru

    Points: 64392

    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.

  • cmw 66135

    Ten Centuries

    Points: 1205

    PostingDetailsDate returns a date and time, not sure if thats the issue. Not even sure how to use the CDbl expression

  • David Burrows

    SSC Guru

    Points: 64392

    cmw 66135 wrote:

    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.

  • cmw 66135

    Ten Centuries

    Points: 1205

    David you are an absolute legend. That worked perfectly

    Thank you 🙂

  • David Burrows

    SSC Guru

    Points: 64392

    cmw 66135 wrote:

    David you are an absolute legend. That worked perfectly Thank you 🙂

    Your welcome and thanks for the feedback 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply