case statement inside of fetchxml script

Question

Please note that although this question is an entirely different question, it relates directly to this question.

The following is the script that returns the dataset for my SSRS chart:

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

The values for bio_webuseraccountstatus can be Active, Inactive, Disabled, Pending, etc..

In FetchXML is there a way to do a case statement where you "InActive" for every value that is not equal to "Active" ?

As you can see I've been trying to solve this issue within the reporting layer, but am experiencing lots of "string format" issues.

Can this be done with the dataset layer (fetchxml) instead?

Answer 1

According to this Microsoft forum post:

There's no equivalent function in FetchXml. Instead, you will need to return each field value, and process the CASE logic in the calling code. If this FetchXml is to be used in a report, then you may be able to implement the CASE logic in a calculated field on the report.

Answer 2

You can always parse the output of the fetchxml on return. So rather than pushing to the screen, report, or db, the original record field of isreceivetravelalerts, you check the status (zero or one) and rewrite it, and add the new variable to your output. Clunky, but useful.

if ($record->isreceivetravelalerts == 1) 
{           
    $travel_alerts = "Active"; 
}

$travel_alerts = "Inactive";