Citing Data in Reports
Hi
Can I ask if anyone has any guidelines for how to cite data in a report
Being asked to do a few ‘updates’ on tables in reports done a few years ago, and there are no data sources, weblinks, accessed dates etc – so it’s a bit of a detective job!!
Does anyone have a standard format they use, or recomend for when people use data tables etc in reports?
Thanks
This is a great question as we don’t have a standard for the metadata that reports are built on and I’d be interested to know if anyone does.
Detective work on reports is made slightly easier as for spreadsheets we name the file the task number which is generated through an Access front end SQL table back end – historically it was Access. Each task has it’s own folder with the same task number so all documents related go in there, including scripts.
I have done metadata for some of my reports and it’s a combination of information for the user, for analysts and references for the future (like weblinks). I like the metadata from ONS and Public Health but their data rarely has assumptions on it which ours has as it’s the raw data (and not public) so I tend to add things that I now I’ll either be asked later or will probably need myself to redo the report in the future.
Not sure if I can be of great help wtth regards to accepted business standards.
But we have standard naming conventions for all outputs – a request number if you like (whether reports/spreadsheets, powerpoint etc) and a folder with all things relating to the output, emails, data extracts, SQL queries etc. So if people want updates they provide us previous version and we can find the folder with everything in there to easily update.
Or alternatively a cover sheet/backing sheet stating data source and criteria applied is added or, in a tab if excel, again so the recipient can see all the criteria and analysts can see what data source was used. We also save the SQL in a tab for excel outputs so again can see exactly.source and criteria.
We do all of this too but not the SQL in excel. I have done that but the formatting goes strange and it can be a bit confusing for the end user (unless the tab is hidden). I also found the version on the excel can get out of synch very quickly particularly if the code gets reused for other reports or tasks.
We do a lot of connections in excel so the data come directly from the SQL servers which is quite useful as you can then see exactly what brings through the data and it can be updated by just refreshing, There are a couple of ways to link data in excel and I prefer using Power Pivot.
HI Zoe yes you are right SQL can be continually tweaked. It is literally a copy and paste of the script into a tab so we can quickly see criteria – inclusions/exclusions etc and source table so at a glance to answer quick questions as a starter to explain possible discrepancies to similar outputs (e.g. cons led/non cons led, emerg/all non elec, SUS/SLAM) But as we save the SQL in that same folder, I would go to that to actually re-run as it may have adjustments as you suggest.