Dynamic reports

Yooo... I'm trying to build a dynamic report with Reporting Services. The problem is that I have a stored procedure that returns a different number of columns with different name for the columns almost each time. So... how can I get dynamic the number and the name of the columns at runtime.

He is an example of the SP:

CREATE PROCEDURE [dbo].[Test]
@.nrCol INT
, @.CarCol CHAR(5)
AS
CREATE TABLE #Part(DenPart CHAR(10))
DECLARE @.i INT
SET @.i = 0
WHILE @.i < @.nrCol
BEGIN
EXEC('ALTER TABLE #Part ADD [' + @.CarCol + @.i + '] NUMERIC(18,2) NOT NULL DEFAULT(0)')
SET @.i = @.i + 1
END
INSERT INTO #Part (DenPart) VALUES('A')
INSERT INTO #Part (DenPart) VALUES('B')
SELECT * FROM #Part

Any ideeas?

Thanks

I wont think we can use the above stored procedure. To develop a report we need to have a result set at design time. Report need to know what are the data fields at design time.sql

dynamic reporting

Hi Experts...
I want to create report on the fly, for given mdx query , I mean without
having a pre build report format, and I am using FoodMart 2000 sample
database.
pls give me some ideas.....
Thanks in Advance...If I understand what you mean correctly (and using RS) . . . . . I guess
you'd need to create the RDL file dynamically and have it executed
(I think) 8^)
- peteZ
"Sumudu Prasad" <sumudu@.logicalasia.com> wrote in message
news:uo%23XGEhmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Hi Experts...
> I want to create report on the fly, for given mdx query , I mean without
> having a pre build report format, and I am using FoodMart 2000 sample
> database.
> pls give me some ideas.....
> Thanks in Advance...
>
>

Dynamic Parameters

I need to build a report in which every field of the dataset acts as a
Parameter to filter data in the report, so that the user is not binded
only to a fixed set of filters.
Based on the datatype of the filter; the condition should be specified
and the user will only supply the value for which he is looking for.
For Example:
In the report i have about 10 fields
EmpSSN No varchar(20)
Emp FirstName varchar(20)
Emp LastName varchar(20)
Emp Location varchar(20)
Emp MartialStatus char(1)
Emp DOB datetime
Emp Salary numeric(18,2)
Emp Designation varchar(20)
Emp Department varchar(20)
Emp Status varchar(20)
When i choose the filter to be as DOB the condition needs to be
generated as >,>=,<,<=,!= and user can define a specific data for the
filter.
Pls advise.Hi,
I am about to start building the exact same report as you outlined
below. did you get any references or whether or not this can be achieved
using reporting services
--
Ciaran
Software developer
"brinda.shree@.gmail.com" wrote:
> I need to build a report in which every field of the dataset acts as a
> Parameter to filter data in the report, so that the user is not binded
> only to a fixed set of filters.
> Based on the datatype of the filter; the condition should be specified
> and the user will only supply the value for which he is looking for.
> For Example:
> In the report i have about 10 fields
> EmpSSN No varchar(20)
> Emp FirstName varchar(20)
> Emp LastName varchar(20)
> Emp Location varchar(20)
> Emp MartialStatus char(1)
> Emp DOB datetime
> Emp Salary numeric(18,2)
> Emp Designation varchar(20)
> Emp Department varchar(20)
> Emp Status varchar(20)
> When i choose the filter to be as DOB the condition needs to be
> generated as >,>=,<,<=,!= and user can define a specific data for the
> filter.
> Pls advise.
>

Dynamic packages and maxconcurrentexecutables

I have recently prototyped a system, in which I use meta data to build a package of execute package tasks.

Essentially, in a Script task I do the following:

1) Create a new package in memory.

2) I add variables, logging to this package.

3) To allow for precedences I create a sequence container for any and all execute pacakge tasks that can run in parallel.

4) To help with parameters specific to the execute package task, I add another sequnce container for the individual excute package task.

5) Finally I add the actual execute package task.

6) Save the package to local disk.

7) Execute this pacakge.

I hadn't been setting explicitly setting the MaxConcurrentExecutables, but upon opening the saved copy of the last package I built and executed it has the default -1 setting.

My problem is that while this in-memory package is running it appears to be only running a single executalbe at a time. I'm going to try setting maxconcurrentexecutable to 4 or some other number to see if I get some parallel execution going on.

The real question is "Is there a limitation on using dynamic packages that limits them to only run a single executable at a time?". I haven't found anything in BOL that leads me to believe there is, but It was very obvious that only one executable would run at a time when I test this out.

How are you testing this? If you run the dynamic generated package directly, do multiple executables run?|||Right now my testing involves watching the staging tables that I have. I expect to see them start populating with data somewhat simultaneously.|||Try opening the generated package, and running it directly through BIDS. There are a number of reasons why a package might not run concurrent executables (system resources, etc), but if it works in BIDS, then we can narrow it down to a code issue.

Dynamic Operator Help

Hi,

I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated.

Thanks

You would just build the statement dynamically and set the SqlDataSource.SelectCommand propertyhttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selectcommand.aspx

But, be advise that using dynamic SQL is frought with danger due to sql injection attacks. You will really need to scrub your input. Seehttp://en.wikipedia.org/wiki/SQL_Injection or just google "sql injection" for many, many articles on this topic

|||

Dbland thanks for the reply. I do understand that the select command will be built dynamically and this is what I have done. Here is the Data Source.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UsersConnectionString %>"
ProviderName="<%$ ConnectionStrings:UsersConnectionString.ProviderName %>" SelectCommand="SELECT Username, Email, Active, BuildingNum, UserRole, UserID FROM UserInfo WHERE (BuildingNum = @.BuildingNum)">
<SelectParameters>
<asp:ControlParameter ControlID="BuildingDropDown" Name="BuildingNum" PropertyName="Text" />
</SelectParameters>

I would like to parameterize the operator to a dropdown control which would have the =, >, < values options. That way people can select any building less than, greater than or, equal to a building number.

So my question is how do I allow this part of the select statement, "=", to be determined and populated from a users form input. (BuildingNum = @.BuildingNum)">

Is this possible?

|||

In your code you will have to take the value of the drop down and build your statement, eg

string sql = .................

SqlDataSource.SelectCommand = sql;

I'm not exactly sure in what event you would do this but I'm sure there is one just before the SelectCommand gets executed

Is this what you were after?

|||

Can you give me or point me to an example of how to do this?

|||

Let's say you have a Submit button. When they click that you build up your sql statement and set the sqldatasource as follows:

SqlDataSource.SelectCommand = sql;

Or you can put it in one of the data datasource events, like the Selecting event which occurs just before the select is done (http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx). I'm not sure which event gives you the flexibility you want, you'll have to play read about them and play around with it.

|||

Thanks for all your help dbland. I took your advise and set the select command upon the button click event. It seems to work out well. Thanks again.

Dynamic MDX Query

Hi,
I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
ex. I have one SQL Server Report parameter which contain following value:
1. Station
2. Free Test
I have three Dimension Station, Free Test, Overall Result and one measure
Total Test.
If I select Station in report parameter then following report format i need:
Station | Pass | Fail | Total Test
x1 60 40 100
x2 ... ... ...
If I select Free Test in parameter then following report format i need:
Free Test | Pass | Fail | Total Test
yes 60 40 100
no ... ... ...
I tryed using following query but not sucessful:
WITH SET [Groupby] AS STRTOSet(@.Query1)
SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[
Overall
Result].AllMembers ON 1
FROM [OLAP Test Cube]
let me know if you have any solution?
Dinesh Patelproblem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember
.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Lev
el.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.Uni
queName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Co
unt]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2]
,
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns
, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (…) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Va
lue &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Co
unt]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2]
,
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:

> Hi,
> I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
> ex. I have one SQL Server Report parameter which contain following value:
> 1. Station
> 2. Free Test
> I have three Dimension Station, Free Test, Overall Result and one measure
> Total Test.
> If I select Station in report parameter then following report format i nee
d:
> Station | Pass | Fail | Total Test
> x1 60 40 100
> x2 ... ... ...
> If I select Free Test in parameter then following report format i need:
> Free Test | Pass | Fail | Total Test
> yes 60 40 100
> no ... ... ...
> I tryed using following query but not sucessful:
> WITH SET [Groupby] AS STRTOSet(@.Query1)
> SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
> NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].
1;Overall
> Result].AllMembers ON 1
> FROM [OLAP Test Cube]
> let me know if you have any solution?
> Dinesh Patel

Dynamic MDX Query

Hi,
I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
ex. I have one SQL Server Report parameter which contain following value:
1. Station
2. Free Test
I have three Dimension Station, Free Test, Overall Result and one measure
Total Test.
If I select Station in report parameter then following report format i need:
Station | Pass | Fail | Total Test
x1 60 40 100
x2 ... ... ...
If I select Free Test in parameter then following report format i need:
Free Test | Pass | Fail | Total Test
yes 60 40 100
no ... ... ...
I tryed using following query but not sucessful:
WITH SET [Groupby] AS STRTOSet(@.Query1)
SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[Overall
Result].AllMembers ON 1
FROM [OLAP Test Cube]
let me know if you have any solution?
Dinesh Patelproblem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (â?¦) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:
> Hi,
> I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
> ex. I have one SQL Server Report parameter which contain following value:
> 1. Station
> 2. Free Test
> I have three Dimension Station, Free Test, Overall Result and one measure
> Total Test.
> If I select Station in report parameter then following report format i need:
> Station | Pass | Fail | Total Test
> x1 60 40 100
> x2 ... ... ...
> If I select Free Test in parameter then following report format i need:
> Free Test | Pass | Fail | Total Test
> yes 60 40 100
> no ... ... ...
> I tryed using following query but not sucessful:
> WITH SET [Groupby] AS STRTOSet(@.Query1)
> SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
> NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[Overall
> Result].AllMembers ON 1
> FROM [OLAP Test Cube]
> let me know if you have any solution?
> Dinesh Patel
>

dynamic killer

Hi, guys!

I want to build a scheduled job to kill any connections from server
'WS1187' built by some VB applications. The code reads something like
this:

declare @.id int
begin
set @.id = (select spid from sysprocesses where hostname='WS1187' and
program_name='Visual Basic')
kill @.id
end
GO

It doesn't compile. The error message is
"Server: Msg 170, Level 15, State 1, Line 5
Line 4: Incorrect syntax near '@.id'."

Is there any way we can kill a user process with a dynamic "spid"
built based on certain business rules?

Thanks in advance.

Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410072058.4d1b0ab0@.posting.google.c om...
> Hi, guys!
> I want to build a scheduled job to kill any connections from server
> 'WS1187' built by some VB applications. The code reads something like
> this:
> declare @.id int
> begin
> set @.id = (select spid from sysprocesses where hostname='WS1187' and
> program_name='Visual Basic')
> kill @.id
> end
> GO
> It doesn't compile. The error message is
> "Server: Msg 170, Level 15, State 1, Line 5
> Line 4: Incorrect syntax near '@.id'."
> Is there any way we can kill a user process with a dynamic "spid"
> built based on certain business rules?
> Thanks in advance.
> Gary

Check the syntax for KILL in BOL, and you'll see that it doesn't allow a
variable for the SPID, so you need to use dynamic SQL:

exec('kill ' + convert(varchar, @.id))

Simon

Dynamic Field DB Schema Brainstorming

Hello -
Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.
This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:
Tables
Forms
FormFieldNames
FormFieldTypes
FormFieldJS
Any tips on the right direction to go?
Thanks
Rob
Try the link below and right click to download the PPT slides for datamodeling. The key to data modeling is files and associations, that means you may have fifty files but you may only have three tables based on files association. The table relationship is determined by upper and lower bound cardinality and it is not complicated. There are six complete database catalogs in the book. Hope this helps.

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html|||

Thanks for the resource, good stuff - organized nicely. I was looking specifically to my example, so I didn't plow through an ERD of what's already out there, thankx.

|||A relational database sounds like a completely inapropiate tool to build this application you're looking to build. When you are using a RDBMS, you need to know what data you are modeling. You can't just try to make it up on the fly.
You need to consider other solutions, technologies, tools. XML may be suited formatted for this -- the administrator would define an XML schema (possibly through your tool) and then build a form to input data to create XML documents that conform to the schema.
No less, any time I see anyone try to do this in a RDBMS, it falls apart in less than a year -- if it ever makes it out of development.|||

Sounds like you're both directing me the same way - a relatively small DB schema that holds the forms, the fields, and descriptive text for the fields, etc. The actual field types, values, javascript, etc... should be in a bunch of files that describe each fieldID, most likely in XML.

Thank you for that. We were thinking about compiling a bunch of .JS files for each field, and then one large.js file for the form aggregate fields. But XML would make more sense for the fields' descriptive data I believe, no?

dynamic declaration of table type -- any solution?

We got to build a table dynamically using the following code,
====================================
declare @.Part int
declare @.examId int
declare @.tSQL varchar(2000)
declare @.colname varchar(100)
select @.Part = 6

select @.tSQL = 'declare @.Report table ('
Declare mycolumns cursor for
SELECT SubjectCode FROM tblSubjectsMaster WHERE (Part = @.Part) order by SubjectName
OPEN mycolumns
FETCH NEXT FROM mycolumns
INTO @.colname
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.tSQL = @.tSQL + @.colname + ' varchar(100),'
FETCH NEXT FROM mycolumns
INTO @.colname
END
CLOSE mycolumns
DEALLOCATE mycolumns
Select @.tSQL = @.tSQL + ' Total int, Result varchar(200), Place int)'
exec(@.tSQL)
==================================
Then in the next line, I'm giving the following line.
select * from @.Report
which is gioving the following error
======================
Must declare the variable '@.Report'.

Assumption is that there is some scope related problem...But the requirement is demanding to think in this lines.

Any body of any guess on any resolution.

Thanks in advance.

Thanks & Regards
Srinivasa ReddyHi,

A table variable just exists until a go or exec command are executed. You have to create a temp table.

/Mats

dynamic datasource

I am trying to build a dynamic datasource for my report. The problem
is there are multiple where criteria that, if left blank, I want to
ignore. I could go down the road of nested "iif"s but that would get
very difficult to maintain. I have a C# function that already does
something like this and I was wondering if there was a way to use the
code feature of the report properties to build the sql statement, then
pass that, or set the report record source, to the string statement
generated by the code?
here is an example:
="SELECT device_sn, implant_date, model_id FROM dbo.mdrv_device WHERE "
& iif(parameters!device_sn.Value.length > 0,"(device_sn = '" &
parameters!device_sn.Value & "') AND ","") &
iif(parameters!start_date.Value.length > 0 and
parameters!end_date.Value.length >
0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" &
parameters!start_date.Value & "' AND '" & parameters!end_date.Value &
"')","")
the problem comes that if only one where criteria is used, I have to
dump the "AND" from the string. The query will actually have more like
five or six paramaters.
Any help is appreciated...Your expression can call code. So you can do something like this:
= code.CreateMyDynamicReallyCoolSQL(Parameters!device_sn,
Parameters!start_date, ...)
My suggestion is to first start off with a report with parameters and a
single textbox (don't have any datasets). Set the source for the textbox to
your expression. Make sure it creates the string you want.
When I am creating code I quite often create a quick VB Winapp. Create and
test the code there. Then copy and paste into RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen" <switter@.enpathmed.com> wrote in message
news:1122560699.229387.302310@.g43g2000cwa.googlegroups.com...
>I am trying to build a dynamic datasource for my report. The problem
> is there are multiple where criteria that, if left blank, I want to
> ignore. I could go down the road of nested "iif"s but that would get
> very difficult to maintain. I have a C# function that already does
> something like this and I was wondering if there was a way to use the
> code feature of the report properties to build the sql statement, then
> pass that, or set the report record source, to the string statement
> generated by the code?
> here is an example:
> ="SELECT device_sn, implant_date, model_id FROM dbo.mdrv_device WHERE "
> & iif(parameters!device_sn.Value.length > 0,"(device_sn = '" &
> parameters!device_sn.Value & "') AND ","") &
> iif(parameters!start_date.Value.length > 0 and
> parameters!end_date.Value.length >
> 0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" &
> parameters!start_date.Value & "' AND '" & parameters!end_date.Value &
> "')","")
> the problem comes that if only one where criteria is used, I have to
> dump the "AND" from the string. The query will actually have more like
> five or six paramaters.
> Any help is appreciated...
>|||thanks for the help Bruce. I have been able to generate the sql
statement with code. However, I notice something weird when trying to
work with integers. here is my datasource:
=Code.searchCriteria(iif(isnothing(Parameters!device_sn.Value),"",Parameters!device_sn.Value),iif(isnothing(Parameters!start_date.Value),"",Parameters!start_date.Value),iif(isnothing(Parameters!end_date.Value),"",Parameters!end_date.Value),iif(isnothing(Parameters!model_id.Value),"",Parameters!model_id.Value),iif(isnothing(Parameters!birth_end_date.Value),"",Parameters!birth_end_date.Value),iif(isnothing(Parameters!birth_start_date.Value),"",Parameters!birth_start_date.Value),iif(isnothing(Parameters!chamber_id.Value),0,Parameters!chamber_id.Value))
here is my code:
function searchCriteria(ByVal s_device_sn as string, _
ByVal s_start_date as string, _
ByVal s_end_date as string, _
ByVal s_model_id as string, _
ByVal s_birth_end_date as string, _
ByVal s_birth_start_date as string, _
ByVal i_chamber_id as integer) as string
dim tempCriteria as string
dim selectCommand as string = "SELECT device_sn, chamber_id,
implant_date, model_id, thr_v, " _
& "thr_ma, thr_msec, thr_ohms, sen_mv, file_path, last_name, " _
& "first_name, birth_date, chamber, manufacturer,
physician_first_name, " _
& "physician_last_name, facility_name, city, state, country,
province FROM dbo.mdrv_device"
tempCriteria += iif(s_device_sn.length > 0,"(device_sn = '" &
s_device_sn & "') AND ","")
tempCriteria += iif(s_start_date.length > 0 and s_end_date.length >
0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" & s_start_date &
"' AND '" & s_end_date & "') AND ","")
tempCriteria += iif(s_model_id.length > 0,"(model_id = '" & s_model_id
& "') AND ","")
tempCriteria += iif(s_birth_start_date.length > 0 and
s_birth_end_date.length > 0,"(CONVERT(varchar(10),birth_date,101)
BETWEEN '" & s_birth_start_date & "' AND '" & s_birth_end_date & "')
AND ","")
tempCriteria += iif(i_chamber_id > 0,"(chamber_id = " & i_chamber_id &
") AND ","")
searchCriteria = selectCommand & iif(tempCriteria.Length > 0," WHERE "
& tempCriteria.Substring(0,tempCriteria.Length - 5),"")
end function
the problem comes when dealing with the parameter "chamber_id", which
is an integer. if I try to pass 0 as a param to the code for the
chamber_id param (if it is left blank) the code bombs. if I pass 8 if
the param is blank, the query works. if I don't use an "iif" for that
param in both the code and the datasource expression, the dynamic where
statement comes back as "where (chamber_id = 0)". the expression is
obviously passing a zero to the code, but I can't seem to trap that
value in the code using:
iif(i_chamber_id = 0,"","(chamber_id = " & i_chamber_id & ") AND ")
without getting an error.
Any ideas?|||I didn't trace through all the detail but I bet what is happening is you are
getting bitten by one of two things. First both the true and the false
statements for iif get executed. So if 0 causes anything invalid for either
the true or false part of the iif then you will be in trouble. Second, I am
not sure what happens with using +=. I know that when you use + it will add
unless it is absolutely sure it is a string. If an & is used then it know
everything consists of strings. So it could be that your += is causing a
problem. Try going to
tempCriteria = tempCriteria & blah
I looked some more at your code and I don't think either of the two ideas
above make sense. I tend to not ever allow null or blank. Are you sure you
are passing blank and not null instead? What is the datatype? IF the
datatype is string it has two options.
I suggest passing in the parameters and have your code check everything. Put
in the if else logic there. I think that is more readable anyway.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen" <switter@.enpathmed.com> wrote in message
news:1122572779.263908.162450@.o13g2000cwo.googlegroups.com...
> thanks for the help Bruce. I have been able to generate the sql
> statement with code. However, I notice something weird when trying to
> work with integers. here is my datasource:
> =Code.searchCriteria(iif(isnothing(Parameters!device_sn.Value),"",Parameters!device_sn.Value),iif(isnothing(Parameters!start_date.Value),"",Parameters!start_date.Value),iif(isnothing(Parameters!end_date.Value),"",Parameters!end_date.Value),iif(isnothing(Parameters!model_id.Value),"",Parameters!model_id.Value),iif(isnothing(Parameters!birth_end_date.Value),"",Parameters!birth_end_date.Value),iif(isnothing(Parameters!birth_start_date.Value),"",Parameters!birth_start_date.Value),iif(isnothing(Parameters!chamber_id.Value),0,Parameters!chamber_id.Value))
> here is my code:
> function searchCriteria(ByVal s_device_sn as string, _
> ByVal s_start_date as string, _
> ByVal s_end_date as string, _
> ByVal s_model_id as string, _
> ByVal s_birth_end_date as string, _
> ByVal s_birth_start_date as string, _
> ByVal i_chamber_id as integer) as string
> dim tempCriteria as string
> dim selectCommand as string = "SELECT device_sn, chamber_id,
> implant_date, model_id, thr_v, " _
> & "thr_ma, thr_msec, thr_ohms, sen_mv, file_path, last_name, " _
> & "first_name, birth_date, chamber, manufacturer,
> physician_first_name, " _
> & "physician_last_name, facility_name, city, state, country,
> province FROM dbo.mdrv_device"
> tempCriteria += iif(s_device_sn.length > 0,"(device_sn = '" &
> s_device_sn & "') AND ","")
> tempCriteria += iif(s_start_date.length > 0 and s_end_date.length >
> 0,"(CONVERT(varchar(10),implant_date,101) BETWEEN '" & s_start_date &
> "' AND '" & s_end_date & "') AND ","")
> tempCriteria += iif(s_model_id.length > 0,"(model_id = '" & s_model_id
> & "') AND ","")
> tempCriteria += iif(s_birth_start_date.length > 0 and
> s_birth_end_date.length > 0,"(CONVERT(varchar(10),birth_date,101)
> BETWEEN '" & s_birth_start_date & "' AND '" & s_birth_end_date & "')
> AND ","")
> tempCriteria += iif(i_chamber_id > 0,"(chamber_id = " & i_chamber_id &
> ") AND ","")
> searchCriteria = selectCommand & iif(tempCriteria.Length > 0," WHERE "
> & tempCriteria.Substring(0,tempCriteria.Length - 5),"")
> end function
>
> the problem comes when dealing with the parameter "chamber_id", which
> is an integer. if I try to pass 0 as a param to the code for the
> chamber_id param (if it is left blank) the code bombs. if I pass 8 if
> the param is blank, the query works. if I don't use an "iif" for that
> param in both the code and the datasource expression, the dynamic where
> statement comes back as "where (chamber_id = 0)". the expression is
> obviously passing a zero to the code, but I can't seem to trap that
> value in the code using:
> iif(i_chamber_id = 0,"","(chamber_id = " & i_chamber_id & ") AND ")
> without getting an error.
> Any ideas?
>|||I got it to work by changing the parameter type from an integer to a
string and processing it that way. Here is my code:
function searchCriteria(ByVal s_device_sn as string, _
ByVal s_start_date as string, _
ByVal s_end_date as string, _
ByVal s_model_id as string, _
ByVal s_birth_start_date as string, _
ByVal s_birth_end_date as string, _
ByVal i_chamber_id as string) as string
dim tempCriteria as string = ""
dim selectCommand as string = "SELECT device_sn, chamber_id,
implant_date, model_id, thr_v, " _
& "thr_ma, thr_msec, thr_ohms, sen_mv, file_path, last_name, " _
& "first_name, birth_date, chamber, manufacturer,
physician_first_name, " _
& "physician_last_name, facility_name, city, state, country,
province FROM dbo.mdrv_device"
'build criteria for where clause
if s_device_sn.length > 0 then
tempCriteria = "(device_sn = '" & s_device_sn & "') AND "
end if
if s_start_date.length > 0 and s_end_date.length > 0 then
tempCriteria = tempCriteria & "(CONVERT(varchar(10),implant_date,101)
BETWEEN '" & s_start_date & "' AND '" & s_end_date & "') AND "
end if
if s_model_id.length > 0 then
tempCriteria = tempCriteria & "(model_id = '" & s_model_id & "') AND
"
end if
if s_birth_start_date.length > 0 and s_birth_end_date.length > 0 then
tempCriteria = tempCriteria & "(CONVERT(varchar(10),birth_date,101)
BETWEEN '" & s_birth_start_date & "' AND '" & s_birth_end_date & "')
AND "
end if
if i_chamber_id.length > 0 then
tempCriteria = tempCriteria & "(chamber_id = " & i_chamber_id & ")
AND "
end if
'finish the sql statement
if tempCriteria.Length > 0
selectCommand = selectCommand & " WHERE " &
tempCriteria.Substring(0,tempCriteria.Length - 5)
end if
searchCriteria = selectCommand
end function
and here is my recordsource:
=Code.searchCriteria(iif(isnothing(Parameters!device_sn.Value),"",Parameters!device_sn.Value),iif(isnothing(Parameters!start_date.Value),"",Parameters!start_date.Value),iif(isnothing(Parameters!end_date.Value),"",Parameters!end_date.Value),iif(isnothing(Parameters!model_id.Value),"",Parameters!model_id.Value),iif(isnothing(Parameters!birth_start_date.Value),"",Parameters!birth_start_date.Value),iif(isnothing(Parameters!birth_end_date.Value),"",Parameters!birth_end_date.Value),iif(isnothing(Parameters!chamber_id.Value),"",Parameters!chamber_id.Value))

