Sql-server – SSRS 2014 – query running fine on management studio – report timing out – how to troubleshoot

I had a problem in reporting services 2014 – all the queries in a report were running fine from management studio but timing out when running the report.

here is the message from the user:

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS logs?

the report has 3 datasets and all of them run fine when I take the query off and run it through SSMSS.

this reporting services server, has only the service, the databases (ReportServer, etc) are on a different server but that is not the problem, it has been running for months.

this is the query of the report:

USE Product
declare @SeasonId int;set @SeasonId = 16

SELECT DISTINCT 
        EDP [EDP],
        '-' [PO],
        NULL [PO Type Initial/ Repeat],
        NULL [PO Status Open/ Close],
        s.StyleCode + c.ColourCode + sz.SizeCode [SKU] ,
        StyleDesc [Style Description] ,
        s.StyleCode [Style] ,
        c.ColourCode [Option] ,
        SizeCode [Size] ,
        NULL [MPLAN Segment Code],
        SeasonDesc [Season],
        DepartmentDesc [Department],
        CategoryDesc [Category],
        NULL [ALL Season, SPR Only, SUM Only, INTERNET Only],
        NULL [Fabric Type],
        NULL [Garment Composition],
        NULL [Country Of Origin],
        NULL [MID Code],
        NULL [HTS Code],
        0.00 [Cost to use],
        NULL [Exception Flag],
        NULL [Duty Rate]
FROM    [Product].[dbo].[Style] s
        JOIN Product.dbo.StyleDetail sd ON s.StyleID = sd.StyleID
        JOIN [Product].[dbo].[Category] ct ON s.CategoryID = ct.CategoryID
        JOIN [Product].[dbo].[Department] d ON ct.DepartmentID = d.DepartmentID
        JOIN [Product].[dbo].[Segment] sg ON d.SegmentID = sg.SegmentID --
        JOIN [Product].[dbo].[Item] i ON s.styleid = i.styleid
        JOIN [Product].[dbo].[ItemColour] ic ON i.itemid = ic.itemid
        JOIN [Product].[dbo].[Colour] c ON ic.colourid = c.colourid
        JOIN [Product].[dbo].[ItemSize] isz ON i.itemid = isz.itemid
        JOIN [Product].[dbo].[Size] sz ON isz.sizeid = sz.sizeid
        JOIN [Product].[dbo].[SeasonItem] si ON i.itemid = si.itemid
        JOIN [Product].[dbo].[Season] se ON se.seasonid = si.seasonid
        JOIN [product].[dbo].[ItemMPLANReference] pn ON pn.itemid = i.itemid
        JOIN [Product].[dbo].[Product_Staging_CategoryImportMap] ci ON ci.CategoryID = ct.CategoryID
        JOIN [SAPurchaseOrder].[dbo].[tblPO1Detail] pod ON pn.EDP = pod.strItemNo

WHERE se.seasonid IN (@SeasonId)
and EDP not in (SELECT skuEDP from USCarrier.dbo.WHDespatchSku)
ORDER BY 1

this problem has been solved.
how I did it?

I had a look at the query plan and noticed a missed index.

USE [SAPurchaseOrder]
GO
CREATE NONCLUSTERED INDEX [idx_strItemNo]
ON [dbo].[tblPO1Detail] ([strItemNo])

GO

After I added the index the report started to work fine again.

What I think that really happened is:
SSRS had cached the query execution plan, and there was a case of parameter sniffing.
Once I created the index the plan was removed from cache and had to be recompiled.

this is very similar to this question here:
SQL runs slow in SSRS, but fast in SSMS

One good thing I think useful from the question above is how to eliminate the parameter sniffing possibility:

As discussed in the comments, let's get rid of the parameters to see
if your query is getting affected by parameter sniffing.

To do this we build the SQL statement from scratch. Most things in
SSRS can be expressions including the SQL query so you can build it as
a string. With the parameters, we'll convert them to a comma delimited
list using JOIN.

questions remain:

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS 2014 logs?