Going back to the cannon idea, when cannons where popularly used in history, cannons had more than one type of ammo. For taking out ships they had the two-balls-connected-by-a-chain ammo. For city taking they had the big-ball-filled-with-gunpowder ammo. And for people they had a lots-of-little-shots-that-spread-like-shotgun ammo.
All this to say, I think the solution is to make cannons a loadout type unit, kinda like boats can go to docks and get new crews, cannons need to be able to change ammo types in cities. The base cannon strength would be 0 and ranged attack would be X, but the ammo type you choose would give bonuses, city attack ammo, could give a bombard and collateral damage all at once attack, exploding cannon balls that tear down walls are more than likely to hurt the people standing next to it.

The 'Grapeshot' ammo would give the cannon +X amount of strength, and reduce ranged attack, shotgun type blasts are not known for their power from afar.
But with this system not all ammo types would be available outright. The specific ammo types would only be available once the appropriate tech was discovered.