locked
Parameterized Sproc Calls from Excel Chart? RRS feed

  • Question

  • I would like to have a chart in excel which pulls data from a sproc on an oracle database.

    I would also like to have a filter UI which contains several drop downs and a refresh button.

    When the user selects values from the drop downs and clicks Refresh, I want the chart to call the sproc passing the input parameter values chosen in the drop downs.

    For example:

    The dropdown could contain a list of food types. Fruit, Veggies, Meat, etc... If the user chooses "Fruit" and clicks "Refresh" then the chart would call the sproc passing an input parameter value of "Fruit" and receive a list like "Apple, Pear, Plum..."

    Is this possible?

    Furthermore, I want this chart and the filter/refresh UI to be hosted on a SharePoint site, so that this SharePoint page becomes a sort of "self service chart portal". Is that possible?

    Thank you for letting me know if this will work and pointing me to any resources to make this a reality.


    Thursday, March 8, 2018 2:30 PM

All replies

  • Hello Infin8L00perZ0ne,

    >>When the user selects values from the drop downs and clicks Refresh, I want the chart to call the sproc passing the input parameter values chosen in the drop downs

    Yes, we could do it. You could use VBA to pass the parameter to call the sproc and return the result as the chart source data and then refresh the chart to show new data.

    For more details, you could refer to below links.

    How do I call an Oracle stored procedure from an Excel VBA script?

    Code to fetch data from oracle to excel and send the data which has same cell name to different sheets in excel

    >>Furthermore, I want this chart and the filter/refresh UI to be hosted on a SharePoint site, so that this SharePoint page becomes a sort of "self service chart portal". Is that possible?

    We support one issue for one thread and it seems more like a SharePoint issue, so I would suggest you post a new thread for this issue in a SharePoint forum. Thanks for understanding,

    Best Regards,

    Terry 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 9, 2018 1:44 AM
  • Once you have a connection established to the database in your spreadsheet, it's just a matter of setting the command text to what you want.  Here is sample code that I use in an Access application to refresh the data in an Excel spreadsheet by calling a SQL stored procedure:

    objExcel.Workbooks("SSName.xlsx").Connections("ConnName").OLEDBConnection.CommandText = Array("EXEC dbo.ProcName @Yr = " & CStr(Me.Yr) & ", @FldNo = " & CStr(Me.FieldID) & ", @Crop = " & CStr(Me.TissueCropID) & "")

    objExcel.Workbooks("SSName.xlsx").Connections("ConnName").Refresh

    If your CommandText works in Oracle, the same code should work for you.  This code would just be slightly modified if you want to do the same thing from within the Excel spreadsheet.

    Friday, April 19, 2019 7:30 PM