SQL "Where" Statement in Visual Studio 2013 SSRS Report

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (8)

Answers (0)

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

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.

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

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

Was this reply helpful? Yes No
Hariharans87 3 on at

Like (0)

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.

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

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

Was this reply helpful? Yes No
Suggested answer

Hariharans87 3 on at

Like (0)

Report

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

Was this reply helpful? Yes No