The table format was lost after the post submition,
Here you are the correct format
Exam | Date | Result
Exam1 01-01-2012 10
Exam2 01-01-2012 12
Exam1 01-02-2012 11
Exam2 01-03-2012 14
X Y Z
Exam | Jan-01-2012 | Jan-02-2012 | Y. . . (as many as in the table)
Exam1 10 11 -
Exam2 12 14 -
X - - Z
(as many as in the table)
but in my case the columns change every day
Please explain how they change. Any method you choose will tend to encounter limitations in the number of columns on your screen at one time, but there are ways to flexibly control which data appears in each of those columns.
Are the result values shown data for a single exam or an aggregate value such as a total or average?
Thank you for your reply.
The source of the information is the table:
Patient Exam Date Result 1 Exam_Name1 01-Jan-2012 10 1 Exam_Name2 01-Jan-2012 4 1 Exam_Name1 04-Jan-2012 12 1 Exam_Name3 04-Jan-2012 123 ... ... ... ...
It is possible to have more than 50 different exams that can be performed as long as the patient stays at the hospital. It can be 1 week, can be 30 days. It is possible to do 1, 2 .... 10 exams in the same day and those exams are not necessarily repeted in the next day (or next necessity).
The information reported in the crosstable is just the value observed in the exam. There is no need to aggregate them.
So, the table content will vary a lot depending on the patient needs.
The report will be done based only in one patient at time.
Meanwhile, I have created a table where the columns are the most common exams and the lines are the dates and values, but this solution is limited since depends on layout modification in case we have more exams.
The solution will work on desktop, Ipad and Iphone, but this output will be used in Desktop and Ipad.
Currently I am showing the data thru a portal as large as the number of fields on the screen and the user need to move the screen with horizontal finger movement. Althoug it is working, I was asked to make it more flexible.
Searching in the internet I found the MMQuery plug-in that seems that has commands to create a table and change its content (fields and data). So I could create a temporary table based on the patient information to display data and after that delete it.
Any suggestion will be welcome.
My point is that your screen (or printed page) is only so wide. There's a limit to the number of columns that will fit before the page or edge of your screen cuts off the column.
The interesting question in this case is which, in general will be the larger set of values for a long term/large number of tests patient. In cross tabs on any application, the set expected to vary the least is usually displayed in columns with the larger/more variable set of values listed in rows.
I'd be inclined to use sub summary layout parts (when sorted by Exam ID) to get the rows of data grouped by Exam and would use one row filtered portals to display columns of data by date. The filter expressions can work from a "relative date range" where column 1 is the earliest specified date in the date range and the far right column would be the last date in the range specified. A script can set this range from the existing data as a starting point and then if you need to "scroll" the data horizontally, a different script can modify the date range specified to move forward or back in time.
The portals would be based on a self join relationship that links to these test results by patient and Exam ID fields. (2 pairs of fields in one relationship--also known as a multipredicate relationship.)
Ps. I'm not a health care professional, but a family member is a "frequent flier" when it comes to ER's and hospital admittance--aren't there some tests performed more than once in a given day? How would you chart that data in this cross tab?
Your suggestion was one I was considering before trying to find a different way, But it seems to be the best one for this case.
I came up with a different proposal and the guys accepted it instead of the table. The new solution creates a graphic where Y axis is the Exam value and X axis is the date. Nothing new so far, but I included in the chart view, 5 combo boxes where the users can choose the exams they want to analyse at the same time. We could used different ways to select the exams, but they liked the combo box. Once the user change the exam, the graphic is refreshed automatically.
Even though, I will implement the suggestion in an additional layout.
Answering your PS question, yes, depending on the patient situation, it is necessary to follow up some exam numbers to see if, for example, the treatment is working. It is just one example. There are other reasons to do that. In our case, it was conventioned that we will store the last exam value when performed in the same day.
If you have more ideas or questions, please let me know.