Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

SQL "Where" Statement in Visual Studio 2013 SSRS Report

Posted on by Microsoft Employee

Hi, 

I'm working on my first SSRS report on Visual Studio 2013 and I'm using the report designer for the design and AX queries to retrieve data from database. For my report, I need to do a Sum of some values from a field that depend on the value of another field from the table, and i don't know how to do a conditional statement in VS 2013 ("where" condition in SQL). Someone can help me please.

Thank you

*This post is locked for comments

  • Suggested answer
    Hariharans87 Profile Picture
    Hariharans87 4,971 on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    Please explain me that your report design (where you need total) if you can't use IIF condition in the sum function.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    Thanks Hariharan, i'm already working on the iif function, but i didn't pay attention to the add total row. i will try this method

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    thanks Sohaib, first i'm going to try the iif function, if it doesn't work, i will write  the display method

  • Hariharans87 Profile Picture
    Hariharans87 4,971 on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    I hope that you want to add total in the SSRS report. That total should be based on the condition.

    First click on the row group and add total row after/before.

    Add-total.png

    Second, right click on the sum field and click expressions.

    You can sum the value based on the condition. Please refer the below example expression.

    =Sum(IIF(Fields!CalcType.Value="Article" AND Fields!CollectRefLevel.Value = "1", CDbl(Fields!CostAmount.Value), Nothing))

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    you can also group data on SSRS using grouping of rows, but that of course will limit your data/number of rows based on your grouping.

    but you cannot write following on SSRS

    where the CalcType is "Article" and CollectRefLevel is "1"

    if your report is being run using above selections i.e. user is already selecting these where clause as ranges, you can proceed for grouping on SSRS level

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    the simplest solution for you can be to write a display method inside AX.

    if you are using RDP for report, you can run your method inside RDP and fill your temp table.

    in case you are using query, you may write display method, on underlying table.

    doing complex calculation with where clause on AX2012-SSRS is not possible.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    Hi Sohaib,

    tab.PNG 

    Here's a screenshot of my table. What i have to do for my report, is a sum of CostAmount values where the CalcType is "Article" and CollectRefLevel is "1" for distinct  CollectRefId values. For example for the CollectRefId "CFT_000076" i have to display a total of 465 (200+200+65) in a field called Total

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: SQL "Where" Statement in Visual Studio 2013 SSRS Report

    your question is little ambiguous. would you mind to rephrase it by showing some table data or any screenshots, so that we may have little more idea, about what you are trying to do.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans