Hi,
It is my mistake on the test, actually the BAQ is workingfine, just some mistake on the test data.
Problem solved.
Thanks.
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
> >
>