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.

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.