June 25, 2015 at 1:32 pm
Good Day to all,
I'm in the processes of creating a SSRS report but have a couple of stumbling block to get over before it is completed. My query is working and returns the excepted dataset.
But to make it work in SSRS on a subscription is beginning to haunt me.
first, I have two int fields that pull periods
@BeginPeriod,
@End Period both INT.
Inside my query I have the following:
WHERE TT11.PERIOD >= ''+@BeginPeriod+''
AND TT11.PERIOD <= ''+@EndPeriod+''
------- Well period is an INT (example 201501,201502,201503,201504,201505)
I would like the user to be able to execute the report using the date picker, but the parameter is an INT and will not work, so the first thing I would like to do is convert the INT into a datetime. basically if the user picks 04/14/2015 from the date picker the query will know to pull period 201504 and the same for the @EndPeriod.
After (with some help from SQl Server Central) that is working I want to be a able to put this report into a subscription. It's a rolling 12 month report. If this was a straight datetime field i wouldn't be asking for help on either points, but like I mention it is haunting me.
June 25, 2015 at 1:53 pm
After reading, I wasn't 100% sure if you wanted to go from int to date, or date to int. I've included a way to do both. Hopefully this will help.
--Int to Date
DECLARE @Date INT
SET @Date = 201506
SELECT
[AsInt] = @Date
,[AsDate] = CONVERT(DATETIME,CONVERT(VARCHAR,@Date)+'01')
--Date to Int
DECLARE @Date DATETIME
SET @Date = '2015-06-01'
SELECT
[AsDate] = @Date
,[AsInt] = LEFT(CONVERT(varchar,@Date,112),6)
June 25, 2015 at 2:32 pm
GOOD DAY SqlSanctum,
Maybe I didn't explain myself correctly, or I'm just confused.
Both fields
@BeginPeriod INT,
@EndPeriod INT
are being used as parameter(s) in my where clause
WHERE TT11.PERIOD >= ''+@BeginPeriod+''
AND TT11.PERIOD <= ''+@EndPeriod+''
am I replacing the where clause with the script you supplied ? or is this to be added and if added where??
Sorry in advance but this is new to me.
Regards,
David
June 25, 2015 at 2:38 pm
Try this:
...
WHERE TT11.Period>=@BeginPeriod
AND TT11.Period<=@EndPeriod
June 25, 2015 at 2:40 pm
dmarz96 (6/25/2015)
GOOD DAY SqlSanctum,Maybe I didn't explain myself correctly, or I'm just confused.
Both fields
@BeginPeriod INT,
@EndPeriod INT
are being used as parameter(s) in my where clause
WHERE TT11.PERIOD >= ''+@BeginPeriod+''
AND TT11.PERIOD <= ''+@EndPeriod+''
am I replacing the where clause with the script you supplied ? or is this to be added and if added where??
Sorry in advance but this is new to me.
Regards,
David
If the user selected date is @UserSelectedDate, for example, and is a DATE or DATETIME, and you want the year and month for that date, for both of the 2 parameters, then you could set the parameters like this.
SET @BeginPeriod = (DATEPART(YEAR, @UserSelectedDate) * 100) + DATEPART(MONTH, @UserSelectedDate)
SET @EndPeriod = (DATEPART(YEAR, @UserSelectedDate) * 100) + DATEPART(MONTH, @UserSelectedDate)
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 25, 2015 at 2:55 pm
Ok let me start this all over again to be clear.
First the query works perfectly fine and bring back the correct data set. It's when dealing with the parameter that is where the confusing starts for me.
My parameters are as followed:
@BeginPeriod INT,
@EndPeriod INT
and part of my where clause
WHERE TT11.PERIOD >= ''+@BeginPeriod+''
AND TT11.PERIOD <= ''+@EndPeriod+''
TT11.Period is an int field (ex 201501,201502,201502)
my challenge is to be able to let the end user choose a date from the date picker (or calendar).
for this example let's say the user picks 04/14/2015 from the date picker.
I want the query to be able to know to choose 201504 period.
After i get this corrected I want to be able to create a subscription to be able to run this report on a monthly basis and increment the 2 parameter(s) by 1 month.
do something like this =DateAdd("M",+1,Today())
can't do this if the parameter is not a date field.
Hopefully I explained my issue clearer like I mention I new to this.
Regards,
David Martinez
June 25, 2015 at 3:09 pm
Good Day Alvin,
thanks for you input, but I don't understand how that is going to get me the end result that I'm looking for.
Please be patient with me like I mentioned this is new to me.
June 25, 2015 at 3:14 pm
You say you want to run this on a monthly basis, so if I ran this with today's date, what month should the period cover?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 25, 2015 at 3:39 pm
This particular report would be ran after the books are closed.
So if it was ran today it would be for period 201505. Most cases this report would run for the previous month.
Regards,
David
June 25, 2015 at 3:49 pm
dmarz96 (6/25/2015)
This particular report would be ran after the books are closed.So if it was ran today it would be for period 201505. Most cases this report would run for the previous month.
Regards,
David
If you always want to run for the previous month, then you could do something like:
SET @BeginPeriod = CAST(CONVERT(VARCHAR(6), DATEADD(MONTH, -1, GETDATE()), 112) AS INT)
SET @EndPeriod = CAST(CONVERT(VARCHAR(6), DATEADD(MONTH, -1, GETDATE()), 112) AS INT)
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 26, 2015 at 8:47 am
We have lots of reports that use the Period field. We just put a parameter for Period with a description that tells the user what to enter. No coding or converting needed.
The users can do it. It's not that difficult.
Example:
Begin Period (yyyydd):
June 26, 2015 at 9:11 am
Yes this is a true statement but how do you increment the period when you schedule the report??
June 26, 2015 at 9:49 am
The only way that I know of to run a report with custom parameters that use coding is to use a Data Driven Subscription.
Here is link to more information:
https://msdn.microsoft.com/en-us/library/ms169673.aspx
Here is the code I would use to get the rolling 12-month periods:
select GETDATE() AS 'CURRENT_DATE'
,CAST(CONVERT(VARCHAR(4),YEAR(GETDATE())) + Right('0' + Convert(VarChar(2), MONTH(GetDate())), 2) AS INT) AS CURRENT_PERIOD
,CAST(CONVERT(VARCHAR(4),YEAR(DATEADD(mm,-12,GETDATE()))) + Right('0' + Convert(VarChar(2), MONTH(DATEADD(mm,-12,GETDATE()))), 2) AS INT) AS PERIOD_12_MONTH_PRIOR
June 26, 2015 at 9:56 am
If you want them to be able to just use the date picker, you should be able to just use two variables.
The report interface supplies you with a date variable, you convert that date variable to an int, which you then use to run the code.
In the report you'd have @ReportDate DATETIME as a parameter to the stored procedure that generates the report, then you'd convert that datetime to an integer in the code. It's a bit messy, but it's a workaround. So the report is happy that it is using a date, but your code/tables are still happy because they see an integer.
--report UI provides the value for date, you could also provide a default value
--of GETDATE() to @ReportDate so that the report auto runs the current year/month.
DECLARE @CodeDate INT
SET @CodeDate = LEFT(CONVERT(VARCHAR(6),@ReportDate,112),6) --format now YYYYMM
That should make the date into a usable 6 digit integer for your report to recognize. Then use the variable in your where clause and everything should work normally.
June 26, 2015 at 10:27 am
Good Day To All, with respect and thanks to all!
OK I think I have a work around with everyones input and a breif discussion with the users. I can get the user to enter the first of the month for both parameter just have to put verbage in the report design when interacting with the date picker.
I include only the code that was added, prior code worked.
--But I get this error
Msg 241, Level 16, State 1, Procedure ALSP_ROLLING12_MONTH_REPORT_BILLABLE_HOURS_ONLY, Line 52
Conversion failed when converting date and/or time from character string.
-- how the stored procedure is executed
EXEC ALSP_ROLLING12_MONTH_REPORT_BILLABLE_HOURS_ONLY '2014-05-01','2015-04-01'
-- Declare Parameters to passed to report
(
@BeginPeriod DateTime,
@EndPeriod DateTime
)
DECLARE @BeginPeriod2 VARCHAR(20) SET @BeginPeriod2 = CONVERT(VARCHAR(20), @BeginPeriod, 100)
DECLARE @EndPeriod2 VARCHAR(20) SET @EndPeriod2 = CONVERT(VARCHAR(20), @EndPeriod, 100)
-- code in my where clause
WHERE CONVERT(DATETIME,CONVERT(VARCHAR,TT11.PERIOD)+'01') >= ''+@BeginPeriod2+''
AND CONVERT(DATETIME,CONVERT(VARCHAR,TT11.PERIOD)+'01') <= ''+@EndPeriod2+''
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply