SSRS 2017 Cascading parameters with dates
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 at 1:22
aduguid
2,12661031
asked Nov 21 at 2:43
Liliana Torres
1111
add a comment |
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 at 1:22
aduguid
2,12661031
asked Nov 21 at 2:43
Liliana 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 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
add a comment |
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 at 1:22
aduguid
2,12661031
asked Nov 21 at 2:43
Liliana 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 at 1:22
aduguid
2,12661031
asked Nov 21 at 2:43
Liliana Torres
1111
edited Nov 27 at 1:22
aduguid
2,12661031
asked Nov 21 at 2:43
Liliana Torres
1111
edited Nov 27 at 1:22
aduguid
2,12661031
edited Nov 27 at 1:22
aduguid
2,12661031
edited Nov 27 at 1:22
aduguid
2,12661031
2,12661031
asked Nov 21 at 2:43
Liliana Torres
1111
asked Nov 21 at 2:43
Liliana Torres
1111
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
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 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
add a comment |
2 Answers
2
active
oldest
votes
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 at 3:18
Dale Burrell
2,73512147
add a comment |
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 at 3:38
aduguid
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
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
});
}
});
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
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 at 3:18
Dale Burrell
2,73512147
add a comment |
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 at 3:18
Dale Burrell
2,73512147
add a comment |
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 at 3:18
Dale Burrell
2,73512147
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 at 3:18
Dale Burrell
2,73512147
answered Nov 21 at 3:18
Dale Burrell
2,73512147
answered Nov 21 at 3:18
Dale Burrell
2,73512147
answered Nov 21 at 3:18
Dale Burrell
2,73512147
2,73512147
add a comment |
add a comment |
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 at 3:38
aduguid
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
add a comment |
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 at 3:38
aduguid
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
add a comment |
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 at 3:38
aduguid
2,12661031
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 at 3:38
aduguid
2,12661031
edited Nov 27 at 1:33
answered Nov 21 at 3:38
aduguid
2,12661031
answered Nov 21 at 3:38
aduguid
2,12661031
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
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 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
add a comment |
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.
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 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