Dynamic Data Source in DTS

I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
LixinIn short, you could use global variables to specify the data source. When
you invoke the package using DTSRUN, you could specify values for these
global variables. See SQLDTS.com for examples.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
Lixin|||Vyas,
Thanks a lot.
I went to SQLDTS.com. However, I didn't find the example I
really need.
What I want to kow is how to set up the server name in
source server of DTS as global viriable so that I can pass
in the server name as parameter through DTSRUN? Can you
give more instructions about how to do it in EM?
Lixin
>--Original Message--
>In short, you could use global variables to specify the
data source. When
>you invoke the package using DTSRUN, you could specify
values for these
>global variables. See SQLDTS.com for examples.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>What hardware is your SQL Server running on?
>http://vyaskn.tripod.com/poll.htm
>
>"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
>news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
>I want to build a DTS package to transfer data between two
>SQL 2000 servers. I like to set up the data source as a
>variable. In another word, I want to pass in the data
>source (source SQL server 2000) as a parameter to the DTS
>package so that I can reuse this package somewhere else.
>Is it possible? How?
>Thanks in advance,
>Lixin
>
>.
>|||OK
Let's say I have a connection to SQL Server and I call it MySQLServer. The
Global Variable that holds the name of the server is "MyConnVar". In an
Active Script task I would do.
dim PKG
dim Conn
set PKG = DTSGlobalVariables.Parent
set Conn = PKG.Connections("MySQLServer")
Conn.DataSource = DTSGlobalVariables("MyConnVar").Value
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:082501c38921$6977f0a0$a401280a@.phx.gbl...
> Vyas,
> Thanks a lot.
> I went to SQLDTS.com. However, I didn't find the example I
> really need.
> What I want to kow is how to set up the server name in
> source server of DTS as global viriable so that I can pass
> in the server name as parameter through DTSRUN? Can you
> give more instructions about how to do it in EM?
> Lixin
> >--Original Message--
> >In short, you could use global variables to specify the
> data source. When
> >you invoke the package using DTSRUN, you could specify
> values for these
> >global variables. See SQLDTS.com for examples.
> >
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >What hardware is your SQL Server running on?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
> >news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
> >I want to build a DTS package to transfer data between two
> >SQL 2000 servers. I like to set up the data source as a
> >variable. In another word, I want to pass in the data
> >source (source SQL server 2000) as a parameter to the DTS
> >package so that I can reuse this package somewhere else.
> >Is it possible? How?
> >
> >Thanks in advance,
> >Lixin
> >
> >
> >.
> >