CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-services
Alex Gordon
Alex Gordon

Reputation: 60751

grouping data for a pie chart in SSRS

I have a data set that I use to build a pie chart in ssrs:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
  <entity name="account">
    <attribute name="bio_employeesworldwide" alias="TotalWorldWideEmployees" aggregate="sum" />
    <filter>
      <condition attribute="customertypecode" operator="eq" value="2" />
    </filter>
 <link-entity name="contact" from="parentcustomerid" to="accountid" alias="contact">


    <attribute name="bio_webuseraccountstatus" alias="count_bio_webuseraccountstatus" aggregate="countcolumn" />

    <attribute name="bio_webuseraccountstatus" alias="bio_webuseraccountstatusgroupby" groupby="true" />

  </link-entity>
  </entity>
</fetch>

enter image description here

I would like to have only 2 areas in this pie chart. One area should be all Active and the other should be everything that !="Active"

or describe in sql it would be:

Case When "Active" then "Active" else "NotActive".

How do I accomplish this with SSRS?

I've been trying to do this with group expressions for the series:

enter image description here

enter image description here

What am I doing wrong? How do I just get 2 shaded regions?

After attempting the IIF suggestion below, I am getting input string was not in a recognized format:

enter image description here

Following Kyle's advice, I've changed the formula to:

=
IIf(Not (IsNothing(Fields!bio_webuseraccountstatusgroupbyValue.Value) 
    OR Fields!bio_webuseraccountstatusgroupbyValue.Value="")
    ,"Active"
        , "InActive")

and the result i am getting now is still that silly error message:

enter image description here

Another failed attempt:

=iif(NOT IsNothing(Fields!bio_webuseraccountstatusgroupbyValue.Value),

        Switch(Fields!bio_webuseraccountstatusgroupbyValue.Value<>"InActive" 
        AND Fields!bio_webuseraccountstatusgroupbyValue.Value<>"Active", 
            "InActive", "Active")

        ,"InActive")

interestingly this time, there's only one error:

enter image description here

i incorrectly used a string value instead of numeric, so now it is this: 

=IIF(NOT IsNothing(Fields!bio_webuseraccountstatusgroupbyValue.Value),
        SWITCH(Fields!bio_webuseraccountstatusgroupbyValue.Value <> 1
                    AND Fields!bio_webuseraccountstatusgroupbyValue.Value<>3, 
                1,
                True, 3
        ),1)

now i am getting no errors, just an incorrect pie chart:

enter image description here

Upvotes: 0

Views: 2935

Answers (2)

mhep
mhep

Reputation: 2139

The syntax in your final expression is incorrect, you are missing the True part on the last statement in the SWITCH statement.

Though the logic doesn't look right to me, what this expression says is.
If the field is NULL then the value will be Inactive, otherwise if the field DOES NOT equal Inactive AND DOES NOT equal Active then the value will be Inactive, else it is Active.

=IIF(NOT IsNothing(Fields!bio_webuseraccountstatusgroupbyValue.Value),
        SWITCH(Fields!bio_webuseraccountstatusgroupbyValue.Value <> "InActive"
                    AND Fields!bio_webuseraccountstatusgroupbyValue.Value<>"Active", 
                "InActive",
                True, "Active"
        ),"InActive")

Alternative use the following expression against your Value column which contains numeric values:

=IIF(Fields!bio_webuseraccountstatusgroupbyValue.Value = 3, 3, 1)

Upvotes: 1

Kyle Hale
Kyle Hale

Reputation: 8120

You want an IIf expression:

=IIf(Fields!bio_webuseraccountstatusgroupbyValue.Value = "Active", "Active", "NotActive")

You'll also want to set this to the Label property of the SeriesGroup.

Upvotes: 2

Related Questions