April 1, 2012 at 9:37 am
Hi,
We have a parameter which shows dynamically the billing cycle in the report, our billing cycle is from 21th this month-20th next month.
In the billing cycle parameter, we need to create 20 drop-downs.
we had problem to dynamically switch the year to 2011 or so. Here are two examples following:
1. Today is 4/1/2012, so first drop-down should be 3/21/2012-4/20/2012, then 2/21/2012-3/20/2012, 1/21/2012-2/20/2012, 12/21/2011-1/20/2012..(here comes the problem, how do we dynamically know if it goes to Dec, so year should be 2011)
2. If report renders at 2/12/2012, first drop-down is 1/21/2012-2/20/2012, then 12/20/2011-1/20/2012....
I've created ssrs expression for the first drop-down, but cannot implement logic of dec,2011.
=iif(day(Today)<21, format(DateAdd("m",-1,Today),"MM")&"/21/2012-"&Format(Today,"MM")&"/20/2012",Format(Today,"MM")&"/21/2012-"&format(DateAdd("m",1,Today),"MM")&"/20/2012")
Posted this on msdn forum, haven't got any update. Appreciate for the help.
April 2, 2012 at 2:54 am
Hello,
This might help you, rather than doing into SSRS, create new datasource and use below code for your parameter
[Code]
DECLARE @Day INT
SET @Day = datePart(dd,getDate())
IF @DAY < 21
BEGIN
SELECTCONVERT(VARCHAR(25),(dateAdd(MONTH, dateDiff(MONTH, 0, getDate()), 0) + 20),103)+
' - '+
CONVERT(VARCHAR(25),((dateAdd(mm,-1,getDate() ) - datePart(d,getDate()))+20),103)
END
ELSE
SELECTCONVERT(VARCHAR(25),((dateAdd(mm,-1,getDate() ) - datePart(d,getDate()))+21),103)+
' - '+
CONVERT(VARCHAR(25),((dateAdd(mm,-2,getDate() ) - datePart(d,getDate()))+20),103)
[/Code]
and keep amx happy!!!;-)
April 2, 2012 at 8:35 am
Hi Conficker,
Appreciate for your inputs. The dataset you provided is the same as my ssrs expression, from which get the first drop-down. But I had some problems for the rest 19 drop-downs following because of year will change to 2011, maybe 2010 even.
Do you have any suggestions for that? Thank you.
April 2, 2012 at 8:51 am
could explain your problem into more detail, plz?? if possible then with example??
19 drop-downs means, 19 previous months??? is that 19 different parameters??
April 2, 2012 at 9:30 am
Sure, sorry I didn't clarify clearly. This is only one parameter, which has 20 drop-downs of 20 date range. And we can choose any of it to render report. First one is based on the date renders report, and following are previous 20 cycles(months)
For example,
1. Today is 4/2/2012, then for this parameter, there are 20 drop-downs and we can choose any of them to render report. first drop-down should be 3/21/2012-4/20/2012, then 2/21/2012-3/20/2012, 1/21/2012-2/20/2012, 12/21/2011-1/20/2012, 11/21/2011-12/20/2011, 10/21/2011-11/20/2011......and so on
2. If report renders at 2/12/2012, first drop-down is 1/21/2012-2/20/2012 because date 2/12/2012 falls into this range, then 12/21/2011-1/20/2012, 11/21/2011-12/20/2011, 10/21/2011-11/20/2011, 9/21/2011-10/20/2011.....and so on
April 2, 2012 at 9:34 am
For a SQL based solution, you can try this, but please note that it won't work for billing dates that start on the 29th, 30th, or 31st of the month.
declare @BillDate datetime;
declare @StartDay int;
set @StartDay = 21; -- Beginning day of billing cycle, this code will not work properly for billing dates starting 29 - 31
set @BillDate = cast('20120401' as datetime); -- Test date
select @BillDate;
with e2 (
N
) as (
select 1 union all select 1
)
,e10 (
N
) as (
select 1 union all select 1 union all select 1 union all select 1 union all select 1
union all select 1 union all select 1 union all select 1 union all select 1 union all select 1
)
,e20 (
N
) as (
select row_number() over (order by (select null)) from e2 a cross join e10 b
)
--select N from e20;
select
convert(varchar(10),dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)),101) + ' - ' +
convert(varchar(10),dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)))),101)
from e20
April 2, 2012 at 9:52 am
DECLARE @Day INT
SET @Day = datePart(dd,getDate())
DECLARE @i INT
SET @i = 0
CREATE TABLE #temp1
(para VARCHAR(50))
WHILE (@i<20)
BEGIN
IF @DAY < 21
BEGIN
INSERT INTO #temp1
SELECTCONVERT(VARCHAR(25),((dateAdd(mm,-(@i+0),getDate() ) - datePart(d,getDate()))+21),103)+
' - '+
CONVERT(VARCHAR(25),((dateAdd(mm,-(@i+1),getDate() ) - datePart(d,getDate()))+20),103)
END
ELSE
BEGIN
INSERT INTO #temp1
SELECTCONVERT(VARCHAR(25),((dateAdd(mm,-(@i+1),getDate() ) - datePart(d,getDate()))+21),103)+
' - '+
CONVERT(VARCHAR(25),((dateAdd(mm,-(@i+2),getDate() ) - datePart(d,getDate()))+20),103)
END
SET @i = @i+1
END
SELECT * FROM #temp1
DROP TABLE #temp1
here we go ...!:)
April 2, 2012 at 9:56 am
Conficker (4/2/2012)
DECLARE @Day INTSET @Day = datePart(dd,getDate())
DECLARE @i INT
SET @i = 0
CREATE TABLE #temp1
(para VARCHAR(50))
WHILE (@i<20)
BEGIN
IF @DAY < 21
BEGIN
INSERT INTO #temp1
SELECTCONVERT(VARCHAR(25),((dateAdd(mm,-(@i+0),getDate() ) - datePart(d,getDate()))+21),103)+
' - '+
CONVERT(VARCHAR(25),((dateAdd(mm,-(@i+1),getDate() ) - datePart(d,getDate()))+20),103)
END
ELSE
BEGIN
INSERT INTO #temp1
SELECTCONVERT(VARCHAR(25),((dateAdd(mm,-(@i+1),getDate() ) - datePart(d,getDate()))+21),103)+
' - '+
CONVERT(VARCHAR(25),((dateAdd(mm,-(@i+2),getDate() ) - datePart(d,getDate()))+20),103)
END
SET @i = @i+1
END
SELECT * FROM #temp1
DROP TABLE #temp1
here we go ...!:)
Why use a while loop? You will find the code I provided just as efficient and much more scalable.
April 2, 2012 at 10:13 am
Thank you Lynn and Conficker, your solutions both worked.
BTW, is there a button to mark as a answer? I didn't find it.
April 2, 2012 at 10:27 am
Glad to help.
And no, there is no button to mark the thread as answered. The thread will stay open and others may come along and offer other suggestions or even ask additional questions regarding the solutions.
April 3, 2012 at 1:57 am
Lynn,
Just different way to write a code, if both works then nothing to complain:-D
April 3, 2012 at 5:19 am
Conficker (4/3/2012)
Lynn,Just different way to write a code, if both works then nothing to complain:-D
Resources used. Your code has to create a temporary table, populate it using a loop using 20 inserts, read the data back from the table and then drop the temporary table.
It is more than writing what works, it is also writing what works efficiently as well.
December 18, 2017 at 2:56 pm
Lynn Pettis - Monday, April 2, 2012 9:34 AMFor a SQL based solution, you can try this, but please note that it won't work for billing dates that start on the 29th, 30th, or 31st of the month.declare @BillDate datetime;declare @StartDay int;set @StartDay = 21; -- Beginning day of billing cycle, this code will not work properly for billing dates starting 29 - 31set @BillDate = cast('20120401' as datetime); -- Test dateselect @BillDate;with e2 ( N) as (select 1 union all select 1),e10 ( N) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1),e20 ( N) as (select row_number() over (order by (select null)) from e2 a cross join e10 b)--select N from e20;select convert(varchar(10),dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)),101) + ' - ' + convert(varchar(10),dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)))),101)from e20
I know this is a really old thread, but was wondering if you've came up with a solution that works for any start date (including 29th, 30th, and 31st). Thank you
December 18, 2017 at 3:30 pm
o103452 - Monday, December 18, 2017 2:56 PMLynn Pettis - Monday, April 2, 2012 9:34 AMFor a SQL based solution, you can try this, but please note that it won't work for billing dates that start on the 29th, 30th, or 31st of the month.declare @BillDate datetime;declare @StartDay int;set @StartDay = 21; -- Beginning day of billing cycle, this code will not work properly for billing dates starting 29 - 31set @BillDate = cast('20120401' as datetime); -- Test dateselect @BillDate;with e2 ( N) as (select 1 union all select 1),e10 ( N) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1),e20 ( N) as (select row_number() over (order by (select null)) from e2 a cross join e10 b)--select N from e20;select convert(varchar(10),dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)),101) + ' - ' + convert(varchar(10),dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)))),101)from e20I know this is a really old thread, but was wondering if you've came up with a solution that works for any start date (including 29th, 30th, and 31st). Thank you
Nope, haven't had any reason to work on this.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply