flemingg62
asked on
Reading a Cell values in a SQL import statement in Power Pivot for Excel Link to filter data from SQL DB
I have an Excel data model that reads data from a SQL DB, the SQL data contains data from many projects. I have got it to work where I manually type in the number i.e. WHERE [Survey-Data].[Adept Survey UID] = 398
but what I want to be able to do is say type a Project number (Numeric) in a cell (Say cell F3 in Sheet Test) and then only pull that data from the SQL DB for the Project Number in that cell
SELECT
[Survey-Data].[Adept Survey UID]
,[Survey-Data].[Accordant SUS UID]
,[Survey-Data].DataUID
,[Survey-Data].[Survey Time]
,[Survey-Data].[Report Time]
FROM
[Survey-Data]
WHERE [Survey-Data].[Adept Survey UID] = Cell F3 in Sheet "Test"
but what I want to be able to do is say type a Project number (Numeric) in a cell (Say cell F3 in Sheet Test) and then only pull that data from the SQL DB for the Project Number in that cell
SELECT
[Survey-Data].[Adept Survey UID]
,[Survey-Data].[Accordant SUS UID]
,[Survey-Data].DataUID
,[Survey-Data].[Survey Time]
,[Survey-Data].[Report Time]
FROM
[Survey-Data]
WHERE [Survey-Data].[Adept Survey UID] = Cell F3 in Sheet "Test"
Last Comment
ASKER
Thanks, I'll give that a go
If you are not progress with that I can send you an example.
ASKER
Hi, if you can sent me a sample. I got as far as creating a parameter called "fParameters" I think it works (in a table with 1 column and 1 row.
But how do I get it in to my SQL?
SELECT
[Survey-Data].[Adept Survey UID]
,[Survey-Data].[Accordant SUS UID]
,[Survey-Data].DataUID
,[Survey-Data].[Report Time]
FROM
[Survey-Data]
WHERE [Survey-Data].[Adept Survey UID] = ????( fParameters("Parameters",1)&"\"&fParameters("Parameters",2))
But how do I get it in to my SQL?
SELECT
[Survey-Data].[Adept Survey UID]
,[Survey-Data].[Accordant SUS UID]
,[Survey-Data].DataUID
,[Survey-Data].[Report Time]
FROM
[Survey-Data]
WHERE [Survey-Data].[Adept Survey UID] = ????( fParameters("Parameters",1)&"\"&fParameters("Parameters",2))
Here is the sample file for this solution.Book1.xlsx
If you have questions then contact me in EE Live or in Private Message.
If you have questions then contact me in EE Live or in Private Message.
ASKER
Hi I have sent a private message
Ok. I am there in LIVE.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Hi,
it is possible but you have some problems with your saved credentials to your database.
Then you can parametrize your query in PowerQuery.
It is important to build a PowerPivot model on top of a PowerQuery query and not connect to the datasource directly from PowerPivot.
it is possible but you have some problems with your saved credentials to your database.
Then you can parametrize your query in PowerQuery.
It is important to build a PowerPivot model on top of a PowerQuery query and not connect to the datasource directly from PowerPivot.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Then you can import it as a source in Power Query.
Then convert that source to a value parameter.
Then finally you can use it as a parameter in your original query.