MultiValue Parameter

Hello,

I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".

Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.

Thank you,

Itzhak

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

|||

Thank you, this will solve my issue.

I put the following expression and it works well !!

=iif(CountRows("UsersDataSet")=Parameters!UserID.Count,"ALL",join(Parameters!UserID.Label,","))

Thanks,

Itzhak

multiple tables

hi,

I have 3 tables: Unix, Windows & Sybase

there are 3 dropdownlists in .asp page,

dropdown1=<displays the lists of table> (Unix, Windows & Sybase) variable as @.table

dropdown2=<displays the list of Columns from the dropdown1 selected table> variable as @.server

dropdown3=<displays the list of Row Header from the dropdown1 selected table> variable as @.user

how shal i define the select statement for this:

it should be something like,

Select * from @.table WHERE Server= @.server AND Row-Header = @.user

I appreciate if you could kindly help me in getting the correct syntax, thanx in advance.

Hi,

the tablename cannot be defined at runtime without using dynamic Sql, compose your SQLString first (e.g. in a stored procedure) then execute it via sp_executesql:

DECLARE @.SQLString VARCHAR(4000)

SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + ''''

But keep in mind that dynamic sql can be dangerous, read the aarticle of Erland first: http://www.sommarskog.se/dynamic_sql.html

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

|||

hi,

Thanks a million Jens,

I have done the same way as you explained ( First line: DECLARE @.SQLString VARCHAR(4000) Second line: SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + '''').executed the .asp page: it shows only the dropdown & no table. Appreciate your help, many thanks again for this:

-

execution script part:
-
<script runat="server">
Sub OSClick1(ByVal sender As Object, ByVal e As EventArgs)
'EXEC "@.SQLString"'
End Sub
</script>
-
processing script part:
-
<asp:Button ID="btnOSClick1" runat="server" Text="Get" Width="90px" OnClick="OSClick1" /><br />
<br />
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource4">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:Unix.Primary %>"
SelectCommand="CREATE PROCEDURE general_select @.table nvarchar(127), @.server nvarchar(127), @.user nvarchar(127),&#13;&#10;DECLARE @.SQLString VARCHAR(4000)&#13;&#10;SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + ''''&#13;&#10;'">
<SelectParameters>
<asp:ControlParameter ControlID="txtOS" Name="table" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="txtSrvUnix" Name="server" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="txtUnixDes" Name="user" PropertyName="SelectedValue" />
<asp:Parameter Name="SQLString" />
</SelectParameters>
</asp:SqlDataSource>
-

|||Hi,

I am not very familiar with those new SQLDataSource mechanism, but you do not need to create the procedure as a Select Command. Create the procedure before and do just execute the procedure as the Select Command. In plain C# code you will have to assign the Commandtype = StoredProcedure first to the Select command (You will probably have to do this here also) and then just write something like SelectCommand="general_Select"

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de