4 Replies Latest reply: Sep 27, 2012 4:54 AM by Henric Cronström RSS

    Resident Load and Cross Table from loaded SQL

    Helen Pippard

      Hello

       

       

      I was wondering if someone could kindly show me, with the

       

      example below how I would be able to create a cross table through a resident load so that I can calculate the median and count on fields such as [Total Wait] from a loaded SQL statement?

       

       

       

       

       

        • Re: Resident Load and Cross Table from loaded SQL
          Henric Cronström

          I think your SELECT statement is good the way it is.

           

          To create this type of table, you should create a chart (pivot table) where you use your month as dimension and Median([Total Wait]) and Count([Total Wait]) as Expressions.

           

          Then you can drag and drop the columns/dimensions in the pivot table to make it look the way you want.

           

          HIC

            • Re: Resident Load and Cross Table from loaded SQL
              Helen Pippard

              Hello

               

              Thank you for your advice

               

              I forgot to mention that I would numerous calculations under numerous headings to add to the pivot table and the above is just an example,  and therefore was looking at creating a cross table and then making a pivot table upon my cross table

               

              I was looking for the technique of how to create a cross table from a resident load

               

              If you are able to assist on this, then that would be great!

               

              Thanks

              Helen

              • Re: Resident Load and Cross Table from loaded SQL
                Helen Pippard

                Hello

                 

                Thank you for your advice

                 

                I forgot to mention that I would numerous calculations under numerous headings to add to the pivot table and the above is just an example,  and therefore was looking at creating a cross table and then making a pivot table upon my cross table

                 

                I was looking for the technique of how to create a cross table from a resident load

                 

                If you are able to assist on this, then that would be great!

                 

                Thanks

                Helen

                  • Re: Resident Load and Cross Table from loaded SQL
                    Henric Cronström

                    I think that this type of crosstable should be made in the layout and not in the script, also if you have several headings and several calculations. The data model is optimal if you have the different months as field values in one single column. Then a pivot table is perfect for displaying this as a crosstable.

                     

                    However, should you still want do it in the script, then you should look at the "Generic" prefix. It takes field values and converts these to field names and uses the value of another column as field values. Then you will get the separate months as separate fields.

                     

                    HIC