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

Need help with parameter in SQL statement

Hi everybody,

 

I have a simple statement. In the statement below Search is the parameter I made. When I fill custom criteria the statement works fine. When I change the custom criteria it also works fine. But when I enter the parameter search, nothing happings. Tried a lot of solutions, youtube, forums etc. but i can't simply figured out what i'm doing wrong.

 

SELECT [PointName]
            ,[PointID]
            ,[PointSliceID]
            ,[UTCDateTime]
            ,[ActualValue]
FROM [DataBase].[dbo].[RawAnalog]
WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())
AND PointName LIKE '%Search%'

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Need help with parameter in SQL statement

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Need help with parameter in SQL statement

Create a measure like this and add it to table or matrix along with [PointName],[PointID],[PointSliceID],[UTCDateTime]

new ActualValue =
calculate(sum(RawAnalog[ActualValue]),RawAnalog[UTCDateTime] >= date(year(today())month(today())-1,day(today())),
		search("Search",RawAnalog[PointName] ,1,0)>0)

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Need help with parameter in SQL statement

Thanks for your reply, maybe I am not clear enough. The meaning is to import only the data that is needed, so search must contains some characters from Pointname. When search contains '%United States%' only pointnames with United States must be imported. All this in a template. When I only use the IP address as a parameter all works fine, by adding the second parameter (Search) it shows up by making a connection but doesn't do the job well.

 

 
 
 
Highlighted
Super User IV
Super User IV

Re: Need help with parameter in SQL statement

So you have a Query Editor parameter setup like the screen shot below?

 

image.png


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Need help with parameter in SQL statement

Following is what I have;

 

SELECT [PointName]
,[PointID]
,[PointSliceID]
,[UTCDateTime]
,[ActualValue]
FROM [JCIHistorianDB].[dbo].[RawAnalog]
WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())
AND PointName LIKE '%NCE26%'

 

This works fine (with the parameter SQL IP Address, but the NCE26 It must be replaced by a parameter. When connecting to the database the follwing screen appears. (Search is the name of the parameter)

Screenshot_1.jpg

 

 

Highlighted
Super User IV
Super User IV

Re: Need help with parameter in SQL statement

OK, assuming you have the parameter setup correctly, can you share at least the first few lines of your Power Query M code? Used Advanced Editor in Power Query, I mainly need to see your Source line.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Need help with parameter in SQL statement

Highlighted
Frequent Visitor

Re: Need help with parameter in SQL statement

let
Source = Sql.Database(#"SQL IP Address", "JCIHistorianDB", [Query="SELECT [PointName]#(lf) ,[PointID]#(lf) ,[PointSliceID]#(lf) ,[UTCDateTime]#(lf) ,[ActualValue]#(lf) FROM [JCIHistorianDB].[dbo].[RawAnalog]#(lf) WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())#(lf) AND PointName LIKE '%NCE26%'"])
in
Source

 

Complete copy of the source line

Highlighted
Frequent Visitor

Re: Need help with parameter in SQL statement

Power Query M code. Thanks for sharing the posts, the red-gate link gives me the solution!

Highlighted
Super User IV
Super User IV

Re: Need help with parameter in SQL statement

Right, you should have just had to change the code to:

 

let
Source = Sql.Database(#"SQL IP Address", "JCIHistorianDB", [Query="SELECT [PointName]#(lf) ,[PointID]#(lf) ,[PointSliceID]#(lf) ,[UTCDateTime]#(lf) ,[ActualValue]#(lf) FROM [JCIHistorianDB].[dbo].[RawAnalog]#(lf) WHERE UTCDateTime > DATEADD(MONTH, -1, GETDATE())#(lf) AND PointName LIKE '%Search%'"])
in

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors