Last post Apr 15, 2011 08:01 PM by RMcBride
Apr 14, 2011 10:37 PM|RMcBride|LINK
First of all, let say with absolute clarity and humility... I am a complete noob with SSRS and BIDS. So please forgive the apparent simplicity of this question. I know someone will be able to answer this fairly easily, but not me.
I used BIDS to create a report. This report has a measure for expenses, and dimensions of month and business unit (BU-A, BU-B). I am doing this as a vertical bar chart with a trend line over top. I am using the ReportViewer control to display it on an ASPX
page. It works great.
Now I have been asked to make a parameter that the users selects from a drop down list. The parameter selection is either BU-A or BU-B, and on selecting the parameter from the list, the chart should reflect the selected parameter. In other words if the user
select BU-A, then the chart should show the expenses and trendline for BU-A. If the user selects BU-B, then the chart should show the expensesand trend line from BU-B. The first selection should be "All" and this should be the default selection.
I am trying to do this in BIDS, but I end up with a DDL showing each and every instance of BU-A and BU-B rather than a single selectable parameter.
Can someone help me figure this out, or point me to a model or example that I can follow?
Apr 15, 2011 02:43 AM|RMcBride|LINK
I took the time to research this on MSDN, and went and bought a book too!
So I set a ReportParameter allowing mutiple selection, then I set a Report Filter. It seems to work fine as long as I select either one BU or the other BU. But if I select the system generated "All" at the top of the list, the report filter only returns
the first filter on the list, not both filters. In other words, if I select BU-A, I get BU-A; if I select BU-B, I get BU-B; but if I select All, I only get BU-A and not BU-B.
Can someone help me figure out how to get "All" to actually show All?
Apr 15, 2011 01:01 PM|codemihir1|LINK
are you using stored proc or SQL as Text in SSRS?
if Stored Proc then you need to create a split function in your SQL
CREATE FUNCTION [dbo].[Split]
( @String VARCHAR(max), /* input string */ @Delimeter char(1), /* delimiter */ @TrimSpace bit ) /* kill whitespace? */RETURNS @Table TABLE ( [Val] VARCHAR(4000) )ASBEGIN DECLARE @Val VARCHAR(4000) WHILE LEN(@String) > 0 BEGIN SET @Val = LEFT(@String, ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1), LEN(@String))) SET @String = SUBSTRING(@String, ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0), LEN(@String)) + 1, LEN(@String)) IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val)) INSERT INTO @Table ( [Val] ) VALUES ( @Val ) END RETURNEND
you can use this in your SQL @ where :
WHERE Name IN (SELECT Val from dbo.Split(@Name,',',1))
alternatively you can copy your SQL and paste in SSRS as Text and you don't need split function.
Apr 15, 2011 07:25 PM|RMcBride|LINK
Actually I have tried it a couple of ways, including just entering the parameters as items using the dialog. I tried getting them with a SQL select, but I had that problem there to. I am _not_ using a SPROC.
I will take a look at your comments and see if I can figure out what to do.
Apr 15, 2011 08:01 PM|RMcBride|LINK
So far this has failed completely for me. I am not sure what I am doing wrong. As noted previously, I made my chart in BIDS with Expenses as the measure and month and bu as the dimensions.
Then I created a Parameter called @BU.
General... Name: BU, Prompt: Choose BU, Data Type: Text, Allow Multiple Values checked, parameter visible
Available Values... Specify Values, Label: BU-A, Value: BU-A, Label: BU-B, Value, BU-B (these are the correct values)
Next I added a filter to the Chart.
Chart Properties > Filter... Expression: BU_Name, Operator: IN, Value: =Parameters!BU.Value(0)
This gives me a drop-down filter which can filter on BU-A and BU-B, but when the autogenerated "(Select All)" is chosen, I only get BU-A.
I tride using the code as suggested by codemihir1, but that gives me a report with no values at all (after I made the changes to the code to reflect my parameter name and fieldname).
Is it possible this is a bug in the control? I seen a lot of hatemail over that "(Select All)" addition to the BIDS filter. Might there be something else I can try?