Skip to Content

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

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

8 Answers

  • Posted on May 06, 2013 at 09:18 AM

    Hi,

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

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 09, 2013 at 02:55 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 09, 2013 at 10:05 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 09, 2013 at 01:58 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 09, 2013 at 02:31 PM

    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!

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 09, 2013 at 02:59 PM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 09, 2013 at 04:29 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 28, 2013 at 12:47 AM

    Hi,

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

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.