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.
sql-server tsql reporting-services parameters edited Nov 27 '18 at 1:22
aduguid
2,23661132
asked Nov 21 '18 at 2:43
Liliana TorresLiliana Torres
1111
add a comment |
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.
sql-server tsql reporting-services parameters edited Nov 27 '18 at 1:22
aduguid
2,23661132
asked Nov 21 '18 at 2:43
Liliana TorresLiliana Torres
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 '18 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 '18 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 '18 at 2:24
add a comment |
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.
sql-server tsql reporting-services parameters edited Nov 27 '18 at 1:22
aduguid
2,23661132
asked Nov 21 '18 at 2:43
Liliana TorresLiliana Torres
1111
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 sql-server tsql reporting-services parameters edited Nov 27 '18 at 1:22
aduguid
2,23661132
asked Nov 21 '18 at 2:43
Liliana TorresLiliana Torres
1111
edited Nov 27 '18 at 1:22
aduguid
2,23661132
asked Nov 21 '18 at 2:43
Liliana TorresLiliana Torres
1111
edited Nov 27 '18 at 1:22
aduguid
2,23661132
edited Nov 27 '18 at 1:22
aduguid
2,23661132
edited Nov 27 '18 at 1:22
aduguid
2,23661132
2,23661132
asked Nov 21 '18 at 2:43
Liliana TorresLiliana Torres
1111
asked Nov 21 '18 at 2:43
Liliana TorresLiliana Torres
1111
asked Nov 21 '18 at 2:43
Liliana TorresLiliana 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 '18 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 '18 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 '18 at 2:24
add a comment |
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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 2:24
add a comment |
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))))
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
add a comment |
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:
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
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 '18 at 6:11
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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))))
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
add a comment |
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))))
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
add a comment |
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))))
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
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))))
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
answered Nov 21 '18 at 3:18
Dale BurrellDale Burrell
3,36042655
3,36042655
add a comment |
add a comment |
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:
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
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 '18 at 6:11
add a comment |
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:
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
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 '18 at 6:11
add a comment |
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:
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
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:
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
edited Nov 27 '18 at 1:33
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
answered Nov 21 '18 at 3:38
aduguidaduguid
2,23661132
2,23661132
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 '18 at 6:11
add a comment |
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 '18 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 '18 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 '18 at 6:11
add a comment |
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.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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 '18 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 '18 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 '18 at 2:24