How to set Formula in Data Export for Excel

V5 Question
1

I am designing report to export in Excel File. So that I want to set given excel formula

=IF(F2>0,“Yes”,“No”)

in Data Export code.

My Data export code is

[Report:1,1,1,1,1,1,1]
Sr.,T.ID,Customer,Rider,T.Total,Delivery Charges,Other,Balance
{REPORT TICKET DETAILS:
	<row-1>,
	T.TicketNumber,
	EN.Customers,
	TT.Rider,
	T.TotalAmount,
	CA.Delivery Charges,
	(EXCEL FORMULA),
	([T.TotalAmount]-[CA.Delivery Charges]),
:(DE=Delivery)
:"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"}
,,,,,,,=SUM(H2:H<row-1>)

Capture

2

not sure if it would work in a report but samba equivalent is a ternarry expression

Try something like;

[=fieldInQuestion>0?"Yes”:“No”]
Might need to play with quotes and how to call field within it.

3

Thanks @JTRTech I try as you tell me but could not get any result as I shown in attached screenshot that I capture before and after this code apply.

Before

Capture
Capture1161×547 44.2 KB

After

Capture1
Capture11153×317 16.8 KB

4

Obviously didn’t understand, fieldinquestion was reference to what you wanted to compair. Wasnt expected to work as is.
You’ll need tl play arround as not tried in reports before.

5

I just want this formula " =IF(F2>0,“Yes”,“No”) " to work when I export the excel file. Where “F2” is cell number of Excel sheet.

6

It wouldn’t be F2 though would it first of all. The row would obviously need to match as it does down.

7

I get what you mean, I believe there is a way to do it, but it wouldn’t be F2, it would be F2, F3, F4… depending on the row of the data.

8

Got it!

Replace (EXCEL FORMULA) with <row-1>

the format for that field is =IF(F{6}>0\,"Yes"\,"No")

So on your report, you can do like this:

[Report:1,1,1,1,1,1,1]
Sr.,T.ID,Customer,Rider,T.Total,Delivery Charges,Other,Balance
{REPORT TICKET DETAILS:
	<row-1>,
	T.TicketNumber,
	EN.Customers,
	TT.Rider,
	T.TotalAmount,
	CA.Delivery Charges,
	<row-1>,
	([T.TotalAmount]-[CA.Delivery Charges]),
:(DE=Delivery)
:{0},"{1}","{2}","{3}",{4},{5},=IF(F{6}>0\,"Yes"\,"No"),{7}}
,,,,,,,=SUM(H2:H<row-1>)

(I also removed quotes from number values so they go into Excel as a number and not a string)

Tested with my amended version (to work on my setup):

image
image1553×371 23.4 KB

3 Likes