0

I am new to developing reports with large amounts of data, so I am looking for some advice with a problem I am having.

I am developing a SSRS report with 6 parameters. Each Parameter has its' own data set that specifies a distinct list of values for the parameters.

The user will be able to choose as many values as they want for each parameter, except for 1 (the date).

The query looks something like;

SELECT [CURR].[PERIOD]
,[CURR].[PERIOD_MTD]
,[CURR].[CATEGORY1]
,[CURR].[CATEGORY2]
,[CURR].[CATEGORY3]
,[CURR].[CATEGORY4]
,[CURR].[CATEGORY5]
,[CURR].[CALCULATION1_ITD]
,[CURR].[CALCULATION2_ITD]
,[CURR].[CALCULATION3_ITD]
,[CURR].[CALCULATION4_ITD]
,[CURR].[CALCULATION1_MTD]
,[CURR].[CALCULATION2_MTD]
,[CURR].[CALCULATION3_MTD]
,[CURR].[CALCULATION4_MTD]
FROM [BIG_TABLE] [CURR] LEFT OUTER JOIN
[BIG_TABLE] [PREV M]
    ON [CURR].[PERIOD_MTD] = [PREV M].[PERIOD]
    AND [CURR].[CATEGORY1] = [PREV M].[CATEGORY1]
    AND [CURR].[CATEGORY2] = [PREV M].[CATEGORY2]
    AND [CURR].[CATEGORY3] = [PREV M].[CATEGORY3]
    AND [CURR].[CATEGORY4] = [PREV M].[CATEGORY4]
    AND [CURR].[CATEGORY5] = [PREV M].[CATEGORY5]
WHERE [CURR].[PERIOD] = @YYYYMM
AND [CURR].[CATEGORY1] IN (@PARAMETER1)
AND [CURR].[CATEGORY2] IN (@PARAMETER2)
AND [CURR].[CATEGORY3] IN (@PARAMETER3)
AND [CURR].[CATEGORY4] IN (@PARAMETER4)
AND [CURR].[CATEGORY5] IN (@PARAMETER5)

This is the problem I am having; 1 parameter has a distinct list of over 5,500 values that the user can choose from. When all of the values are selected, I notice that the parameter field does not populate like the others (Image below).

Picture of Parameters

When the report runs, I get the following error:

Error Message

This message is pretty ambiguous, but I isolated it to the fact that the report will run with fewer values in this parameter, but not all of them.

I am not sure what else to try. I am thinking this may be a matter of getting a large volume a data through the main data set.

Extra information:

Datasource is accessed with a shared connection to SharePoint

The table that is queried for this report has no indexes. I wonder if this is important because the table has roughly 26.5mill rows.

Clifford Piehl
  • 483
  • 1
  • 4
  • 11
  • 1
    When you checked the error on the report server, what did it tell you? – Thom A Jun 26 '20 at 13:20
  • how are you passing the parameters to the main query? Please edit the question to show the dsMain dataset query. – Alan Schofield Jun 26 '20 at 13:27
  • @Larnu I don't think I have any ability to do that. I cannot change the settings of the server to enable remote errors. – Clifford Piehl Jun 26 '20 at 13:39
  • @AlanSchofield The parameters are inserted into a WHERE claus IN (@parameter). I will edit the question to show this. – Clifford Piehl Jun 26 '20 at 13:39
  • I'm not asking you to enable remote errors, @CliffordPiehl, I'm asking you to check the logs on the server, and find the error and post that. – Thom A Jun 26 '20 at 13:41
  • @Larnu can you give me a resource that would show me how to do that? I do not think I have access to those logs. I am trying to access them under the 'Management' folder in SSMS and the execution fails because I do not have permissions. – Clifford Piehl Jun 26 '20 at 14:05
  • 2
    You won't see them in SSMS, no. If you don't have access to the server, I suggest talking to someone who does, as they can tell you the error. – Thom A Jun 26 '20 at 14:10

1 Answers1

0

I would suggest your approach is wrong.

If you are presenting a user with a list of 5000+ items to choose from I'm guessing they would either choose small number of them or want to select all of them, it would be unlikely that they would sit there and choose 100 items from a list.

if this is the case then I would suggest appending an "ALL" option to the list (UNION to your original list) and then amend the query something like this...

WHERE [CURR].[PERIOD] = @YYYYMM
AND (@PARAMETER1 = 'ALL' OR [CURR].[CATEGORY1] IN (@PARAMETER1))
...
...

You might want to also read these previous SO questions and MS Article.

"IN" clause limitation in Sql Server

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thank you for the articles! I think these relate to my problem. I am confused about your query suggestion. How does the parameter identify records from the main dataset when it is set equal to 'ALL'? Would it not have to find records in the main dataset where the column has 'ALL' as its' value? (I have tried it, the report comes back blank) Also, parameters that allow multiple values already have a 'select all' option where it imputes a string with all of the possible parameter values into the where clause. How is this an alternative method? – Clifford Piehl Jun 26 '20 at 19:02
  • The manually added 'All' option is different as only one entry will be passed to the query, the parameter will just contain 'Al'' rather than 'option1', 'option2', 'option3', etc... By changing the where clause to check for the literal value 'ALL' we are just saying, if the parameter is 'all' then select the record. Try this on a small sample with just a couple of parameters in SSMS to test it. Ensure brackets are in the correct place! If you have problems let me know and I will post a longer example/explanation – Alan Schofield Jun 26 '20 at 20:45