March 21, 2016 at 6:25 am
Hi All,
Apologies if I have committed a sin but am starting a new thread after updating another as this is more SSRS than SQL itself.
So I have this sp I have created:
CREATE Procedure [dbo].[rpt_APV_BY_JOB_TITLE_SALES_PERF]
(
@CLUSTER_CODE as nvarchar(3),
@START_MONTH as nvarchar(2),
@START_YEAR as nvarchar(4),
@END_MONTH as nvarchar(2),
@END_YEAR as nvarchar(4),
@TITLES as varchar(8000)
)
as
begin
-- exec [dbo].[rpt_APV_BY_JOB_TITLE] 'SUK','1','2013','12','2013
set nocount on
declare @START_DATE AS datetime
declare @END_DATE AS datetime
/*SET @START_DATE = @START_YEAR + right('0' + @START_MONTH, 2) + '01 00:00:00'
SET @END_DATE = @END_YEAR + right ('0' +@END_MONTH, 2) + '01 00:00:00'
SET @END_DATE = Dateadd(m,1,@END_DATE)*/
set @START_DATE = @START_YEAR + '-' + @START_MONTH + '- 01 00:00:00'
set @END_DATE = @END_YEAR + '-' + @END_MONTH + '- 01 00:00:00'
set @END_DATE = DateAdd(m,1,@END_DATE)
;
select count(distinct V.REF) as [Number Of Refs],
count(distinct APP.APP_ID) as [Number of Apps],
case when v.SERVICE_SUB_CATEGORY like 'L%' then 'Standard'
when v.SERVICE_SUB_CATEGORY like 'S%' then 'Branded'
else 'Bespoke' end as [Ref Type],
s.Item as [Job Title],
coalesce(count(distinct app.APP_ID),0)/cast(count(distinct v.REF)as float) as [APV]
from REF V
inner join APP APP with (NOLOCK) on APP.REF = V.REF
inner join SITE_CLUSTER_X scx with (NOLOCK) on V.AGENCY_CC = scx.SITE_CODE and scx.CLUSTER_CODE = @cluster_code
-- Here's the magic
inner join dbo.DelimitedSplit8k(@TITLES, ',') s ON V.JOB_TITLE like '%' + s.Item + '%'
------------------
where v.CREATE_DATE >= @START_DATE
and v.CREATE_DATE < @END_DATE
group by s.Item,
case when v.SERVICE_SUB_CATEGORY like 'L%' then 'Standard'
when v.SERVICE_sub_CATEGORY like 'S%' then 'Branded'
else 'Bespoke' end
order by [Job Title]
option(recompile)
end
I had the awesome help from others with this:
http://www.sqlservercentral.com/Forums/Topic1769850-8-1.aspx#bm1770134
So I have added a delimiter to my report so people can add in job titles wildcard style between specific dates.
Works as an sp like a DREAM. In SSRS not so much it throws up the conversion failed error.
Any pointers? I am assuming it's because there are no datetime on the function but I am unsure where I would put that. Can't change the date format in SSRS params as they are month and year and not the whole date.
As per usual I am almost there and this is warping my mind hehe. As always, greatly appreciated!:-)
March 21, 2016 at 6:29 am
Please post the full text of the error message.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 21, 2016 at 6:35 am
It is:
An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset "FULL_DATA".
Conversion failed when converting date and/or time from character string.
No errors when I run the sp manually in the data set or in SSMS, only happens when I do a preview and try to view the data via that.
Thank you!
March 21, 2016 at 6:52 am
Instead of
set @START_DATE = @START_YEAR + '-' + @START_MONTH + '- 01 00:00:00'
set @END_DATE = @END_YEAR + '-' + @END_MONTH + '- 01 00:00:00'
Try this:
set @START_DATE = try_Parse(@START_YEAR + '-' + @START_MONTH + '- 01 00:00:00' as datetime);
set @END_DATE = try_parse(@END_YEAR + '-' + @END_MONTH + '- 01 00:00:00' as datetime);
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 21, 2016 at 8:14 am
You know, I am a good baker I wish I could pay all you good people in cakes.
Worked perfectly, am adding try_parse to my list of things newly learnt. I kept trying convert with the datetime piece and it wasn't working. Thank you VERY much
March 21, 2016 at 8:50 am
Hmm it's always something. Oddly no data is going into the table although the report will run/no errors. So got a table with the headers but no data.
Running fine everywhere else, any suggestions? I am also googling as well.
March 21, 2016 at 9:30 am
Those cakes sound tempting:-)
What I suspect is that the TRY_PARSE is merely hiding a more fundamental problem. The fact that you were getting errors before means (I think) that the result of the TRY_PARSE is NULL and that's feeding into your WHERE clause and consequently selecting zero rows.
Also, do you really need to use DateTimes - it looks like dates should be accurate enough.
You could try feeding some error-trapping in too, in the hope of getting some useful info back out at point of failure. Have a look at this & see whether it may help:
SET nocount ON;
DECLARE @START_DATE date;
DECLARE @END_DATE AS date;
DECLARE @Msg varchar(100);
BEGIN TRY
SET @START_DATE = DATEFROMPARTS(@START_YEAR, @START_MONTH,1)
SET @END_DATE = DATEFROMPARTS(@END_YEAR, @END_MONTH,1);
SET @END_DATE = DateAdd(m,1,@END_DATE);
END TRY
BEGIN CATCH
SET @Msg = concat('Date conversion issue. ', 'Start year: ', cast(@start_year AS varchar(10)), ' Start month: ', cast(@Start_Month AS varchar(10)));
THROW 999999, @Msg, 1
END CATCH
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 21, 2016 at 9:51 am
Check the defaults for your parameters or avoid your report from executing automatically.
March 21, 2016 at 9:55 am
Thank you again for this, I am reading everything at the moment so thoroughly as it can only make my coding better :).
If I tell you what I did you might laugh. I deleted the dataset and started again and lo and behold it appeared. I just had a funny feeling that might work, of all the things!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply