IIF And Multi-valued Parameters
Apr 15, 2007
Hi
i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.
basically, the report gets loads of data back from DataCube.
if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.
but
i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart
so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)
am doing this by trying to get the selected value or "xxxxx" if noting selected
to hide the second chart when only one category has been selected, tried
e.g. for the third chart
=iif(
Parameters!LOOKUPITEMCategoryDescription.count>2,
Parameters!LOOKUPITEMCategoryDescription.Label(2),
"xxxxx")
doesnt work - gives an #Error as a reult
But it works fine when there are > 2 categories selected
also
this works fine when there is 2 or >2 category selected
=iif(
Parameters!LOOKUPITEMCategoryDescription.count>2,
"more than 2",
"not more than 2")
it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!
help please
thx
View 4 Replies
ADVERTISEMENT
Jan 14, 2008
I have a MultiValued parameter called Owners, which stores Owner Ids.
I wish to set the value of a textbox to the following:
=iif(3 IN(Parameters!Owners.Value),"foo","bar")
but the "IN" is not recognised.
How can I achieve this functionality using an expression?
Thanks,
Kate
View 1 Replies
View Related
May 2, 2006
Hi,
after the installation of SP1 I have a problem with multi-valued report parameters.
The option to select all values are gone.
Is there a bug within SP1?
Before the installtion of SP1 multi-valued report parameters works fine, the option to select all values was added automatically within the preview in Visual Studio 2005 and in published reports on the server.
The server was migrated from SQL2000 SP4 to SQL2005.
So long
Lemmi
View 41 Replies
View Related
May 7, 2007
I am creating a Line chart report from a table. The source table includes a code column (String), date column (Datetime), and 12 statistic columns (Int). The dates are end of month dates only. ("1/31/2006", "2/28/2006","3/31/2006", etc.) There are about 8 different codes for each month. The statisic columns are the totals for the month for each code for a particular statistic.
PeriodEndDate Code Statistic1 Statistic2 Statistic3 Statistic4 ...
XX/XX/XXXX XX 999 999 999 999 ...
On my line chart I want to show one or more statistics for one year for one or more codes. (A line for each statistic for a given code) The report has multi-valued parameters for the codes and the statistics. I have figured out how to create the chart for one or more codes for a given statistic. How can I select one or more statistic? Can any one help me out?
Thanks,
Fred
View 1 Replies
View Related
Jul 3, 2007
Hi There.
I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.
This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!
Thanks, Mike
View 4 Replies
View Related
May 7, 2008
I am trying to selectively show or hide a table based on the values in a multi valued parameter. How can I examine all values (simulate the like functionality) within an expression?
View 4 Replies
View Related
Dec 20, 2007
Hello,
I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving
Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.
I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.
Can any one help me with this. Its very urgent.
Thanks,
SqlNew
View 1 Replies
View Related
Dec 27, 2006
I am currently working with 3 multi-valued parameters whose data sources are queries. The first 2 are required to have entries, 100% of the time, but the third one may or may not require selecting a value. Parm3's data source is filtered by the selections of Parm1 & Parm2. The data source for my report references Parm3 in a derived table that is then LEFT OUTER JOINed.
In the cases where the report does not require any selection from Parm3 I am still required to pick at least 1 entry. Can anyone shed some light on this, or provide a solution so I am not forced to pick any if I don't want?
Thanks in advance,
Michael
View 6 Replies
View Related
Feb 15, 2007
Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example:
ReportParm1 String [Deal Dim].[Shelf].&[AAM]
But, how would i define it with multiple values as a 'default value' ?, for example:
ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF]
NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space.
Any help would be greatly appreciated.
thank you.
View 1 Replies
View Related
Jun 27, 2009
I have researched this question and so far have found very different opinions as to how or if it is possible.I have created 2 Data-driven subscriptions in SSRS. 1 for our sales persons and another for our managers reports. For the salespersons report, the solution works brilliantly as expected and I can appreciated the increased simplicitly for having 1 subscription to drive reports to a sales staff of over 100 folks.One the other hand, I can't seem to grasp the significance of this type of solution for the Sales managers when they have multiple salespersons that report to them.
Apparently, SSRS 2008 has provided no practical solution for this scenario that would be easily implemented in a Data driven subscription.Since I can't find a way to pass in muliple integer values that represent each of the salesreps for a single manager, I am stuck with potentially sending multiple reports for each of the Sales Reps residing under a single manager. Not very elegant or useful as I'd hoped for using Data driven subscriptions. I have even changed the parameter datatype to string and used something like:
paramSalesPeople = substring((SELECT ( ', ' + CAST(territoryid AS VARCHAR(2000)))
FROM Reports..SalesForce t2
WHERE t1.SalesManagerID = t2.SalesManagerID
ORDER BY SalesManagerID
FOR XML PATH( '' )), 3, 1000 )
To create a comma-delimited list of values for the parameters in effort of generating 1 (ONE) record per Manager. But the Data Driven Subscription fails miserably.So the question, Is it or is it not possible to have multiple values passed as a single parameter to a data driven subscription to consolidate the number of required reports into one. If not, it would seem that it should be possible since it can be done from the reports parameters drop-down menu.I have read someones recommendation to script the multi-valued parameter which seems to defeat the intent of the term "Data Driven' Subscription if I have to hard-code this logic into a script.
View 8 Replies
View Related
Mar 19, 2008
I downloaded the sample reports for report execution from http://technet.microsoft.com/en-us/library/ms161561.aspx
to display reporting services report execution data which worked fine until I'd written a report with multi-valued parameters. As soon as this was designed and more importantly run by an user the package used to create the reports crashed.
I've found out why it's breaking mainly through digging around in the script task where the package crashes and also through surfing the net and finding this
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=967081&SiteID=1
thread. I've tried their fix but it still crashes unfortunately.
It seems like the issue is populating an array indexes in the script component in the update parameters data flow task. I think this was written to deal with minimal parameter requests, however I've recently written a multi-valued parameter report and it's made the string containing the parameter details has now hit the maximum length for the string. Changing the WSTR 4000 to a different size doesn't seem to have an effect, and the forum entry above doesn't hint at what the problem is apart from the script not being able to deal with the length.
The error I now get is as follows:
at MyComponent1.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) in dts://Scripts/MyComponent1/ScriptMain:line 35
at MyComponent1.UserComponent.Input0_ProcessInput(Input0Buffer Buffer) in dts://Scripts/MyComponent1/ComponentWrapper:line 68
at MyComponent1.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer) in dts://Scripts/MyComponent1/ComponentWrapper:line 25
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
Anyone have any ideas on how to fix this?
View 3 Replies
View Related
Jun 29, 2007
Hello,
I am forced to use a Sp in teh first place as my query is too long for the text dataset. I have a parameter Time_Period which ia a multivalued one but it is not allowing me to use this in the SP. using RS 2005.
Any help appereciated.
Thanks
View 1 Replies
View Related
May 25, 2004
Hello
I am trying to do the following:
1. Create a Multi-statement Table-valued Functions, say mstvF1, with 1 parameter.
2. Then, use it like this: "Select * from table T, mstvF1( T.id )"
It gives me Line 100: Incorrect syntax near 'T', referring to the T of T.id.
If I do
Select * from table T, mstvF1( 5 ), then it works.
Is there any way to do a select from a table T combined with an MSTV function and passing in as a parameter a field from T?
Thanks for any help.
View 3 Replies
View Related
Oct 22, 2007
Hello,
I am attempting to create a cascading parameter.
Parameter's 1 & 2 are multi-valued.
Datasets 1 & 2 supply Parameter's 1 & 2.
The values for Parameter 1 as derived from Dataset 1 are 'A', 'B', 'C', 'D' and 'E'.
The potential values for Parameter 2 will only be created if value 'C' is amongst the multiple values selected for Parameter 1.
Thus I need to write my query for Dataset 2 so that it can check the values of Parameter 1 for the existence of 'C' otherwise it returns nothing.
What is the best practice for a dataset referencing the values of a multi-valued parameter in order to generate it's resultset?
Any help is appreciated.
View 2 Replies
View Related
Jan 27, 2006
I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report). However, using RS2005, I can't seem to figure out how to do this. I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.
Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty? Checking "Allow Null" or "Allow Blank" doesn't fix this problem.
I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this. I'm not sure if it will let you do that anyway...
Trent
View 3 Replies
View Related
Aug 16, 2007
Hi,
I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC.
To consider a sample code:
EXEC('select * from country where country_key in ('+@country+')')
where @country is a multi-valued report parameter.
Regards,
Emil
View 7 Replies
View Related
Oct 18, 2007
I'm creating a Multi-statement Table-Valued Function...
Is it possible to insert variables into the table? In other words, is it possible
to have something like
declare
@value1 varchar(10)
@value2 varchar(10)
BEGIN
<do some work on value1 and value2>
INSERT @returningTable
@value1, @value2
instead of
BEGIN
<do some work on value1 and value2>
INSERT @returningTable
SELECT col1, col2 from T_SOURCE
Here's why I want to insert variables...My function needs to return a table which contains a 'partial' incremental key.
I'll go with an example to explain what i have to do
Source_table
col1 col2
Mike 10
Mike 20
Ben 50
John 15
John 25
John 35
The table that my function needs to create should look like this
col1 col2 col3
Mike 10 1
Mike 20 2
Ben 50 1
John 15 1
John 25 2
John 35 3
I thought of creating a cursor and then looping through it generate col3 and save values of other individual columns in variables. But don't know how to use those variables when inserting records into function table.
Any other ideas? I'm caoming from Oracle world, I might be having some strange ideas on how to solve this problem. Any help is appreciated.
Thank you.
View 7 Replies
View Related
Mar 9, 2007
I have a multi-valued parameter that I want to pass to a subreport. The values are 11, 12, 13, and 14.
So here's what I've done:
1. For the properties of the clickable field of the first report, I have gone to the Navigation tab and chosen the subreport in the "jump to report" pulldown.
2. Then I clicked on the Parameters button and added a parameter name for the multi-valued parameter I am trying to send. For the Parameter value, I have tried 11,12,13,14. I have also tried =Split(11,12,13,14)
3. On the subreport, I create a report parameter with the same name, data type is string, multi-value is checked, and the available values pulldown is populated by a query.
So I run the first report and click on the link that brings me to the subreport, but all the subreport shows is the results for 11, not 12, 13, and 14.
How can I get the second report to understand that I want it to show all the records related to 11, 12, 13, and 14? If I bypass the first report and simply use the drop-down to choose 11, 12, 13, and 14 in the subreport, it works fine. I just can't seem to figure out how to correctly have the first report tell the subreport that it wants 11, 12, 13, and 14.
Thanks in advance for any advice!
Dan
View 4 Replies
View Related
May 6, 2007
Hey
I have created a multi-statement
table valued function
alter
function
fn_x(@x int)returns
@tbl table
(
position int identity primary key, i
int)
as
begin
insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) returnend
Is it possible skipping the
definition of the table columns (the light blue
part)?I need to return a different
structure based on a parameter.Dropping those lines throws an
error "incorrect syntax near 'as'"
The other solution is declaring
each udf separately as one statement udf.Thanks
View 2 Replies
View Related
Jul 18, 2007
Hello everybody,
I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?
Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?
Thanks in advance for your answers.
View 7 Replies
View Related
Aug 14, 2007
Hello Everyone,
I am trying to expand the multi-valued parameter menu object so I can display the selection list in a more user-friendly format since the text is fairly long. If left at the default setting then I have to scroll to the right to see the complete string(s). For a single-select parameter I can just CAST the string to CHAR(100) and the menu object will expand. However, it seems that this approach is not working for multi-valued parameter menu objects.
Is this a bug in SSRS, and is there another way to accomplish this. Please let me know.
Thank You!!!
View 2 Replies
View Related
Feb 15, 2007
How does one return all selected values in a multi-valued parameter? Right now i have a filter on the dataset where
(Expression)
=Fields!LOCATION_ID.Value
(Operator)
=
(Value)
=Parameters!Loc.Value(0)
This is just giving me data from the first value that is selected in the multi-valued dropdown. I need all returned from the parameter. Any ideas.
View 4 Replies
View Related
Jul 3, 2007
Hi There.
I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about?
Thanks, Mike
View 9 Replies
View Related
May 2, 2008
Hi All,
I'm having trouoble with changing my report name when I swtich to different parameter values.
I use the following expression.
IIf(Parameters!GroupVar1.Label = "CBank", "CBank", IIf(Parameters!GroupVar1.Value ="DTC","DTC","Total"))
This works fine when I clear Mutli-Value box in paramter properties. but when I use multi-value, this does not work.
Is there any solution for this?
Thanks
View 1 Replies
View Related
Dec 19, 2007
Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:
CREATE PROCEDURE spprodsales
@productid int
AS
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
RETURN
And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
So, can anyone please help me out possibly using the same stored procedure as above.
Thanks,
Kripa
View 5 Replies
View Related
Jul 2, 2015
I have a sp where query is as below.
DECLARE @ServerCIName varchar(5000)
SELECT * FROMÂ dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.
But @ServerName contains multiple values and it is dynamic (not constant).
How do I query the data?
View 6 Replies
View Related
Jul 21, 2015
I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name  in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .
View 5 Replies
View Related
Apr 28, 2008
I have a report that includes two multi-valued parameters.
In the Default Values section, I choose 'from query' and select dataset and value field.
In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field.
When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected).
How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.
View 5 Replies
View Related
Nov 2, 2006
Hi all,
I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.
I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?
Thanks, Jos
Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/
PRINT @@VERSION
go
PRINT 'Scalar function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS INT
AS
BEGIN
RETURN 1
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'In-line table-valued function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS TABLE
AS
RETURN SELECT 1 c
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go
CREATE FUNCTION sys_tmp
()
RETURNS @t TABLE
(c INT)
AS
BEGIN
INSERT INTO @t VALUES (1)
RETURN
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
/*
----------------------------------------------------------------------------------------------------
*/
And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Scalar function with name "sys_" creates ok...
In-line table-valued function with name "sys_" creates ok...
Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.
View 3 Replies
View Related
Jul 11, 2015
I want to insert lots of data into two tables. For this I want to use table valued parameters and a stored procedure. So, what is the better way for best performance? Using two stored procedures or a single procedure with two parameters? How does SQL Server consume the data if I use a single procedure with two parameters. Is it really streaming the data? I mean, is SQL Server already starting to insert the first rows as soon as it gets it even if the client is still sending more and more rows and than the same with the next table?
e.g. I have a procedure like this:
CREATE PROCEDURE [dbo].[usp_UpdateElements] (
@tvpElementsToInsert As [dbo].[tvpElements] Readonly,
@tvpElementValuesToInsert As [dbo].[tvpElementValues] Readonly) AS
               Begin
                   Insert Into Elements Select * From @tvpElementsToInsert;
                   Insert Into ElementValues Select * From @tvpElementValuesToInsert;
               End
View 6 Replies
View Related
Mar 13, 2014
I am creating a function where I want to pass it parameters and then use those parameters in a select statement. When I do that it selects the variable name as a literal not a column. How do I switch that context.
Query:
ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE
[code]....
Returns:
I execute this:
select * from ufn_Banner_Orion_Employee_Comparison_parser_v2 ('a.BANNER_RANK' , 'b.[rank]')
and get:
CerecerezNULLNULLa.BANNER_RANKb.[rank]
View 7 Replies
View Related
Jan 14, 2015
I have a SP with Parameters as:
R_ID
P1
P2
P3
P4
P5
I need to insert into a table as below:
R_ID P1
R_ID P2
R_ID P3
R_ID P4
R_ID P5
How can I get this?
View 2 Replies
View Related
May 21, 2014
Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?
View 1 Replies
View Related
