none
Report Builder parameter issue

    Domanda

  • Helle,

    i have such code in Report Builder main query as below

    SELECT
      concat(aw.CKT,aw.IDFIRM) zlo
      ,aw.T
      ,aw.CKT
      ,pr.Nazwa Artykul
      ,aw.IDFIRM
      ,pk.Nazwa Apteka
      ,aw.Ilosc
      ,aw.Marza
      ,aw.Netto
      ,aw.Brutto
      ,aw.Klienci_ilosc
      ,aw.Rodzaj
    FROM hurtownia_test.dbo.agr_Week_CKT_Pkt aw
    join Hurtownia_test.dbo.DimPunkty pk on pk.IDFCode=aw.IDFIRM
    join Hurtownia_test.dbo.DimProdukty pr on pr.CKT=aw.CKT
    join (select distinct d.YearWeek,d.Week,cast(concat(d.YearWeek,right(concat('0',d.Week),2)) as numeric(6,0)) T from Hurtownia_test.dbo.DimDates d where d.Date>=@start and d.Date<=@stop) d1 on d1.T=aw.T
    WHERE aw.Rodzaj = 'S' 
    and aw.CKT =(@CKT) or @CKT is null
    and pk.idfirm in(@apteka)

    Parameters are set properly. what is the issue? parameter @CKT is set as text with possibility to have null value. With null query takes all possible values for aw.CKT and it works fine, Also works for one choose eg. 66666, when taped in parameter textbox. But i would like to make choose also for several values eg. 66666,444444,99999 and tape/paste it in the textbox. but then of course query does not work.

    I've tried with below

    declare @CKT varchar(MAX)='9445,10229,8949';
    declare @apteka varchar(8)='850361';
    Declare @start date = '01/01/2018';
    Declare @start_t varchar(10) = cast(left(@start,10) as varchar(10));
    Declare @stop date = '04/18/2018';
    Declare @stop_t varchar(10) = cast(left(@stop,10) as varchar(10));
    declare @sqla as varchar(MAX);
    
    set @sqla =
    'SELECT
      concat(aw.CKT,aw.IDFIRM) zlo
      ,aw.T
      ,aw.CKT
      ,pr.Nazwa Artykul
      ,aw.IDFIRM
      ,pk.Nazwa Apteka
      ,aw.Ilosc
      ,aw.Marza
      ,aw.Netto
      ,aw.Brutto
      ,aw.Klienci_ilosc
      ,aw.Rodzaj
    FROM hurtownia_test.dbo.agr_Week_CKT_Pkt aw
    join Hurtownia_test.dbo.DimPunkty pk on pk.IDFCode=aw.IDFIRM
    join Hurtownia_test.dbo.DimProdukty pr on pr.CKT=aw.CKT
    join (select distinct d.YearWeek,d.Week,cast(concat(d.YearWeek,right(concat(''0'',d.Week),2)) as numeric(6,0)) T from Hurtownia_test.dbo.DimDates d where d.Date>='''+@start_t+''' and d.Date<='''+@stop_t+''') d1 on d1.T=aw.T
    WHERE aw.Rodzaj =''S'' 
    and aw.CKT in ('+replace(@CKT,'''','')+') 
    and pk.idfirm in ('+@apteka+') 
    order by aw.CKT'
    
    print @sqla;
    
    execute (@sqla);

    and it works fine in MS SQL Managment Studio but in Report Builder unfortunately not.

    Any idea? Please help.

    Regards

    Tom

    giovedì 19 aprile 2018 08:21

Tutte le risposte

  • You've make the Parameter as multivalue for this

    query behind will look like this

    declare @CKT varchar(MAX)='9445,10229,8949';
    declare @apteka varchar(8)='850361';
    Declare @start date = '01/01/2018';
    Declare @start_t varchar(10) = cast(left(@start,10) as varchar(10));
    Declare @stop date = '04/18/2018';
    Declare @stop_t varchar(10) = cast(left(@stop,10) as varchar(10));
    declare @sqla as varchar(MAX);
    
    SELECT
      concat(aw.CKT,aw.IDFIRM) zlo
      ,aw.T
      ,aw.CKT
      ,pr.Nazwa Artykul
      ,aw.IDFIRM
      ,pk.Nazwa Apteka
      ,aw.Ilosc
      ,aw.Marza
      ,aw.Netto
      ,aw.Brutto
      ,aw.Klienci_ilosc
      ,aw.Rodzaj
    FROM hurtownia_test.dbo.agr_Week_CKT_Pkt aw
    join Hurtownia_test.dbo.DimPunkty pk on pk.IDFCode=aw.IDFIRM
    join Hurtownia_test.dbo.DimProdukty pr on pr.CKT=aw.CKT
    join (select distinct d.YearWeek,d.Week,cast(concat(d.YearWeek,right(concat('0',d.Week),2)) as numeric(6,0)) T from Hurtownia_test.dbo.DimDates d 
    where d.Date>= @start_t
    and d.Date< dateadd(dd,1,@stop_t) 
    )d1 
    on d1.T=aw.T
    WHERE aw.Rodzaj ='S'
    and aw.CKT in (select val for dbo.ParseValues(@CKT,',')) 
    and pk.idfirm in (select val for dbo.ParseValues(@apteka ,',')) 
    order by aw.CKT
    

    ParseValues is a UDF you can find here

    https://visakhm.blogspot.ae/2010/02/parsing-delimited-string.html

    You need to create it first before using above code

    Also you dont need any dynamic sql

    if SQL 2016 you dont need UDF

    you can use system function string_split instead

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

    like

    declare @CKT varchar(MAX)='9445,10229,8949';
    declare @apteka varchar(8)='850361';
    Declare @start date = '01/01/2018';
    Declare @start_t varchar(10) = cast(left(@start,10) as varchar(10));
    Declare @stop date = '04/18/2018';
    Declare @stop_t varchar(10) = cast(left(@stop,10) as varchar(10));
    declare @sqla as varchar(MAX);
    
    SELECT
      concat(aw.CKT,aw.IDFIRM) zlo
      ,aw.T
      ,aw.CKT
      ,pr.Nazwa Artykul
      ,aw.IDFIRM
      ,pk.Nazwa Apteka
      ,aw.Ilosc
      ,aw.Marza
      ,aw.Netto
      ,aw.Brutto
      ,aw.Klienci_ilosc
      ,aw.Rodzaj
    FROM hurtownia_test.dbo.agr_Week_CKT_Pkt aw
    join Hurtownia_test.dbo.DimPunkty pk on pk.IDFCode=aw.IDFIRM
    join Hurtownia_test.dbo.DimProdukty pr on pr.CKT=aw.CKT
    join (select distinct d.YearWeek,d.Week,cast(concat(d.YearWeek,right(concat('0',d.Week),2)) as numeric(6,0)) T from Hurtownia_test.dbo.DimDates d 
    where d.Date>= @start_t
    and d.Date< dateadd(dd,1,@stop_t) 
    )d1 
    on d1.T=aw.T
    WHERE aw.Rodzaj ='S'
    and aw.CKT in (select value for String_Split(@CKT,',')) 
    and pk.idfirm in (select value for String_Split(@apteka ,',')) 
    order by aw.CKT


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    giovedì 19 aprile 2018 08:34