We have some reports that allow null values for parameters and I find the "Null" label next the parameters in the report viewer to be completly the wrong thing i want to show to business users.
Has anyone worked out a way to change this label to something more meaningful?
aaron
Hello!
Is it possible to create a row of "labels" in a multi-value parameter list?
i mean something like this:
Name, Age
JJ, 12
PP, 10
Thanks
//C
Hi,
View 16 Replies View Related
Is there a way to set the default so that the parameter area is displayed by default when running a report in Report Manager. Currently you have to go up and hit the arrows to open the parameter area. I'd like it to be open by default.
Thanks.
Greetings,
Is there a way to force the parameter input area to always show even when default values are specified? I want to always display the parameter area even when I set initial parameters.
Thanks
Does anyone know a way to force RS to not collapse the parameter area on a drill through? I searched this forum and found some related threads but nothing that covered this.
I have several reports where they drill through to each other and in all cases, while they pass the parameters between, the users usually want to go directly to different products or time periods within the reports. They constantly have to expand the input area every time they go to another report. This confuses a lot of them.
Is there a setting somewhere that controls this?
Thanks, Tim
Hi all,
In Table Detail session,
=Fields!test.Value
Will popup as numbers. 1 2 3 4 etc.
I set parameter as Value 1 = Label "First" Value 2 = Label "Second"
Is that possible to show the lable of Fields that I set in Parameter?
Advice plz.
Thank you
Hello.
I have a multi value parameter in my report.
I want to know how can I display all of the selected values in this parameter in a text box.
Whan I try to use this parameter in a textBox it automaticly take the value:
Parameter.param.Value(0) Whice take only the first select value.
Is there a simple way of doing it?
Thanks.
Hi,
I have a parameter "country" that currently has something like 50 labels (each with a different value) for different countries. I want to be able to design this so that I can select US, Canada and World. This requires me to put multiple values under the "World" label.
Is this doable in reporting services?
Thanks in advance,
Steven
I have two parameters: year and month. The Month parameter includes All, 1, 2, ect..
I tried to display the parameter slected with the following expression:
=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,
MonthName(Parameters!DateShippedMonth.Label)) & ", " & Parameters!DateShippedYear.Label
I want it to either display the label (All) or the name of the month. I get an error when All is the parameter selected but not when any month is selected. If I removed the MonthName() function, I don't get an error but I also don't get the month name.
Any ideas?
Is there a way to change the background color of the parameter prompt area on the web report viewer control?
Thanks!
BobP
I reviewed many threads and looked through many pages in my book, I cannot find my answer.
I have a report in need of carrying multiple values per parameter label. Here is the report parameter and how it is used;
Our IS system considers A-Active, L-On Leave, P-Paid Leave to be "Active"
Our IS system considers T-Terminated, D-Deceased to be "Termed"
Instead of having to have the user click upon three options (A, L, P), I would like to group these and have the Label appear as "Active", while the two options for "Termed" (D, T), would appear as the second parameter option.
Does anyone know how I would get this correctly setup in the Report Parameters... module?
I have tried
Label|Value
Active|A,L,P
Termed|D,T
And
Label|Value
Active|(A,L,P)
Termed|(D,T)
Thank you,
Mark
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
Is it possible through SSRS designer?
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Key cannot be null. Parameter name: key
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies View RelatedHi,
I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.
The idea is the create snapshot nightly and then display the report filtered based on parameters to users.
I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,
Right now I defined it this way :
Left =Fields!RegionCode.Value
Operator =
Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)
I do this for the 4 parameters that are passed, if they are null, make then equals to the field.
I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.
Thanks
I have a sql proc. I need to make a parameter optional. I've setup the parameter to use a dataset as its source, and I need NOT to allow the value to be null. I've NOT selected allow nulls check box, But, when the parameter is null (‘’), still data comes back in a table as empty or if it is date will default to 1/1/1900. How do I send a null value to the database through sql proc and raise an error without inserting a null value? I’m hoping to avoid creating a special query just for that purpose.
Thanks.
Josephine
I Created a Procedure that Generates an ID value based in the current Date (Year/Month) and a value which are stored in a table. This ID value is update in the table, and passed on to an output parameter as folows:
Code Snippet
CREATE PROCEDURE [dbo].[CNSPGetNewGuideID]
@SectionID bigint
,@Year bigint
,@Month bigint
,@ID bigint output
AS
BEGIN
(...)
SELECT @ID = [Year]* 10000000 + [Month] * 100000 + [Number]
FROM [dbo].[CNGuideID]
WHERE [Year] = @Year
AND [Month] = @Month
print 'New ID : '
print @ID
END
This is working and the value printed here is the correct one.
but when I execute the procedure inside another one, the variable i pass on as an output parameter remains NULL...
Code Snippet
CREATE PROCEDURE [dbo].[CNSPInsertNewGuide]
@SectionID int
,@ClientID int
,@UsuaryID bigint
,@UsuarySectionID int
,@ID int OUTPUT
AS
BEGIN
DECLARE @Date as datetime
DECLARE @Year as int
DECLARE @Month as int
SET @Date = GetDate()
SET @Year = YEAR(@Date)
SET @Month = MONTH(@Date)
EXEC CNSPGetNewGuideID @sectionID,@Year,@month,@ID
(...)
I also tried just declaring a variable and executing the procedure but the same thing happens
Code Snippet
declare @ID2 bigint
EXEC CNSPGetNewGuideID 06,2008,03, @ID2
print 'ID : '
print @ID2
this is the result I get:
New ID :
20080300030
ID :
I must be missing something...
Can anyone help?
thanks.
Hi all,
I'm passing a list of values to a stored procedure using an XML parameter - using SQL 2005.
The stored procedure is used to search a table for matching values so this list can contain one or many values, or could be NULL to indicate all values should be returned.
Returning one or more than one value works fine in the code sample given below. How can I phrase my query to cope with the scenario of the parameter being NULL to select all values?
I currently have the following code to cope with one or many values being passed:
Code Snippet
SELECT * FROM dbo.MyTable
WHERE idField IN
(SELECT paramvalues.id.value('.', 'int') FROM @testxml.nodes('/rootnode/idfieldvalue') as paramvalues(id))
where @testxml is the XML parameter passed into the stored procedure.
Thanks as always
Shivonne
In the report I created, there is a drop down used as a parameter, i.e. Sector.
This drop down gets populated with data. It also has <Select a Value> in there.
When no item is selected, i.e. only <Select a Value> is shown in the drop down list, when the view Report button is clicked, the message says:
Please select a value for the parameter 'Sector'
The view Report used a stored procedure which allows null for this Sector parameter.
The SP does work fine in the sql server query analyser because it returns data with and without the sector parameter.
Any thoughts please?
Thanks
We are using forms authentication with SSRS2005 and we are able to access Report Manager (http://servername/reports) and Reportserver (http://servername/reportserver). The problem we are having is every time we add a user to the Content Manager or the Publisher role. The user is able to login and reach the Report Manager but no folder is showing up and also we have the following error message : "Key cannot be null. Parameter name: key".
We removed users from the Content Manager and Publisher Roles and they are able to reach report manager and see the folders. This only happens with report manager. everything works fine with reportserver(http://servername/reportserver).
Any idea why we're getting an error message when trying to assign the Content Manager and publisher Roles to a user.
here is the error log from ReportServerWebApp :
aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: Key cannot be null.
Parameter name: key
aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: HTTP status code --> 500
-------Details--------
System.ArgumentNullException: Key cannot be null.
Parameter name: key
at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
at System.Collections.Hashtable.Add(Object key, Object value)
at Microsoft.ReportingServices.UI.BasePermissions.ReadPermissions(String[] permissions)
at Microsoft.ReportingServices.UI.Permissions.GetPermissions()
at Microsoft.ReportingServices.UI.Permissions.CurrentUser(String itemPath)
at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
Thanks!
chi
How can I pass NULL to a parameter, if now entry is made in the textbox?
Dim KeywordParam As New SqlParameter("@Keyword", Me.KeyWordText.Text)
MyCommand.Parameters.Add(KeywordParam)
Hi:I have an application in which I am producing a lists of media outlets based on a number of 6 possible parameters including location, medium, format, scope, language and coverage. I have an interface in which users can select some or all of the possible parameters. Where they don't choose a parameter - e.g. location, I would like the SELECT query to include all locations. I am at the point now of writing 6 X 6 = 36 possible queries to handle the different combinations of possible parameters. This seems a bit excessive and clumsy. I am just learning about stored procedures and was wondering if it is possible to create a procedure that would accept the 6 parameters including ones with null or 0 values and generate a query that would ignore those that were null or zero. Any help in this regard greatly appreciated.Roger Swetnam
View 5 Replies View RelatedI have datagrid whose data source is set to SqlDataSource. SqlDataSource's connection string is set dynamically at pageload. When i click on delete link in gridview it executes delete command but row doesnt get deleted from data source. When i run Sql server profiler i found the delete statement is getting executed but the parameter is always null. why?
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
Caption="Events emitted by the application." DataKeyNames="EventId" OnDataBound="GridView1_DataBound" Width="600px" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" OnRowDeleting="GridView1_RowDeleting" OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="EventId" HeaderText="EventId" SortExpression="EventId" />
<asp:BoundField DataField="EventTime" HeaderText="EventTime" SortExpression="EventTime" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:CommandField SelectText="Vew Details" ShowDeleteButton="True" ShowSelectButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" DeleteCommand="DELETE FROM aspnet_WebEvent_Events WHERE (EventId = @EventId)"
SelectCommand="SELECT EventId,EventTime,Message FROM aspnet_WebEvent_Events WHERE (ApplicationVirtualPath = @ApplicationVirtualPath)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="ApplicationVirtualPath" PropertyName="SelectedValue" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="EventId" Type="String"/>
</DeleteParameters>
</asp:SqlDataSource>
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Delete")
SqlDataSource2.Delete();
}
I keep getting "Value cannot be null. Parameter name: dataSet" when I run this statement and bind it to a dataset using a sqldataadapter in ASP.NET using VB.NET.
"SELECT playerstats.playerid, playerstats.gameid, SUM(playerstats.fta), SUM(playerstats.ftm), SUM(playerstats.tpm), SUM(playerstats.rb), SUM(playerstats.fga), SUM(playerstats.fgm), SUM(playerstats.tp), SUM(playerstats.st), SUM(playerstats.a), 100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, player.playerid, player.lname + ', ' + player.fname AS fullname FROM playerstats, player WHERE playerstats.playerid = player.playerid AND player.leagueid = " & ddlLeague.SelectedValue & " ORDER BY " & strSortField
I know there is data in the db.
Please help :confused:
I have a stored procedure as a recordsource from a contacts table. Inthis example, users can enter parameters to limit contacts by firstletter of last name or company name or keywords:Example:@myName nvarchar(30) = null,@Alpha char(1) = nullSELECTContacts.ContactID, ContactType,CASE WHEN Contacts.ContactType = 0THEN Contacts.CompanyNameELSEisNull(Contacts.LastName,'?') + ', ' +isNull(Contacts.FirstName,'?')ENDAS CNAMEFROMContactsWHERE(Keywords Like '%' + @myKeyword + '%' OR @myKeyword is Null)So far, so good, but...The problem is I want to also give the user the option of filteringalphabetically by first letter. I can't figure out how to deal withnulls in this example (user doesn't enter anything as parameter@Alpha):AND(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1) ENDOR@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyName,1) END)Any help is appreciated,LQ
View 2 Replies View RelatedHello,
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.
Hi,
I have a report with datetime parameter which is required to be optional (there should be an option not to select this parameter which would cause in not narrowing result of this report by this parameter).
I used datetime parameter (to have the calendar control for selecting date) for which I enabled NULL value. The result is that next to this parameter there is a checkbox "NULL" which allows to send NULL as a value of this parameter. Everything is fine except for the prompt: "NULL"... Is there a way to change it? I would see there something like "Empty" or "Not selected", which would be understandable for users of the report.
Or is there any other way to send an empty date as a value of this parameter? (Setting some unusual value like 1/1/1900 for empty date is not sufficient as the empty value shouldn't appear in the parameter control).
Thank you,
Michal
In my report I have a dataset that is returning an ID field among other things. I am setting a hidden parameter = to that ID field. That parameter is then used to return another dataset.
My problem is that the original dataset with the ID field does not always bring back data (this is expected). If data is not brought back, I get an error when I try to execute the report ("The 'X' parameter is missing a value").
I have the parameter set to "Allow Null Values".
This value is not required in the SQL Server stored procedure that returns values for the new dataset.
The parameter is of type Integer. (So I am not able to set the "Allow Blank Value" property)
This is NOT a multi-value parameter.
Does anyone have any suggestions/ideas/workarounds to solve my problem?
I am having a trouble when I try to retrieve a SQL parameter outside to .Net Clr
I did a clr example like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
[Code] ...
I now my trouble and it is because the parameter @p_it is null when I try to sed it to pipe in SqlContex but really it is not null. When I test the clr with this code:
CREATE ASSEMBLY
[Test]
FROM
N'Z:SomeRouteTest.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE [dbo].[Test]
[Code] ....
I obtain the next error message:
"Mens 6522, Nivel 16, Estado 1, Procedimiento Test, Línea 14
A .NET Framework error occurred during execution of user-defined routine or aggregate "Test":
System.ArgumentNullException: El valor no puede ser nulo. -----> The value can not be null
Nombre del parámetro: message
System.ArgumentNullException:
en Microsoft.SqlServer.Server.SqlPipe.Send(String message)
en Test.StoredProcedures.Test(SqlString p_t)"
Hi All,
I have a problem while trying to set "NULL" value to my datetime parameters.
I have two datetime parameters (AllowNull=True) on my report. (BeginDate and EndDate)
it works fine in preview mode in reporting design.
The problem occurs, if I try to set an emtpy value to these parameters in my asp.net application.
The code looks like;
Dim repParams(1) As ReportParameter
repParams(0) = New ReportParameter("BeginDate", String.Empty)
repParams(1) = New ReportParameter("EndDate", String.Empty)
-----
ReportParameter method requires String parameters. If user set any datetime value, I am sending "String.Empty" value to my paramaters in order to set them as NULL. But I am getting "rsReportParameterTypeMismatch Error."
How Can I set NULL value to these parameters from asp.net application?
Thans alot in advance.
I have the following database structure:
The table aspnet_users, which we all know
Another table with an uniqueidentifier which references the user iD
I want to set the value of the iD in one of the entries in the second table to null.
I tried this:
string assigned is null, i am passing this as a method parameter
...............
string queryString = "UPDATE [table2] SET AssignedUserId=@assigned WHERE ProblemId = @id";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
...................
System.Data.IDataParameter dbParam_au_pr = new System.Data.SqlClient.SqlParameter();
dbParam_au_pr.ParameterName = "@assigned";
if (assigned == null)
{
dbParam_au_pr.Value = null;
}
else
{
dbParam_au_pr.Value = assigned;
}
dbParam_au_pr.DbType = System.Data.DbType.String ;
dbCommand.Parameters.Add(dbParam_au_pr);
I also tried using "" instead of null, or not using that "if" statement at all.
I get an error that says:
Parameterized Query '(@id nvarchar(1),@assigned
nvarchar(4000))UPDATE [tracker_Probl' expects parameter @assigned,
which was not supplied.
Please help with this as soon as possible.Thanks
I'm trying to write a stored procedure for a search criteria. A user will have few controls on ASP.Net webpage to enter/select the search criteria, based on which , it returns the data.
A Textbox( to enter Username), A Textbox (to enter Employee name),
A DropDownList (To select the profile. Its Id is sent as parameter) &
A DropDownList (To select status). This is where I am stuck.
The last dropdown has 3 list items
1. --All-- value is null/empty
2. Active value is 0
3. Blocked value is 1
Though the Database table will have only 0's and 1's for the status column, the user should be able to see all records when -all-- is selected.I also tried excluding the status parameter and its condition, but no luck either way.