March 7, 2019 at 4:57 am
Hi,
I have the following sql query
SELECT DISTINCT
scheme.ABC.users_text_field, scheme.efg.PAL_efg, scheme.efg.PAL_oqty, scheme.efg.PAL_product, scheme.efg.PAL_date,
CASE WHEN scheme.efg.PAL_product = '000' THEN 'Test 1' WHEN scheme.efg.PAL_product = '999' THEN 'Test 2' WHEN scheme.efg.PAL_product = '888' THEN 'Test 3' WHEN scheme.efg.PAL_product
= '111' THEN 'Test 4' WHEN scheme.efg.PAL_product = '222' THEN 'Test 5' WHEN scheme.efg.PAL_product = '333' THEN 'Test 6' END AS ProductName, scheme.efg.PAL_porder,
scheme.efg.PAL_bin, scheme.xyz.users_text01, scheme.xyz.users_text02, scheme.xyz.users_text03, scheme.xyz.users_text04, scheme.xyz.users_text05, scheme.efg.PAL_insp_name,
scheme.LMN.alpha
FROM scheme.efg INNER JOIN
scheme.ABC ON scheme.efg.PAL_efg = scheme.ABC.users_text_field INNER JOIN
scheme.xyz ON scheme.ABC.lot_number = scheme.xyz.lot_number INNER JOIN
scheme.LMN ON scheme.efg.PAL_porder = scheme.LMN.order_no
WHERE (scheme.efg.PAL_product LIKE 'R00%')
Which runs fine, if I try and filter on the date column as below WHERE (scheme.efg.PAL_product LIKE 'R00%') AND (scheme.efg.PAL_date = CONVERT(DATETIME, '2019-04-03 00:00:00', 102))
It errors with a time out. If I leave it without the date filter and try to filter within SQL Server Report Builder it also times out.
Any ideas how to get around this?
Thanks
March 7, 2019 at 7:02 am
Shabbaranks - Thursday, March 7, 2019 4:57 AMHi,
I have the following sql query
SELECT DISTINCT
scheme.ABC.users_text_field, scheme.efg.PAL_efg, scheme.efg.PAL_oqty, scheme.efg.PAL_product, scheme.efg.PAL_date,
CASE WHEN scheme.efg.PAL_product = '000' THEN 'Test 1' WHEN scheme.efg.PAL_product = '999' THEN 'Test 2' WHEN scheme.efg.PAL_product = '888' THEN 'Test 3' WHEN scheme.efg.PAL_product
= '111' THEN 'Test 4' WHEN scheme.efg.PAL_product = '222' THEN 'Test 5' WHEN scheme.efg.PAL_product = '333' THEN 'Test 6' END AS ProductName, scheme.efg.PAL_porder,
scheme.efg.PAL_bin, scheme.xyz.users_text01, scheme.xyz.users_text02, scheme.xyz.users_text03, scheme.xyz.users_text04, scheme.xyz.users_text05, scheme.efg.PAL_insp_name,
scheme.LMN.alpha
FROM scheme.efg INNER JOIN
scheme.ABC ON scheme.efg.PAL_efg = scheme.ABC.users_text_field INNER JOIN
scheme.xyz ON scheme.ABC.lot_number = scheme.xyz.lot_number INNER JOIN
scheme.LMN ON scheme.efg.PAL_porder = scheme.LMN.order_no
WHERE (scheme.efg.PAL_product LIKE 'R00%')
Which runs fine, if I try and filter on the date column as belowWHERE (scheme.efg.PAL_product LIKE 'R00%') AND (scheme.efg.PAL_date = CONVERT(DATETIME, '2019-04-03 00:00:00', 102))
It errors with a time out. If I leave it without the date filter and try to filter within SQL Server Report Builder it also times out.Any ideas how to get around this?
Thanks
Saying "it errors out" doesn't tell us a thing. What is the full and complete error message that you receive?
March 7, 2019 at 7:44 am
Sorry the query was timing out - I have managed to get it working within SSMS by adding
Declare @theDateDATETIME
Set @TheDate ='2019-03-04'
and then using this in my select statement
WHERE (scheme.efg.PAL_product LIKE 'R00%') AND scheme.pallet.PAL_date= @theDate
But I was wondering how I would call this in my Report builder dataset as a Parameter instead of setting a specific date as per the query?
Thanks
March 7, 2019 at 8:22 am
Shabbaranks - Thursday, March 7, 2019 7:44 AMSorry the query was timing out - I have managed to get it working within SSMS by addingDeclare @theDateDATETIME
Set @TheDate ='2019-03-04'
and then using this in my select statement
WHERE (scheme.efg.PAL_product LIKE 'R00%') AND scheme.pallet.PAL_date= @theDateBut I was wondering how I would call this in my Report builder dataset as a Parameter instead of setting a specific date as per the query?
Thanks
Create a report parameter for PAL_date. There is a tutorial for report parameters - make note of step 3 in the tutorial:
Tutorial: Add a Parameter to Your Report (Report Builder)
Sue
March 7, 2019 at 9:14 am
Thanks Sue but I may be confusing my original question. I was struggling with my original question (which I was developing in SSMS and saving it as a view to link with SSRS). Although I was explicitly setting the scheme.pallet.PAL_date in SSMS I want to be able to use a parameter so the date can be queried (in SSRS). So creating a parameter isn't a problem but I think Im correct in saying you cant use the declare, set and then referencing the declared as @theDate for example given SSRS creates a parameter based on the @xxx if you see what I mean?
March 7, 2019 at 10:33 am
Would have to see the query plan to be sure, but it seems like SQL may be doing an implicit conversion on the date column, which would cause big performance issues.
The safest way to code it with a literal would be this:
(scheme.efg.PAL_date = '20190403')
assuming that PAL_date is any type of date column: smalldatetime, date, datetime, etc.. Then SQL will convert the literal to match the table column and never vice versa.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 7, 2019 at 10:41 am
Shabbaranks - Thursday, March 7, 2019 9:14 AMThanks Sue but I may be confusing my original question. I was struggling with my original question (which I was developing in SSMS and saving it as a view to link with SSRS). Although I was explicitly setting the scheme.pallet.PAL_date in SSMS I want to be able to use a parameter so the date can be queried (in SSRS). So creating a parameter isn't a problem but I think Im correct in saying you cant use the declare, set and then referencing the declared as @theDate for example given SSRS creates a parameter based on the @xxx if you see what I mean?
If you want it so the date can be queried in SSRS I would guess you mean that the user can enter a date in the report?
If that's what you are trying to do then yes you create a parameter for the report. You don't declare it like you do in SSMS. In the query for the dataset, you create the parameter there without using declare. When the user runs the report they enter a date and the date is passed to the query as a report parameter.
Sue
March 8, 2019 at 2:30 am
I think the reason for the loading loop and SQL Report Builder crashing when I run this report is something to do with the date selection format I have set up or it needs changing. Within SMSS the datatype of the scheme.pallet.PAL_date is datetime, I have formatted the text box on the report as Date DD/MM/YY (and set the parameter as date selection) but it doesn't seem to make any difference. As said the query works fine in SSMS but just not as a report in SSRS?
March 13, 2019 at 10:05 am
This would be a really good reason to create a stored procedure, which can be easily paremeterized, and from within SSRS.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 14, 2019 at 2:41 am
sgmunson - Wednesday, March 13, 2019 10:05 AMThis would be a really good reason to create a stored procedure, which can be easily paremeterized, and from within SSRS.
Hi - wouldn't you say the view is doing the same as the stored procedure? The problem lies somewhere within the report builder as I can run the report in no time within SSMS. I can save it as a stored procedure and then add that as a dataset and see if that makes a difference?
March 14, 2019 at 1:31 pm
Shabbaranks - Thursday, March 14, 2019 2:41 AMsgmunson - Wednesday, March 13, 2019 10:05 AMThis would be a really good reason to create a stored procedure, which can be easily paremeterized, and from within SSRS.Hi - wouldn't you say the view is doing the same as the stored procedure? The problem lies somewhere within the report builder as I can run the report in no time within SSMS. I can save it as a stored procedure and then add that as a dataset and see if that makes a difference?
That's what I would recommend. A view is not the same as a stored procedure. The entire view may be getting materialized before any filters get applied, and that could be what slows it down.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2019 at 4:14 am
Hi,
I've given it a go and it seems to work although I cant seem to set a parameter on the data now I am using a stored procedure? Before I would edit the query and add the parameter in there but this option doesn't seem to be available?
March 15, 2019 at 7:39 am
Shabbaranks - Friday, March 15, 2019 4:14 AMHi,
I've given it a go and it seems to work although I cant seem to set a parameter on the data now I am using a stored procedure? Before I would edit the query and add the parameter in there but this option doesn't seem to be available?
Post your stored procedure. Stored procedures can have parameters, and you should build that into the procedure.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 18, 2019 at 5:49 am
sgmunson - Friday, March 15, 2019 7:39 AMShabbaranks - Friday, March 15, 2019 4:14 AMHi,
I've given it a go and it seems to work although I cant seem to set a parameter on the data now I am using a stored procedure? Before I would edit the query and add the parameter in there but this option doesn't seem to be available?Post your stored procedure. Stored procedures can have parameters, and you should build that into the procedure.
Hi,
Ive managed to get this sorted thank you for all your help. I added the parameter into the stored procedure and then created the parameter in SSRS.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply