March 30, 2017 at 9:22 am
Hello,
I want to create a cascading parameter not via another dataset but via a stored procedure I order to filter parameter "tariff" due to parameter "customer_profile".
I've created parameter "@Reserved_2" in SP and filter it as shown below:
SELECT t.tariff_id AS tariff_id ,CAST (t.name AS NVARCHAR(25)) AS tariff_name
FROM ssrs.SSRS_VIEW_DIMTariffs t
LEFT JOIN ssrs.SSRS_VIEW_DIMCustomerProfiles cp ON cp.customer_profile_id = t.customer_profile_id
--LEFT JOIN ssrs.SSRS_VIEW_DIMTariffGroups tg ON tg.tariff_goup_id = t.tariff_group_id
--WHERE (EXISTS (SELECT tmp.strName FROM dwhsys.strSplit(@Reserved, ',') tmp WHERE CAST (tmp.strName AS BIGINT)=t.tariff_group_id) OR @Reserved IS NULL OR @Reserved=CAST (-1 AS NVARCHAR(MAX)))
WHERE (EXISTS (SELECT tmp.strName FROM dwhsys.strSplit(@Reserved_2, ',') tmp WHERE CAST (tmp.strName AS BIGINT)=cp.customer_profile_id) OR @Reserved_2 IS NULL OR @Reserved_2=CAST (-1 AS NVARCHAR(MAX)))
-- AND (EXISTS (SELECT tmp.strName FROM dwhsys.strSplit(@Reserved_3, ',') tmp WHERE CAST (tmp.strName AS BIGINT) = d.center_id ) OR @Reserved_3 IS NULL OR @Reserved_3 = CAST (-1 AS NVARCHAR(MAX)))
ORDER BY t.name
Now, in VS I have a dataset with that parameter which have parameter value according to the field I want to filter by.
Evidently, it does not work as hoped, In a report is blank filed "Reserved_2".
Any ideas? Thanks in advance.
March 30, 2017 at 11:54 am
First, the purpose of cascading parameters is to simplify reporting by tailoring possible responses based on previous responses. The key word here being responses. The stored procedure cannot pause to ask for a response.
Second, if you want help troubleshooting an error, you need to give us the code where the error is occuring. For a variable with the wrong value that error occurs when the value is SET, not when the value is referenced. You haven't given us the code for where the value is being set.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 30, 2017 at 12:52 pm
Hi,
thanks for your reply.
1) In my work we have reports which tailors possible responses based on previous ones and these cascade parameters are handled via stored procedure. It works fine and actually even better, because there's no need for additional datasets and I guess performance of a query is slightly better too.
2) The code I provided is a core of the stored procedure which handles the parameters. I believe, there's nothing wrong in the code at SQL server.
My question points to VS/Data Tools side and how to handle the "filtering" there.
I see the parameter "Reserved_2" created in the SP in Parameters folder in Object Explorer, it's completely blank, but I think it should be so and set in a dataset I use under Parameters tab. I've tried to wrote there some expression, including System.DBNull.Value, which obviously does not resolves the handling, but the parameter field in Preview is still blank and therefore the report cannot be viewed.
March 30, 2017 at 2:28 pm
We would need the information about the dataset used under the Parameters tab. That's where the problem is, and we don't enough information to tell you what is wrong with it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 31, 2017 at 6:46 am
It already works...my colleague spotted an insidious mistake in my project, unrelated to the cascading parameters.
However, here's the expression for parameter "Reserved_2" in the dataset:
=IIF(Parameters!AllCustomerProfiles.Value = TrueTrue, system.DBNull.Value, Parameters!CustomerProfiles.Value), system.DBNull.Value, Parameters!CustomerProfiles.Value)
I've not seen the approach my colleague use anywhere on the Internet, but it seems as very efficient way. He use one additional parameter for each cascading level share by every cascading parameters sets via tmp in the stored procedure I provided - so there's no need even for additional datasets and it works quite fast.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply