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


ADVERTISEMENT

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

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

SQL Server 2012 :: Create Variable In Select Query And Use It In Where Clause To Pass The Parameter

Sep 9, 2014

I am writing a stored procedure and have a query where I create a variable from other table

Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision

But I am getting error for Decision1 , Decision2. How can I do that.

View 6 Replies View Related

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 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 SP Dynamically

Sep 2, 2005

Hello,

I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:

CREATE PROCEDURE .....

to

Alter PROCEDURE ....

Then I am obvoiusly getting the error msg:

Invalid object name.

How can I get rid of that?

View 14 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

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

Create Tables Dynamically

Sep 12, 2007

Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
 
Thanks & Regrads
Jai Shankar
 
 
 

View 1 Replies View Related

Create Table Dynamically

Apr 14, 2008

Hi,

I have N1 table where columns name(id,Field). Base on the fields of this table I want to create N2 table from SP where data from N1 will be columns in N2.
id Field
-- ------
1 ID
2 First
3 Last

Create table N2(ID,First,Last)

regards,
Mark

View 6 Replies View Related

How To Dynamically Create Tables

Oct 7, 2006

I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.

The Typed Table might get created by something like:

public void CreateTypedLiterals(Type type)
{
String sql = String.Format(
"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",

// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()
);

...

As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:

[SqlProcedure]
public void CreateTypedLiterals(Type type)
{
DataTable TypedLiterals =
new DataTable(
String.Format("Literals_{0}", type.ToString()));
TypedLiterals.Columns.Add(
"ID", typeof(int), "DISTINCT NOT NULL");
TypedLiterals.Columns.Add("Value", type);

...

But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:

using(Microsoft.SqlServer.Server)
{
CurrentDatabase.Tables.Add(TypedLiterals);
}

But this is afaik not possible. Has anybody an idea how to solve this issure?

View 5 Replies View Related

Create Subtotals Dynamically

Apr 3, 2007



I am trying to dynamically create subtotals base on values in a column. I also have to group by year. I have gotten the group by year. I used the (previous function) to check for the year and col1 not being equal. I am getting the first subtotal back, don't know how to proceed in my function to return the remaining subtotals



i.e.

col1 col2 col3

aaa 111 2005

bbb 222 2006

aaa 333 2006

ccc 444 2005

ccc 555 2006

bbb 666 2006

bbb 777 2006

ddd 888 2007



2005

aaa subtotal: 1

ccc subtotal: 1

total: 2

2006

aaa subtotal: 1

bbb subtotal: 3

ccc subtotal: 1

total: 5

2007

ddd subtotal: 1

total: 1

grandtotal:

aaa 2

bbb 3

ccc 2

ddd 1

totalnumber 8

View 1 Replies View Related

Create Charts Dynamically

May 3, 2008

Hi there!

I have a certain scenario and would like to know if it would be recommendable to try and realize it through RS.

I have a matrix of sales data (countries, regions, categories, total sales, Year-To-Date, changes, etc.) that need to be shown on charts. The charts themselves have a single format (bars). The out put needed is a relatively large number of charts (3000+) that would allow for a great number of comparisons (country to country, region to region, etc.). All these charts share a single layout. What would be varying greatly are the values and the labels attached to the elements on the chart (labels of the bars, title of the chart, legend, footnotes, etc.)

My plan is the follwoing:
- create a .net module that would prepare the data for the chart. This would be done with two tables

- parent table with general information
- child table with the the values for the bars and the labels
- Create a parametrized report in RS that accepts the Id of a report from the parent table
- Call this report for each and every report that can be found in the parent table

I would really appreciate any information of the feasabilitiy of this plan. I have worked a lot with Crystal before, but I don't have it anymore and will be using RS from now on. I haven't really started working with RS as of yet, but would need to know if this is "doable" with RS!

Thank you very much in advance for your replies!

Greetings

NB

View 5 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

T-SQL (SS2K8) :: Create Table Dynamically?

Sep 5, 2014

I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.

Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.

Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.

During each load we can drop table, No issue and we can handle this through SSIS Package.

View 2 Replies View Related

Create Table And Column Dynamically

Jun 2, 2008

Hi,
There is a table exists in a database, I have to write a stored procedure to create the same table in different database, with the same column name and field. This should be done in runtime. Is it possible. The table will be passed as a parameter to the stored procedure.

View 3 Replies View Related

Create And Then USE A Dynamically-named Database?

Nov 2, 2006

I have a need to create a database, and then populate it. However, thecode below doesn't work as I hoped it might (it creates the table inthe "master" database, which is Not A Good Thing). I know already(thanks Tony!) that if you use Dynamic SQL for the USE command, thenthe subsequent operations need to be Dynamic SQL as well, which is apity since there are over 11,000 lines of it and I don't really fancydebugging it!Does anyone have a cunning plan? In a nutshell, I would like to beable to:1. Create a new database with a derived name - as in the case below aname based on the year, though it might be month as well.2. Create and populate tables in this new database.These operations would ideally be running from a scheduled job.Any thoughts?TIAEdward====================================USE MASTERDECLARE @DBName VARCHAR(123)SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)if not exists(select dbid from master.dbo.sysdatabases where name =@DBName)exec('CREATE DATABASE ' + @DBName)elseraiserror('Database already exists.',3, 1)EXEC ('USE ' + @DBName)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[TestTable]GOCREATE TABLE [dbo].[TestTable] ([TestID] [int] NOT NULL ,[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GO

View 3 Replies View Related

How To Dynamically Create Columns For A Table

Apr 29, 2008

Hi,
how to dynamically create columns for a table

View 2 Replies View Related

I Need To Dynamically Create A Script Component

Oct 8, 2007

Hi,
I need to dynamically create a script component (by code).
Is it possible?

Thanks ahead,
Eran

View 6 Replies View Related

Trying To Dynamically Create Text Files

Oct 27, 2006

I need to get a list of customer ids and then use them as a parameter to select from a transaction table and then create a file for each customer. I have used an execute SQL task to get the list of customers and have put the result set into a variable.

How do go through the recordset to create a file for each customer?

View 1 Replies View Related

Create File Location Dynamically

Apr 10, 2008

hi,

Can you guys please give me the steps of how to create a destination file dynamically. What i mean is for example i want to get everything from a table and ssis should create a file distination according to today's date and save it in a specific folder.

thanks

View 7 Replies View Related

Transact SQL :: Dynamically Create A View

Jun 25, 2015

Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this

Create Table [dbo].[BuildingInformation] (
ID INT IDENTITY NOT NULL,
BuildingName varchar(50),
RAName varchar(50)

[code]....

Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA].  If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change.  If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table

View 6 Replies View Related

Create Report.rdlc In Vb.net Dynamically

Oct 23, 2007

I' m using Microsoft Reporting. Is there a way to create Report .rdlc file Dynamically on the fly? I' m using VB.net.
Any help can be appreciated.
Thanks
Mythili

View 9 Replies View Related

How To Dynamically Create SQL Inside A Stored Procedure?

Feb 28, 2005

I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?



ALTER PROCEDURE dbo.spGetNextPageRecords

(
@pageSize int,
@previousMaxId int

)

AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN

View 4 Replies View Related

Dynamically Generating CREATE INDEX Statements

Feb 15, 2001

Does anyone have any SQL that will look at a DB and dynamically generate CREATE INDEX statements? I
know I can use EM but I want to make this a scripted process and you can only generate CREATE INDEX statement if
you script out the tables too.

Any Ideas??

Thanks

View 2 Replies View Related

Dynamically Create Connection Managers @ Run Time

Jan 28, 2006

Is there a way to dynamically create a connection manager @ run time? I would like to do this from a data set of connection strings so I can link them into a union all component.

View 1 Replies View Related

Is It Possible To Dynamically Create Columns In A Table In SSRS

Nov 19, 2007



Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..

any help is appreciated..
Regards
Karen


View 9 Replies View Related







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