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
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
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
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?
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.
Greetings,
Being fairly new to SQL Server, I created a new field with the data type 'bit' so I can store the equivalent of a yes/no response (0 or 1).
When I try to enter either a 0 or 1 into the field via MS SQL Server Management Studio Express table view, I get
"String not recognized as valid Boolean"
I'd appreciate if someone would let me know how I 'noobed' this up.
Thanks very much.
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
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.
I have a long query that returns a table like:
ID Name bool
1 Joe 0
2 James 0
3 John 1
When the boolean field is equal to 1 for any of the records I would like to return an empty record set. What is the best way to do this?
I know I could use IF EXISTS but my query is so long that I would prefer not to repeat the query multiple times. Is there an alternative?
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] ....
When I enter a a true or false value into a boolean field, what is the proper way to enter it? I'm using:
INSERT INTO tblTable (IsSomething)
VALUES (False)
I've heard that a better way is to use 1 and 0, or something.
I wanted to test how SQL server 2005 works with Boolean values.
so I created this table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BoolTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Operand1] [bit] NULL,
[Operand2] [bit] NULL,
CONSTRAINT [PK_BoolTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I populated it like so
insert dbo.booltest (operand1, operand2) values (1,1)
insert dbo.booltest values (1,0)
insert dbo.booltest values (1,null)
insert dbo.booltest values (0,0)
insert dbo.booltest values (0,null)
Then I tried this
select case when operand1 OR operand2 then 'True' else 'False' End
from dbo.booltest where id=1
thinking that since row 1 has 1 and 1 I'll get 'True' as a string showing in the output window
but instead I got this error
Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.
question:
how do you work with bit value table Fields as booleans in a boolean test
many thanks in advance for your time/help
CarlitoA
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=#
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
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 )
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
Thanks
Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:
SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable
but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.
thanks a lot for your help!
Hi,
In my report i have a multi valued parameter, when i view my report in the Web application with Report Viewer, The multi valued parameter is displaying in light color.Can we able to change this.
Thanks in advance
I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:
Code Snippet
SE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]
(@Region int = Null)
AS
BEGIN
SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,
Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,
ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,
Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,
Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,
Forecast.SalesPerson_Purchaser_Code
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN
RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN
(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget
) AS Forecast INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON
Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON
Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON
Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND
Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND
Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code
WHERE (Region.Region_Key IN( @Region)) AND (Forecast.Budget_Year = 2007)
END
what am i doing wrong?
Hi:
I am building a report and have a few parameters. One of this parameters is set up as Multi-Value. When I only select one value everything is running fine. But when I select multi values I get an error saying I must declare my variable.
Any idea why this is happening?
Ben
hi,
i read from this forum that to pass array to report is using multi value parameter . my problem now is
1. can i pass multi dimension array.
2. how do i show the multi value parameter in a table . for example i have a multi value parameter that may contain 10 or 20 array . how do i dynamic it show in table.
rgds,
charles
I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction
(
@ClientID int,
@Priority int,
@TStart datetime,
@TEnd datetime,
@Status nvarchar,
@ConTypeID int,
@Caller nvarchar,
@Keyword nvarchar
)
AS
SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROM tblTasks
WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND
(ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL)
RETURN
Is it possible for an AND statement to take mulitple parameters? See below code:
SELECT tblQuestion.Question, tblAnswer.Answer
FROM (tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID)
WHERE (tblAnswer.StateID = ?) AND (tblAnswer.QuestionID = 14)
Is something like this possible?:
AND (tblAnswer.QuestionID = 14, 26)
Thanks
OK Guys....
If you answer this one, you will save my life...and you WILL be the MAN or WOMAN!!!!
Problem: I have a set of 24 matrix's that need to calculate the difference between the last two years and display in a field to the right of the last rendered column. Since I have been struggling with this, let's just assume there is no better way than how I currently have it set up. (one table that does the calculations for me and I set one field on the report to display the most recent two columns difference in my report)
what I can not figure out is: when I choose one of my parameters the report displays the information I want...but when I choose more than one...well there is the problem....
In order to obtain the most help for myself I will ask this in the most general way possible so as not to get bogged down into my specific solution...
Desired Result: How to pass all my parameter values from my multi-value parameter during runtime to a SQL Stored proc from my dataset within Reporting Services at runtime, Match the parameter to the field, get the result and store it in a variable, then do it again and add the second to the first within the variable, and so on and so on , until all of the parameters are used. Then sum the values and display in a field.
HELP, HELP, HELP Please....
Hello,
I have a multi-value parameter field and each item comes from a table in the DB.
This multi-value parameter allows to select all or some of the items.
What if I don't want to select ANY item??
I read in the forum the several tray to do the same but the answers are not so clear..
If I say that at the end is a bug in SSRS... AM I right?
Thank you
Marina B.
Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL:
WHERE ( [Field1] = @Field1_Filter OR @Field1_Filter IS NULL )
How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter.
I also don't know how you would check for the "not entered". Someone suggested the following SQL:
WHERE ( [Field1] IN (@Field1_Filter) OR @Field1_Filter IS NULL )
This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ".
How is this supposed to be done?
Thanks for any insight
hi,
i'm trying to perform a query against a db2 database like this:
SELECT ... FROM ... WHERE (field IN (?))
Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:
An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension
But when i type the query like this
SELECT ... FROM ... WHERE (field IN ('value1','value2'))
it executes flawlessly.
I am using the IBM ole db driver for db2 if that matters
Can anyone help me?
HI there.
I have been working on this for quite some time and I can not figure out what I am doing wrong.
I have a report filter called ItemFilter and I have declared as a report parameter where the multi value checkbox is checked. The values from this filter are coming from the item table.
SO the users drilldown on the filter when they run the report an select the Items that they would like to display info for. I had the following code before in the where
where
([CRONUS International Ltd_$Item].No_ = @itemfilter). This only worked if they selected on item from the list.
I want them to be able to select multiple Items or all. The code above did not work because there were commas separating one item from the other and I was getting an error.
I changed to
whrere
([CRONUS International Ltd_$Item].No_ IN (@itemfilter)).
Now this only returns the first item that I enter on my Itemfilter parameter.
Does anyone have any clue in regards to this ?? I am sure I am using the wrong syntax, but I do not know what to change it too.
Thanks
Good afternoon,
I've designed a Reporting Services report that has a multi-value parameter. The report works just great. The only issue I'm running into is that the users are complaining that when they want to select the values, within the multi-value parameter box, they have to scroll it to the sides way too much. This also makes them select values by mistake.
Is there away to inscrease the width of the multi-value parameter box?
Best regards.
I am experiencing a problem with the multi-value parameter dropdown in the ReportViewer when using FireFox, but in IE it seems to work fine. The symptom is when I click on the dropdown the list of options appears and then quickly disappears before I can select anything and I only experience the problem with multi-value parameters. I also have noticed that the dropdown is grayed out but I can still click it. Also, the position of the dropdown options are different in FireFox (pushed all the way to the left). I have created a very basic example at https://www.register4this.com/testreport.aspx to demonstrate the problem. There is no report, only the single "Orgs" parameter to keep things as simple as possible.
I suspect that I am missing something obvious because I haven't been able to find anyone else reporting this issue so any help would be appreciated. My ReportView control is configured as follows:
Code Snippet
<rsweb:ReportViewer ID="ReportViewer1" runat="server" AsyncRendering="False" Font-Names="Verdana" Font-Size="8pt" Height="400px" ProcessingMode="Remote" Width="400px">
<ServerReport ReportPath="/REG_Report/testreport" ReportServerUrl="http://reports.register4this.com/reportserver" />
</< FONT>rsweb:ReportViewer>
The report code is as follows:
Code Snippet
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Members">
<DataSourceReference>Members</DataSourceReference>
<rd:DataSourceID>54f7f4de-9fe5-44e9-bcf0-467d024725cb</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Organizations2">
<DataType>String</DataType>
<Prompt>Orgs:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Organizations</DataSetName>
<ValueField>orgID</ValueField>
<LabelField>orgName</LabelField>
</DataSetReference>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>2in</Height>
</Body>
<rd:ReportID>504c91c0-4beb-421e-bae0-4d581303b3e2</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Organizations">
<Query>
<CommandType>StoredProcedure</CommandType>
<CommandText>REG_spGetOrganizations_org</CommandText>
<QueryParameters>
<QueryParameter Name="@orgID">
<Value>=System.DBNull.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Members</DataSourceName>
</Query>
<Fields>
<Field Name="orgID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>orgID</DataField>
</Field>
<Field Name="orgName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>orgName</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Any help wpuld be greatly appreciated.
Hi All,
Is it possible to pass a multi value parameter from one report to another using the "jump to report" feature?
ie;
I have report A that has a mutli value parameter (para1). This report has a link with the action property set to jump to another report(Report B). Report B requires the same multivalue parameter. I have tries setting the parameter in the interface as well as trying the join function. Nothing seems to pass the values correctly.
regards
I have a multi-value parameter and I need to print its value in report.
View 4 Replies View RelatedI have a report with the following dataset query:
Code Snippet
SELECT ...,
dbo.getActivitySinceBeginningYear(..., @countryId ,...) AS HoursYear
FROM ....
WHERE cast(detail.COUNTRY_ID as nvarchar(max)) IN (@countryId)
AND ...
GROUP BY ...
countryId is defined as a String multi-valued parameter.
The getActivitySinceBeginningYear is defined as follows:
Code Snippet
ALTER FUNCTION dbo.getActivitySinceBeginningYear
(
...
@countryId ntext,
...
)
RETURNS decimal(18,5)
AS
BEGIN
declare @return decimal(18,5)
set @return = 0.0
SELECT @return = SUM(detail.HOURS_WORKED)
FROM ...
inner join charlist_to_table(@countryId, Default) f on detail.COUNTRY_ID = f.str
WHERE ...
RETURN @return
END
This function works as expected if I transmit one ID or several.
The report works fine if I check only one ID.
If I check several IDs, the report displays this error:
Procedure or function dbo.getActivitySinceBeginningYear has too many arguments specified.
Why???
My second problem is with the Where clause:
Code Snippet
WHERE cast(detail.COUNTRY_ID as nvarchar(max)) IN (@countryId)
If I do:
Code SnippetWHERE detail.COUNTRY_ID IN (@countryId)
I have the following error:
Conversion failed when converting the nvarchar value '3,5' to data type int.
(I transmitted 3 and 5 for countryId)
That's why I tried to use a cast, but the data returned by the query is wrong when I transmit several value.
??