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
Please explain me that your report design (where you need total) if you can't use IIF condition in the sum function.
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
thanks Sohaib, first i'm going to try the iif function, if it doesn't work, i will write the display method
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.
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))
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
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.
Hi Sohaib,
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
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.
#1
André Arnaud de Cal...
291,391
Super User 2024 Season 2
#2
Martin Dráb
230,445
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
