0

I have question similar as Open SSRS URL in New Window, but one of my parameters has many values and the URL becomes too long. This parameter always equals such parameter in initial report.

How can I solve it? Can I make post request in "go to url" field or define parameter default value from initial report?

aduguid
  • 3,099
  • 6
  • 18
  • 37
Дмитрий Малкин
  • 11
  • 2
  • You could apply the default value for the parameter on the report server and then you wouldn't need to pass it in. – aduguid Sep 03 '18 at 05:22
  • This parameter depends on equal parameter from parent report and is dynamic. It hasn't static default value. – Дмитрий Малкин Sep 03 '18 at 05:41
  • Can you post an example of the URL with the parameter values? – aduguid Sep 03 '18 at 10:52
  • something like this: http://bi2.cdu.so/ReportServer/Pages/ReportViewer.aspx?/%D0%98%D0%A2-%D0%B0%D0%BA%D1%82%D0%B8%D0%B2%D1%8B/%D0%9A%D0%B0%D0%B0%D0%BA%D1%82%D0%B8%D0%B2%D0%BE%D0%B2&checkCond=2&checkTitle=%D0%9C%D0%BE%D0%B4%D0%B5%D0%BB%D1%8C&casID=26475736&pFilial=2089202&pFilial=2104302&pFilial=2134802&pFilial=2266784&pFilial=6920302&pTeams=0&pTeams=2323303&pTeams=2323304&pTeams=2323308&pTeams=2323310&pTeams=2320925&pTeams=2324982&pTeams=7049005&pTeams=7049019&pTeams=7049013&pTeams=7049018&pTeams=7049020&pMOL=0&pMOL=2289628&pMOL=2290590&pMOL=2284276&pMOL=2270441&pMOL=2270431 – Дмитрий Малкин Sep 03 '18 at 13:13
  • Are any of the parameters you are passing in to the URL all of the values from the drop-down? – aduguid Sep 03 '18 at 13:43
  • Yes, there are three long parameters, all from the drop-down of initial report. One of them has ~60 values, second ~400 values and the third ~400 values too. – Дмитрий Малкин Sep 03 '18 at 14:01

1 Answers1

0

Here is how I reduce the amount of characters passed into my report URL with parameters. Since Microsoft Internet Explorer has a maximum uniform resource locator (URL) length of 2,083 characters you have to get creative.

First, add parameter values for all variables. The other option would be to use a defaults table.

screenshot

Then union the parameter(s) to the datasets for your other parameters. This way you'll get the all option in your parameter dropdowns.

;WITH
teams_source
AS
(
    SELECT tbl.* FROM (VALUES
      ( 2323304)
    , ( 2323305)
    , ( 2323306)
    , ( 2323307)
    , ( 2323308)
    , ( 2323309)
    , ( 2323310)
    , ( 2323311)
    , ( 2323312)
    , ( 2323313)
    , ( 2323314)
    , ( 2323315)
    , ( 2323316)
    ) tbl ([Teams]) 
)
SELECT [Teams], [TeamsFormat] = CAST([Teams] AS VARCHAR) FROM teams_source
UNION
SELECT [Teams] = @all_value_nbr, [TeamsFormat] = @all_value_text
ORDER BY 1

screenshot

screenshot

Then in the dataset for your report change the WHERE clause to check for the all variable.

WHERE 
1=1
AND (@all_value_nbr IN(@Teams) OR [Teams] IN(@Teams))

When you build the URL with parameters, you can count the number of values.

IIF(Parameters!Teams.Count = Count(Fields!Teams.Value, "TeamsDataset"), "", "@Teams=" + Join(Parameters!Teams.Value, "@Teams="))
aduguid
  • 3,099
  • 6
  • 18
  • 37
  • 1
    Thank you for answer, I thought about it, but it ist'n satisfactory workaround. there are about 400 values in all values, but user mays need in 399 values. And 399 values will be too long too. Are there any other solutions or it is constraint of SSRS? – Дмитрий Малкин Sep 04 '18 at 05:51
  • You could check the count of parameters to build your URL, but you'll still run into the same problem with exceeding the maximum amount of characters for the URL `IIF(Parameters!Teams.Count = Count(Fields!Teams.Value, "TeamsDataset"), "", "@Teams=" + Join(Parameters!Teams.Value, "@Teams="))` – aduguid Sep 04 '18 at 06:22
  • No worries mate, can you click on the check mark beside the answer to toggle it from greyed out to filled in? – aduguid Sep 04 '18 at 08:26