CODEWITHSUNDEEP
sqlt-sqlstored-proceduresreporting-servicespowerbi-paginated-reports
Mike Eale
Mike Eale

Reputation: 121

Passing parameter from paginated report down to stored procedure

I am creating a Power BI Paginated Report using the stored procedure below to pass parameter in the report. There are two parameters passed and it is cascading from one parameter to another e.g Timeframe parameter as By Month, By Week, By Dates dropdown and it cascade down to Calendar Date parameter.

enter image description here

Calendar Date parameter has the dropdown below when By Month value is selected from Timeframe parameter.

enter image description here

When By Week value is selected from the Timeframe parameter, the Calendar Date dropdown will be enter image description here

TY means current year and LY means previous year e.g LY01 means pervious year week 01 and TY01 is current year week 01. I created the stored procedure below but it does work for Timeframe: By Month. How can I incorporate By Week in the Timeframe and Calendar Date for LY01, LY02.... and TY01, TY02.... in stored procedure where clause.

Please, how can make this work so that when passing calendar Date parameter in paginated report will pass down to the stored procedure.

CREATE procedure [dbo].[Test] (
            @CalendarDate varchar(50)
        )
as
begin
   declare @sql nvarchar(MAX)

   SELECT
    i.[Item Nbr]
    ,i.[Brand Name]
    ,i.[Calendar Date] as  Calendar_date
    ,i.[Asda Week]
    , AVG([sell_price])as [Avg_Price]
    , SUM([sell_price]) as [Sell_Price]
    , SUM([wkly_sales])as [wkly_sales]
    , SUM([wkly_qty]) as [wkly_qty]
    FROM dbo.vw_item as i
    WHERE       
    DATENAME(MONTH,i.[Calendar Date]) = CASE
                                        WHEN    RIGHT(@CalendarDate,2) = 'TY' 
                                        THEN    SUBSTRING(@CalendarDate,1,Charindex(' ',@CalendarDate,1)-1)
                                        ELSE    SUBSTRING(@CalendarDate,1,Charindex(' ',@CalendarDate,1)-1)  END

         AND    YEAR(i.[Calendar Date])=  CASE WHEN RIGHT(@CalendarDate,2) = 'TY'  
                                          THEN Year(Getdate())
                                          ELSE Year(Getdate())-1  END
    GROUP BY
    i.[Item Nbr]
    ,i.[Brand Name]
    ,i.[Calendar Date] 
    ,i.[Asda Week]
end

Upvotes: 0

Views: 610

Answers (1)

SamBouKoa
SamBouKoa

Reputation: 206

You should pass the timeframe parameter to the stored procedure.

Your where condition will have embeded case statments

Case when timeframe = 'By Month' then

DATENAME(MONTH,i.[Calendar Date]) = CASE WHEN RIGHT(@CalendarDate,2) = 'TY' THEN SUBSTRING(@CalendarDate,1,Charindex(' ',@CalendarDate,1)-1) ELSE SUBSTRING(@CalendarDate,1,Charindex(' ',@CalendarDate,1)-1) END ...

when timeframe = 'By Week' then ....

Upvotes: 1

Related Questions