none
Mehrere optinale Parameter in SSRS 2008 RRS feed

  • Frage

  • Guten Tag,

    ich bin gerade dabei viel mit den Parametersystem aus SSRS 2008 auszuprobieren. Zuletzt hat es geklappt wie ich es schaffe, NULL zu filtern trotz einer Mehrfachauswahl eines Parameters. ALlerdings sucht er dann in der Tabelle die Datensätze raus die NULL beinhalten, was ja kein Datensatz hat. Komme ich nun zur aktuellen Frage- oder Problemstellung. Folgende Beispiel-Tabelle liegt vor:
    Nummer Beschreibung OGC GC UGC
    68015 Hundefutter 2 21 13
    68016 Katzenfutter 2 21 14
    68030 Blumenerde 3 31 15
    68043 Zement 4 42 19
    65302 Steine 4 42 30

    Der Benutzer soll nun über mehrere Parameter auswählen können ob er nun nach OGC, GC, oder UGC filtern soll. Wenn kein Filter gefüllt ist soll die gesamte Tabelle ausgeworfen werden. Die jeweiligen Parameter haben eine separate Abfrage und können auch mehrfach ausgewählt werden.

    Beispiel der Benutzer gibt in die Parameter OGC = 2, 3 an und im Parameter GC = 21, 31 an, dann soll die Ausgabe wie folgt aussehen:
    Nummer Beschreibung OGC GC UGC
    68015 Hundefutter 2 21 13
    68016 Katzenfutter 2 21 14
    68030 Blumenerde 3 31 15

    Wie muss das Dataset und die Parameter OGC, GC und UGC aufgebaut sein?

    Normalerweise ja so:
    SELECT Nummer, Beschreibung, OGC, GC, UGC
    FROM table
    WHERE OGC IN (@OGC) AND GC IN (@GC) AND UGC in (@UGC)

    Allerdings kann man dann ja nicht zwischen den Parametern wählen, da ja UGC vom Benutzer nicht gefüllt wird.
    Dienstag, 14. November 2017 11:22

