How can I add a total in bold, how can I make credits show a "-" sign

[Former Member]

I want to total this query in each colum that has a total and see credits as credits with a negative sign.. how can I do this?

DECLARE @CardName VARCHAR(100)

SELECT @CardName = T0.CardName FROM (SELECT CardName FROM OCRD) T0 WHERE T0.CardName = '[%0]'

Select T0.cardname as 'Customer',T0.Docdate as 'Month of Service', T0.docnum as 'Invoice Number', T0.[U_AIS_DVIInvName] as 'SF Invoice Name', T0.Taxdate as 'Date Sent',isnull(T0.U_AIS_DVISFSO,T3.U_AIS_DVISFSO) as 'SO#',T0.NumAtCard as 'PO#',

isnull(T0.U_AIS_DVIAdvNm,T3.U_AIS_DVIAdvNm) as 'Advertiser', isnull(T0.U_AIS_DVIOpptyNm,T3.U_AIS_DVIOpptyNm) as 'Campaign',T1.Dscription,T1.Quantity as 'Impressions',T1.Pricebefdi as 'CPM', T1.linetotal as 'Total',

T0.doctotal as 'Invoice Total',(T0.Doctotal-T0.Paidsum) as 'Balance after payment',T0.docstatus, 'Credit' AS TransactionType

from ORIN T0

left outer join RIN1 T1 on T0.docentry = T1.docentry

left outer join RDR1 T2 on T1.Baseentry = T2.docentry and T1.baseline = T2.linenum

left outer join ORDR T3 on T2.docentry = T3.docentry

where T0.[CardName]=@CardName

UNION ALL

Select T0.cardname as 'Customer',T0.Docdate as 'Month of Service', T0.docnum as 'Invoice Number', T0.[U_AIS_DVIInvName] as 'SF Invoice Name', T0.Taxdate as 'Date Sent',isnull(T0.U_AIS_DVISFSO,T3.U_AIS_DVISFSO) as 'SO#',T0.NumAtCard as 'PO#',

isnull(T0.U_AIS_DVIAdvNm,T3.U_AIS_DVIAdvNm) as 'Advertiser', isnull(T0.U_AIS_DVIOpptyNm,T3.U_AIS_DVIOpptyNm) as 'Campaign',T1.Dscription,T1.Quantity as 'Impressions',T1.Pricebefdi as 'CPM', T1.linetotal as 'Total',

T0.doctotal as 'Invoice Total',(T0.Doctotal-T0.Paidsum) as 'Balance after payment',T0.docstatus, 'Invoice' AS TransactionType

from OINV T0

left outer join INV1 T1 on T0.docentry = T1.docentry

left outer join RDR1 T2 on T1.Baseentry = T2.docentry and T1.baseline = T2.linenum

left outer join ORDR T3 on T2.docentry = T3.docentry

where T0.[CardName]=@CardName

[kothandaraman_nagarajan]

Hi,

Please close this thread if you got the answer by clicking correct/helpful answer.

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

If you got answer, please close the thread by marking helpful/correct answers.

Thanks & Regards,

Nagarajan

[Former Member]

Helpful correct answers

[kothandaraman_nagarajan]

Hi,

To close this thread need to mark legend "Correct answer" or Helpful answers at your right side of this forum.

Not by wording.

Thanks & Regards,

Nagarajan

[Former Member]

I do not see a legend.. so can you advise...

[kothandaraman_nagarajan]

Hi,

Can you see below images in my previous reply?

Click if correct answer, it answer is correct.

Thanks & Regards,

Nagarajan

[Former Member]

Thank you  I get it now.. Control at the same time you are clicking on the field.

[kothandaraman_nagarajan]

Hi,

Yes correct.....

Please close this thread if you got the answer by marking helpful or correct answers.

Thanks & Regards,

Nagarajan

[Former Member]

Not sure what you mean by ctrl+ click..  I run the report and see it on the screen now how do I see the total?

[kothandaraman_nagarajan]

Hi,

Please refer below screen shots:

ctrl + click at document header

Hope helpful.

[Former Member]

I think I answered my own question regarding the - sign. It only goes on the credits and I was entering it on both the invoices and credits. Second.. So now all I need to know is how to total the column. Thanks!

[Former Member]

Thank you all.. the question I have is how can I total the column on a query.

[Former Member]

Also when I add the - sign it adds it to all the lines not just the credits.

[kothandaraman_nagarajan]

Hi,

After running above query, use ctrl+ click at end column in query report.

[VamcParimisetti]

Hi Danielle,

It is not Possible to make the Values Bold or any other Formar in the Query.If you Want to Make the Letters as Bold Use Reporting Tools like,

     1.Query Print Layout Designer

     2.Crystal Reports

     3.XL Reporter

Hope Helpful,

Vamsi

[KennedyT21]

Hi Danielle Napoli...

For - sign add - before the field name...

In Query Pld you can use the Bold Option for the Total by default in query it is not Possible..

1. Create a Query in SAP Business One
2. Open the Query Manager
3. Find your Report
4. Highlight your report (single click)
5. Click on the Create Report button at the bottom of the window.
6. A window appears with the name of your query at the top of the window.
7. Window is divided into two parts: Top is existing reports, bottom is a list of the report templates.
8. Click on the User Report (System) template in the bottom part of the window.
9. Click the OK button.
10. You are returned to the Query Manager
    (At this point you are probably thinking that nothing happened, but it did).
11. Click on the Create Report button once more.
12. The Create User Report window opens again. Now in the top part (the box under Name) of the window you will see the name of the report you were trying to create. This indicates that the initial report was created successfully.
13. Now...Double Click on the the name of your query in that section and the Print Layout Designer (PLD) windows will open and allow you to modify the layout of the query information on the report.

After you have completed your changes to the layout and have saved your work, you can see the actual printed document by accessing the Query Manager, highlighting your report name, and clicking the print preview icon at the top of the screen.

Hope Helpful

Regards

Kennedy