locked
PowerPivot with accept window at start up RRS feed

  • Question

  • Hi,

    I have an idea of a project using PowerPivot. I will create a excel file which will get external data from an oracle database using a sql. When I create the sql I use, let say, gor_date>='&DATE'  so that the script will ask me the starting date condition. The problem is that when I create such of sql and I save it I get this error:

    The refresh operation failed because the source data base or the table does not exist, or because you do not have access to the source

     

    More Details:

    OLE DB or ODBC error.

    An error occurred while processing the 'Query' table.

    The operation has been cancelled.

    If I use a date like '01-JAN-2011' the script works.
    The purpose of this project is to use a PowerPivot excel file with Charts instead of using a database application and downloading data into an excel file and then making charts. So using one minute instead of half of hour. 
    Thx

    Monday, August 15, 2011 1:17 PM

Answers

  • Nop,

    You create a parameter table like CREATE TABLE PARAM (ParamDate DATETIME) and then create a view which makes something like SELECT * FROM SALES WHERE SaleDate = (SELECT ParamDate FROM Param).

    As soon as you update the Param table, the view will return the sales of that date and, refreshing PowerPivot, will show the desired result.

    Clearly, all this need to be applied to your specific scenario


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by Challen Fu Wednesday, August 24, 2011 7:33 AM
    Thursday, August 18, 2011 12:08 PM

All replies

  • You cannot use parameters in SQL queries inside PowerPivot.

    What you can do is to load data from a view which uses another table to filter your data. Your script will update the filter table, thus updating the view content. After having done that, you can reload data inside PowerPivot and you will get the filtered view.

    Quite tricky, but should work.


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    Wednesday, August 17, 2011 3:33 PM
  • So maybe I should create another excel file that will have a sql script inside, and this script will be the database for the powerpivot file. That's what you are thinking?
    Thursday, August 18, 2011 10:59 AM
  • Nop,

    You create a parameter table like CREATE TABLE PARAM (ParamDate DATETIME) and then create a view which makes something like SELECT * FROM SALES WHERE SaleDate = (SELECT ParamDate FROM Param).

    As soon as you update the Param table, the view will return the sales of that date and, refreshing PowerPivot, will show the desired result.

    Clearly, all this need to be applied to your specific scenario


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by Challen Fu Wednesday, August 24, 2011 7:33 AM
    Thursday, August 18, 2011 12:08 PM
  • Thanks for the idea. Maybe I understand now. I have an idea:

    1. Create the parameter table on the database:

    DROP TABLE TTT_DATEPAR;

    COMMIT;

    CREATE TABLE TTT_DATEPAR

    (

     STARTDATE,

    ENDDATE

    )

    COMMIT;

    INSERT INTO  TTT_DATEPAR

     VALUES (FROMDATTE, TODATE)

    COMMIT;

     

    2. Create a excel file that will start with a windows where the user will type the FROMDATTE and the TODATE parameters. (this will gonna be the hard part because I don't know VBA, but I will find how to do create an oracle sql integrated in a macro that will user a start up window).

     

    3. Create the PowerPivot table that will run the sql that I want. This sql will have this condition among others:

     where

    DATE BETWEEN (SELECT STARTDATE FROM TTT_DATEPAR) AND (SELECT ENDDATE FROM TTT_DATEPAR).

    So finaly the user will just open the xlsx file that will ask the date values, and after that will just update the powerpivot table.

    I will come back after I finish the vba script.




    Thursday, August 18, 2011 1:11 PM