Parameters - Multiple Values For One Label - Possible?

Sep 18, 2007



Is it possible to have a parameter with one label but multiple values. For example:

Label Value
---------------------------------------------

Machinist (100,200,300)



Is it possible to set up an expression that when the user selects this label it will look for the job codes 100, 200 and 300
and return all employees in those codes?

Thanks,
A

View 2 Replies


ADVERTISEMENT

Parameter Label With Multiple Values

Oct 22, 2007

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

View 3 Replies View Related

Parameter Setup: One Label Multiple Values --- Can This Be Done?

Mar 12, 2008



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

View 3 Replies View Related

Reporting Services :: Selecting Multiple Parameters Values For Comma Separated Values In SSRS?

Jun 17, 2012

I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.

View 5 Replies View Related

Parameters With Multiple Values Error

Apr 4, 2008

Hello,

Using a Report Designer of the SQL Server 2008 connecting to an ORACLE database:

- I want to use Parameters filters with multiple Values but i get the error :

"FilterExpression for the data set 'DATAset1' cannot be performed. Cannot compare data of types System.String and System.Object[]. Please Check the Data Type Returned by the FilterExpression"


without the multiple Values it works but don't resolve my problem.

The filter configuration is =Fields!DT_OPERACAO.Value = =Parameters!FLT_Ano_Lectivo.Value


how can i solve this situation ?

View 3 Replies View Related

Parameters Dependencies When Using Parameters!Name.Label

Mar 7, 2007

Hello:

I just recently bumped into this problem and I think I know what's causing it. This is the setup:

Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep

dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,

dsDivisions Parameters: N/A

dsManager Parameters: DivisionalOffice.Value

dsSalesRep: DivisionalOffice.Label

When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!

If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )

Has anybody seen this behavior and more importantly, is there a work around?

Regards,

View 3 Replies View Related

Passing Multiple Values To A Single Parameters

Dec 28, 2007

I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?

View 4 Replies View Related

Reporting Services :: Multiple Values In Parameters - Error

Aug 24, 2015

I have done this several times before but somehow unable to make it work this time around.

select ast.AssetId, fu.ParentDescription from
dbo.asset ast
inner join tblPatchViaFU FU ON ast.AssetId = fu.Assetid
where fu.ParentFUId IN (@IMO)

In tblPatchViaFU table column [ParentFUid] is int datatype

***** Dataset for Parameter "IMO" ******

SELECT DISTINCT
ParentFUId ParentFUId
, ParentDescription IMOfficer
FROM
[dbo].[tblPatchViaFU]
WHERE
ParentName = 'Income Patch'

***** @IMO Parameter ******

Datatype : Integer
Allow multiple values

Available Values
Dataset : IMO
Value Field : ParentFUId
Label Field : IMOfficer

The report runs perfectly fine for 1 parameter value but the moment I select more than one it fails with the error messageĀ  "Error converting data type nvarchar to int."

View 3 Replies View Related

Parameters!Blah.Label(0) - How To Iterate Through Array

Nov 27, 2007

I have a multi select param that I display on the title of the report using something like so:
Parameters!Division.Label(0)


The problem is when a user selects more than one value it does not get displayed on the title. Addding Parameters!Division.Label(0) + Parameters!Division.Label(1) + Parameters!Division.Label(2) doesn't returns an error if user only selects 1 or 2 values because Parameters!Division.Label(2) is empty.

Is there a way to iterate through or do error checking on the array so that I can dynamically populate the title with the values selected by the user.

Any help would me much appreciated.

View 5 Replies View Related

Error When Reading The Label Field From Report Parameters

Aug 16, 2007

Hi there,

I am trying to fill a Textbox with the Label-Field from the Report Parameters. The Multi-Value-Option is checked and it is possible to select multi-values from the time hierarchy [Time].[Year - Half Year - Quarter - Month].

For example you can choose:
"Calendar 2007" ([Time].[Year - Half Year - Quarter - Month].[Year].&[2007-01-01T00:00:00])
"Quarter 4, 2006" ([Time].[Year - Half Year - Quarter - Month].[Quarter].&[2006-10-01T00:00:00])
and "April 2006" ([Time].[Year - Half Year - Quarter - Month].[Month].&[2006-04-01T00:00:00])

Now I want to fill a Textbox with the chosen parameter values by putting following expression as value of the textbox:

Parameters!TimeYearHalfYearQuarterMonthDate.Label ... with "TimeYearHalfYearQuarterMonthDate" as the name of the DataSet.

That gives an Error.

Can you please help me out?

Best regards
Claudio

View 6 Replies View Related

Automatically Print Multiple Copies Of The Same Report With Different Label On Letter Size Paper

Dec 31, 2006

Hi,

I am new to reporting services and I'm really stuck on a design problem. Can someone please help me?

I would like to design my own print function. When a user clicks on the print icon (preferably the one that came with reporting services), the report is automatically printed twice, once with "For Person A" and the second time with "For Person B" on it. It doesn't matter where these two labels are placed on the page. These two reports need to be printed on letter-size paper regardless of user's selection. How do I do this with minimum amount of code?

Any help would be greatly appreciated!

View 8 Replies View Related

SQL 2012 :: Find Out Values Of Parameters When SP Is Executed With Default Values?

May 30, 2014

I am working with SP. How can we find out values of parameters when the SP is executed with the default values?

View 9 Replies View Related

Default Values Does Not Include All The Values (Cascading Parameters)

Mar 18, 2007

A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.

One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).

If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with  Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.

What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.

 

View 8 Replies View Related

Search Multiple Parameters In Multiple Tables

Dec 21, 2007

Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
  @name var(30),

@nationality (30),

@phone int,

etc

as



Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c

WHERE

name LIKE '%' @name '%'

OR nationality LIKE '%' @nationality '%'

OR phone LIKE '%' @phone '%'

etc
 
But I got an error when I am trying to execute this code because the nulls values so I wrote
 1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25

 
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
 
Thanks in advanced

View 4 Replies View Related

Retrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports

Mar 30, 2007

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.



Thanks.



I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.



--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE ca.name = 'Service Opportunity'



--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Num of Hours'



--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Grade'



--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')

View 11 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Adding Values To A Parameter That Can Take Multiple Values

Jun 6, 2007

If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?

View 2 Replies View Related

'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go

Sep 1, 2007

Please be easy on me...I haven't touched SQL for a year. Why given;



Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF


do I get

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ','.
for the following;




Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');


Thank you,
hazz

View 3 Replies View Related

SqlDataReader - Pulling Multiple Values Into Multiple Variables

Mar 29, 2007

Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{    String UserName;    String userId;    String HiveConnectionString;    String Current_Location;    ArrayList Location;    public String Location1;    public String Location2;    public String Location3;    //Int32 x = 0;    private void Page_Load(object sender, EventArgs e)    {        if (User.Identity.IsAuthenticated)        {            UserName = Membership.GetUser().ToString();            userId = Membership.GetUser().ProviderUserKey.ToString();            HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647";            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString))            {                // Map Updates                 MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "SELECT Location FROM tracker WHERE Location = IsOnline = '1'");                Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                Locationcmd.Connection = conn;                conn.Open();                MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader();                 while (LocationReader.Read())                {                        Location1 = LocationReader.GetString(0);                        //Location2 = LocationReader.GetString(1); // This does not work..                }                                LocationReader.Close();                conn.Close();                // IP Display                MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "SELECT UserName FROM tracker WHERE PKID = ?PKID");                Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                Checkcmd.Connection = conn;                conn.Open();                object UserExists = Checkcmd.ExecuteScalar();                conn.Close();                if(UserExists == null)                {                    MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)");                                        Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress;                    Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName;                    Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                    Insertcmd.Connection = conn;                    conn.Open();                    Insertcmd.ExecuteNonQuery();                    conn.Close();                }                else                {                    MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand(                        "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID");                    Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress;                    Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName;                    Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                    Updatecmd.Connection = conn;                    conn.Open();                    Updatecmd.ExecuteNonQuery();                    conn.Close();                }            }        }    } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone! 

View 1 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Dts Parameters, More Values In One Parameter

Feb 21, 2007

hy all,

i'm using the object transform data task from a server (where i'm not dbo) to another server (where i'm dbo).

i'm getting data from a table. i need to select just some records from this table. i need to use a query like this one below...

select * from {table_name} where operationedate in ('20070101', '20070205', '20060524')

... cause everytime i run the dts the operationdate field must be filtered on different date.

so i tried to use the parameter from a global variables. i've tried lots of things but everytime i failed.

i used to try:

select * from {table_name} where operationedate in (?)

but it doesnt work!

any one can understand what i try to explain and even help me?

bye

nicola

View 7 Replies View Related

Assigning Values To Parameters Dynamically

Oct 25, 2006

i using a bound data grid which is using a stored proc. The stored proc needs the ClientID "if logged in" there is no form or control on the page outside of the loginstatus.  I am wanting to pass the Membership.GetUser.ProviderUserKey.ToString()  to the asp:parameter but I cant get it to work.So How do I pass a variable to a stored proc parameter using a bound data grid.I this its very strange that this cant be dont and there are a raft of reason why you wold want to do this with out the need to pass it to a form control.please helpjim

View 2 Replies View Related

Values In Define Query Parameters Box

Nov 21, 2007

How do I create value from the dropdown list of each parameter within the "Define Query Parameters" box, which appears when I run a query/dataset from the "Data" tab of the SSRS Report Designer ?

It usually list all the "Parameter Name"s I defined, and has another column for "Parameter Value", but it always shows only <Null> or <Blank> even though I have assigned the report parameters with data (from a Query, not hard coded).

The parameter values work fine when in "Preview" mode, but it would be of convenience to have them during design time.

Thanks in advance.

View 7 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

Updating/getting Values From Datagrid In C# With Variable Parameters

Oct 24, 2006

HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT

View 1 Replies View Related

Filters Whith Diferent Values (parameters)

Apr 11, 2006

I want to make a filtering by using a string parameter (a,b).
The initial result of my query is this:

Name Letter
Luis a
Juan b
Michel c

Note: There are 2 columns (Name) and (letter)

So after filtering the result pane should be like this

Name Letter
Luis a
Juan b

If i send a diferent filtering parameter (b), the result should change like this

Name Letter
Juan b

How can I do this??

Thyanks

View 2 Replies View Related

Can You Force A Refresh Of The Default Values Of Your Parameters?

Aug 1, 2006

This is regarding Report Services 2005 designing the report in Visual Studio.

Currently I am using a query to populate 2 date selectors in the parameter section at the top of a report.  The start and end dates are determined by a query which returns the values based on yet another parameter.  If I change the value of the paramter which is supposed to be used by the date selectors they don't seem to feel the need to re-calc themselves.  I have 3 other dependent param's in the same report which behave reasonably.

Has anyone seen this before or is there way to somehow force a parameter re-caluclation?

 

jSiegel

 

View 1 Replies View Related

How To Get The Values From Dataset To Parameters On Report Header

Mar 13, 2008



Is it possible to get the reportdataset field values into parameters. dynamically when the report is generated.

Thanks.

View 1 Replies View Related

Store Into Database Report Parameters Values?

Feb 3, 2007

Hallo
is it possible to perform an insert/update query upon the execution of a report, using the report Visual Basic expressions?
I mean, the user enters some values for the report parameters, and i would like to store them, along with the resulting recordset data, into a dedicated table in the database.

View 1 Replies View Related

Cascading Parameters With Values Supplied By Query

Feb 11, 2008



Hi,

I would like to set up cascading report parameters but need to supply the list of values in my dataset query as they are not being pulled from a table or stored procedure.

I would like to set up something like

select 'LS' Source
Union
select 'RH' Source
Union
select 'UK' Source

as the value list for my first parameter, then, I would like the second parameter to default to '1' if the first parameter has 'RH' or 'UK' selected but default to blank if the first parameter has 'LS' selected. I can get all to default to blank or 1 but can not seem to make it dependent.

thanks!

Martha

View 2 Replies View Related

SqlDecimal Function Parameters With NULL Values

Oct 4, 2007

Hi,

I have a CLR function that throws an error if one of the parameters is NULL. Am I using the IsNullable tag correctly or am I supposed to do this another way? The function simply formats decimal values using .NET culture information and returns a string. Thanks very much for any help. -- Erik


[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, DataAccess=DataAccessKind.None)]

[return: SqlFacet(MaxSize = 30, IsNullable=true)]

public static string FormatGeneralDecimal([SqlFacet(Precision = 28, Scale = 8, IsNullable = true)] SqlDecimal sqlDc,

[SqlFacet(MaxSize = 10)] string cultureName)

{


string result = null;

if (!sqlDc.IsNull)

{


CultureInfo ci = CultureInfo.CreateSpecificCulture(cultureName);

result = sqlDc.Value.ToString("G", ci);

}


return result;

}

It works great unless I call it with a NULL value for sqlDc, in which case I get this:


select [dbo].[FormatGeneralDecimal](NULL, 'de-DE')



Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "FormatGeneralDecimal":

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException:

at System.Data.SqlTypes.SqlDecimal.ToDecimal()

at System.Data.SqlTypes.SqlDecimal.get_Value()

at MyFunctions.FormatGeneralDecimal(SqlDecimal sqlDc, String cultureName)

View 1 Replies View Related

Setting The Range Of Values For SSRS Parameters

May 8, 2007

Hoping someone may be able to help with a problem I'm having with SSRS parameters....

My report has 2 parameters - the User Id used to login to the application and the Department(s) within the organisation. Based upon the User's role, the user may have access to data for one or many Departments.

Thus, the first parameter needs to be set in code based upon the User's login, however, the range of the second parameter (i.e. the range of Departments that the user can access) is controlled by the value of the first parameter.

The second parameter is to appear as a drop-down of Departments to which the User has access.

The report is to be produced for the selected Department.

Are you able to advise how to restrict the range of values for the second parameter based upon the value of the first parameter?

Any help is much appreciated.

View 3 Replies View Related

Use Of Parameters To Hide Column Values Of A Report

Oct 10, 2007



HI All,

I want to send a reports to two person. Reports are going to be delivered automatically. I hope to use snapshot option. In my report there is one column which can be seen by only one person. Can I use parameters to hide one column from one person? If its possible, can you explain how to do that please. If its not possible, what are the other option excet creating two different reports.
Also If I use parameters , can reports be executed automatically?
Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved