Arty should have its best effect against infantry and cavalry. Field pieces wouldn't be too terribly effective against buildings (a solid roundshot is just going to fly right through most non-military buildings with minimal damage), and while grapeshot might knock the paint off and put some dents in the brickwork it probably wouldn't go through.
Now, against men, artilleries effects should vary. Roundshot depended on the depth of formation of the enemy. Against a line of men three-deep, the most it could kill would be three men. Although you might be able to wound six with a lucky, one-in-a-million shot.
This is assuming no collateral damage from the wounded men suffering 'friendly stab' with their bayonet or accidentally firing their musket.
Grapeshot, though, at the right range, could take out massive numbers of men. Considering it was basically a concentrated musket volley firing 1 inch balls (Or, if you were desperate, whatever fit down the barrel. Nails, bolts, cats, small children...
Regardless, any infantry foolish enough to get too close to a cannon should suffer for it. Cavalry too.
There was a reason it was difficult to get people to charge artillery batteries. Nobody wanted to find themselves looking down the barrel of a 12 pounder cannon charged with grapeshot. And the effect of those shots on morale should route all but the best trained units in 1-2 shots.