Populate an SSRS Report parameter(hidden) by a sproc or user defined function

Question

My SSRS report fetches data from my DATAWAREHOUSE.

The ASP.NET application I have is connected to an OLTP database.

I invoke the SSRS Report from my ASP.NET application and provide a parameter as CustomerID(this is an application key in datawarehouse) to my report.

Since my report is connected to my datawarehouse, I do not query my report databased on the OLTP's CustomerID. Instead I use my datawarehouse's surrogate key (CustomerDimKey). Now, in my report, I need to find the correct surrogate key for the CustomerID parameter, which I passed from my ASP.NET application!

My report already has a parameter as @CustomerDimKey(this is used across all the report sprocs). We used it for testing, but now we'll hide this as we have integrated it with the ASP.NET application.

I have already added a new parameter to the report as @CustomerID(this will have the OLTP's CustomerID), which will now get a value from ASP.NET.

I need to know a way to re-use the @CustomerDimKey report parameter, which should now get value from a sql statement or a sproc once the report is requested. Based on the value contained by the @CustomerID parameter.

Answer 1

Sounds pretty simple. Assuming you have SQL or a stored proc that can look up the CustomerDimKey, just add a dataset that does so using @CustomerID parameter then selects that CustomerDimKey. Let's assume it's a stored proc called LookUpCustomerDimKey that takes the @CustomerID and an output parameter @CustomerDimKey. I'd create a report dataset like so:

declare @CustomerDimKey bigint

exec LookUpCustomerDimKey @CustomerID, @CustomerDimKey out

select @CustomerDimKey CustomerDimKey

Then set the CustomerDimKey report parameter to be internal and get its default value from the query I just created. Now the report will wait for CustomerID to be provided then calculate the CustomerDimKey before using it in your other report datasets.

It doesn't have to be a stored proc, of course. You just have to make sure the body of the dataset produces a single row result set containing the key. It can be a simple SQL select or a series of TSQL statements or stored proc calls. Pretty much anything that you could put into a stored proc can go into the dataset body. It just needs to ultimately produce a row.

Answer 2

Ensure that you keep the order of parameters correct, during design.

@CustomerID -- First parameter

@CustomerDimKey -- Second parameter and depends on @CustomerID for its value.

Ensure the new dataset returns a resultset with single record(value)

declare @CustomerDimKey bigint

SELECT @CustomerDimKey = CustomerDimKey FROM Datawarehouse.CustomersDimension WHERE CustomerID = @CustomerID

select @CustomerDimKey AS CustomerDimKey