Formatting issues in excel template for BIP report (formatted as number in data model but is text)

Summary:

So, we have a BIP report which details the amount for various pay elements, the element name is hard coded in sub select clauses so we can control the column order and include two sums, one for the basic salary and one that includes additional allowances. In the data model the amounts are formatted as number and the columns that have data in the sample data set come out fine but if there isn't any data in that column in the sample data when I extract the full report the data comes out as text which looks shoddy and