BAQ for Invoice Detail

Hi,
It is my mistake on the test, actually the BAQ is workingfine, just some mistake on the test data.

Problem solved.

Thanks.

--- In vantage@yahoogroups.com, "kw_foo" <kwfoo88@...> wrote:
>
> Do you mind to elaborate more on the running total with a condition formula?
>
> My BAQ as follow:
>
> Tables:
> -------
> InvcDtl
> InvcHead
> InvcMisc - aggregate enable
> MiscChrg - aggregate enable
>
> Links:
> ------
> InvcDtl - Inner Join with InvcHead (InvoiceNum)
> InvcDtl - Outer Join with InvcMisc (InvoiceNum & InvoiceLine)
> InvcMisc - Inner Join with MiscChrg (MiscCode)
> (tried to put in a filter in the MiscChrg table to include only SalesAnalysis = TRUE, but seem like not functioning, records in InvcMisc with MiscCode where SalesAnalysis still included in the TOTAL sum)
>
> Calculate Fields:
> MiscAmt - TOTAL(InvcMisc.MiscAmt)
>
> Please advice.
>
> --- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@> wrote:
> >
> > Yes, you can use Running Totals with a conditional formula.
> >
> > -bws
> >
> > --
> > Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
> > Â Â Â Â bspolarich@ ~ 734-864-5618 ~ www.advancedphotonix.com
> >
> >
> > -----Original Message-----
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of kw_foo
> > Sent: Friday, October 16, 2009 8:08 AM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Re: BAQ for Invoice Detail
> >
> > Hi,
> > I manage to find the solution on the Crystal report to add the Sum of a formula field to the chart for TopN purpose.
> >
> > Later I notice another problem on the MiscAmt which I use the aggregate function TOTAL to add up all Misc Amount for the invoice line, but I need to EXCLUDE the MiscCode which is not for Sales Analysis (MiscChrg->SalesAnalysis = FALSE). Is there any aggregate function can do conditional sum ?
> >
> > Please help.
> >
> > --- In vantage@yahoogroups.com, "kw_foo" <kwfoo88@> wrote:
> > >
> > > Hi,
> > > I am trying to generate a BAQ which show the total amount of each invoice line, which include the Misc Amt for that line.
> > >
> > > I able to create a calculation field:
> > > ItemAmt -> (InvcDtl.OurShipQty * InvcDtl.UnitPrice - InvcDtl.Discount)
> > >
> > > Also
> > > MiscAmt -> TOTAL(InvcMisc.MiscAmt) as a seperate field in the BAQ.
> > >
> > > When I am trying to create another calc field:
> > >
> > > TotalInvAmt -> ItemAmt + MiscAmt
> > >
> > > The TotalInvAmt always return <empty> ?? Both the ItemAmt & MiscAmt have no problem.
> > >
> > > The main purpose is to pass this field to Crystal Report to generate a chart group by Product Group which use the "Sum of TotalInvAmt" as the TopN base.
> > >
> > > I also trying to work on Crystal Report to add as a formula field, but not sure how to make it appear under the TopN base selection.
> > >
> > > Please help.
> > > Thanks in advance.
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
> > (1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
> > (2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
> > (3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
> >
>
Hi,
I am trying to generate a BAQ which show the total amount of each invoice line, which include the Misc Amt for that line.

I able to create a calculation field:
ItemAmt -> (InvcDtl.OurShipQty * InvcDtl.UnitPrice - InvcDtl.Discount)

Also
MiscAmt -> TOTAL(InvcMisc.MiscAmt) as a seperate field in the BAQ.

When I am trying to create another calc field:

TotalInvAmt -> ItemAmt + MiscAmt

The TotalInvAmt always return <empty> ?? Both the ItemAmt & MiscAmt have no problem.

The main purpose is to pass this field to Crystal Report to generate a chart group by Product Group which use the "Sum of TotalInvAmt" as the TopN base.

I also trying to work on Crystal Report to add as a formula field, but not sure how to make it appear under the TopN base selection.

Please help.
Thanks in advance.
Hi,
I manage to find the solution on the Crystal report to add the Sum of a formula field to the chart for TopN purpose.

Later I notice another problem on the MiscAmt which I use the aggregate function TOTAL to add up all Misc Amount for the invoice line, but I need to EXCLUDE the MiscCode which is not for Sales Analysis (MiscChrg->SalesAnalysis = FALSE). Is there any aggregate function can do conditional sum ?

Please help.

--- In vantage@yahoogroups.com, "kw_foo" <kwfoo88@...> wrote:
>
> Hi,
> I am trying to generate a BAQ which show the total amount of each invoice line, which include the Misc Amt for that line.
>
> I able to create a calculation field:
> ItemAmt -> (InvcDtl.OurShipQty * InvcDtl.UnitPrice - InvcDtl.Discount)
>
> Also
> MiscAmt -> TOTAL(InvcMisc.MiscAmt) as a seperate field in the BAQ.
>
> When I am trying to create another calc field:
>
> TotalInvAmt -> ItemAmt + MiscAmt
>
> The TotalInvAmt always return <empty> ?? Both the ItemAmt & MiscAmt have no problem.
>
> The main purpose is to pass this field to Crystal Report to generate a chart group by Product Group which use the "Sum of TotalInvAmt" as the TopN base.
>
> I also trying to work on Crystal Report to add as a formula field, but not sure how to make it appear under the TopN base selection.
>
> Please help.
> Thanks in advance.
>
Yes, you can use Running Totals with a conditional formula.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of kw_foo
Sent: Friday, October 16, 2009 8:08 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ for Invoice Detail

Hi,
I manage to find the solution on the Crystal report to add the Sum of a formula field to the chart for TopN purpose.

Later I notice another problem on the MiscAmt which I use the aggregate function TOTAL to add up all Misc Amount for the invoice line, but I need to EXCLUDE the MiscCode which is not for Sales Analysis (MiscChrg->SalesAnalysis = FALSE). Is there any aggregate function can do conditional sum ?

Please help.

--- In vantage@yahoogroups.com, "kw_foo" <kwfoo88@...> wrote:
>
> Hi,
> I am trying to generate a BAQ which show the total amount of each invoice line, which include the Misc Amt for that line.
>
> I able to create a calculation field:
> ItemAmt -> (InvcDtl.OurShipQty * InvcDtl.UnitPrice - InvcDtl.Discount)
>
> Also
> MiscAmt -> TOTAL(InvcMisc.MiscAmt) as a seperate field in the BAQ.
>
> When I am trying to create another calc field:
>
> TotalInvAmt -> ItemAmt + MiscAmt
>
> The TotalInvAmt always return <empty> ?? Both the ItemAmt & MiscAmt have no problem.
>
> The main purpose is to pass this field to Crystal Report to generate a chart group by Product Group which use the "Sum of TotalInvAmt" as the TopN base.
>
> I also trying to work on Crystal Report to add as a formula field, but not sure how to make it appear under the TopN base selection.
>
> Please help.
> Thanks in advance.
>




------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
Do you mind to elaborate more on the running total with a condition formula?

My BAQ as follow:

Tables:
-------
InvcDtl
InvcHead
InvcMisc - aggregate enable
MiscChrg - aggregate enable

Links:
------
InvcDtl - Inner Join with InvcHead (InvoiceNum)
InvcDtl - Outer Join with InvcMisc (InvoiceNum & InvoiceLine)
InvcMisc - Inner Join with MiscChrg (MiscCode)
(tried to put in a filter in the MiscChrg table to include only SalesAnalysis = TRUE, but seem like not functioning, records in InvcMisc with MiscCode where SalesAnalysis still included in the TOTAL sum)

Calculate Fields:
MiscAmt - TOTAL(InvcMisc.MiscAmt)

Please advice.

--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...> wrote:
>
> Yes, you can use Running Totals with a conditional formula.
>
> -bws
>
> --
> Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
> Â Â Â Â bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
>
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of kw_foo
> Sent: Friday, October 16, 2009 8:08 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: BAQ for Invoice Detail
>
> Hi,
> I manage to find the solution on the Crystal report to add the Sum of a formula field to the chart for TopN purpose.
>
> Later I notice another problem on the MiscAmt which I use the aggregate function TOTAL to add up all Misc Amount for the invoice line, but I need to EXCLUDE the MiscCode which is not for Sales Analysis (MiscChrg->SalesAnalysis = FALSE). Is there any aggregate function can do conditional sum ?
>
> Please help.
>
> --- In vantage@yahoogroups.com, "kw_foo" <kwfoo88@> wrote:
> >
> > Hi,
> > I am trying to generate a BAQ which show the total amount of each invoice line, which include the Misc Amt for that line.
> >
> > I able to create a calculation field:
> > ItemAmt -> (InvcDtl.OurShipQty * InvcDtl.UnitPrice - InvcDtl.Discount)
> >
> > Also
> > MiscAmt -> TOTAL(InvcMisc.MiscAmt) as a seperate field in the BAQ.
> >
> > When I am trying to create another calc field:
> >
> > TotalInvAmt -> ItemAmt + MiscAmt
> >
> > The TotalInvAmt always return <empty> ?? Both the ItemAmt & MiscAmt have no problem.
> >
> > The main purpose is to pass this field to Crystal Report to generate a chart group by Product Group which use the "Sum of TotalInvAmt" as the TopN base.
> >
> > I also trying to work on Crystal Report to add as a formula field, but not sure how to make it appear under the TopN base selection.
> >
> > Please help.
> > Thanks in advance.
> >
>
>
>
>
> ------------------------------------
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
>