Hello guys,
is there any way to display unique records from same (single table) ? Actualy this table is User defined table…Any built in function like DISTINCT…
You can have the dashboard ask for a criteria to be entered using the
parameter criteria. Then when you do a refresh a pop-up Bax with ask for
the criteria entry.
Is that what you are after?
Brad
Prakash,
I think Key1, key2…key5 fields on UD tables are used to enter unique data. May be you could use key1 to enter current date and time to get unique records.
Binita
@BoostERP : Is there any direct way, so i can display only unique records? i dont want to ask for criteria…
Binita,
Sorry, it is base table, not UD table…and actually i picked data which i needed from different tables and dumped to this table(which and only table ,that i am using in BAQ)…which ready to display in Dashboard. But problem is,there are so many duplicate records…I cannot distinguish them in BAQ and how i can filter them ? Because ,there is not inbuilt functionality to avoid displaying duplicate records…
Prakash,
Which tables are you using for the BAQ? Generally, tables have field like seqnum, linenum…to separate the records.
If not that then on the BAQ, there is “table list” tab at the lower left side, there on the qualifier you can select may be first or last option instead of each option. hope that helps.
If you are on SQL you can create a SQL View using SELECT DISTINCT and build an External BAQ that just uses that SQL View. It’s typically a lot faster than using a BAQ as well.
John
@speaktobinita : This table is appearing in blue color, what is it mean? I can’t select qualifier as first… because it has not any reletion…
@John_Mitchell : yes, it is an external query, but now i am confused,because, if it is a View, why it is showing in SQL server’s Table list. another thing…How i can apply ‘DISTINCT’ to an external query. Because this setup is new to me.
Thanks…
It seems to be an external query. If it is a SQL view then you need to pull distinct data in the view itself. You can’t apply distinct on the external BAQ.
Yes Binita,
i have already selected DISTINCT records in View design. Still,distinct records are not displayed .
Thanks…
Can you post your SQL code?
John
Hi john,
Sure, this is the design,
SELECT DISTINCT
ARH.LegalNumber, ARH.InvoiceNum, ARH.InvoiceDate, ARH.InvoiceDate AS InvoicedDate, ARH.ApplyDate, ARH.ApplyDate AS AppliedDate, ARH.TranDocTypeID, ARH.CreditMemo, ARH.CorrectionInv,
ARH.GroupID, ARD.PartNum, ARD.LineDesc, ARD.SellingShipQty, ARD.DocUnitPrice, ARD.DocTotalMiscChrg AS Miscellanous, ARD.LineType, P.CommodityCode, C.CustID, C.CustNum, C.Name,
C.Address1 + C.Address2 + C.Address3 AS CustomerAddress, L.ShortChar01 AS CSTNumber, dbo.ShowARVATorCSTTax(ARH.InvoiceNum) AS VATorCSTType, dbo.GetARVATorCST(ARH.InvoiceNum)
AS VATorCSTPercent, dbo.GetARTaxAmt(ARH.InvoiceNum) AS ExcisePercent, dbo.ExciseARTaxAmount(ARH.InvoiceNum) AS ExciseAmount, dbo.ARVATorCSTAmount(ARH.InvoiceNum) AS VATorCSTAmount,
ISNULL(ARD.DocExtPrice, 0) + ISNULL(dbo.ExciseARTaxAmount(ARH.InvoiceNum), 0) AS SalesAmount, ISNULL(ARD.DocExtPrice, 0) + ISNULL(dbo.ExciseARTaxAmount(ARH.InvoiceNum), 0)
+ ISNULL(dbo.ARVATorCSTAmount(ARD.InvoiceNum), 0) + ISNULL(ARD.DocTotalMiscChrg, 0) AS [Grand Total], dbo.GetARInvoiceType(ARH.TranDocTypeID) AS InvoiceType
FROM
dbo.InvcHead AS ARH INNER JOIN
dbo.InvcDtl AS ARD ON ARD.InvoiceNum = ARH.InvoiceNum AND ARD.Company = ARH.Company AND ARH.OrderNum = ARD.OrderNum INNER JOIN
dbo.Customer AS C ON ARH.CustNum = C.CustNum AND ARH.Company = C.Company AND ARH.OrderNum = ARD.OrderNum LEFT OUTER JOIN
dbo.Part AS P ON P.PartNum = ARD.PartNum AND ARD.Company = P.Company AND ARD.PartNum = P.PartNum INNER JOIN
dbo.Local001 AS L ON L.Company = C.Company AND L.Key2 = C.CustNum AND L.LocalName = ‘CSF’ AND L.Key1 = ‘Customer’
Thanks