Evaluating A Parameter's Value Dynamically

Jan 25, 2006

Hi, I'm creating a dynamic SQL statement in MS SQL Server that is similiar to this:

EXEC('IF @' + @current_column + ' (SELECT ' + + @current_column etc...

I'm basically looping through a large list of parameters that correspond to column names.  However, since SQL Server treats EXEC() as its own scope when it gets to what "@' + @current_column" evaluates to it says the parameter must be declared.

Is there a way to convert "@' + @current_column " into the actual value of the parameter?

View 2 Replies


ADVERTISEMENT

Is It Possible To Dynamically Populate A Parameter List With Values Based On Another Parameter Value?

Aug 11, 2005

Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student.  The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID".  Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!!  Thank you

View 5 Replies View Related

How To Set The Parameter's Default Value Dynamically?

Jan 30, 2007

Hi all,

Does anybody know how to set the parameter's default value dynamically?

I'm working on a report with some parameters against datacube, and I hope the default value of one of the parameters could be set dynamically based on the user's login.

Thanks,

Jone

View 1 Replies View Related

Can An RS Parameter Be Disabled Dynamically?

Dec 5, 2007

we learned that they cannot be hidden dynamically, but what about disabled, ie not allowed to dropdown, grayed out?

View 4 Replies View Related

Dynamically Add Update Parameter To Formview

May 3, 2007

I have a formview with name, email, and password.  I bind all fields to sql except the password which is blank.
In my sqldatasource, I define parameters for name, email and id:
UpdateCommand="UPDATE UserProfile SET Name = @Name,Email = @Email WHERE (ID = @ID)"><UpdateParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Email" /><asp:Parameter Name="ID" /></UpdateParameters>
In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated.  If I add define a password parameter like above then if a user left the password field blank then their new is blank.  That's way I think adding it dynamically is the way.  But I am having problems with the code to add the parameter in sqldatasource_updating event.
Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.UpdatingDim password As TextBox = FormView1.FindControl Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating
Dim password As TextBox = FormView1.FindControl("tb_password1")
If Not password.Text.ToString & "" = "" Then
SqlProfile.UpdateParameters.Add(New Parameter("@Password", TypeCode.String, password.Text.ToString))
End If

End Sub Thanks

View 9 Replies View Related

Dynamically Changing Default Parameter??????

Jun 4, 2007

I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.

Can this be done?

View 4 Replies View Related

Create Query Dynamically According To Parameter

Dec 6, 2007

I have a parameter in my report with 2 options.
1."Less than Jan 1 2007"
2."Greater than Jan 1 2007"

According to the option selected, I should have the query look like:
1."Less than Jan 1 2007"
select * from table_name
where open_date < Jan 1 2007
or

2."Greater than Jan 1 2007"
select * from table_name
where open_date > Jan 1 2007

Please let me know how can I acheive this.I'm Using ORACLE database

View 3 Replies View Related

Dynamically Display/hide The Parameter Input

Jul 11, 2007

I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).



Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.



What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.



Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?

View 8 Replies View Related

Using A Dynamically Created Parameter In Another Dataset Query

Jul 27, 2007



It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:

reportMonth - Just a non-queried list of months for the user to select
reportYear - Generated using the following query from dataset "Years":

declare @curYear int
set @curYear = 2000
declare @yearTable table (repYear int not null)
while @curYear <= year(getdate())
begin
insert into @yearTable(repYear) values (@curYear)
set @curYear = @curYear + 1
end
select * from @yearTable

This generated a list like:

2000
2001
2002
etc.

I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.

Thanks

View 10 Replies View Related

Setting Default Value Of A Report Parameter Dynamically

Jan 26, 2007

Hi all,

Does sombody have experience on dynamically set or change the default value of a report parameter?

Assuming: report parameters p1, p2, p3, p4 have been set up(and have their default value 'all') with the creation of the report1; report browseing is through reportviewer that embedded in the web application; datasource is datacube

What I want to do: based on the login user of the my web application, set default value of p1 as the user's username.

What I did is:

Microsoft.Reporting.WebForms.ReportParameter reportParam = new Microsoft.Reporting.WebForms.ReportParameter("P1","Mary");

ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter []{ reportParam });

what I got when reveiw the report:

P1 has no default value, and p2, p3, p4 greyed and could not choose value from them.

Any idea and suggestion will be pre-appreciated!

Jone

View 1 Replies View Related

SqlDataSource: How To Give A Dynamically Defined SelectCommand Parameter

Dec 12, 2006

Hi all
I have a cms-page where i want to display various entry-categories like news etc....I want to define which kind of entries should be shown by a parameter in the URL (e.g. cms.aspx?category=news). So far everything is OK.
To display the entries actually I'm using the following SqlDataSource:
<asp:SqlDataSource ID="SqlDataSourceCMS" runat="server" ConnectionString="......."SelectCommand="SELECT * FROM [cms] where category = news"></asp:SqlDataSource>
What i need, is to set the category which i want to show dinamically, like with a variable.
Does anybody know how i can set something like a variable in the SelectCommand property of the SqlDataSource?

View 1 Replies View Related

How To Hide A Report Parameter Control Dynamically At Runtime?

Jan 10, 2006

How would I hide certain parameter controls based on user sections in certain controls dynamically at runtime.  I am using RS 2003 edition.

View 5 Replies View Related

Reporting Services :: Displaying Data In Parameter Dynamically

Apr 20, 2015

I want to set the default value of the parameter based on the query result, when the current_status parameter is set as "opened" I want a certain date value to be displayed in the next parameter (start date), if it is set as closed I want a different date value to be displayed in the next parameter (start date), so I created the dataset with the query given below and assigned the default value to the query fields start and end date, however my Report parameters are not changing as expected.

dataset query:

IF @current_status = 'Opened'
SELECT DISTINCT cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(MAX(estcompletion_date) AS DATE) AS End_date
FROM  <Table Name>
ELSE IF @current_status = 'Closed'
SELECT      distinct  cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(getdate() AS DATE) AS End_date FROM <Table Name>

View 4 Replies View Related

Transact SQL :: Pass Parameter To Convert Function To Format Decimal Precision Dynamically?

Nov 4, 2015

I want to change decimal precision dynamically without rounding value

For example

10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount

[Code] ....

I am getting error as follows......

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'

This decimal precision format value will vary  company to company ...

View 7 Replies View Related

Reporting Services :: How To Arrange Dynamically Column Based On Parameter In SSRS 2008 R2

Apr 19, 2013

i have below table in DB

DB Table
ID
Column
Row data
1
Supplier CODE
1001
2
Supplier Name
ACB
3
Product
7K7
4
Price
1000

now I create  one report parameter order1 IF I will   give order1.value=1,2,3 then Report will come like this :--

Suppliercode

Supplier Name

Product

1001

ACB

7K7

IF I will   give order1.value=3,2,1 then Report will come like this :--

Product

Supplier Name

Suppliercode

7K7

ACB

1001

IF I will   give order1.value=1,3 then Report will come like this :--

Suppliercode

Product

1001

7K7

View 35 Replies View Related

Evaluating Disk Space

May 9, 2000

We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ???
Thanks so much for any help...
Nancy

View 3 Replies View Related

Evaluating Lumigent Entegra

Jul 23, 2005

Hi,I am evaluating Lumigent's Entegra for doing security and businessaudit of some of the critical database(s) in the company I work for. Iwould like to know what has been your experience in using this productfor doing similar audits in your company, if you have also done suchaudits.Thanks,Sanjeev

View 2 Replies View Related

Evaluating String Content

Jun 12, 2006

Our Business partners request me to read the field names from a SQL table dynamically so that my SSIS package will not get impacted if Web Service hosts make a change.

Is there a way to evaluate a string at runtime that contains a field name ?

Thanks,

Gulden 

View 6 Replies View Related

Error Evaluating Expression

May 22, 2006

Greetings my SQL friends.

Why do I get an error message saying that the following expression won't evaluate?

"Select * from Price_grp where price_grp_id >= " + (dt_str, 10, 1252) @[User::MIN_PRICE_GRP_ID] +
" and " + (dt_str, 10, 1252) @[User::MIN_PRICE_GRP_ID] + " > 0"

Thanks for your help in advance.

View 4 Replies View Related

Date Not Evaluating In An Expression

Dec 5, 2006



I have been looking all over for some info about other people having this problem, but haven't found anything.

I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:

@[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!

View 4 Replies View Related

Question On Evaluating Expression

May 15, 2008


Hi all€”I'm new to expressions, and am trying to write one where I can evaluate if a field is null, to handle it by inserting a default value if a null value is found. Given this table definition:

CREATE TABLE [dbo].[Meter Readings_stage](
[Meter System ID] [varchar](8) NOT NULL,
[Reading Date/Time] [datetime] NOT NULL,
[Meter Status] [varchar](50) NULL,
[Totalizer] [float] NULL,
[Secondary Totalizer] [real] NULL CONSTRAINT [DF_Meter Readings_Secondary Totalizer2] DEFAULT (NULL),
[Demand] [real] NULL CONSTRAINT [DF_Meter Readings_Demand2] DEFAULT (NULL),
[Demand Date/Time] [datetime] NULL CONSTRAINT [DF_Meter Readings_Demand Date/Time2] DEFAULT (NULL),
[Reading Type] [varchar](30) NULL CONSTRAINT [DF_Meter Readings_Reading Type2] DEFAULT (NULL),
[Comments] [varchar](200) NULL CONSTRAINT [DF_Meter Readings_Comments2] DEFAULT ('None'),
[Checked?] [bit] NULL CONSTRAINT [DF_Meter Readings_Checked ?2] DEFAULT ((1)),
[Estimate Required] [bit] NULL CONSTRAINT [DF_Meter Readings_Estimate Required2] DEFAULT ((0)),
[Hold] [bit] NULL CONSTRAINT [DF_Meter Readings_Hold2] DEFAULT ((0)),
[Processed?] [bit] NOT NULL CONSTRAINT [DF_Meter Readings_Processed2] DEFAULT ((0))

I am importing data from a CSV file where any of the fields can be null. The format is as follows:

[Meter System ID],[Reading Date],[Reading Time],[Totalizer],[Demand],[Demand Date],[Demand Time], [Reading Type]

I need to handle it something like the following in a derived column transformation, where:

[Meter System ID]: string [DT_STR]
[Reading Date]: database date [DT_DBDATE]
[Reading Time]: database time [DT_DBTIME]
[Totalizer]: float [DT_R4]
[Demand]: float [DT_R4]
[Demand Date]: database date [DT_DBDATE]
[Demand Time]: database time [DT_DBTIME]
[Reading Type]: string [DT_STR]

This is what I have built so far:
NULL((DT_DBDATE)("Reading Date")) ? (DT_DBDATE)(GETDATE()) : (DT_DBDATE)("Reading Date")

I am getting an error on the cast conversion, however. Where is the error in the evaluation?

Thanks,
Jon

View 5 Replies View Related

Need Help Evaluating And Changing The Value Of A Subquery

Feb 1, 2008



Hello all,
I currently have a pain in the butt with a subquery that needs to be evaluated, and if the result gives me a null value, I want to re-evaluate the condition and fill the column with the proper information.
I'll try to explain it as best as I can:

The query retrieves information from 2 tables basically, but i do need some inner joins of diferent tables in order to follow the relations..
I got 2 conditions that need to be acomplished, the 'typeof' (intId_Tipo) has to be diferent from 6 and a keycode equals to 3.

For the "general query" i do filter this just fine, but in the subquery is where I get stucked:
Since the evaluation that I do in oder to find the initial point is just by substracting 1, there are some cases where the type called intId_Tipo is actually 6 and the subquery returns me a null value.
In this particular case I want to substract 2..if not I just want to substract 1.
I added a SQL CASE in oder to evaluate the null value, but it is not evaluating properly, why? I'm not sure, I need your help and recomendation guys



Here's the query that I currently have: (the comments were made in order to take the next screenshot)



Code Snippet
SELECT --h.dt_Lectura,
--h.int_Velocidad, rp.int_VelMaxima,
(SELECT intpunto FROM tblRecorrido_Puntos WHERE intPunto = case when (intPunto) is null then rp.intPunto - 2 else rp.intPunto -1 END AND rp.intId_Ruta_Ramal =intId_Ruta_Ramal AND intId_Tipo =rp.intId_Tipo) as PuntoInicial,
rp.intPunto as PuntoFinal
--rp.strDescripcion_Punto as DescripcionPuntoFinal,
--1 as Contador
--a.idRuta_Guid, a.idRamal_Guid,a.idUnidad_Guid, a.idOperador_Guid

FROM tblHistorico h
INNER JOIN tblAsignaciones a
ON a.id=h.intIdAsignaciones
INNER JOIN tblRutaRamal_Generado rrg
ON rrg.id_Ruta = a.intRuta_Asignada
INNER JOIN tblRecorrido_Puntos rp
ON (rp.intId_Ruta_Ramal =rrg.intId_Ruta_Ramal AND rp.intPunto=h.int_PtoDest)

WHERE (
h.int_ClaveTDE =3 and rp.intId_Tipo <> 6
)

ORDER BY rp.intPunto ASC

Here's the screenshot of the query result: http://img218.imageshack.us/my.php?image=consultahd0.jpg

Any help is appreciated!
Jaime,

View 8 Replies View Related

Evaluating/Validating Paramaters In A SqlDataSource

Dec 21, 2007

I want to do some error checking on the parameters found in a SQLDataSource before I run the insert.  The problem is these are ControlParameters and I want to do this dynamically so I can't just call the Control.Text property and grab its value. So how can I get access to what the ControlParameter evaluates to?
Secondly, is there a way to access what the update parameters evaluate to in order to check them before they're inserted - if so how do I get access to these?
Here's an example of one of the data sources i'm using:<asp:SqlDataSource ID="sqlContact" runat="server" ConnectionString="<%$ ConnectionStrings:strConn %>"
SelectCommand="SELECT [ContactID], [FirstName], [LastName], , [Address], [Phone], [Grade], [Contacted], [ListServe] FROM [Contact]"
UpdateCommand="UPDATE Contact SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Address = @Address, Phone = @Phone, Grade = @Grade WHERE ContactID = @ContactID"
DeleteCommand="DELETE FROM [Contact] WHERE ContactID = @ContactID"
InsertCommand="INSERT INTO [Contact] ([FirstName],[LastName],,[Address],[Phone],[Grade],[Contacted],[ListServe]) VALUES (@FirstName,@LastName,@Email,@Address,@Phone,@Grade,0,0)">
<UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="Phone" Type="String" />
<asp:Parameter Name="Grade" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter Name="FirstName" Type="String" ControlID="txtContactFirst" PropertyName="Text" />
<asp:ControlParameter Name="LastName" Type="String" ControlID="txtContactLast" PropertyName="Text" />
<asp:ControlParameter Name="Email" Type="String" ControlID="txtContactEmail" PropertyName="Text" />
<asp:ControlParameter Name="Address" Type="String" ControlID="txtContactAddress" PropertyName="Text" />
<asp:ControlParameter Name="Phone" Type="String" ControlID="txtContactPhone" PropertyName="Text" />
<asp:ControlParameter Name="Grade" Type="String" ControlID="ddlContactGrade" PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
 An Event is fired when I click add on a button which looks similar to this:
//Add btnAddContact_Clickprotected void btnAddContact_Click(object sender, EventArgs e)
{
InsertRow(sqlContact);
}The InsertRow() function is then what i'm using to evaluate the values...  So how can get the values those controlparameters actually are in order to evaluate them before I actually insert.  Or is there a better way to do it?

View 4 Replies View Related

Evaluating Derived Columns Within A Select

Sep 19, 2006

Hi
I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!

The original Access SQL appears below:

SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded,
ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode,
ResearchStudent.RegistrationDate, StudentExamination.Decision,
IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate,
ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension
FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID)
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID)
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)]
And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1))
AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False))
ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365

View 15 Replies View Related

Evaluating 2 Fields And Returning The Larger

May 22, 2007

I have two int fields in my database, CEOAnnualBonus and CEOBonus, and I want to return the value of whichever one has the larger value as CEOBonusCombined. I thought using COALESCE would do the trick like below but there are many cases where either CEOAnnualBonus or CEOBonus have a zero value instead of NULL and it doesn't work.

SELECT
COALESCE(CEOAnnualBonus, CEOBonus)
AS CEOBonusCombined
FROM tbenchmarktemp
WHERE Ticker='F'

Thanks for any help

View 3 Replies View Related

Evaluating Varchar String With Math

Mar 5, 2008

In reference to this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50392

madhivanan asks for sample table data to avoid using a cursor. I could really use this, so here is the table



CREATE TABLE #T (
FORMULA VARCHAR(15),
ANSWER decimal(15,8)
)
INSERT INTO #T SELECT '1', NULL
INSERT INTO #T SELECT '1.0 + 2', NULL
INSERT INTO #T SELECT '1/2', NULL
INSERT INTO #T SELECT '1/2+2', NULL
INSERT INTO #T SELECT '1/(2+2)',NULL
INSERT INTO #T SELECT '3*7.5', NULL
INSERT INTO #T SELECT '3/2*.6-2', NULL
INSERT INTO #T SELECT '(1-1)', NULL

SELECT * FROM #T

-- RUN THE MATH FUNCTIONS, UPDATE THE TABLE'S ANSWER COLUMN WITH THE ANSWER

SELECT * FROM #T

DROP TABLE #T


I would expect this output:

FORMULA ANSWER
====================
1 NULL
1.0 + 2 NULL
1/2 NULL
1/2+2 NULL
1/(2+2) NULL
3*7.5 NULL
3/2*.6-2 NULL

FORMULA ANSWER
====================
1 1.00000000
1.0 + 2 3.00000000
1/2 0.50000000
1/2+2 2.50000000
1/(2+2) 0.25000000
3*7.5 22.50000000
3/2*.6-2 -1.10000000
(1-1) 0.00000000


Any help appreciated, thanks,

View 9 Replies View Related

SQL Server 2012 :: Evaluating Rows And Selecting Specific Records

Mar 18, 2015

I have a table with some examples below. I need to identify records where:

1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.

PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely

[code]....

View 6 Replies View Related

SQL Server 2014 :: Case Syntax In Store Procedure Evaluating 2 Query Options

May 4, 2015

What will be the best way to write this code? I have the following code:

BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal

[Code] ...

then if Signal Sent.... do the following query:

Select * from package.....

if signal not sent don't do anything

How to evaluate the first query with the 2 options and based on that options write the next query.

View 2 Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It

Feb 19, 2007

Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.





View 3 Replies View Related

Reporting Services :: Preview Has One Extra Parameter Than Parameter List - SSRS

Aug 21, 2015

I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter. 

How do I know , when this parameter is coming in the preview ?

View 5 Replies View Related

Reporting Services :: Multi-value Parameter Not Passing In Subreport Parameter List

Jul 29, 2015

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 .

View 2 Replies View Related

Default Parameter Value Is No Longer Part Of Cascading Parameter In SSRS 2005?

Jan 30, 2007

Hi,

I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.

I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.

Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)

It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.

Is this a SSRS 2005 bug? is there any other work arounds or suggestions?

Thanks.

Kong

View 6 Replies View Related

How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters

Oct 15, 2007



Hi,

I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.

My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?



Other question :

Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.


Thanks !

JF

View 7 Replies View Related







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