SearchKit: How can I do math on currencies?

Question

I'm trying to replicate a (buggy) report in Extended Reports, basically:

Campaign	Goal	Amount Raised	Amount Left
Capital Campaign	$10,000	$8,000	$2,000
Another Campaign	$15,000	$6,000	$9,000

The equivalent SQL would be:

SELECT campaign.title as "Campaign", campaign.goal_revenue AS "Goal", SUM(contrib.total_amount) AS "Amount Raised", goal_revenue - SUM(contrib.total_amount) as "Amount Left"
FROM civicrm_campaign campaign
JOIN civicrm_contribution contrib ON campaign.id = contrib.campaign_id
GROUP BY campaign.id;

However, there seems to be no way to do this. SQL transformations won't work because I can't subtract the aggregated Amount Raised from Goal (or do arithmetic transforms on aggregated fields). But Smarty treats the values as strings, with no way to convert to numbers (using intval filter or casting to (int)) both generate errors.

Answer 1

I was finally able to use Smarty to rewrite a random field to be the "Amount Left", but it's not pretty:

{capture name=goal assign=goal}[goal_revenue]{/capture}
{capture name=goal2 assign=goal}{$goal|replace:"$":""|replace:",":""}{/capture}
{capture name=raised assign=raised}[SUM_Campaign_Contribution_campaign_id_01_total_amount]{/capture}
{capture name=raised2 assign=raised}{$raised|replace:"$":""|replace:",":""}{/capture}
{capture name=remaining assign=remaining}{math equation="x - y" x=$goal y=$raised}{/capture}
{$remaining|crmMoney}

It seems like it would be easier if any of the following were true:

• A "raw" Smarty filter were available to pass my currency as a number.
• It's possible to use Smarty filters directly on SK tokens.
• It's possible to use arithmetic SQL transformations with aggregated values.
• A field transformation "Raw" existed to disable formatting.