Microsoft Dynamics CRM (Archived)

How can I count using the IIF and SUM functions in SSRS

(0) Share

Report

Posted on by Jeremiah

2,555

Hello,

I want to sum up possible different values of a field if a condition is true. For example:

=Sum(IIF(Fields!status.Value = "Open",0, (IIF(Fields!status.Value = "Waiting",0, (IIF(Fields!status.Value = "Closed",0, (IIF(Fields!status.Value = "Completed",0, Fields!status.Value))))))))

I want a numeral count if the values of a field (Fields!status.Value) are Open, Waiting, Closed or Completed.

Using the Case entity for example, if there are different possible values for Status (Open, Waiting, Closed and Completed). There should be a count of how many Case records are in the different stages...

The code above is giving some errors, I am using SSRS report builder 3.0 for this.

Please help

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Royal King 27,686 on at

Like (0)

Report

RE: How can I count using the IIF and SUM functions in SSRS

Use group by on case status column with count function which will give you count of all case status

like Open = 20 , closed = 55 . You could also use Chart on the All case view to show count of cases by status

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

RE: How can I count using the IIF and SUM functions in SSRS

Hi Jeremiah,

May I know your SSRS Layout.

Actually it is recommended to use Chart for this, either Chart in CRM or Chart in SSRS for each status, or you can try using report wizard to SUM, example here:

www.powerobjects.com/.../sum-of-revenue-field-report-created-using-report-wizard-dynamics-crm

But, if you need very complex scenario, you need to use SSRS, I need to see your Layout first, what is the error message?

It is online or onpremise CRM?

If possible you use grouping in your SQL.

Hope this helps.

Thank you.

Was this reply helpful? Yes No
Jeremiah 2,555 on at

Like (0)

Report

RE: How can I count using the IIF and SUM functions in SSRS

Thanks Chitra for your response,

However I am actually trying to use the expression I stated earlier in a calculated field on the report builder.

This is the error I get below:

System.Web.Services.Protocols.SoapException: The expression used for the calculated field '=Sum(IIF(Fields!status.Value = "Open",0, (IIF(Fields!status.Value = "Waiting",0, (IIF(Fields!status.Value = "Closed",0, (IIF(Fields!status.Value = "Completed",0, Fields!status.Value))))))))' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

Thanks again for your help, I would appreciate your help again so I can fix this error.

Was this reply helpful? Yes No
Jeremiah 2,555 on at

Like (0)

Report

RE: How can I count using the IIF and SUM functions in SSRS

Thanks alot Aileen for your help,

This is the error I get below:

System.Web.Services.Protocols.SoapException: The expression used for the calculated field '=Sum(IIF(Fields!status.Value = "Open",0, (IIF(Fields!status.Value = "Waiting",0, (IIF(Fields!status.Value = "Closed",0, (IIF(Fields!status.Value = "Completed",0, Fields!status.Value))))))))' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

I actually used a tablix matrix to design the report. I have three row groups, Region, State and Branch.

So, the fourth column should now be a column where a count is done for all the Cases logged in each branch, that was why the calculated field became compulsory.

For example, the Northern region has 4 states which in turn has 6 branches each. In each branch, there should be a total count of cases logged in that branch, that's where the expression(calculated field) becomes compulsory.

I would really appreciate your help...

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

RE: How can I count using the IIF and SUM functions in SSRS

Jeremiah,

Have you tried to include the dataset name in the expression:

=Sum(IIF(Fields!status.Value = "Open",0, (IIF(Fields!status.Value = "Waiting",0, (IIF(Fields!status.Value = "Closed",0, (IIF(Fields!status.Value = "Completed",0, Fields!status.Value))))))),"dataset1")

change the dataset1 to your name.

Btw, why you set as one expression for the status, why not you create 3 columns different for each status?

If this possible you give the screenshot to make clearer picture I am kind of confused to imagine now.

Thanks.

Was this reply helpful? Yes No
Jeremiah 2,555 on at

Like (0)

Report

RE: How can I count using the IIF and SUM functions in SSRS

Thanks again Aileen,

I'm sorry I could not take a snapshot of the grid, but please find below how the structure of the report should look like.

There are four columns: Region, State, Branch and Total Cases/Branch.

The example below: North region has two states (Northern State A and Northern State B).

Northern State A has two branches: Northern State A (Branch 1) and Northern State A (Branch 2).

Northern State A (Branch 1) has a total of 10 cases, and the breakdown is shown as: 5 open cases + 3 waiting cases + 2 closed cases + 0 completed cases.

I used a tablix matrix in the report. Region, state and branch are in the row groups.

Total Cases/Branch is the calculated field that I had to create so that I can do a sum total of all cases regardless of their status (open, waiting, closed and completed) in each branch.

That is where I get the error.

Region State Branch Total Cases/Branch

North Northern State A Northern State A (Branch 1) Northern StateA (Branch 1) :

5 Open, 3 waiting, 2 closed

and 0 completed cases >

Total = 10.

Northern State A (Branch 2)

Northern StateA (Branch 2) 4

Open, 1 waiting, 2 closed

and 0 completed cases >

Total = 7.

Northern State B

Northern StateB (Branch 1)

Northern StateB (Branch 2)

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

RE: How can I count using the IIF and SUM functions in SSRS

Jeremiah,

If you want to group by Status, you need to group them into the Status as well.

So the format will be:

Region State Branch Total Cases/Branch

--> At first, you group by Region/ Branch right

--> Then group by Case Status field (Let's say the group name is group_bystatus)

So in your Sum expression for the just use the count, not Sum

=Count(Fields!Size.Value,"group_bystatus")

Nah, for the Total all, you can use the region group

=Count(Fields!Size.Value,"group_byregion")

For the total all you can use the data set name

=Count(Fields!Size.Value,"dataset1")

Or you can implement this subtotal method:

forums.asp.net/.../1927767.aspx

www.youtube.com/watch

munishbansal.wordpress.com/.../customizing-subtotal-functionalityusing-inscope-function-in-matrix-reports-ssrs

All is using inscope or scoping data, you can also use RowCount as well to count the total row.

Do not using IIF and Sum for this purpose.

Hope this can help you!

Thanks.

Was this reply helpful? Yes No