Hi!
This is the point at which I really need to refer you to Tech Support because there's one way of accessing Oracle tables "outside" of the Enterprise Intelligence Platform (using the LIBNAME statement and options -- and SAS/Access for Oracle) and another way of accessing Oracle tables "inside" the Enterprise Intelligence platform (using the Metadata Library definitions and SAS/Access for Oracle) and setups in SAS Management Console and the metadata for your tables.
Once all your definitions and configurations are set up, either method should make the tables visible to you in EG; however, it would be irresponsible of me to just toss you a LIBNAME statement knowing that you are developing a stored process and must eventually go through the Metadata Server to get to the data. So, Tech Support really should be the ones to help you figure out how to do the setup, if you have the authority to define the tables in the SAS Management Console. OR, they can tell you how to access them once somebody else sets them up for you if you do NOT have the authority to set them up yourself.
Because you said that you wanted to surface your data in a particular way in Web Report Studio and because you said you were new to SAS, I did not go into a very detailed explanation about why I recommended EG or PROC PRINT (or PROC REPORT) for your report...but here goes:
1) Inside the context of the Enterprise Intelligence Platform, if you did a simple LIST DATA task on your data and turned that task into a stored process and then surfaced that stored process in Web Report Studio, you would see all the variables spread out and you would have to scroll right and left in the browser window to see from one side of the table to the other. (I tested a Stored Process created from a LIST DATA task, so the "vanilla" task is probably NOT what you want if you want to avoid scrolling.)
2) In the regular SAS LISTING window, there is a way, using FILE and PUT statments to write 2 output lines for every single observation. However, this technique is designed for the LISTING window and does not translate well from the LISTING window to the type of SAS Report XML that is generated for Web Report Studio. So that FILE PRINT technique is not really available to you as a stored process, although it would work very well for LISTING output.
3) The way that I would do this, if I had my data available would be to would be to make a unique ID number or row number for every observation. Then, I would "split" my data, so where the original data file might have had just var1-var40; the new dataset has 2 rows for every row in the original file: [pre]
cntr var1 var2 var3 var4 ...var20
1 0101 0202 0303 0404 ...2020
2 2121 2222 2323 2424 ...4040
[/pre]
Once I had my data structured this way -- a fairly simple transformation program -- THEN I would do a Proc Print (List Data task) with grouping by the CNTR variable or I would do Proc Report (in a code node) with grouping by the CNTR variable. I can assign labels to my variables, so that the label for VAR1 reads as "Var1 and Var 21" for the 1st column, "Var 2 and Var 22" for the 2nd column and so forth.
3) Next, I would turn that task into a Stored Process for surfacing in Web Report Studio. Depending on whether I had started with a task or a code node and whether I have access to SAS Management Console, I have 2 choices for how to create my stored process. However, in order to execute in Web Report Studio, the stored process must be written in a certain folder in the Metadata and so you must make sure that you have been given access to that folder or else you will not be able to save your stored process where it needs to be saved. Until this step is done, you can still test your stored process inside EG.
4) Once steps 1,2, and 3 are done, you could test your stored process in Web Report Studio.
Each one of these steps, taken alone, requires a more lengthy explanation. Step 1 & 3 might require the intervention of an administrative person at your site to make the data accessible to you or the repository folder accessible to you. Each step could potentially have an effect on the next step and how you accomplish what you want to do. Tech Support can guide you through the complete process, which is far better than getting piecemeal and/or incomplete answers through the forum -- because you ask me about connecting to Oracle data and my answer really is -- it depends. And we could go back and forth in numerous postings without a definitive answer and in the end, I'd still tell you that you needed to contact Tech Support.
To learn how to contact Tech Support, refer to:
http://support.sas.com/techsup/contact/index.html
Good luck!
cynthia
p.s. Once you have everything set up, stored processes are way cool! If you read all the way down here and if you would like to see an example of a program that splits one observation of 40 variables into two observations of 20 variables, then send an email to me at: cynthia.zender@sas.com and I will send you the code sample.