...
Visit level --> Visit Type
Encounters level --> Treatment type (surgery, treatment, procedure), Treatment outcome
Any view at a particular level would be able to provide columns for dimensions their and higher level. For example a program view should not only provide program outcome, program name with every program instance but also age, gender, tribe, date and location. All the views can be defined such that they repeat the applicable dimensions for every row.
Questions
Should referred-in and referred-out be handled using visit attributes.
Do we have department information in OpenMRS? Should we maintain this as location instead of concept?
References
http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/