[sims] Report Design - Export To Excel converts numbers as Text

[bwfc_nottingham]

I'm in report designer and I've built a behaviour report that exports the data to excel.

The only issue is that the numeric data (attendance percentages, count of number of incidents for the student) exports as text.

 

Excel gives me a warning exclamation mark / shreik saying that the number in the cell is formatted as text or is preceded by an apostrophe.

 

I can correct the columns when in excel, but if someboody else is running the report from the SLT, then I don't expect them to have to start formatting cells, especially if the want to do analysis on the counts e.g. pivot tables

 

Any ideas how I can force the report design to output the fields as numbers rather than text?

 

Many thanks

 

Regards

 

 

 

Phil

[Ecclesbury]

Hi Phil,

 

This is actually built into the macro that runs when reports are exported to Excel.

 

Have you done much Excel macro programming? The only way to do it (I think) is to amend the report template to either convert the text back to numbers [with something like 'For Each Cell in Range, Cell.Value = Val(Cell.Value)'], or amend the section of code in the standard template which converts the numbers to text.

 

There is a great document that has been doing the rounds for years which explains how to create your own custom excel report templates, but it requires some VBA knowledge. Let me know if you want this, and I can dig around for it (if someone else doesn't beat me to it.)

[Ecclesbury]

p.s. in case you're wondering why Capita convert all numbers to text in the output, the following comment is included in the standard template code:

 

' GERAINT 11/8/06: Additional code to enable correct interpretation of dates.

' We define every column as being a TEXT type column - so we avoid any mis-interpretation

 

Incidentally, if you wanted to amend the code directly you could do it under here, but I tend not to, as I don't fully understand why they have done it, so wouldn't want to break anything. I also never amend the actual standard template, just take a copy of it to create my new templates.

[bwfc_nottingham]

Ecclesbury

 

Thanks for the reply, I've only been in my post as data manager for 3 weeks, my background is as a Business / Sytems Analyst for big PLC's

 

I must say it's very unusual for numeric data to be exported as text.

 

In my previous roles I've exported data from databases (Oracle, SQL Server), either via native SQL or via utilities such as MSQUERY, MS ACCESS that utilise ODBC drivers and I've never had any 'dodgy cell' conversions.

 

I've not done much work with templates, so I need to do a bit of research on that.

 

I've recorded macros in excel, but never had the need to write them in VB. Reading these forums and the one's on Capita, I do worry that there's a lot of data manipulation and massaging outside of SIMS or the core MIS systems i.e. 'desktop computing' is occurring. If you are not careful you can end up with errors in your 'desktop computing' and making wrong decisions based on the contents.

 

In the short term I will just convert them manually, but the manual you refer to would be useful.

 

While I'm finding my feet in the new job I tend to post the same question on here and on Capita's own forum.

 

Thanks for the reply

 

Forums are wonderful places for exchange of ideas and tips !

 

Phil

 

 

Phil

Edited April 12, 2013 by bwfc_nottingham
another typo

[number34]

 

Reading these forums and the one's on Capita, I do worry that there's a lot of data manipulation and massaging outside of SIMS or the core MIS systems i.e. 'desktop computing' is occurring. If you are not careful you can end up with errors in your 'desktop computing' and making wrong decisions based on the contents.

 

 

 

Phil

 

Could not agree more, I know I have made errors transferring and analysing in Excel and try to keep it to the absolute minimum.Captita still have some work to do in this area.

As to Excel the latest version does a good job treating text as numbers but if you do not like macros or VBA then you might find this ASAP Utilities for Excel - The essential add-in for Excel users. FREE excel tools and macros to save time. Download Excel tools useful.

[vikpaw]

Ecclesbury

 

Thanks for the reply, I've only been in my post as data manager for 3 weeks, my background is as a Business / Sytems Analyst for big PLC's

 

I must say it's very unusual for numeric data to be exported as text.

 

In my previous roles I've exported data from databases (Oracle, SQL Server), either via native SQL or via utilities such as MSQUERY, MS ACCESS that utilise ODBC drivers and I've never had any 'dodgy cell' conversions.

 

I've not done much work with templates, so I need to do a bit of research on that.

 

I've recorded macros in excel, but never had the need to write them in VB. Reading these forums and the one's on Capita, I do worry that there's a lot of data manipulation and massaging outside of SIMS or the core MIS systems i.e. 'desktop computing' is occurring. If you are not careful you can end up with errors in your 'desktop computing' and making wrong decisions based on the contents.

 

In the short term I will just convert them manually, but the manual you refer to would be useful.

 

While I'm finding my feet in the new job I tend to post the same question on here and on Capita's own forum.

 

Thanks for the reply

 

Forums are wonderful places for exchange of ideas and tips !

 

Phil

 

 

Phil

 

You're right, there is much done externally, and reliance on manual intervention is a pain. If you want others to use the report i'd recommend recording a macro into the template, the guide @Ecclesbury referred to is here http://www.edugeek.net/forums/mis-systems/38519-edit-excel-output.html#post359319

 

I have a few reports that use this method and it's useful. The VBA is just the macro code, and you will need to tweak it no doubt to get it to work. It's very useful when creating pivot tables of your exported data.

 

All excel based reports, first go out to a csv / text file and then are read back in to Excel using the default macro.

[MattMitchell]

My experience back in more commercial environments was that businesses, sites, and sometimes individual users, often land up using a mixture of date locale settings, so that converting dates (text) to dates (date!) could cause confusion, especially if a date got mangled through UK-text-US misconversion or similar.

 

Rather than deal with people complaining that the dates were coming out wrong, I suspect the chosen solution was to format them as text, so that no-one could complain the wrong data was being output!

 

The "text" format is a pain, though, as it also fails on some versions of Excel when the text is more than 1023/1024 chars long. ironically, formatting this text as "general" avoids the problem. It also leaves Excel to determine that the text date literals are, in fact, dates, and to format them as such. As long as it won't cause any issues at your site, creating a new default template with the format-as-text line removed can make life a *lot* easier, and I've yet to be able to find a test case where it would be a problem.