Paginated Report export to .csv cutting off/rounding decimal places

[d_m_LNK]

Hi Everyone,

I have a simple paginated report built on a SQL query that needs to be exported to a .csv file.  When running the report the report shows correct values with 4 decimal places.  The source query formats this column as a decimal number with 4 decimal places.  However, when exporting it to .csv (either from the service or report builder) it rounds or cuts off the decimal places seemingly in descriminately. 

Note, exporting to xlsx format keeps correct formatting.

Example output:

running the report: 

1234.9770
4567.6370
78910.1300
4321.4300

151515.246

exporting to .csv:

1234.977

4567.637

78910.13

4321.43

151515.25

I have tried mulitple options on the report builder side to change the dataelementoutput -> output, changing the formatting formula to hard code 4 decimal places, trying a 0.#### format string but nothing has worked to correctly export the desired values.

Any ideas on how to correct this when exporting to .csv?

[burakkaragoz · Accepted solution]

Hi @d_m_LNK ,

This is a pretty common issue with CSV exports in paginated reports. Even if your SQL returns the correct precision and the report shows it properly, the CSV export often drops trailing zeros or rounds the values. It treats the field as a number and trims what it sees as unnecessary.

What worked for me before was converting the number to a string directly in the SQL query like:

CAST(ROUND([YourColumn], 4) AS VARCHAR)

That way, the export keeps the exact format you want. Just keep in mind that this turns the value into text, so if you're doing any numeric operations later, you'll need to convert it back.

Also, make sure DataElementOutput is set to Output, not Auto. And yeah, those custom format strings like 0.#000# can be unreliable in CSV exports.

Hope this helps.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[d_m_LNK · Accepted solution]

Thanks @burakkaragoz for your reply.  What I found out after some troubleshooting yesterday was that the main issue I was seeing was with the excel formatting of the .csv file.  I agree your solution will work as well.  What I ended up doing was CAST([NumberColumn] AS DECIMAL(20,4))  on the SQL side.  On the paginated report side I also changed the column to have no formatting so it would pass through the value -- much like you are doing.

The opening the .csv in a text or code editor verified my exports were working properly even though when opening in excel still showed the same formatting.  Thanks for the help!

[burakkaragoz]

@d_m_LNK ,

Glad to hear you got it working. Yeah, Excel can be sneaky with how it auto-formats CSVs. Opening the file in a text editor is definitely the best way to confirm the actual output.

Your approach with CAST([NumberColumn] AS DECIMAL(20,4)) on the SQL side and removing formatting in the report is spot on. That combo usually gives the cleanest result when exporting to CSV.

[burakkaragoz · Accepted solution]

Hi @d_m_LNK ,

This is a pretty common issue with CSV exports in paginated reports. Even if your SQL returns the correct precision and the report shows it properly, the CSV export often drops trailing zeros or rounds the values. It treats the field as a number and trims what it sees as unnecessary.

What worked for me before was converting the number to a string directly in the SQL query like:

CAST(ROUND([YourColumn], 4) AS VARCHAR)

That way, the export keeps the exact format you want. Just keep in mind that this turns the value into text, so if you're doing any numeric operations later, you'll need to convert it back.

Also, make sure DataElementOutput is set to Output, not Auto. And yeah, those custom format strings like 0.#000# can be unreliable in CSV exports.

Hope this helps.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.