Filtering Data Using A Boolean Parameter
Apr 24, 2008
Hello, I am trying to use a boolean parameter to filter data in a table, but there is something I am missing.
Basically I want something like this:
I have a boolean parameter, "EP", and I have a filter set on my table as:
Expression Operator Value
=Fields!REFERRAL_SOURCE.Value = IIF (Parameters!EP.Value, "1297", ????)
Using the filter tab, I can't specify an expression for the 'Operator' so I was trying to work it out using either '=' or 'like'. What needs to go into the ???? in order for the referral source to be "not 1297" (i.e. the inverse of the filter)?
Or, am I completely missing an easy way to do this?
Thanks.
View 4 Replies
ADVERTISEMENT
Mar 18, 2008
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :)
I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query.
(tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server">
2 <asp:ListItem Value="0">Open</asp:ListItem>
3 <asp:ListItem Value="1">Completed</asp:ListItem>
4 </asp:DropDownList>
5
6
7 <asp:DropDownList ID="drpdwnCompany" runat="server">
8 <asp:ListItem Value="">All</asp:ListItem>
9 <asp:ListItem Value="cur">Cur District</asp:ListItem>
10 <asp:ListItem Value="jho">Jho District</asp:ListItem>
11 <asp:ListItem Value="sea">Sea District</asp:ListItem>
12 <asp:ListItem Value="san">Net District</asp:ListItem>
13 <asp:ListItem Value="sr">Research District</asp:ListItem>
14 </asp:DropDownList>
15
16
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>"
18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')">
19 <SelectParameters>
20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" />
21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" />
22 </SelectParameters>
23 </asp:SqlDataSource>
24
View 3 Replies
View Related
Jun 6, 2006
Hi,
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters:
@subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ
FRRIC
FRMM
ROBECO
FRJV
MAIL
FRUKV
FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL
, @numDaysCutoff INT = 1
, @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount)
, SecurityID = ISNULL(h.security_id, lo.security_id)
, SecurityName = s.name
, QtyHeldAndPending = ISNULL(h.quantity, 0) +
(CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1)
WHEN lo.type = 2 THEN lo.resulting_quantity
ELSE 0 END )
, L.AverageDailyVolume
, XDaysVol = L.AverageDailyVolume * @numDaysPcts
, CutoffVol = L.AverageDailyVolume * @numDaysCutoff
, DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0)
, HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100
, TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd
, CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd
, TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100
, DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * s.price_usd), 0)
, NDaysCutoff = @numDaysCutoff
, NDaysPcts = @numDaysPcts
FROM subaccount_positions_table h --vGlobalHoldings h
JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount
FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id
FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id
LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id)
LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99
WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount)
OR lo.subaccount = ISNULL(@subaccount, h.subaccount) )
AND status = 1
AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol
OR -- Targeted Vol exceeds cutoff
ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) >
ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol
)
ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
Thanks in advance!!!
sqlnovice123
View 2 Replies
View Related
Aug 29, 2007
Hi,
I have a Summary report and a Detail (drillthrough) report.
the summary report displays.
Summary report
Code Snippet
Adult | Male | Count
-----------------------------
Yes | Yes | 50
Yes | No | 9
No | Yes | 20
No | No | 50
------------------------------
| 129
When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.
The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.
Any ideas?
View 5 Replies
View Related
Nov 2, 2007
Hi Folks,
I'm having a problem showing my boolean default parameter on report server in our test environment.
The report server shows my boolean parameter with a yes and no radio button. I assigned my boolean parameter with a 'No' default. The default shows and runs fine when I run the report within Visual Studio.(I hope I'm using the correct terminology.) However, on the report server the 'No' radio button is not checked by default. What am I doing wrong?
Thanks in advance for your assistance.
View 3 Replies
View Related
Mar 29, 2007
I have a boolean field.
I want the users to be able to select
show all marked YES
show all marked NO
show all.
How can this be accomplished?
Thanks
View 6 Replies
View Related
Jan 17, 2007
hello at everybody well i have a problem. with in a package i defined a bollean variable with the name "x" and with a default value FALSE.
i created a configuaration file that contains the variable "x" as [user::x]. when i deploy the package to a server and i try to execute the package putting to the set values TRUE fror the x variable it doesnt work.
what can i do??
thnxs
View 1 Replies
View Related
Jan 13, 2007
Hello,
Can anyone say me how i can make a Report with Parameter Boolean field,and as Default Value true and False. ( Both ).
With Multivalue and in the Query = Field in (@BoolPara) have i a Error in the Query.
Thanks
View 1 Replies
View Related
Sep 23, 2015
I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.
See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0
CREATE TABLE #temp
( Id int, Name char(30), YesNo bit )
INSERT INTO #temp (Id, Name,YesNo)
Select 1, 'a', 0
INSERT INTO #temp (Id, Name,YesNo)
[Code] ....
View 5 Replies
View Related
Jan 13, 2015
This is for Microsoft Server SQL 2012.
I'm trying to create a WHERE clause that will have different results depending on a parameter that is entered. For example, if you put in a number, it will only calculate the rows where the column ID matches that number. However, if you put in 0, which doesn't exist in that column ID, it will instead calculate all the data in the table.
So the below would be a very basic idea of what I'm trying to do, but I'm not sure how to do it with proper syntax.
WHERE IF ID=0, THEN do this
ELSE do this AND ID=#
View 2 Replies
View Related
Sep 7, 2015
How to call Boolean/Text parameter in SSRS Query.
Parameter (Cust )Properties:
My Query:
SELECT dbo.incident.incident_ref, Customer.cust_n,incident.date_logged FROM
incident INNER JOIN Customer ON incident.incident_id = Customer.cust_id
WHERE incident.date_logged BETWEEN @date_from AND DATEADD(day, 1, @date_to)
I need to use a condition here to display customer based on my selection in dropdown list (Include ABC )
View 5 Replies
View Related
Sep 21, 2013
We can use LIKE '% ...%' for character string search but how can we do the same thing to a datatime parameter like '2013-09-20 13:34:43.098'?
View 12 Replies
View Related
Apr 18, 2007
Hello,
I have built a report using a Cube (and not a relational database).
I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.
Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.
Thank you in advance !
Alexis
View 3 Replies
View Related
Aug 28, 2007
Hello, I have a report in which I would like to give the user the ability to select how a parameter is filetered.
Option 1 would be to filter by a range of value ie: WHERE member.age BETWEEN (@Start) AND (@End)
-say everyone between the ages of 50 through 80
Option 2 would be to filter by specific but multiple values ie: WHERE member.age IN (@Age)
-say anyone who is 25, 50 and 75.
How can this be done? Thanks
View 5 Replies
View Related
Apr 2, 2006
<br><br>I obtain
table names from a database and pass them to a dropdownlist. Based on
user selection, I want to pass each table name to a query.Here is an
extract from my code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="select * from @dDTable">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1"
Name="dDTable" PropertyName="SelectedValue" DefaultValue="product"
Direction="InputOutput" Size="15" Type="String" />
</SelectParameters>
</asp:SqlDataSource>I keep getting this error: Must declare
the table variable "@dDTable".Please does anyone knows the best way to
go about this?
View 3 Replies
View Related
Apr 9, 2008
I am using two drop downs, like so:
<asp:DropDownList ID="ChurchStateDrop" runat="server" DataSourceID="ChurchStateDropData" DataTextField="State" DataValueField="State" AutoPostBack="True" onselectedindexchanged="ChurchStateDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All States" /></asp:DropDownList>
<asp:DropDownList ID="ChurchCityDrop" runat="server" DataSourceID="ChurchCityDropData" DataTextField="City" DataValueField="City" AutoPostBack="True" onselectedindexchanged="ChurchCityDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All Cities" /></asp:DropDownList>
I have ????? in the value fields because I don't know what value needs to be passed to negate filtering (to choose all). The Dropdowns have the following SQLDatasources:<asp:SqlDataSource ID="ChurchStateDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [State] FROM [ChurchView]" DataSourceMode="DataReader"></asp:SqlDataSource>
<asp:SqlDataSource ID="ChurchCityDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [City] FROM [ChurchView] WHERE ([State] = @State)" DataSourceMode="DataReader"> <SelectParameters> <asp:ControlParameter ControlID="ChurchStateDrop" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource>
Now, lets say I wanted to pass a value to the WHERE statement in ChurchCityDropData to coincide with 'All States', what would I replace value="??????" with? Now you may think I'm crazy to do such a thing, but this actually has to do with adding a Denomination Dropdown to show Denominations from all states or all cities. I will figure out the best logic for that later, I just want to know the wildcard to pass to the parameter to choose all states (negate filtering).
View 3 Replies
View Related
Jan 24, 2008
consider a stored procedure with a parameter @OrderID, i want to perform the following query :select * from Orders where OrderID = @OrderIDi want the condition to be true when parameter @OrderId is null so what is the syntax for that? i think there is an IF CONDITION that can be embedded with where clause.
View 6 Replies
View Related
Feb 7, 2007
In my report I want an optional parameter to filter all records with a specific field that is not blank. I tried several scenario's without result...
In the parameter I want to set a text value like "exampletext".
In the filter I want a check: if the parameter value is "exampletext", only show the records where field "abc" is not blank.
On the tab Filters from the Table properties I can set three values: Expression, Operator and Value.
Please help!
View 1 Replies
View Related
May 15, 2008
I have gridview and i want to ignor that filtering if that parameter is empty how do i write that query.
my query is
select * from UserMAster where UserId=@USerID and RoleId=@RoleId
if @UserId is blank then i want to select all records without filtering with user Id a
nd if i have passed null or anything as parameter in roleId then alsoi want all the records if it contain value then i want to do filetring is it possible to with query. or else send me some alternative so that i don't need to execute more query.
View 7 Replies
View Related
Apr 13, 2007
I am currently writing a report that will use as one of the filters the date (in this case it is the date of service of a physician office visit).
however the DB developers included the date and the time of the visit in one field so my resulting data set contains 4/13/2007 4:30pm for example, so using an @date parameter as a filter on the data field doesn't work because when you run the report and enter the date in the parameter it doesn't return anything.
I am an SQL Report writer amateur so I am sure there is an easy fix, any and all help will be appreciated.
Mark
View 7 Replies
View Related
Dec 28, 2007
Hi,
Im trying to create a drop down parameter whereby if i select a certain field, a different dropdown will be filtered off only the relevant selections, is this possible.
View 7 Replies
View Related
Jun 19, 2005
Obviously, I'm new to SQL Server, because I'm still scratching my head wondering where in the world the boolean data type is. Can anyone please explain to me how boolean data types REALLY work in SQL Server? I've created a few Bit type fields, but when using a datagrid, they sometimes show up as 0 and 1 and sometimes as True/False, and I can't see a difference in how the grids (or fields(!)) are set up that would explain this. Any insight would be greatly appreciated!
View 1 Replies
View Related
Jun 24, 2004
What data type should I use as a replacement of Boolean in SQL server? I need to create a table column with a "two-state" data type and must ensure, that only one record can hold value "True" in this column... Any ideas? Thanks
View 1 Replies
View Related
Apr 22, 2008
I know this is a stupid question, but I've searched and can't seem to find it.
What do you use in SQL when creating a table and the field is a "yes/no" type field? In VB isn't that a boolean data type? What is the corresponding SQL data type?
View 3 Replies
View Related
Dec 3, 2007
I am trying to find boolean data type in SQL Server. but i didn't find.
is this data type available or not.
I want to make a column true or false
Thanks.
View 3 Replies
View Related
May 5, 2004
What is the syntax to pass in null value for boolean data type in VB in the stored procedure?
This is what I tried and it doesn't work.
.Parameters.Append .CreateParameter("@disposition", adBoolean, adParamInput, 1, vbNull)
Thank you.
View 1 Replies
View Related
Jan 3, 2007
Hello. A question please. To define a column of a table in SqlServer, Does it exist a boolean data type ?
Thanks...
View 4 Replies
View Related
Feb 16, 2008
Ok so i've got a database containing a table called Quote.
I need one of the field's datatype to be Boolean?
which option do i choose?
and also is there a way to make the Key Field auto increment?
And is the datatype: ntext, the correct option for a text only field?
thanks
View 6 Replies
View Related
Sep 2, 2006
I have a page for inventory price entry that I have used for a while. Now I need to add a checkbox for whether or not the price includes shipping. I added the checkbox to the form and had it posting 'True' or 'False' to the database as nchar(10) data type. When the gridview pulls up the data, I have the Item Template like this, so it shows a disabled checkbox either checked or not: <asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Convert.ToBoolean(Eval("Shipping")) %>' Enabled="False" /> This works fine for displaying the values, but I copied the checkbox to the Edit Item Template, but did not disabled this one. At first, I didn't change the databindings, leaving it Convert.ToBoolean(Eval("Shipping")), which allowed me to go into Edit mode, change the checkbox, then click update. At which point it would return to it's original state (meaning Update wasn't actually updating). However if I change the databindings, then the page won't display. I checked the SQL statement, and sure enough, it has theUpdateCommand="UPDATE [PartsTable] ... SET [Shipping] = @Shipping... WHERE [PartID] = @original_PartIDAfter fiddling with the sql statement, now I get Object cannot be cast from DBNull to other types. I think that means that the checkbox is sending a null value to the database.Any insight as to how to get this to work is much appreciated. Thanks in advance.
View 4 Replies
View Related
Oct 5, 2007
I'm having a real hard time coming up with a solution to this problem. I created a custom gridview control from Dino Esposito's "Extending Gridview" article which autogenerates a checkbox column that allows for multiple record selection. Once a user checks a box, the entire row gets selected. I added a dropdown list on the top of the page that has only two options, "Yes" or "No". What I'm trying to do is update a boolean column called "contract" (I'm using the Pubs sample database) for all selected rows (via checkboxes checked) depending on whether the user selects "Yes" or "No" from the drop down menu. For example,1) the user selects "No" in the dropdown2) The user checks all rows in the checkbox column for which he wants the all the values"contract" field set to "No"3) The user then clicks on a button called "Submit" and all selected records get updated to "No" under the "contracts" column. The idea is to allow the user to change the boolean values from a field for multiple records. Hence, making individual cells editable is pointless. Anybody have an idea how to go about this?
View 3 Replies
View Related
Nov 9, 2004
Why is Select * from [Merchandise] where [Product Name] like '[ABCD]%'the same as Select * from [Merchandise] where [Product Name] between 'A' and 'D'I can run Select * from [Merchandise] where [Product Name] like 'A%'and get Products that start with the letter "A" but they don't show up when I try to get all "A","B","C","D" Products.
View 2 Replies
View Related
Nov 13, 2001
Hi,
Our current method of limiting what data a user can see is implemented solely through our Web based business intelligence tools. No filtering is enabled at the database level. This has become somewhat cumbersome as security is tied exclusively to these tools. The tools use one common logon to access the underlying database.
I would like to implement security at the database level (SQL Server 2000) and thereby produce a more flexible/portable solution. I was thinking of setting up individual database accounts for each user and then tying these into our company structure table by passing system_user result to a constraint.
For example System User name 'Store 2' would reference Store '2' in the structure table. Depending on the user, different columns will need to be referenced to filter the rows. A store user would be validated against the store column, an Area Manager user would be validated against the Area Manager column and Head Office users would not be valiadated at all i.e. they are not filtered.
1) What is the best method to implement such a look up. Can or should I use Check constraints for such a solution?
2) Would a UDF be useful?
Any ideas on the best approach to take would be greatly appereciated.
Thanks
Rob
View 1 Replies
View Related
Sep 24, 2007
Hi,
I have following query. I want to insert the value MBR_COV_EFF_DATE
to table fixed_MM if the function dbo.CheckTheDate2 returns correct date and if it returns NULL I want to insert it on error_MM table.
How can I do this?
select dbo.CheckTheDate2(MBR_COV_EFF_DATE,'MBR_COV_EFF_DATE') AS MBR_COV_EFF_DATE
from [unfixed_MM]
Thanks
Sanjeev
View 2 Replies
View Related
