Convert Scoop Query to MS Query

Convert Scoop Query to MS Query

Open Scoop on the Excel worksheet containing the query

Click the SQL button

Keep the SQL open

Open a new worksheet

Click on the Data ribbon, then From Other Sources and then Microsoft Query from the drop down menu.

Click WPRD* on the Databases tab and click ok

Enter your Oracle User Name and Password.  Type whprod in the Server field and click ok

In the Query Wizard, highlight any available table and add it to the column by pressing the > key.  Click next.

Click Next

Click Next

Check View data or edit query in Microsoft Query and Finish

Click on SQL in the Microsoft Query

Highlight all of the text displayed in the SQL statement box and then hit the delete button to remove the SQL statement

Then go back to the Scoop SQL query, highlight everything and right click and select copy

Go back to the Microsoft Query and paste in the new SQL by right clicking and selecting paste.   Then Highlight the first row after “Select” and press the delete button, then click ok.

The query is now copied into Microsoft query.  Click on File and then return data to excel, then select table and select where to put the data.  Click ok.

To update the query

Right click anywhere there is data in the query.  Then select table and then edit query.  Press ok at the message below.

Update the values in the Criterial Field section by double click on the value

Insert the desired value or click on Values to search, then press ok.

Then File and return data to excel. Refresh pivot tables as needed.

To Add Parameters

Right click anywhere there is data in the query.  Then select table and then edit query.  Press ok at the message below.

In the value section under Criteria Field replace the existing value with brackets and desired descriptive language - ex. [Name of the Field] and hit enter.  Enter the value at the prompt.  Note:  The name can be anything descriptive.

Then File and return data to excel.  To add parameter entry box on a new sheet, name a blank tab to the workbook and rename it “Start” or something descriptive.  Enter the description of the parameter on the start tab.

Then go back to the query and right click anywhere in the data.  Click on table, then parameters.  Click Get the value from the following cell, check the box to refresh automatically and then press the spreadsheet button.

Then go to the “Start” tab and click on the cell to enter the value and press enter.

Repeat for all desired parameters. Refresh pivot tables as needed.