4 Replies Latest reply on Oct 30, 2017 11:35 PM by Tomer Davidyan

    All option for custom sql query parameter

    Tomer Davidyan

      Hi,

      I have a query that have 2 parameters that i call them from the report (bold):

      select

        [Dwh_Fact_Broadcasted_Items].[AssetId] AS [AssetId],

        [DWH_Dim_Story_Content].[Story_Asset_ID] AS [Story_Asset_ID],

        [Dwh_Fact_Broadcasted_Items].[RundownId] AS [RundownId],

        [Dwh_Fact_Broadcasted_Items].[startDate] AS [startDate],

        [Dwh_Dim_Title_Media_Info_V].[Title_Type_Name] AS [Title_Type_Name],

        [DWH_Dim_Rundown_Assets].[Asset_Type],

        [DWH_Dim_Stations].[name] AS [StationsName],

        [Dwh_Dim_Title_Media_Info_V].[Title_Name] AS [Title_Name],

        [Dwh_Dim_Title_Media_Info_V].[AssignTo] AS [AssignTo],

        [Dwh_Dim_Title_Media_Info_V].[Journalist] AS [Journalist],

        [DWH_Dim_Story_Content].[PlanningEvent] AS [PlanningEvent],

        [V_FOR_RepetitionAssetID].[CountRepetitionAssetID],

        [V_FOR_RepetitionPlanningEvent].[CountRepetitionPlanningEvent]

        FROM [dbo].[Dwh_Fact_Broadcasted_Items] [Dwh_Fact_Broadcasted_Items]

        INNER JOIN [dbo].[DWH_Dim_Rundown_Assets] [DWH_Dim_Rundown_Assets] ON (([Dwh_Fact_Broadcasted_Items].[AssetId] = [DWH_Dim_Rundown_Assets].[Asset_ID]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Rundown_Assets].[Site_id]))

        INNER JOIN [dbo].[Dwh_Dim_Title_Media_Info_V] [Dwh_Dim_Title_Media_Info_V] ON (([Dwh_Fact_Broadcasted_Items].[AssetId] = [Dwh_Dim_Title_Media_Info_V].[Title_Id]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [Dwh_Dim_Title_Media_Info_V].[Site_Id]))

        INNER JOIN [dbo].[DWH_Dim_Stations] [DWH_Dim_Stations] ON (([Dwh_Fact_Broadcasted_Items].[StationId] = [DWH_Dim_Stations].[id]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Stations].[Site_id]))

        INNER JOIN [dbo].[DWH_Dim_Story_Content] [DWH_Dim_Story_Content] ON (([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Story_Content].[Site_id]) AND ([Dwh_Fact_Broadcasted_Items].[AssetId] = [DWH_Dim_Story_Content].[Story_Asset_ID]))

        LEFT JOIN

        (

      SELECT

      distinct Dwh_Fact_Broadcasted_Items.AssetId,

      [DWH_Dim_Stations].[name] AS [StationsName],

      [Dwh_Dim_Title_Media_Info_V].[Title_Type_Name] AS [Title_Type_Name],

      COUNT(Dwh_Fact_Broadcasted_Items.AssetId) AS CountRepetitionAssetID

      FROM [dbo].[Dwh_Fact_Broadcasted_Items] [Dwh_Fact_Broadcasted_Items]

          INNER JOIN [dbo].[DWH_Dim_Rundown_Assets] [DWH_Dim_Rundown_Assets] ON (([Dwh_Fact_Broadcasted_Items].[AssetId] = [DWH_Dim_Rundown_Assets].[Asset_ID]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Rundown_Assets].[Site_id]))

          INNER JOIN [dbo].[Dwh_Dim_Title_Media_Info_V] [Dwh_Dim_Title_Media_Info_V] ON (([Dwh_Fact_Broadcasted_Items].[AssetId] = [Dwh_Dim_Title_Media_Info_V].[Title_Id]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [Dwh_Dim_Title_Media_Info_V].[Site_Id]))

          INNER JOIN [dbo].[DWH_Dim_Stations] [DWH_Dim_Stations] ON (([Dwh_Fact_Broadcasted_Items].[StationId] = [DWH_Dim_Stations].[id]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Stations].[Site_id]))

          INNER JOIN [dbo].[DWH_Dim_Story_Content] [DWH_Dim_Story_Content] ON (([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Story_Content].[Site_id]) AND ([Dwh_Fact_Broadcasted_Items].[AssetId] = [DWH_Dim_Story_Content].[Story_Asset_ID]))

      where Asset_Type = 'StoryAsset'

      GROUP BY Dwh_Fact_Broadcasted_Items.AssetId,

      [DWH_Dim_Stations].[name],

      [Dwh_Dim_Title_Media_Info_V].[Title_Type_Name]

        ) V_FOR_RepetitionAssetID

        ON ([Dwh_Fact_Broadcasted_Items].AssetId = [V_FOR_RepetitionAssetID].AssetId) AND ([DWH_Dim_Stations].[name] = [V_FOR_RepetitionAssetID].StationsName) AND ([Dwh_Dim_Title_Media_Info_V].[Title_Type_Name] = [V_FOR_RepetitionAssetID].Title_Type_Name)

        LEFT JOIN

        (

      SELECT

      distinct DWH_Dim_Story_Content.PlanningEvent,

      [DWH_Dim_Stations].[name] AS [StationsName],

      [Dwh_Dim_Title_Media_Info_V].[Title_Type_Name] AS [Title_Type_Name],

      COUNT(DWH_Dim_Story_Content.PlanningEvent) AS CountRepetitionPlanningEvent

      FROM [dbo].[Dwh_Fact_Broadcasted_Items] [Dwh_Fact_Broadcasted_Items]

          INNER JOIN [dbo].[DWH_Dim_Rundown_Assets] [DWH_Dim_Rundown_Assets] ON (([Dwh_Fact_Broadcasted_Items].[AssetId] = [DWH_Dim_Rundown_Assets].[Asset_ID]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Rundown_Assets].[Site_id]))

          INNER JOIN [dbo].[Dwh_Dim_Title_Media_Info_V] [Dwh_Dim_Title_Media_Info_V] ON (([Dwh_Fact_Broadcasted_Items].[AssetId] = [Dwh_Dim_Title_Media_Info_V].[Title_Id]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [Dwh_Dim_Title_Media_Info_V].[Site_Id]))

          INNER JOIN [dbo].[DWH_Dim_Stations] [DWH_Dim_Stations] ON (([Dwh_Fact_Broadcasted_Items].[StationId] = [DWH_Dim_Stations].[id]) AND ([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Stations].[Site_id]))

          INNER JOIN [dbo].[DWH_Dim_Story_Content] [DWH_Dim_Story_Content] ON (([Dwh_Fact_Broadcasted_Items].[Site_id] = [DWH_Dim_Story_Content].[Site_id]) AND ([Dwh_Fact_Broadcasted_Items].[AssetId] = [DWH_Dim_Story_Content].[Story_Asset_ID]))

          where Asset_Type = 'StoryAsset'

          and [DWH_Dim_Stations].[name] IN (<Parameters.Report1_StationName_Parameter>)

          and Title_Type_Name = <Parameters.Report1_TitleTypeName_Parameter>

      GROUP BY DWH_Dim_Story_Content.PlanningEvent,

      [DWH_Dim_Stations].[name],

      [Dwh_Dim_Title_Media_Info_V].[Title_Type_Name]

        ) V_FOR_RepetitionPlanningEvent

        ON ([DWH_Dim_Story_Content].PlanningEvent = [V_FOR_RepetitionPlanningEvent].PlanningEvent) AND ([DWH_Dim_Stations].[name] = [V_FOR_RepetitionPlanningEvent].StationsName) AND ([Dwh_Dim_Title_Media_Info_V].[Title_Type_Name] = [V_FOR_RepetitionPlanningEvent].Title_Type_Name)

        where Asset_Type = 'StoryAsset'

        and [DWH_Dim_Stations].[name] IN (<Parameters.Report1_StationName_Parameter>)

        and [Dwh_Dim_Title_Media_Info_V].[Title_Type_Name] = <Parameters.Report1_TitleTypeName_Parameter>

       

      Those 2 parameters are from closed lists.

      I added to the lists the 'All' option.

       

      I created 2 calculated filed (for example what i wrote for the Station Name):

      [Report1_TitleTypeName_Parameter]= 'All' OR  [Report1_TitleTypeName_Parameter]= [StationsName]

       

      i added those filters to the filter section but i am getting just false option (not true as i saw in different examples that i founded and did in different report).

       

      When i choosing the 'All' option, i don't get anything (empty report).

       

      Can someone help me to understand what i am missing and how i can solve this issue ?

       

      Thank u,