Antworten

  • Habe den Befehl mal in einem kleinen  dynamischen Befehl gewandelt um ihn zu testen:

    IF (@extGC = '')
    BEGIN
        EXECUTE('SELECT Nr_, Beschreibung, [Beschreibung 2], [Externer Gruppencode]
        FROM [RWG Gnarrenburg eG$Artikel]')
    END
    ELSE
    BEGIN
        EXECUTE('SELECT Nr_, Beschreibung, [Beschreibung 2], [Externer Gruppencode]
        FROM [RWG Gnarrenburg eG$Artikel]
        WHERE [Externer Gruppencode] = @extGC)'
    END
    Weder mit EXEC noch mit EXECUTE kann er was anfangen. Ich gehe mal davon aus, dass nicht jede 2008-Datenbank dyn. SQL verarbeiten kann. Ich verstehe nicht warum das so schwer ist, mehrere optionale Parameter mit Multi-Pick-Werten aus einem anderen Dataset dem Benutzer zur Verfügung zu stellen. Das Macht das ganze SSRS für uns überflüssig. Ich habe eine große Tabelle mit Artikelstammdaten. Da muss es doch dem Benutzer frei zu stehen, nach welchen Kriterien er heute und nach welchen iKriterien er morgen sucht.


    Zwei kleine Hinweise:

    Es ist eine Best Practice über SSRS auf Prozeduren zuzugreifen. Anstelle von Ad-Hoc Befehlen, die dann mit gesamten Text im Report stehen.
    In Prozeduren kann man dann auch alles tun, was T-SQL so hergibt. Ansonsten ist man auf ein Select beschränkt.

    Das Handling von Multiple-Choice Parameter-Sets ist deswegen etwas "eigen", weil viele Nutzer es gewohnt sind, nur mit genau einem ParameterWert-in T-SQL zu arbeiten. Dabei gibt es dort schon lange Möglichkeiten und Lösungen, die Kommagetrennten Werte aufzulösen und dann zu verarbeiten.

    Hier mal ein Beispiel. Vielleicht hilft das auf den Weg: Working With Multi-Select Parameters for SSRS Reports


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Mittwoch, 15. November 2017 10:59

Alle Antworten

  • Das ginge dann so:

    and ( OGC in (@OGC) or @OGC is NULL) and ( 2. variable ...) and (....)

    Je nachdem, was also der Parameter im Default-Fall (also nicht angegeben) enthält.

    Dienstag, 14. November 2017 12:20
  • Ich nutze das "abschalten" nicht benötigter Parameter auch öfter und gerne, allerdings muss man (bei größeren) Datenmengen aufpassen / sich bewusst sein, dass man sich damit auch ein Performance Problem einhandeln kann!

    Siehe "Die schlaue Logik"

    Dienstag, 14. November 2017 13:37
  • Nachtrag:

    Ich hab' die "Problematik" gerade nochmal mit einem Kollegen diskutiert und das kurz quick & dirty nachgestellt:

    -- Testszenario
    CREATE TABLE dbo.Tab01 (id     INT IDENTITY(1, 1) PRIMARY KEY,
                            Feld01 INT,
                            Feld02 INT);
    GO
    
    CREATE NONCLUSTERED INDEX ix_Tab01_Feld01_Feld02
    ON dbo.Tab01 (Feld01 ASC, Feld02 ASC);
    GO
    CREATE NONCLUSTERED INDEX ix_Tab01_Feld02_Feld01
    ON dbo.Tab01 (Feld02 ASC, Feld01 ASC);
    GO
    
    INSERT Tab01 (Feld01,
                  Feld02)
    VALUES (cast(abs(cast(cast(newId() AS VARBINARY) AS INT)) AS VARCHAR), cast(abs(cast(cast(newId() AS VARBINARY) AS INT)) AS VARCHAR));
    	GO 50000
    GO
    Abfragen - Einmal mit "is NULL" einmal mit dynamischem SQL
    SET STATISTICS IO ON;
    
    DECLARE @f1 VARCHAR(50) = NULL; 
    DECLARE @f2 VARCHAR(50) = 815; 
    
    SELECT * FROM dbo.Tab01 WHERE (Feld01 = @f1 OR @f1 IS NULL) AND (Feld02 = @f2 OR @f2 IS NULL);
    
    --dynamisches SQL
    DECLARE @sqlCommand VARCHAR(MAX);
    DECLARE @pCount INT = 0;
    
    SET @sqlCommand = 'SELECT * FROM dbo.Tab01 WHERE ';
    
    IF NOT @f1 IS NULL
    BEGIN
        SET @sqlCommand = @sqlCommand + '(Feld01 = ' +  @f1 + ')';
        SET @pCount = @pCount + 1;
    END;

    Hier die Ergebnisse:

    Dienstag, 14. November 2017 15:23
  • Nichts desto trotz kommt immer die Fehlemeldung: "Bitte geben Sie einen Wert für OGC" ein. Muss ich dann trotz alledem immer noch per UNION einen NULL-Wert in die Auflistung möglicher Werte aufnehmen?  Irgendwo verstehe ich das nicht dass das so kompliziert sein muss.
    Dienstag, 14. November 2017 15:25
  • Entschuldigung, aber das Problem ist folgendes:

    OGC in (@OGC)

    Wenn nun @OGC NULL ist, ist dies in SQL nicht erlaubt.
    Ich weiß allerdings nicht, warum du "in" verwendest, da du auf diese Weise grundsätzlich nur einen Wert übergeben kannst. Wenn du z.B. " 'Wert1', 'Wert2' " übergibst, wird dies nur als genau 1 Wert interpretiert, der so gesucht wird.
    Anders sieht es dann tatsächlichmit dynamischem SQL aus, allerdings bist du da ggf. auch für die Hochkommatas verantwortlich.

    Nun zur Korrekur:

    OGC = coalesce(@OGC, 'Unsinn') or @OGC is NULL

    Dienstag, 14. November 2017 15:50
  • Habe den Befehl mal in einem kleinen  dynamischen Befehl gewandelt um ihn zu testen:

    IF (@extGC = '')
    BEGIN
        EXECUTE('SELECT Nr_, Beschreibung, [Beschreibung 2], [Externer Gruppencode]
        FROM [RWG Gnarrenburg eG$Artikel]')
    END
    ELSE
    BEGIN
        EXECUTE('SELECT Nr_, Beschreibung, [Beschreibung 2], [Externer Gruppencode]
        FROM [RWG Gnarrenburg eG$Artikel]
        WHERE [Externer Gruppencode] = @extGC)'
    END
    Weder mit EXEC noch mit EXECUTE kann er was anfangen. Ich gehe mal davon aus, dass nicht jede 2008-Datenbank dyn. SQL verarbeiten kann. Ich verstehe nicht warum das so schwer ist, mehrere optionale Parameter mit Multi-Pick-Werten aus einem anderen Dataset dem Benutzer zur Verfügung zu stellen. Das Macht das ganze SSRS für uns überflüssig. Ich habe eine große Tabelle mit Artikelstammdaten. Da muss es doch dem Benutzer frei zu stehen, nach welchen Kriterien er heute und nach welchen Kriterien er morgen sucht.
    Mittwoch, 15. November 2017 07:12
  • Laut Doku ab 2008 verfügbar.
    Aber ggf. kommt er hier nicht zurecht, da du 2 Exec's verwendest.
    Baue den String erst zusammen und führe dann genau einen Exec aus.

    Mittwoch, 15. November 2017 08:41
  • Habe den Befehl mal in einem kleinen  dynamischen Befehl gewandelt um ihn zu testen:

    IF (@extGC = '')
    BEGIN
        EXECUTE('SELECT Nr_, Beschreibung, [Beschreibung 2], [Externer Gruppencode]
        FROM [RWG Gnarrenburg eG$Artikel]')
    END
    ELSE
    BEGIN
        EXECUTE('SELECT Nr_, Beschreibung, [Beschreibung 2], [Externer Gruppencode]
        FROM [RWG Gnarrenburg eG$Artikel]
        WHERE [Externer Gruppencode] = @extGC)'
    END
    Weder mit EXEC noch mit EXECUTE kann er was anfangen. Ich gehe mal davon aus, dass nicht jede 2008-Datenbank dyn. SQL verarbeiten kann. Ich verstehe nicht warum das so schwer ist, mehrere optionale Parameter mit Multi-Pick-Werten aus einem anderen Dataset dem Benutzer zur Verfügung zu stellen. Das Macht das ganze SSRS für uns überflüssig. Ich habe eine große Tabelle mit Artikelstammdaten. Da muss es doch dem Benutzer frei zu stehen, nach welchen Kriterien er heute und nach welchen iKriterien er morgen sucht.


    Zwei kleine Hinweise:

    Es ist eine Best Practice über SSRS auf Prozeduren zuzugreifen. Anstelle von Ad-Hoc Befehlen, die dann mit gesamten Text im Report stehen.
    In Prozeduren kann man dann auch alles tun, was T-SQL so hergibt. Ansonsten ist man auf ein Select beschränkt.

    Das Handling von Multiple-Choice Parameter-Sets ist deswegen etwas "eigen", weil viele Nutzer es gewohnt sind, nur mit genau einem ParameterWert-in T-SQL zu arbeiten. Dabei gibt es dort schon lange Möglichkeiten und Lösungen, die Kommagetrennten Werte aufzulösen und dann zu verarbeiten.

    Hier mal ein Beispiel. Vielleicht hilft das auf den Weg: Working With Multi-Select Parameters for SSRS Reports


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Mittwoch, 15. November 2017 10:59