drill-down arrows are in a query, but not in an alert

[Former Member]

Hello guys,

I have this SQL query which I need to use in an alert with drill-down links to orders:

SELECT TOP 10000

    ORDR.DocEntry,

    ORDR.DocNum,

    T1.CardCode,

    T1.CardName,

    DATEDIFF(day, T2.Oldest, GETDATE()) AS [Dnů po splatnosti nejstarší faktury],

    T2.Invoices AS [Počet faktur po splatnosti],

    T2.NotPaid AS [Dluží celkem]

FROM

    ORDR     /* kvůli drill-down šipce ve výstraze */

JOIN (

    SELECT   /*  zakázky, které mají min. jednu položku v limitu */

        ORDR.DocNum,

        ORDR.CardCode,

        ORDR.CardName

    FROM

        ORDR

    JOIN

        RDR1

    ON

        RDR1.DocEntry = ORDR.DocEntry

    WHERE

        DATEDIFF(day, GETDATE(), RDR1.ShipDate) BETWEEN 0 AND 5

    GROUP BY

        ORDR.DocNum,

        ORDR.CardCode,

        ORDR.CardName

) T1

ON

    T1.DocNum = ORDR.DocNum

JOIN (    

    SELECT   /* hříšníci po splatnosti */

        OINV.CardCode,

        MIN(OINV.DocDueDate) AS [Oldest],

        COUNT(*) AS [Invoices],

        SUM(OINV.DocTotal - OINV.PaidToDate) AS [NotPaid]

    FROM

        OINV

    WHERE

        DATEDIFF(day, OINV.DocDueDate, GETDATE()) >= 1 AND

        OINV.DocStatus = 'O'

    GROUP BY

        OINV.CardCode

) T2

ON

    T2.CardCode = T1.CardCode

FOR BROWSE

I use two sub-queries in it. Why? Just to be able to safely use necessary GROUP BY in both of them. The main SELECT doesn't utilize GROUP BY so the links in DocEntry, DocNum to their apropriate tables should not be destroyed.

This works well when running the query directly as you may see in the picture -  arrows_YES.png.

But the drill-down arrows disappear when the query is run as an alert.

Do you know why?

SAP B1 8.82

[Former Member · Accepted solution]

Hi,

The alert query has to be as simple as possible. In your case, there is no way to get drill-down links.

Try to simplify it first by you and get the same result.

Thanks,

Gordon

[Former Member · Accepted solution]

OK, thank you Gordon. Unfortunately, in this case I need to get some sort of data and this query already is as much simplified as possible. I don't see any way of simplifiing it even more.

And 'dear' SAP, do finally something about this problem,  we payed you big bucks just to get application with this alert issue which has been reported for many long years and still hasn't been fixed!!!

We REALLY need to build more complex alerts than SELECT * FROM ORDR.....

Hint for your corrective work: when there are drill-downs displayed in directly launched query, they should be in the alerts as well. I don't want so much, right?