0

I build a query in AOT where there are many data sets joined to get some data.

For Example: i have following table.

Vendor ID  -  Trans Date  -  Invoice Amount  -  Invoice ID  -     Status    -  NTN No.
A001       -   1/1/2000  -     82,000      -    US-102   -      Reg      -  123456
A001       -   1/1/2000  -     10,000      -    US-101   -      NTN#     -  NTN12341
A001       -   1/1/2000  -     82,000      -    US-102   -     UnReg     -  123456

What i want to ask is that how can i merge STATUS and NTN nO. requirement is i want to show the status whether it is Reg aur UnReg I dont want to show NTN# in status. and in front of status i want to display NTN numbers. Like this

Vendor ID  -  TransDate  -  InvoiceAmount  -  InvoiceID  -     Status    -  NTN No.
A001       -   1/1/2000  -     82,000      -    US-102   -      Reg      -  NTN12341
A001       -   1/1/2000  -     10,000      -    US-101   -               -  NTN12342
A001       -   1/1/2000  -     82,000      -    US-102   -     UnReg     -  NTN12343
tshepang
  • 12,111
  • 21
  • 91
  • 136
RizwanHaider
  • 1
  • 2

2 Answers2

0

in your "Status" textbox do:

=iif(Fields!Status.Value = "NTN#", "", Fields!Status.Value)

That will blank out the field when the value is NTN, and otherwise just leave them alone.

In your NTN No. field, do:

=iif(InStr(Fields!NTNNo.Value, "NTN") = true, Fields!NTNNo.Value, ("NTN"+Fields!NTNNo.Value))

That uses InStr() to check if the NTNNo field contains the string "NTN". If it does, then it just uses the existing value. Otherwise, it appends "NTN" to the front of the string, and uses that.

*Typed up on a phone, watch for syntax errors

kyzen
  • 1,579
  • 1
  • 9
  • 13
0

Voucher Sales tax code Source Name Status NTN Invoice No Invoice date Invoice amount Sales tax rate Sales tax amount Total amount VIV14-0000049 Extra Tax Purchase order Pakistan Poultry 123456 INV/001/01 03/13/2014 12716 2% 220.00 12936 VIV14-0000049 GST Goods Purchase order Pakistan Poultry 123456 INV/001/01 03/13/2014 12716 17% 1,870.00 14586 VIV14-0000049 WHT Sales Purchase order Pakistan Poultry 123456 INV/001/01 03/13/2014 12716 -20% -374.00 12342 VIV14-0000049 Extra Tax Purchase order Pakistan Poultry NTN987644 INV/001/01 03/13/2014 12716 2% 220.00 12936 VIV14-0000049 GST Goods Purchase order Pakistan Poultry NTN987644 INV/001/01 03/13/2014 12716 17% 1,870.00 14586 VIV14-0000049 WHT Sales Purchase order Pakistan Poultry NTN987644 INV/001/01 03/13/2014 12716 -20% -374.00 12342

                                            79,728.00

here is the actual problem.

Voucher Sales tax code VEND ID Name Status NTN Invoice No Invoice date Invoice amount Sales tax rate Sales tax amount Total amount VIV14-0000049 Extra Tax Pakistan Poultry Reg NTN987644 INV/001/01 03/13/2014 12,716 2% 220.00 12,936 VIV14-0000049 GST Goods Pakistan Poultry Reg NTN987645 INV/001/01 03/13/2014 12,716 17% 1,870.00 14,586 VIV14-0000049 WHT Sales Pakistan Poultry Reg NTN987646 INV/001/01 03/13/2014 12,716 -20% -374.00 12,342

just because of the duplication there occure problem in total ammount. and i want my report to be like this. in front of REG or UNREG status i want to show NTN number.

RizwanHaider
  • 1
  • 2