Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JayJay11
Resolver II
Resolver II

Problem with variables and dynamic API query

Hello all,

 

so I am trying to use OData with a specific URL to retrieve data from SAP HANA. The URL string that I want to send looks as the following:

 

$select=CompanyCode,AccountingDocument,Ledger,GLRecordType,ControllingArea,ChartOfAccounts,GLAccount,BusinessTransactionType,CostCenter,ProfitCenter,FunctionalArea,Segment,PartnerCompany,GlobalCurrency,AmountInGlobalCurrency,BaseUnit,Quantity,DebitCreditCode,FiscalPeriod,FiscalYearVariant,FiscalYearPeriod,PostingDate,DocumentDate,AccountingDocumentType,PostingKey,OriginObjectType,GLAccountType,DocumentItemText,Product,Supplier,Customer,OffsettingAccount,WBSElementInternalID,ProjectInternalID&$filter=PostingDate gt datetime'2024-01-31T00:00:00'

 

When I put this static string in the query box in the copy action, everything works fine.

 

However, I want to make the filter argument (datetime) dynamic using a variable. To generate the current datetime, I use the activity Set variable and use the following expression:

 

@formatDateTime(utcnow(), 'yyyy-MM-ddTHH:mm:ss')

 

The first problem is, that when I run the pipeline and inspect the variable, it is returned as the following:

 

{
	"name": "vDateFrom",
	"value": "2024-02-21T07:11:39Z"
}

 

So the pipeline adds the "Z" at the end, although I clearly stated the shortable format as the desired output. Is this a bug?

 

Further, how can I use then this variable as the query? So far I am only getting errors. Even when I just try the following:

 

concat('$select=CompanyCode,AccountingDocument,Ledger,GLRecordType,ControllingArea,ChartOfAccounts,GLAccount,BusinessTransactionType,CostCenter,ProfitCenter,FunctionalArea,Segment,PartnerCompany,GlobalCurrency,AmountInGlobalCurrency,BaseUnit,Quantity,DebitCreditCode,FiscalPeriod,FiscalYearVariant,FiscalYearPeriod,PostingDate,DocumentDate,AccountingDocumentType,PostingKey,OriginObjectType,GLAccountType,DocumentItemText,Product,Supplier,Customer,OffsettingAccount,WBSElementInternalID,ProjectInternalID&$filter=PostingDate gt datetime', '2024-01-31T00:00:00')

 

Only when I just put the string as stated above (without any functions) the data preview works.

 

pipelineproblem1.png

 

What am I misunderstanding here?

1 ACCEPTED SOLUTION

Again, this is not producing what I want, as it is adding things at the end of the string.

 

I solved it like that:

 

@concat('&$filter=PostingDate gt datetime''',substring(variables('vReferenceDateTime'), 0, 10), 'T00:00:00''')

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi @JayJay11 ,

Thanks for using Fabric Community.

Can you please try this -

@formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss zzz')

 

Output
{ "name": "Change Date format", "value": "2024-01-31T22:26:37 +00:00" }

Hope this is helpful. Please let me know incase of further queries.

Hi @Anonymous thank you for supporting. Though I am not sure how this is solving it, as this is again not the desired format. But let's nevermind that for a moment, I have a general problem understanding how variables work.

 

I try the following:

pipelineproblem2.png

 

So with the first activity, I create a string. That string I store in a variable, which I then want to use in my copy action as following:

 

pipelineproblem3.png

 

This pipeline should only run about 20 seconds, which is the case if I put the string directly to the Query without using a variable. However, when I use a variable, the pipeline runs much much longer, I had to stop it after 15 minutes.

 

What am I doing wrong here?

Anonymous
Not applicable

Hi @JayJay11 ,

I have tried both the ways using Set Variable and normal but didn't find any difference in exceution.

With Set Variable:

vgchennamsft_1-1708506975737.png


Without Set Variable:

vgchennamsft_0-1708506635276.png


Inorder to debug please check the output of Set Variable -

vgchennamsft_3-1708507248286.png

Can you please check your syntax?

vgchennamsft_4-1708507354229.png

Correct Syntax:

@concat('sales__1_test','1')

 

@Anonymous thank you - actually adding the "@" solved it. Great!

 

Now only the problem with the datetime. How can I get exactly the format "2024-02-21T00:00:00" as a string starting from the function utcnow()? The pipeline always adds this "Z" at the end, why?

Anonymous
Not applicable

Hi @JayJay11 ,

 

@formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss zzz')

 



vgchennamsft_0-1708509521040.png

 

vgchennamsft_1-1708509585310.png


Can you check this format and let me know if that works?

Again, this is not producing what I want, as it is adding things at the end of the string.

 

I solved it like that:

 

@concat('&$filter=PostingDate gt datetime''',substring(variables('vReferenceDateTime'), 0, 10), 'T00:00:00''')
Anonymous
Not applicable

Hi @JayJay11 ,

Glad to know that your query got resolved. Please continue using Fabric Community for your further queries.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

November Update

Fabric Monthly Update - November 2024

Check out the November 2024 Fabric update to learn about new features.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.