10 Replies Latest reply on Apr 29, 2015 9:12 AM by RobertT

    Making a Report to make the years divided by columns instead of rows

    RobertT

      Title

      Making a Report to make the years divided by columns instead of rows

      Post

       

      I managed to create a report  where I am totaling the number of scripts a patient received by the year of different dates.

       

      Patient Name      

      Year 2014    ( total number)

      Year 2015    (Total Number) 

      How would i Change it so it goes

      Patient Name          2014      2015

                                   total scripts    total scripts

                                 Total for year (2014)    Total year (2015)

       

       

       

       

        • 1. Re: Making a Report to make the years divided by columns instead of rows
          philmodjunk

          It's possible, but a bit of work to set up. What you describe is called a "cross tab report" and you can search on that text to find a number of different approaches to how you might set it up.

          I usually set up a list view layout based on the patients table so each row is one patient record in the body layout part. I then set up filtered one row portals to show the summary data for each specified year. You can set up one portal that filters for 2014 and when it works for you, you can duplicate it and update the portal filter to specify a different year.

          You can set up global fields to specify the year for each filter so that you can update your report from year to year by entering different years in the global fields. (And i've sometimes used one global field for year 1 and then used calculation fields for that year  + 1, That year + 2..., to specify the years for other columns so that I can update the report by changing just one field's value.)

          • 3. Re: Making a Report to make the years divided by columns instead of rows
            RobertT

            when I create a year calculation do i  write  year(dateofservice=2014)  or write  year(dateofservice="1/1/2014")   ???

            • 4. Re: Making a Report to make the years divided by columns instead of rows
              philmodjunk

              Neither. If you are using a calculation in a portal filter, it would be:

              Year ( YourTable::DateOfService ) = 2014

              But I'd write it this way:

              Year ( YourTable::DateOfService ) = LayoutTable::gYear

              where gYear is a global field. That way I can specify different years in this field as the needs for the report change over time.

              • 5. Re: Making a Report to make the years divided by columns instead of rows
                RobertT

                Great, so I did a the first calculation in the portal...  ( I will try the g year caclulation)....  I got what I wanted but I can't seem to manage to line up the value of scripts in 2014 next to 2015...  they seem to be separated by an inch vertically  and there are more gaps if the there is a "0" value in one year vs if all values are in one year... attached is a photo

                • 6. Re: Making a Report to make the years divided by columns instead of rows
                  philmodjunk

                  You do know that there is a key or key combination that you can press to capture an image of your screen without using your cell phone? wink On windows, press PrintScr while holding down either alt or shift, then open windows paint and Paste the screen shot into the windows paint window. You can then save it as a jpeg, gif or png file for uploading here. (you can also crop such an image.)

                  From what little that I can make out, it does not appear that you have set up any portals, only fields. but you'd need a screen capture of your layout while it is in layout mode for me to see much.

                  • 7. Re: Making a Report to make the years divided by columns instead of rows
                    RobertT

                    Lol.. yes I know how to that in windows.. I'm learning filemaker 12 on a mac and have no learned how to do that on a mac.    The Boxes are the portals.. then I put a field over the portal  ill send another photo tommrow in layout mode..  I filtered by the year(dateofservice)=2014  and 2015...
                     

                    By doing that It creates 4 box portals in browse mode... 2 boxes in 2014 and 2015  instead of one in each... creating extra space between each patient.   For example  Amanda Anderson patient shows 3 scripts written but why is there a blank box below??  and for 2015  the 6 scripts is lower in 2nd box and not aligned the same as the 2014 input  (3 and 6 should be parallel side by side).  Basically I got extra boxes for no reason, or is that only way you can do a cross tab report?

                    • 8. Re: Making a Report to make the years divided by columns instead of rows
                      RobertT

                      Ok So the software was upgraded to Filemakerpro13 from 12.... and the data was lost and need help again... I tried to recreate a table "Patientdata"  that has patient FIrst and Last name and ID.  A second table "PatientVisit" with an IDfk field (connected relationship to ID)  

                      A "scripts" field (as a number) A date field (for "dateofservice")  and Fullname Field. Created 3 sets records for set of 10 patients ..random number of scripts and dates per each visit.   Similar to what I have listed on the photos.  Example  Patient Amanda Steven  had  3 scripts written on 1/1/2015,  4 on 1/10/2015 and  2 on 2/10/2015    I used only years 2014 and 2015...  created a Summary field of the Sum of Scripts sorted by the Fullname   and Summary Part "Fullname"... that allowed for me to sum the total number of scripts a patient had of all the records via their Fullname, but I tried to create portal to Filter  year(dateofservice)=2014  and also for 2015....  on the body, summary part Fullname, IDFK, Date of Service.. nothing worked!   even with the Portal Filter on the subsummary part of Fullname, it just shows total number of scripts the patient had of all the dates.  Hope you can guide me back in the right direction

                      • 9. Re: Making a Report to make the years divided by columns instead of rows
                        RobertT

                        Heres what I have here...   (Sumscript)  is sum of total scripts sorted by "Fullname"

                        • 10. Re: Making a Report to make the years divided by columns instead of rows
                          RobertT

                          Here is result.. but it totals the Number of scripts per the patient of all the years... I tried a portal to filter  year(dateofservice:patientdata)=2014        Alas nothing worked I tried it in the body and every subsummary part..