4 Replies Latest reply: Apr 25, 2012 2:45 AM by 932932 RSS

    Oracle View (for Excel)

      Hello people,

      Im using Oracle XE11g and Excel 2007. I've created a View in SQL Developer which selects fields from multiple tables. The intention is to allow Excel to import this View using ODBC. The problem is that the view isn't giving the data the way I want it in Excel

      If I use my created view into Excel I get something like this:

      personname   - testname       -     time        - objectname    - result - date
      Edward - RunningTest - 15:22:01 - Speed (km/h) - 12 - 24-04-2012
      Edward - RunningTest - 15:22:01 - Heart Rate - 98 - 24-04-2012
      Edward - RunningTest - 15:22:01 - Power - 50 - 24-04-2012
      Edward - RunningTest - 15:22:02 - Speed (km/h) - 13 - 24-04-2012
      Edward - RunningTest - 15:22:02 - Heart Rate - 99 - 24-04-2012
      Edward - RunningTest - 15:22:02 - Power - 12 - 24-04-2012
      Edward - RunningTest - 15:22:03 - Speed (km/h) - 12 - 24-04-2012
      Edward - RunningTest - 15:22:03 - Heart Rate - 100 - 24-04-2012
      Edward - RunningTest - 15:22:03 - Power - 12 - 24-04-2012

      And this is the query of the view I use:

      select psn.naam personname
      , tst.name testname
      , tms.seconds time
      , obj.name objectname
      , trt.result result
      , dtm.days date
      from persons psn
      , results rst
      , times tms
      , tests tst
      , objects obj
      , dates dts
      where psn.id=trt.persons_id
      and obj.id=trt.objects_id
      and obj.tests_id=tst.id
      and trt.date_id=dts.id
      and rst.times_id=tms.id
      and tst.name='RunningTest'

      Please note that the query is translated into English fieldnames so there might be some errors in it.

      The way I'd like to have the data represented in Excel is like this:



      Time        -   Speed (km/h) - Heart Rate - Power
      15:22:01 - 12 - 98 - 50
      15:22:02 - 13 - 99 - 52
      15:22:03 - 12 - 100 - 51
      etc -----------

      Is there any way to build the view so it shows the data like above?
      Any help is appreciated.
        • 1. Re: Oracle View (for Excel)
          929929 wrote:
          Is there any way to build the view so it shows the data like above?
          Nothing i'd recommend actually doing.

          I think you'd be best served manipulating the data inside excel (writing code) to have it display how you need. That or migrate to a more sophisticated reporting tool, Oracle's freely available APEX comes to mind.

          • 2. Re: Oracle View (for Excel)
            If it must be Excel try the other way around.
            Take it just as an idea.
            Start in Excel and save the required data representation as *.xml (I'm not sure which Excel has the ability to do that)
            Then try to write an Oracle function which returns "that" xml (using as a template the *.xml stored by Excel).
            No experience with calling from Excel (did it only once just to demonstrate a security breech to colleagues)


            • 3. Re: Oracle View (for Excel)
              Mark Malakanov (user11181920)
              Yes, there are ways in Oracle to present data this way.

              But you better use Pivot table in Excel. It is more convenient.
              You can feed it from your query.

              let us know if you still want Oracle SQL for pivoting.
              • 4. Re: Oracle View (for Excel)
                I'm not sure if a pivot table would work out. If the data is displayed in Excel the way I'd like it the process isn't finished yet. It's supposed to be just raw data coming in from the view. After that the intention is to use that data to create/calculate graphs and things like median % and other analyses in another tab page.

                I'm looking for a view that can export the sporttest data of one specific sport (there are many different sporttests with different objecs in the Oracle DB) to Excel in the way i've described. So I'm free to use that loaded data to make standard formulas and graphs. Then, certain people can use those reports to inform the persons who took the sporttests about their performances.