cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

@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
Microsoft
Microsoft

@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

There is a solution: check post below

v-sihou-msft
Microsoft
Microsoft

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!