Cscading Services Reporting Services Update

Question

I googled a lot and found that usually this could not be done. I came across one of the hackers:

http://www.bp-msbi.com/2011/04/ssrs-cascading-parameters-refresh-solved/

But it does not work for me in ssrs 2005. It's just interesting if anyone else tried in 2005. Or there are other hacks that you can try.

In accordance with this article, a dependent parameter is updated only when its values ​​are invalid by a selection in the first parameter. If we can cancel the dependent parameter each time the parameter is changed, we will provide a complete update. An easy way to do this is to add a value, such as a GUID, obtained using the T-SQL NEWID () function.

So basically we want to introduce a fake parameter between two real parameters. This fake parameter is supposed to return new values ​​every time the stored file behind it adds a guide to the result set every time you call proc. Thus, it completely updates the other parameters.

Now the main problem that I am facing is the following:

Sets the default value for this fake parameter. For available values, the program is stored behind a false parameter, it starts and returns data in the format say: result1, result2_GUIDFROMSQL

Now it looks like the same storeproc is being called again to set the defult value if I ask it to get the default value from the request. But as storeproc starts up, a new guid appears again, so the old value cannot be found, therefore it cannot be set as desired.

I just need to figure out the mechanism for passing this pointer from the entered parameter to the next parameter.

That's where I fail.

My problem can simply be replicated by creating a parameter whose data source is this request.

select getdate() id, @name nid 

So, in this case, how to set the default value for this parameter.

Answer 1

Finally, I was able to solve this problem. This link was a useful start. http://www.optimusbi.com/2012/07/16/multilevel-cascading-select/

Basically, this is that it records the request for parameters so that the dependent parameter changes its value each time you change its parent parameter.

The request adds a roll with a space "_". Thus, each time the user selects other values, the roll number and, therefore, the query result set changes.

Then, when using the result set, delete the text after the underline to get real codes.

Answer 2

There is a workaround to fix this problem for all situations.

Please note that in the previous answer the query record parameter was specified so that the dependent parameter changes its value each time you change its parent parameter, it works in some cases, but not in all cases. If the dependent “Available” parameters change as a result of another parameter AND the visible dependent parameter works, if the dependent parameter is hidden or internal, or if the “Available” values ​​do not change as a result of another parameter, it will not work.

A workaround is to define a custom function in the report and call it in the expression for the dependent parameter. A user-defined function takes a parameter (a parameter depending on a dependent parameter) as an argument, and simply returns its value. Despite the fact that the function simply takes the value and returns it, SSRS does not check the code and assumes that the code can do something (generate a random number, extract files from disk, etc.). Thus, the SSRS calls the function every time the value changes regardless of the change in the value of the "Available" dependent parameter and whether the dependent parameter is visible, hidden or internal.

Something like that:

 public function returnArg(ByVal TheArg As String) As String return TheArg end function 

Suppose you have two options:

Parameter1 Parameter2

And this parameter2 depends on parameter 1.

Set Parameter2 as an expression that invokes Parameter1 with a function, for example:

 =CODE.returnArg(Parameters!Parameter1.Value) 

Now in this case, Parameter2 simply displays the value in Parameter1, but this logic can be extended to more complex expressions and several parameters. As long as there is a call to the CODE.returnArg (...) function for each parameter in the expression, SSRS will always update the value.

Answer 3

@Abbi the second link is no longer valid. Now this article can be found at: http://www.optimusinfo.com/multilevel-cascading-select/

Unfortunately, the images are broken, and the article is a bit incomplete without them. There is one image that I could find in its moved location (I could not find others even when trying the appropriate options): http://www.optimusinfo.com/wp-content/uploads/2012/07/Multilevel-Cascading-with -Select-All-18.jpeg

This gave me the last key that I need to make this technique work: when you configure the parameters of the data set parameters, you need to manually specify the parameter value, for example. [@ City.Label], not the usual regular [@City] (which corresponds to [@ City.Value].

This is due to the fact that the method changes the value "Value" to a user-defined value, which can no longer be viewed in the database! Thus, you need to use the user-friendly existing in the Label database as a parameter. (It should probably be obvious, but ...). The rest is pretty standard if you understand the report builder.

Also note that this link in its current form does not work with several cascading parameters. However, it can be easily changed for this: just change the parameter saved by procs to join the value sharing function. I adapted my udf_splitvarible function specified in SQL Server - In the section with the declared variable

The modification is quite simple, but I will give an example. This is from an article (with fatal formatting).

 SELECT l11.CompanyName1+'_'+ CAST(row_number() over( order by l11.CompanyName1 desc) AS VARCHAR(50) )as CompanyName11 ,l11.CompanyName FROM ( SELECT l1.CompanyName+'_'+ CAST(row_number() over( order by l1.CompanyName asc) AS VARCHAR(50) )as CompanyName1 ,l1.CompanyName FROM ( SELECT DISTINCT CompanyName FROM Customers WHERE City IN(@City) )l1 )l11 ORDER BY l11.CompanyName ASC 

Edit:

 SELECT DISTINCT CompanyName FROM Customers WHERE City IN(@City) 

in

 SELECT DISTINCT CompanyName FROM Customers INNER JOIN udf_SplitVariable(@City,',') v ON City = v.Value 

All this applies to SQL Server 2008 / Report Builder 3.

I could not understand / apply @borkmark answer. The restrictions do not seem to apply to my case.

Answer 4

Below I will describe a detailed scenario, and then I will show an implementation example based on the answers of Visser and Abby.

Imagine I have a report where each line is a project. The project has a status column with the values ​​“Running” or “Finish” and a column of the project manager, the values ​​of which are the name of the person. Here are the rows in the table:

• Project A (Status = Running, Project Manager = Bob)
• Project B (Status = Running, Project Manager = Volume)
• Project C (Status = Complete, Project Manager = Jack)
• Project D (Status = Complete, Project Manager = Tom)
• Project E (Status = Complete, Project Manager = Jill)

I want to have 2 parameters in my report

• Show completed projects? is a boolean parameter
  • When false will show only projects "Running" A and B
  • When true will show projects "In Process" A and B in addition to "Complete" projects C, D and E.
• Project manager . Is this a multi-valued text parameter, the parameters of which and the default values ​​will have to change depending on the Show completed projects option ? on which he depends
  • Should Show Completed Projects? set to false , then only the "Bob" and "Tom" options will be displayed, because they are the project managers for the current Project A and B projects, respectively
  • Should Show Completed Projects? set to true , then in addition to “Bob” and “Tom” you will also have “Jack” and “Jill”, as they are project managers for inactive projects Project C and Project E respectively.

Now for the implementation:

• Show completed projects

  

• Project managers request a dataset (see Visser and Abbi's answers for details on how this generates a key, which will change based on an independent parameter and cause SSRS to reset the default values)

   SELECT [ProjectManager_Key] = pOuterAlias.[ProjectManager_Key] + '_' + CAST(ROW_NUMBER() OVER(ORDER BY pOuterAlias.[ProjectManager_Key] DESC) AS NVARCHAR(MAX)), [ProjectManager] = pOuterAlias.[ProjectManager] FROM ( SELECT [ProjectManager_Key] = pInnerAlias.ProjectManager + '_' + CAST(ROW_NUMBER() OVER(ORDER BY pInnerAlias.ProjectManager ASC) AS NVARCHAR(MAX)), [ProjectManager] = pInnerAlias.ProjectManager FROM ( SELECT [ProjectManager] FROM [dbo].[Project] WHERE Status = 'In Progress' OR @ShowCompletedProjects = 1 ) pInnerAlias ) pOuterAlias ORDER BY pOuterAlias.[ProjectManager] 

• Project manager

  • Are common

    

  • Available Values

    

  • Default values

    

• Dataset projects

  • Inquiry

     SELECT * FROM [dbo].[Project] WHERE ( Status = 'In Progress' OR @ShowCompletedProjects = 1 ) AND Project Manager IN (@ProjectManager) 

  • Parameters (Be sure to pay attention to the part [@ProjectManager.Label] , which will correspond to the project in the actual value of the project manager from the database, and not in what we generated.