0

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

Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062

2 Answers2

2

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.

Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • thank you but i am getting this error: http://screencast.com/t/1MykJEygTq5n string in wrong format? – Alex Gordon Oct 30 '14 at 20:37
  • One of your values in that field is blank or not able to be compared to a string, you can try casting your field as a string (CStr) or adding an additional check on that value to see if it's not a string (IsNothing, etc.) – Kyle Hale Oct 30 '14 at 21:28
  • anymore advice would be greatly apperciated – Alex Gordon Oct 31 '14 at 16:45
  • Without seeing your data, it's hard to speculate - but your issue is that your IIf statement is not able to process one of the values in that field's Value property as a string. Can you try to filter your dataset to only show (for example) fields where that value is Active, and just get it to work, then slowly de-filter your dataset until you come across the error? – Kyle Hale Oct 31 '14 at 16:49
1

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)
mheptinstall
  • 2,109
  • 3
  • 24
  • 44
  • What is the difference between the two columns bio_webuseraccountstatusgroupbyValue and bio_webuseraccountstatusgroupby? Does the one ending in Value contain numeric data and the other textual data? If so, then you need to use the other column. – mheptinstall Oct 31 '14 at 16:55
  • thanks so much! i realized i used string instead of numeric, and you helped me realize this, ive corrected it using the latest formula you gave me, no errors anymore, however the chart is still not correctly grouped, i added to my question as you will see at the top. thank you again – Alex Gordon Oct 31 '14 at 17:05
  • Using a similar IIF statement in the other answer suggested would be much simplier to understand the logic: `=IIF(Fields!bio_webuseraccountstatusgroupbyValue.Value = 3, 3, 1)` What this says is if the status is 3 (Active) then set the value to 3, otherwise set it to 1 (Inactive). You should only get 2 groups. – mheptinstall Oct 31 '14 at 17:13
  • thanks! ill try it right now, you dont think i need to do something with the chart filters? http://screencast.com/t/GwPPsTkg – Alex Gordon Oct 31 '14 at 17:18
  • now i am getting this: http://screencast.com/t/UkWIdZ0kndz when using this expression: =IIF(Fields!bio_webuseraccountstatusgroupbyValue.Value = 3, 3, 1) – Alex Gordon Oct 31 '14 at 17:19
  • What are you using in the label? I've just tried this on my machine using some sample data and it worked fine. Do you have anything in the Categories as well as Series? http://pbrd.co/1udkE1D – mheptinstall Oct 31 '14 at 17:36
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/64050/discussion-between-yuck-and-mheptinstall). – Alex Gordon Oct 31 '14 at 17:37