cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simonb Frequent Visitor
Frequent Visitor

Using Paramters to define ODBC connection

Hi,

 

Wondering if there's a way to use a parameter to define the ODBC Data source. I want to connect via CData's ODBC driver for Xero. This provides access to multiple tables via SQL statements. I have parameters for the ConnectionString, and the SQL filters. The SQL filter works fine, but if I swap in a parameter for the COnnectionString it reads the parameter reference as the ConnectionString and throws an error.

 

This works:

= Odbc.Query("dsn=BFA Sys", "Select * from TrialBalance where date=[PeriodEnding]")

 

would like it to be:

= Odbc.Query("dsn=[Connection]", "Select * from TrialBalance where date=[PeriodEnding]")

 

where [Connection] is a parameter returning the connection source, but this gives an error saying [Connection] is not a valid source.

Is there some syntax I'm missing?

Thanks!

 

5 REPLIES 5
Super User
Super User

Re: Using Paramters to define ODBC connection

@simonb I believe it would be like below, assuming your parameter name is Connection

 

 

= Odbc.Query("dsn=" & [Connection] & ", "Select * from TrialBalance where date=[PeriodEnding]")





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





simonb Frequent Visitor
Frequent Visitor

Re: Using Paramters to define ODBC connection

@parry2k thanks,  that returns a Token Comma Expected error highlighting Select

 

 I've tried a few iterations but can't find one that works...any thoughts?

 

Thanks,

Simon

Super User
Super User

Re: Using Paramters to define ODBC connection

@simonb sorry I had typo in my message, try this

 

= Odbc.Query("dsn=" & [Connection] & ", Select * from TrialBalance where date=[PeriodEnding]")





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





simonb Frequent Visitor
Frequent Visitor

Re: Using Paramters to define ODBC connection

@parry2k 

 

That changes the error to:

 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

 

Sorry!

Highlighted
klinejordan Frequent Visitor
Frequent Visitor

Re: Using Paramters to define ODBC connection

I think this will work:

= Odbc.Query("dsn=" & Connection & "", Select * from TrialBalance where date=[PeriodEnding]")

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 267 members 2,796 guests
Please welcome our newest community members: