USING PARAMETERS IN NATIVE SQL QUERY

[Syndicate_Admin]

Dear Community, I have the following query.

I have a native SQL server query which has a date parameter and I need to be able to automate it from power query.

I share the original query that feeds on customer collection data from Sap B1:

DECLARAR @cont INT

DECLARE @FECHAHASTA DATETIMESET @cont = (SELECCIONE TOP 1 T9. TransId DE [dbo]. [JDT1] T9 DONDE T9. RefDate <='[%0]')

SET @FECHAHASTA = '[%0]'

SELECCIÓN
	T0. CódigoTarjeta,
	T0. Nombre de la tarjeta,
	CASO
		CUANDO T3. DebHab = 'D' ENTONCES Sum(T1. Debit-T1.Credit-T3.ReconSum)
		CUANDO T3. DebHab = 'C' ENTONCES Sum(T1. Debit-T1.Credit+T3. ReconSum)
		ELSE Suma (T1. Débito-T1. Crédito)
	FIN 'Saldo',
	CASO
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) <= 30 y T3. DebHab = 'D' luego Sum(T1. Debit-T1.Credit-T3.ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) <= 30 y T3. DebHab = 'C' luego Sum(T1. Debit-T1.Credit+T3. ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) <= 30 luego Sum(T1. Debit-T1.Credit)
	hasta '0-30 días',

CASO
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 31 Y 60 y T3. DebHab = 'D' luego Sum(T1. Debit-T1.Credit-T3.ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 31 Y 60 y T3. DebHab = 'C' luego Sum(T1. Debit-T1.Credit+T3. ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 31 Y 60 luego Sum(T1. Debit-T1.Credit)
	fin '31-60 días',

CASO
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 61 Y 90 y T3. DebHab = 'D' luego Sum(T1. Debit-T1.Credit-T3.ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 61 Y 90 y T3. DebHab = 'C' luego Sum(T1. Debit-T1.Credit+T3. ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 61 Y 90 luego Sum(T1. Debit-T1.Credit)
	fin '61-90 días',

CASO
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 91 Y 120 y T3. DebHab = 'D' luego Sum(T1. Debit-T1.Credit-T3.ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 91 Y 120 y T3. DebHab = 'C' luego Sum(T1. Debit-T1.Credit+T3. ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) ENTRE 91 Y 120 luego Sum(T1. Debit-T1.Credit)
	fin '91-120 días',

CASO
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) > 120 y T3. DebHab = 'D' luego Sum(T1. Debit-T1.Credit-T3.ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) > 120 y T3. DebHab = 'C' luego Sum(T1. Debit-T1.Credit+T3. ReconSum)
		CUANDO DATEDIFF(DAY,T1. RefDate,@FECHAHASTA) > 120 luego Sum(T1. Debit-T1.Credit)
	Fin '+120 días'

DESDE dbo. OCRD T0
	UNIÓN INTERNA dbo. JDT1 T1 EN T1. ShortName = T0. CardCode
	UNIÓN INTERNA dbo. OJDT T4 EN T4. TransId = T1. TransId
	UNIRSE A LA IZQUIERDA (
		SELECCIÓN
			X0. Nombre corto 'SN',
			X0. TransId 'TransId',
			SUMA(X0. ReconSum)'ReconSum',
			X0. IsCredit 'DebHab',
			X0. TransRowId 'Línea'
		DESDE dbo. ITR1 X0
			UNIÓN INTERNA dbo. OITR X1 EN X1. ReconNum = X0. ReconNum
		DONDE X1. ReconDate <= @FECHAHASTA AND X1. CancelAbs = ''
		GRUPO POR X0. Nombre corto, X0. TransId, X0. IsCredit, X0. TransRowId
	) T3 ON T3. TransId = T1. TransId AND T3.SN = T1. ShortName AND T3. Linea = T1. Line_ID

DÓNDE
	T0. TipoTarjeta = 'C' Y
	T1. RefDate <= @FECHAHASTA Y
	( CASO
			CUANDO T3. DebHab = 'D' ENTONCES (T1. Debit-T1.Credit-T3.ReconSum)
			CUANDO T3. DebHab = 'C' ENTONCES (T1. Debit-T1.Credit+T3. ReconSum)
		DE LO CONTRARIO (T1. Débito-T1.Crédito)
	FIN) != '0'

GRUPO POR T0. CódigoTarjeta,T0. NombreTarjeta,T3. DebHab,T3. DebHab,T1. RefDate
ORDENAR POR T0. NombreTarjeta

By running it in power query tuver to replace [%0] by a date (31-05-2022) to be able to visualize it.

The idea is to be able to automate it with parameters within power query, I don't know if you understand my problem.

Waiting for your help, he bids farewell cordially,

diego

[v-yalanwu-msft]

Hi, @Syndicate_Admin ,

. Generally speaking we can reference parameters via:

• Data Source
• Filter Rows
• Keep Rows
• Remove Rows
• Replace Rows

1-Click “New”

2-Type a name for the parameter

3-You can also write a description

https://biinsight.com/power-bi-desktop-query-parameters-part-1/

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/power-bi-introduction-workin...

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hello to clarify a little my doubt, so I was putting together the query:

I was occupying the next function in the advanced power query editor replacing [%] with "&Text.From(Origendinamico)&", but when loading the data my brings the base but without values, they could indicate that I am doing wrong.

Your help is appreciated.

Diego

[Syndicate_Admin]

I think the inconsistency is in the format of the date, have you tried to place that same date structure that you have stored in the Origendinamico parameter directly in the query? If it doesn't bring you data, you should check the exact format used by the database engine or perform a field conversion to match the values.