web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to create function...
Microsoft Dynamics AX (Archived)

How to create function max(date) in SSRS Report?

(0) ShareShare
ReportReport
Posted on by fajar 3,805

Hi all..

i try create report.

i just select field LineNum, Name, Date, Amount in myTable.

and this is output in my report.

3531.1.jpg

But, i want output report like this.

if maximum date, value for Amount in column Amount A is null and move in column Amount B.

4606.2.jpg

max(date) in number 3, and amount move to column Amount B from Amount A.

only max(date).

Can i do it use function iif in SSRS Report.

if i can, How?

Thanks all.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    You can do something like this:

    In expression of column B =iif(Max(Fields!Date.Value) = Fields!Date.Value, Fields!Amount.Value, 0)

    In expression of column A =iif(Max(Fields!Date.Value) = Fields!Date.Value, 0, Fields!Amount.Value)

  • Verified answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 49,438 User Group Leader on at

    Hi PIL,

    I don't think of this working, without specifying DataSet Name. I cannot see max value to return something valuable without mentioning name of dataset.

    Try following

    For column A of amount:-

    =IIF(Fields!TransDate.Value = (Max(Fields!TransDate.Value, "NameOfSSRSDataSet")),0,Fields!AmountA.Value)

    For Column B of amount :-

    =IIF(Fields!TransDate.Value = (Max(Fields!TransDate.Value, "NameOfSSRSDataSet")),Fields!AmountA.Value,0)

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    @Sohaib

    Did you try to use it without second parameter ?

    Because it is a scope and it's optional. If you won't specify a scope SSRS will use default and default scope should be data source of the tablix here....

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,438 User Group Leader on at

    Hi ievgen,

    Yes, I tried with single parameter, unfortunately it did not gave required result.

    I would give another try to use code suggested by you. I will share results :)

  • fajar Profile Picture
    fajar 3,805 on at

    Thank Sohaib Cheema..

  • fajar Profile Picture
    fajar 3,805 on at

    Hi,

    can you help me again.
    i have problem again in my report.
    please follow this link.

    https://community.dynamics.com/ax/f/33/t/202563

    Thanks.

    [View:https://community.dynamics.com/ax/f/33/t/202563:750:50]

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,438 User Group Leader on at

    Hi PIL,

    Thank you for your question. Well, I cannot say any words without doing my TESTING for your 2nd Question.

    You may stay connected with your new thread unless you get any replies.

  • fajar Profile Picture
    fajar 3,805 on at

    Yes, thanks Sohaib for your respond.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Sukrut Parab Profile Picture

Sukrut Parab 2 Moderator

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans