SSRS 2017 Cascading parameters with dates












0














I have a parameter called Year and you can choose one year at a time. I have a date/time parameter with a calendar called start date and another one called end date.



I would like it to work such that if I pick the year to be 2017 it will show the calendar start and end dates for 2017.



Since my start date and end date parameters are not defaulted from a dataset I am not sure how to control that.



Any help will be appreciated.










share|improve this question
























  • I assume you mean you want to default the start and end date parameters and that the user can then modify them? i.e. the Year parameter is a quick way to set start and end dates?
    – Dale Burrell
    Nov 21 at 3:22










  • Yeaj if I pick year 2017 I want the calendar start date and end date show months for 2017 . I am using SQL server database
    – Liliana Torres
    Nov 22 at 6:09










  • You can tag the database version, and you can try the answers below and ask if you if you need more assistance.
    – Dale Burrell
    Nov 23 at 2:24
















0














I have a parameter called Year and you can choose one year at a time. I have a date/time parameter with a calendar called start date and another one called end date.



I would like it to work such that if I pick the year to be 2017 it will show the calendar start and end dates for 2017.



Since my start date and end date parameters are not defaulted from a dataset I am not sure how to control that.



Any help will be appreciated.










share|improve this question
























  • I assume you mean you want to default the start and end date parameters and that the user can then modify them? i.e. the Year parameter is a quick way to set start and end dates?
    – Dale Burrell
    Nov 21 at 3:22










  • Yeaj if I pick year 2017 I want the calendar start date and end date show months for 2017 . I am using SQL server database
    – Liliana Torres
    Nov 22 at 6:09










  • You can tag the database version, and you can try the answers below and ask if you if you need more assistance.
    – Dale Burrell
    Nov 23 at 2:24














0












0








0


0





I have a parameter called Year and you can choose one year at a time. I have a date/time parameter with a calendar called start date and another one called end date.



I would like it to work such that if I pick the year to be 2017 it will show the calendar start and end dates for 2017.



Since my start date and end date parameters are not defaulted from a dataset I am not sure how to control that.



Any help will be appreciated.










share|improve this question















I have a parameter called Year and you can choose one year at a time. I have a date/time parameter with a calendar called start date and another one called end date.



I would like it to work such that if I pick the year to be 2017 it will show the calendar start and end dates for 2017.



Since my start date and end date parameters are not defaulted from a dataset I am not sure how to control that.



Any help will be appreciated.







sql-server tsql reporting-services parameters






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 27 at 1:22









aduguid

2,12661031




2,12661031










asked Nov 21 at 2:43









Liliana Torres

1111




1111












  • I assume you mean you want to default the start and end date parameters and that the user can then modify them? i.e. the Year parameter is a quick way to set start and end dates?
    – Dale Burrell
    Nov 21 at 3:22










  • Yeaj if I pick year 2017 I want the calendar start date and end date show months for 2017 . I am using SQL server database
    – Liliana Torres
    Nov 22 at 6:09










  • You can tag the database version, and you can try the answers below and ask if you if you need more assistance.
    – Dale Burrell
    Nov 23 at 2:24


















  • I assume you mean you want to default the start and end date parameters and that the user can then modify them? i.e. the Year parameter is a quick way to set start and end dates?
    – Dale Burrell
    Nov 21 at 3:22










  • Yeaj if I pick year 2017 I want the calendar start date and end date show months for 2017 . I am using SQL server database
    – Liliana Torres
    Nov 22 at 6:09










  • You can tag the database version, and you can try the answers below and ask if you if you need more assistance.
    – Dale Burrell
    Nov 23 at 2:24
















I assume you mean you want to default the start and end date parameters and that the user can then modify them? i.e. the Year parameter is a quick way to set start and end dates?
– Dale Burrell
Nov 21 at 3:22




I assume you mean you want to default the start and end date parameters and that the user can then modify them? i.e. the Year parameter is a quick way to set start and end dates?
– Dale Burrell
Nov 21 at 3:22












Yeaj if I pick year 2017 I want the calendar start date and end date show months for 2017 . I am using SQL server database
– Liliana Torres
Nov 22 at 6:09




Yeaj if I pick year 2017 I want the calendar start date and end date show months for 2017 . I am using SQL server database
– Liliana Torres
Nov 22 at 6:09












You can tag the database version, and you can try the answers below and ask if you if you need more assistance.
– Dale Burrell
Nov 23 at 2:24




You can tag the database version, and you can try the answers below and ask if you if you need more assistance.
– Dale Burrell
Nov 23 at 2:24












2 Answers
2






active

oldest

votes


















0














You can do it as an expression, but as its easier in SQL you can just create a second dataset with the following:



select convert(date, '1 jan ' + convert(varchar(4), @Year)), dateadd(day, -1, dateadd(year, 1, convert(date, '1 jan ' + convert(varchar(4), @Year))))





share|improve this answer





























    0














    I used a recursive CTE common table expression that references itself to solve this. Then you can set the Dataset of the start_date and end_date parameters.



    Dataset for the default values of the start and end date parameters



    DECLARE @year AS INT
    SET @year = 2018

    SELECT [start_date] = DATEFROMPARTS(@year, 1, 1), [end_date] = DATEFROMPARTS(@year, 12, 31)


    Dataset for the available values of the start and end date parameters



    DECLARE @year AS INT
    SET @year = 2018

    ;WITH
    source_data_dates
    AS
    (
    SELECT
    [date_start] = DATEFROMPARTS(@year, 1, 1)
    , [date_end] = DATEFROMPARTS(@year, 12, 31)
    )
    ,
    year_date_list([rn], [date_value])
    AS
    (
    SELECT
    [rn] = 1
    , [date_value] = CAST([date_start] AS DATETIME)
    FROM
    source_data_dates
    UNION ALL
    SELECT
    [rn] = [rn] + 1
    , [date_start] = CAST(DATEADD(DAY, [rn], [date_start]) AS DATETIME)
    FROM
    year_date_list
    , source_data_dates
    WHERE
    [rn] <= DATEDIFF(DAY, [date_start], [date_end])
    )
    SELECT
    [rn]
    , [date_value]
    FROM
    year_date_list
    OPTION (MAXRECURSION 0)


    Results:



    screenshot






    share|improve this answer























    • The year values come from another dataset from sql server.. I put as example 2017 but there are a couple nore year available..
      – Liliana Torres
      Nov 22 at 6:11











    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53404586%2fssrs-2017-cascading-parameters-with-dates%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    You can do it as an expression, but as its easier in SQL you can just create a second dataset with the following:



    select convert(date, '1 jan ' + convert(varchar(4), @Year)), dateadd(day, -1, dateadd(year, 1, convert(date, '1 jan ' + convert(varchar(4), @Year))))





    share|improve this answer


























      0














      You can do it as an expression, but as its easier in SQL you can just create a second dataset with the following:



      select convert(date, '1 jan ' + convert(varchar(4), @Year)), dateadd(day, -1, dateadd(year, 1, convert(date, '1 jan ' + convert(varchar(4), @Year))))





      share|improve this answer
























        0












        0








        0






        You can do it as an expression, but as its easier in SQL you can just create a second dataset with the following:



        select convert(date, '1 jan ' + convert(varchar(4), @Year)), dateadd(day, -1, dateadd(year, 1, convert(date, '1 jan ' + convert(varchar(4), @Year))))





        share|improve this answer












        You can do it as an expression, but as its easier in SQL you can just create a second dataset with the following:



        select convert(date, '1 jan ' + convert(varchar(4), @Year)), dateadd(day, -1, dateadd(year, 1, convert(date, '1 jan ' + convert(varchar(4), @Year))))






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 21 at 3:18









        Dale Burrell

        2,73512147




        2,73512147

























            0














            I used a recursive CTE common table expression that references itself to solve this. Then you can set the Dataset of the start_date and end_date parameters.



            Dataset for the default values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            SELECT [start_date] = DATEFROMPARTS(@year, 1, 1), [end_date] = DATEFROMPARTS(@year, 12, 31)


            Dataset for the available values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            ;WITH
            source_data_dates
            AS
            (
            SELECT
            [date_start] = DATEFROMPARTS(@year, 1, 1)
            , [date_end] = DATEFROMPARTS(@year, 12, 31)
            )
            ,
            year_date_list([rn], [date_value])
            AS
            (
            SELECT
            [rn] = 1
            , [date_value] = CAST([date_start] AS DATETIME)
            FROM
            source_data_dates
            UNION ALL
            SELECT
            [rn] = [rn] + 1
            , [date_start] = CAST(DATEADD(DAY, [rn], [date_start]) AS DATETIME)
            FROM
            year_date_list
            , source_data_dates
            WHERE
            [rn] <= DATEDIFF(DAY, [date_start], [date_end])
            )
            SELECT
            [rn]
            , [date_value]
            FROM
            year_date_list
            OPTION (MAXRECURSION 0)


            Results:



            screenshot






            share|improve this answer























            • The year values come from another dataset from sql server.. I put as example 2017 but there are a couple nore year available..
              – Liliana Torres
              Nov 22 at 6:11
















            0














            I used a recursive CTE common table expression that references itself to solve this. Then you can set the Dataset of the start_date and end_date parameters.



            Dataset for the default values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            SELECT [start_date] = DATEFROMPARTS(@year, 1, 1), [end_date] = DATEFROMPARTS(@year, 12, 31)


            Dataset for the available values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            ;WITH
            source_data_dates
            AS
            (
            SELECT
            [date_start] = DATEFROMPARTS(@year, 1, 1)
            , [date_end] = DATEFROMPARTS(@year, 12, 31)
            )
            ,
            year_date_list([rn], [date_value])
            AS
            (
            SELECT
            [rn] = 1
            , [date_value] = CAST([date_start] AS DATETIME)
            FROM
            source_data_dates
            UNION ALL
            SELECT
            [rn] = [rn] + 1
            , [date_start] = CAST(DATEADD(DAY, [rn], [date_start]) AS DATETIME)
            FROM
            year_date_list
            , source_data_dates
            WHERE
            [rn] <= DATEDIFF(DAY, [date_start], [date_end])
            )
            SELECT
            [rn]
            , [date_value]
            FROM
            year_date_list
            OPTION (MAXRECURSION 0)


            Results:



            screenshot






            share|improve this answer























            • The year values come from another dataset from sql server.. I put as example 2017 but there are a couple nore year available..
              – Liliana Torres
              Nov 22 at 6:11














            0












            0








            0






            I used a recursive CTE common table expression that references itself to solve this. Then you can set the Dataset of the start_date and end_date parameters.



            Dataset for the default values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            SELECT [start_date] = DATEFROMPARTS(@year, 1, 1), [end_date] = DATEFROMPARTS(@year, 12, 31)


            Dataset for the available values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            ;WITH
            source_data_dates
            AS
            (
            SELECT
            [date_start] = DATEFROMPARTS(@year, 1, 1)
            , [date_end] = DATEFROMPARTS(@year, 12, 31)
            )
            ,
            year_date_list([rn], [date_value])
            AS
            (
            SELECT
            [rn] = 1
            , [date_value] = CAST([date_start] AS DATETIME)
            FROM
            source_data_dates
            UNION ALL
            SELECT
            [rn] = [rn] + 1
            , [date_start] = CAST(DATEADD(DAY, [rn], [date_start]) AS DATETIME)
            FROM
            year_date_list
            , source_data_dates
            WHERE
            [rn] <= DATEDIFF(DAY, [date_start], [date_end])
            )
            SELECT
            [rn]
            , [date_value]
            FROM
            year_date_list
            OPTION (MAXRECURSION 0)


            Results:



            screenshot






            share|improve this answer














            I used a recursive CTE common table expression that references itself to solve this. Then you can set the Dataset of the start_date and end_date parameters.



            Dataset for the default values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            SELECT [start_date] = DATEFROMPARTS(@year, 1, 1), [end_date] = DATEFROMPARTS(@year, 12, 31)


            Dataset for the available values of the start and end date parameters



            DECLARE @year AS INT
            SET @year = 2018

            ;WITH
            source_data_dates
            AS
            (
            SELECT
            [date_start] = DATEFROMPARTS(@year, 1, 1)
            , [date_end] = DATEFROMPARTS(@year, 12, 31)
            )
            ,
            year_date_list([rn], [date_value])
            AS
            (
            SELECT
            [rn] = 1
            , [date_value] = CAST([date_start] AS DATETIME)
            FROM
            source_data_dates
            UNION ALL
            SELECT
            [rn] = [rn] + 1
            , [date_start] = CAST(DATEADD(DAY, [rn], [date_start]) AS DATETIME)
            FROM
            year_date_list
            , source_data_dates
            WHERE
            [rn] <= DATEDIFF(DAY, [date_start], [date_end])
            )
            SELECT
            [rn]
            , [date_value]
            FROM
            year_date_list
            OPTION (MAXRECURSION 0)


            Results:



            screenshot







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 27 at 1:33

























            answered Nov 21 at 3:38









            aduguid

            2,12661031




            2,12661031












            • The year values come from another dataset from sql server.. I put as example 2017 but there are a couple nore year available..
              – Liliana Torres
              Nov 22 at 6:11


















            • The year values come from another dataset from sql server.. I put as example 2017 but there are a couple nore year available..
              – Liliana Torres
              Nov 22 at 6:11
















            The year values come from another dataset from sql server.. I put as example 2017 but there are a couple nore year available..
            – Liliana Torres
            Nov 22 at 6:11




            The year values come from another dataset from sql server.. I put as example 2017 but there are a couple nore year available..
            – Liliana Torres
            Nov 22 at 6:11


















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53404586%2fssrs-2017-cascading-parameters-with-dates%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            404 Error Contact Form 7 ajax form submitting

            How to know if a Active Directory user can login interactively

            TypeError: fit_transform() missing 1 required positional argument: 'X'