"Fatal error" when using MySQL variable in query

[EmersonSavage]

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?

[Dan_PCE]

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

This fixed my issue immediately! 

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

[JasperD]

Great, that's it!

[v-haibl-msft · Accepted solution]

@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

[JasperD]

There is a solution: check post below

[v-sihou-msft]

@EmersonSavage

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

Regards,

[Chihiro]

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.