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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EmersonSavage
Frequent Visitor

"Fatal error" when using MySQL variable in query

I am trying to create a visualization which shows the cumulative contributions to a portfolio over time and its market value at given dates.

 

I'm connecting to a MySQL database.  I initiate the connection normally by clicking New Source, entering the database credentials and my query, which includes a user-defined variable, @csum.  It appears that PBI does not recognize the user defined variable as it is "outside the session."  When I try to run the below, I receive the error, "MySQL: Fatal error encountered during command execution."

 

 

set @csum := 0;
select t.*, p.client_id, p.code, p.report_name1, if(t.type = 'WITHDR' or t.type = 'DELIV', -t.net_amount, t.net_amount) as Settlement_Amount, (select (@csum := @csum + Settlement_amount)) as cumulative_sum

 

from transaction t join portfolio p on t.portfolio_id = p.id

 

where t.trade_date > '2013-12-31' and (t.type = 'CONTR' OR t.type = 'WITHDR' OR t.type = 'ADD' OR t.type = 'DELIV') and t.sub_code <> 'ME' and p.code = 'REDACTED'

 

ORDER BY t.transaction_number

 

Does anyone know of a workaround for this?

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@EmersonSavage

 

I’ve got response from the Product Team.

 

This is outside the control of Power BI. We rely on the MySQL ADO.NET driver to correctly execute this code. It appears that in this case, it does not work.

 

Best Regards,
Herbert

View solution in original post

6 REPLIES 6
Dan_PCE
Regular Visitor

Yes! Workaround is to change @csum to @'csum'

 

This fixed my issue immediately! 

 

Kudos to https://stackoverflow.com/a/45605184/10089530

Great, that's it!

v-haibl-msft
Employee
Employee

@EmersonSavage

 

I’ve got response from the Product Team.

 

This is outside the control of Power BI. We rely on the MySQL ADO.NET driver to correctly execute this code. It appears that in this case, it does not work.

 

Best Regards,
Herbert

There is a solution: check post below

v-sihou-msft
Employee
Employee

@EmersonSavage

 

I reproduced same issue as you described. We have reported it interanlly.

 

11.PNG

 

22.PNG

 

33.PNG

 

Regards,

Chihiro
Solution Sage
Solution Sage

I'd imagine you can write query without using MySQL variable, using correlated query for cumulative sum column.

 

See link for some sample (Tom H's post).

https://stackoverflow.com/questions/439138/running-total-by-grouped-records-in-table

 

Another option may be to do cumulative sum calculation in PowerBI and not at query stage.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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