Multiple Calls To ServerReport.SetParameters() With Varying Numbers Of Parameters

Jun 20, 2007

I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.



Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:



protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);

this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;

}

}

protected void btnViewRpt_Click(object sender, EventArgs e)

{

ReportParameter[] rptParams = GetReportParameters();

this.rptViewer.ServerReport.SetParameters(rptParams);

this.rptViewer.ServerReport.Refresh();

}

private ReportParameter[] GetReportParameters()

{

int paramCount = 0;

ReportParameter[] retVal;

string emptyVal = null;

if (txtName.Text != "") paramCount++;

if (txtAddress.Text != "") paramCount++;

if (txtZip.Text != "") paramCount++;

retVal = new ReportParameter[paramCount];

paramCount = 0;

if (txtName.Text != "")

retVal[paramCount++] = new ReportParameter("Name", txtName.Text);

if (txtAddress.Text != "")

retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);

if (txtZip.Text != "")

retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);

return retVal;

}



The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.



The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.



So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.



Any suggestions?

View 7 Replies


ADVERTISEMENT

Can't Use ReportViewer.ServerReport.SetParameters() When Parameter Values Are 'from Query'?

May 21, 2008

I have a report that includes two required parameters where the values are defined to be 'from query' and where the set of values displayed/allowed for the second parameter/drop-down are dependent on the selection from the first parameter/drop-down.

While this works fine in Visual Studio and via the ReportManager web interface, when I attempt to use the Windows Forms ReportViewer control and supply the parameter values via the ReportViewer.ServerReport.SetParameters() call the report does not accept the input values and indicates that no value has been supplied for the first parameter even though it was. Note that I am calling SetParameters() in code and not using the viewer control's parameter prompts (because they are ugly and allow for no validation beyond required/not-required).


I have tried a number of work-arounds but it seems that the only option is to change the implementation of the report so that the 'from query' parameters are no longer 'from query' but instead just simple input fields.

This allows the ReportViewer control and associated code to work, but since the input values I need are actually GUID values this is obviously less than ideal for running the report from Visual Studio or the web interface.


Am I missing something or is this some kind of a limitation/bug in the viewer control?

Thanks in advance -- /chris

View 3 Replies View Related

Webservice Calls With Parameters From Reporting Services

May 21, 2007

I am trying to query a list in sharepoint using its lists.asmx webservice and in turn its GetListItems call. I currently have the call working using the following code in the Querystring for the report



<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>



This seems to work fine except that the list has some empty fields in the first record and for some unbelivable reason they (the empty fields) do not get returned as part of the dataset unless i put a value into them. I then came across a parameter for GetListItems called viewFields which is supposed to . Tehn sample Soap request is shown as follows



<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<listName>string</listName>
<viewName>string</viewName>
<query>
<xsdchema>schema</xsdchema>xml</query>
<viewFields>
<xsdchema>schema</xsdchema>xml</viewFields>
<rowLimit>string</rowLimit>
<queryOptions>
<xsdchema>schema</xsdchema>xml</queryOptions>
</GetListItems>
</soap:Body>
</soap:Envelope>





As i have a parameter for viewfields setup.... What value do i pass to it ??? My XML is in its infancy but i though something along the lines of the following would do as an input.



<FieldRef Name='Title'/><FieldRef Name='Address1'/>



Also, maybe i am going the wrong way about this.Is there an easier way of defining the Parameters as part of the Querystring or do Parameters always have to be setup in the parameters tab (My Parameter values are static and will never change).

View 1 Replies View Related

Multiple Calls To SP

May 16, 2006

Hi group,

I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))

All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.

The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.

When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.

Can someone give me some pointers on where to look for performance wins?

If you like I can add the SP's

Regards,

Sander

View 7 Replies View Related

Combine Multiple Sql Calls Into 1

Jan 15, 2006

I have an old app that I'm trying to recode and improve performance.

From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.

Here is a quick example of the script

Select * from table1 where id = " & tempVariable
If Not RS.EOF Then
strTable1 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec1
RS.UPDATE
RS.Requery
strTable1 = RS("SomeRec")
End If
RS.CLOSE

Select * from table2 where id =2
If Not RS.EOF Then
strTable2 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec2
RS.UPDATE
RS.Requery
strTable2 = RS("SomeRec")
End If
RS.CLOSE

Select * from table3 where id =3
If Not RS.EOF Then
strTable3 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec3
RS.UPDATE
RS.Requery
strTable3 = RS("SomeRec")
End If
RS.CLOSE

INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'


These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
Thanks
-Scott

View 1 Replies View Related

Join Results From Multiple EXEC Calls

Apr 25, 2006

I would like to make inner join with results from different exec %procedureName% calls.

thanks.

View 3 Replies View Related

Dealing With Large Numbers Of Parameters

Sep 11, 2007

Hi there,

I am getting a headache trying to research what to do when you have a large number of parameters to include in a query. For example, if I have a large number of checkboxes for the user to pick criteria for a report and they select several, I'm assuming it would be bad practise to say:

WHERE Field = "a" OR Field = "b" OR Field = "c" OR Field = "d" OR Field = "e" OR.....etc etc etc

Is there a good solution for this, given that the number of parameters may vary dramatically depending on what the user selects to include in a report?!

I'm running SQL Server 2000 with an ASP front end.

Any help would be greatly appreciated!

Thanks in advance!

Matt

View 9 Replies View Related

ServerReport.ReportServerCredentials Issues

Mar 14, 2007

hi all,

I am trying using network credentials in

Myreportviewer.ServerReport.ReportServerCredentials, and let users access the reports on the report server no matter whether the asp app and report server are on the same server. I set up the username, password and domain for the network credentials, and set it to the reportServerCredentials. but when the app running, throught the reportviewer, the report can be access, but no data can be got from datasource(some parameters comes from queries on datasource, and they are unavaible and greyed). if I don't set the reportservercredentials but use <indentidy impersonate = true> and use the windows authentication(disable the anonymouse access) in the iis, it works well, but it works only when aasp app and reportserver are on the same server.

MyReportViewer.ServerReport.ReportServerCredentials =

new MyReportServerCredentials();



Does somebody have any idea?

Thanks a lot,

Jone

View 3 Replies View Related

Vb.net And Reportingservices Setparameters Takes A Long Time

Jul 25, 2006

I have a vb.net application using report services that has a big delay when I set the parameters with which to call the report.

I create a new reporting.reportviewer.

I set the ReportServerCredentials.NetworkCredentials, ReportServerUrl, ProcessingModem, ReportPath and everything is fine.

When I call SetParameters with a very simple parameter set, I get a delay of between 0.5 and 2.5 seconds. That delay is very noticible to the users. Below is an extract of a sql profiler trace to a database showing the start time, end time, event class and data text of the sql. I've marked the area with the delay in red.

I have no idea what is happening at that time, but Is there anything I can do to get rid of that delay?

It seems that it could be the first time the my application has had to interface with reporting services.





21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec ReadChunkPortion @ChunkPointer=0xFDFF126D000000006804000001000400,@IsPermanentSnapshot=1,@DataIndex=4148,@Length=8
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec ReadChunkPortion @ChunkPointer=0xFDFF126D000000006804000001000400,@IsPermanentSnapshot=1,@DataIndex=8,@Length=4100
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec sp_reset_connection
21/07/2006 13:29:48 363 21/07/2006 13:29:48 363 10 exec CreateSession @SessionID='5zz5gh2wgwfs2hf0qb0gh155',@ReportPath=N'/Symphony Reporting/report3',@Timeout=600,@AutoRefreshSeconds=0,@OwnerSid=0x010500000000000515000000F13F1E839A1DDABDC36D00302B060000,@OwnerName=N'MCR-SYSTEMS
hutchinson',@AuthType=1,@DataSourceInfo=0x0001000000FFFFFFFF01000000000000000C020000006F4D6963726F736F66742E5265706F7274696E6753657276696365732E50726F63657373696E67436F72652C2056657273696F6E3D392E302E3234322E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D3839383435646364383038306363393105010000004A4D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E52756E74696D6544617461536F75726365496E666F436F6C6C656374696F6E03000000106D5F636F6C6C656374696F6E42794944146D5F636F6C6C656374696F6E42795265706F7274146D5F636F6C6C656374696F6E427950726F6D70740303041C53797374656D2E436F6C6C656374696F6E732E486173687461626C651C53797374656D2E436F6C6C656374696F6E732E486173687461626C653D4D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E436F6C6C656374696F6E427950726F6D7074020000000200000009030000000A0A04030000001C53797374656D2E436F6C6C656374696F6E732E486173687461626C65070000000A4C6F6164466163746F720756657273696F6E08436F6D70617265721048617368436F646550726F7669646572084861736853697A65044B6579730656616C756573000003030005050B081C53797374656D2E436F6C6C656374696F6E732E49436F6D70617265722453797374656D2E436F6C6C656374696F6E732E4948617368436F646550726F766964657208EC51383F010000000A0A0B000000090400000009050000001004000000010000000906000000100500000001000000090700000004060000000B53797374656D2E477569640B000000025F61025F62025F63025F64025F65025F66025F67025F68025F69025F6A025F6B0000000000000000000000080707020202020202020232D4E1DE4550ED4AB904FB9304E177480507000000394D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F13000000046D5F6964066D5F6E616D650E6D5F6F726967696E616C4E616D650B6D5F657874656E73696F6E1B6D5F636F6E6E656374696F6E537472696E67456E63727970746564236D5F6F726967696E616C436F6E6E656374696F6E537472696E67456E63727970746564266D5F6F726967696E616C436F6E6E656374537472696E6745787072657373696F6E4261736564156D5F64617461536F757263655265666572656E6365086D5F6C696E6B49441D6D5F44617461536F757263655769746843726564656E7469616C734964096D5F73656344657363166D5F63726564656E7469616C7352657472696576616C086D5F70726F6D7074136D5F757365724E616D65456E63727970746564136D5F70617373776F7264456E63727970746564076D5F666C616773096D5F6D6F64656C4944136D5F6973456D626564646564496E4D6F64656C156D5F69734D6F64656C536563757269747955736564030101010707000103030704010707040300000B53797374656D2E477569640202010B53797374656D2E477569640B53797374656D2E4775696402544D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B43726564656E7469616C7352657472696576616C4F7074696F6E020000000202494D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B44617461536F75726365466C616773020000000B53797374656D2E4775696401010200000001F8FFFFFF0600000032D4E1DE4550ED4AB904FB9304E1774806090000000441525453060A0000000441525453060B0000000353514C090C0000000A00060D000000182F53796D70686F6E79205265706F7274696E672F4152545301F2FFFFFF06000000A814BB258EC9884888698BC39F85117601F1FFFFFF060000007C8BA03B04528840BACCDE1CC6E465BD091000000005EFFFFFFF544D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B43726564656E7469616C7352657472696576616C4F7074696F6E010000000776616C75655F5F00080200000003000000061200000039456E74657220612075736572206E616D6520616E642070617373776F726420746F2061636365737320746865206461746120736F757263653A0A0A05EDFFFFFF494D6963726F736F66742E5265706F7274696E6753657276696365732E44617461457874656E73696F6E732E44617461536F75726365496E666F2B44617461536F75726365466C616773010000000776616C75655F5F0008020000000300000001ECFFFFFF060000000000000000000000000000000000000000000F0C00000098000000024E923E067F7AAB8735076784BE58B5E7FB39D61C8C5A39887A49C8F639783D2A0E4883F7901E6FE948DD535C1E2A84707D3071F31B5FDD7FCFC51278114BC810B48C8EC63D000F395FA8C28BFD18401221C78DDF7DB335425960CBB69E5823B62A418D46AE7120F1CCF3FCD3E3335E78DB72188BCF64457DB622592A9615775FC5C03758D12948BE507CCD16B2201C66A092A41F12B8D1D00F10000000190200000206050054000000020100048034000000440000000000000014000000020020000100000000041800FF00060001020000000000052000000020020000010200000000000520000000200200000102000000000005200000002002000054000000030100048034000000440000000000000014000000020020000100000000041800FFFF3F00010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000401000480340000004400000000000000140000000200200001000000000418001D000000010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000501000480340000004400000000000000140000000200200001000000000418001F000600010200000000000520000000200200000102000000000005200000002002000001020000000000052000000020020000540000000601000480340000004400000000000000140000000200200001000000000418001F00060001020000000000052000000020020000010200000000000520000000200200000102000000000005200000002002000054000000070100048034000000440000000000000014000000020020000100000000041800FF0106000102000000000005200000002002000001020000000000052000000020020000010200000000000520000000200200000B,@EffectiveParams=N'<Parameters>
<Parameter>
<Name>DataSource</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>AuthLvl</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>Home</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>False</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt />
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>SiteFilter</Name>
<Type>Integer</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>True</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>Select Required Site(s)</Prompt>
<PromptUser>True</PromptUser>
</Parameter>
<Parameter>
<Name>DateFilter</Name>
<Type>DateTime</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<MultiValue>True</MultiValue>
<UsedInQuery>False</UsedInQuery>
<State>MissingValidValue</State>
<Prompt>Select Required Date(s)</Prompt>
<PromptUser>True</PromptUser>
</Parameter>
</Parameters>'
21/07/2006 13:29:48 377 21/07/2006 13:29:48 377 10 exec sp_reset_connection
21/07/2006 13:29:48 377 21/07/2006 13:29:48 377 10 exec ObjectExists @Path=N'/Symphony Reporting/report3',@AuthType=1
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec sp_reset_connection
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec GetSessionData @SessionID='5zz5gh2wgwfs2hf0qb0gh155',@OwnerSid=0x010500000000000515000000F13F1E839A1DDABDC36D00302B060000,@OwnerName=N'MCR-SYSTEMS
hutchinson',@AuthType=1,@SnapshotTimeoutMinutes=1440
21/07/2006 13:29:48 847 21/07/2006 13:29:48 847 10 exec sp_reset_connection

View 2 Replies View Related

Need To Identify Student With Multiple Id Numbers

Jan 17, 2008

My working table:


CREATE TABLE [dbo].[Name_ID4](

[id_num] [decimal](18, 0) IDENTITY(1,1) NOT NULL,

[student_last_name] [varchar](30) NULL,

[student_first_name] [varchar](30) NULL,

[student_middle_init] [varchar](1) NULL

[local_student_id] [bigint] NULL,


I need to identify only the students with more than one local_student_id and the associated local_student_id's.

I can identify the students with two id's but not the associated id's


SELECT COUNT(*) AS Dup_Num,

rtrim(student_last_name),

rtrim(student_first_name),

rtrim(student_middle_init),

from Name_ID4

group by

student_last_name,

student_first_name,

student_middle_init,

having (count(*) > 1)

order by student_last_name, student_first_name, student_middle_init

Thanks

View 4 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



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

Want 2 Store Multiple Room Numbers Of A Hotel In A Single Column Using SQL Server2k.

Jan 15, 2004

Hi!
Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k.

Can anybody tell me how to do it??

Thanx.

View 2 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Sql WHERE Clause With Multiple Parameters

Jun 28, 2006

hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE  Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!

View 2 Replies View Related

Querying With 1, Or 2, Or Multiple Parameters

Mar 4, 2007

Hi all. I have the problem on query.
This is my query.

select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
where P.religion in ('Born Again','Baptist', 'Catholic')

How could I make a query that returns a result for either "Born Again, Baptist, or Catholic". The parameter would depend on the input of the user depending on how many religion the user inputed.
If the user inputed Born Again and Baptist, the result is the employees that have a religion of Born Again and Baptist.
Thanks
-Ron-

View 6 Replies View Related

Multiple Choices With Parameters

Aug 15, 2007

I need to pass a parameter to a stored procedure that allows the user to select all store numbers or a combination thereof.
The portion that selects all works fine. I am have trouble with the later. My code looks like this:

CREATE PROCEDURE vch_GetSurgeryPatientsWithoutOrdersFromEyeSite_clb
(
@StartDate datetime,
@EndDate datetime,
@center varchar (1000)
)
AS

IF @StartDate IS NULL
BEGIN
Set @StartDate = GetDate()
Set @EndDate = (GetDate ()+90)
END
Declare @StoreList TABLE ( CenterID int )

-- Get our Centers
IF ( @Center = 'ALL' ) or ( @Center IS NULL )
BEGIN
INSERT @StoreList
SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock)
END
ELSE
BEGIN

INSERT @StoreList
SELECT DISTINCT POS_Site_ID
FROM LVIGP.dbo.POS40108 (nolock) WHERE POS_Site_ID = @Center
END

the first if statement returns all of the store number like this
CenterID
001
002
010
024
057

the second if returns nothing
I tried using coalesce but it returns the store number like this:
CenterID
001, 002, 010, 024, 057
This does not work with the rest of my program.

View 1 Replies View Related

Multiple/optional Parameters

Sep 11, 2007



Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

View 6 Replies View Related

Varying Parameter Size..?

Nov 6, 2006

Hello,

I want to write a function that takes varying number of parameters.I mean:

Fonk(4,5,7)

Fonk(2)

Fonk(6,6,6,8)

There is a way to do this in T-SQL?

Thanks in advance...

View 5 Replies View Related

Bestpractice For Varying Outputformat

Nov 28, 2006

Hello

I need an advice for an easy maintainable and highly flexible solution using SSIS. We're supplying our customers with an exportservice. The data is extracted from different tables, all with the same tableschema, fairly easy to create a foreach-container and iterate through the tablenames and extract data and use a flatfile destination to write it to...

But the not so easy part is to have different exportformats and still be using only one package. I could maybe use different ConnectionManagers for the FlatFileDestination, but that's not a very easy maintainable solution in my world. Our Customers will soon demand a webinterface where they can select necessary columns and apply different formatting for example they would demand the datetime to be '2006-12-24' or maybe '12/24/2006' etc. for alot of the available columns.

Any good suggestion on how to accomplish such a task?

Kind regards

View 1 Replies View Related

Multiple Parameters To A Stored Procedure

Sep 6, 2006

Hi All,

I have a database with very heavy volume of data.

I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.

Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.

Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?

Thanks in advance,

HHA

View 4 Replies View Related

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

How To Pass Multiple Parameters In SSRS

Apr 25, 2008


Hi,

I am working on SSRS. I need to open a new report from one report when user clicks on some particular summerized count link.

Its a sort of drilled down report. I am not getting how to pass the respected Ids (more than one) to the next report when user clicks on the link in the 1st report. These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from the database.

Please help.

Regards,
Sachin

View 4 Replies View Related

Multiple Checkbox In A Report Parameters.

Oct 30, 2007

Hello,

I have a report in which one parameter has with multiple selections (List of CheckBoxes). SSRS automatically adds a "Select All" value as the first option. I'm showing a User Type list, but for example, usually the user running the report will be selecting two or three sets of "User Types" (*), then I named those selections and put them in the list also. The list looks like:
"Select All"
IT Users (*)
HR Users (*)
Programmers
DB Analyst... etc...

What I'm trying to do is that when the user selects "IT Users" (per example), then the options "Programmers" and "DB Analyst" will be checked automatically, because they belong to "IT Users" group.


Any help will be appreciated... Thanks in advance

View 2 Replies View Related

Multiple Parameters - How To Configure The Logic?

Apr 15, 2008



Hi,

Im trying to set up a report in visual studio 2005 which uses multiple parameters (6) on which the user can filter to get the information they want.

here is what i want to happen - I've tried to explain as best i can but i dont think i've done a very good job...please ask questions if things need clarifying:

the 6 parameters are - userid, printers, default printer, area, applications, supervisor.

these need to be able to be filtered on any/all/combination of those parameters. parameters are set up and data is accessible, however when i run the report and try and filter on these it only works if all parameters are set to "All" (this is made available through a UNION select statement), or if i individually select a value for each category. if i choose a combination of say userid =myname, printers = myprinter, default=all, area=all etc it will match if ANY of the criteria is matched (including the ALL criteria, therefore it will always display all the values). i need to it work so if i pick the userid and the printer it will only match records containing BOTH the values i select, not either, or if i choose just the area, it will only return those records that contain that department.


Below is the WHERE statement i am currently entering in


WHERE (AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (((AllUserData.nvarchar3 LIKE @Area) OR (@Area = 'All')) OR ((AllUserData.nvarchar4 LIKE @Default) OR (@Default = 'All')) OR ((AllUserData.ntext1 LIKE '%' + @Printer + '%') OR (@Printer = 'All')) OR ((AllUserData.ntext2 LIKE '%' + @Application + '%') OR (@Application = 'All')) OR ((@Application = 'All') AND (@Printer = 'All') AND (@Default = 'All') AND (@Area = 'All')))

and here is what Visual Studio automatically translates that into as soon as i execute the script:


WHERE (AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.nvarchar3 LIKE @Area) OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Area = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.nvarchar4 LIKE @Default) OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Default = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.ntext1 LIKE '%' + @Printer + '%') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Printer = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (AllUserData.ntext2 LIKE '%' + @Application + '%') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Application = 'All') OR
(AllUserData.tp_ListId = '36948548-bfa8-4b25-aff8-b3d1f401dca1') AND (@Area = 'All') AND (@Default = 'All') AND (@Printer = 'All') AND
(@Application = 'All')


Note: AllUserData.tp_ListId references a specific row in the database which must be queried to get the correct information. so it must match on that before anything else.

any help structuring the logic i am using or better understanding the way visual studio/sql handles this kind of thing would be fantastic! thanks!

Shannon

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

Errors Using Multiple Parameters In A SQL Statement

Feb 23, 2007

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

View 15 Replies View Related

Can DTS Import Varying File Names?

Oct 5, 2000

I receive several TXT files daily that need to update information in SQL Server databases. The process requires that all TXT files be appended to a master file and also update individual files' information based on the TXT file name. For example:

File TABLE1_x_ddmmyy.TXT (ddmmyy = date, x = "O" or "B") is to be appended to the master file and also update SQL table "TABLE1" by setting a flag for those records in the table that match a unique key that is provided in the TABLE1.TXT file.

In VFP, I had the following process in place:
a) open the TXT file.
b) read its file name and open the corresponding VFP file
c) update the VFP file based on the key provided in TXT
d) append the key to the master file.
e) repeat c-d for next record in TXT
f) repeat c-e for next TXT file

Using the same process with ADO takes a considerable time since I am processing one line at a time.

Is there any way to do this using a DTS package of some sort? How can I read the TXT file names in SQL Server?

Thank you.

View 3 Replies View Related

Storage Of Varying Data Types In SQL

Jun 2, 2001

re: [Windows 2000 SP1, SQL Server 7.0 SP2]

I am developing an online web-based address book for multiple users. There are STANDARD FIELDS and CUSTOM FIELDS.

Standard fields include: Name,Street,City,State,Zip.
Custom fields are those defined by a specific user. For example:

User-A Custom fields:
Interest Rate <real>
Loan Amount <currency>
Start date <date>

User-B Custom fields:
Blood type <char 3>
Date of birth <date>
Referred by <varchar 50>

Different users can have different custom fields in their address book. As you can see, while the standard fields for each user can be

stored in a single table. However, I have several methods by which I can store the CUSTOM fields.

------------------------------------------------
Method 1: Create 2 separate tables called CustomField and CustomValue:

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
UserID <int>

CustomValue has fields:
ValueID <int>
Value <varchar 50>
FieldID <int>

------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
FieldType <smallint> (determines which TABLE, below, contains the data)
UserID <int>

CustomCharValue
CharValueID <int>
IntValue <Varchar 50>
FieldID <int>

CustomIntValue
IntValueID <int>
IntValue <int>
FieldID <int>

etc....etc...


The structures of those tables would be similar to Method 1, but the data would be segregated based on their data type.

--------------------------------------------------

I'm thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I'm going

to assume that I'll have at least 1-5 million records to work with over the course of my first year and I will need the ability to sort

records based on values in the custom fields as well.

My first question is: Which method should I be considering and is there an alternative or hybrid that I should be considering?

My second question is: What statements should I use in my stored procedure that will enable me to retrieve a list of USERID, CustomFieldIDs and their values as one resulting table that I can query at will and with solid performance?

Gregory
email: sqlGuy@clubtel.com

View 1 Replies View Related

Dynamic SQL On Cross-Tab W/ Varying Col Count

Feb 25, 2008

Hi, I have this idea of using Dynamic SQL on a cross-tab and the following questions popped-up:

1) Is there a limit to the length of the statement the EXEC / sp_executesql command can execute?
2) Should I use cursors (Haven't used this one, I preferred work tables)?

Here are the details:

TABLE_SAMPLE
RptDate DATETIME,
SomeField CHAR(1),
ID CHAR(8),
RetVal VARCHAR(15)

SP Input:
A date range that defines what the RptDates are:
@DateFrom DATETIME,
@DateTo DATETIME

SP Output could be:

| SomeField | RptDate1 | RptDate2 | RptDate3 | RptDate4 | RptDate5 | RptDate..n
--------------------------------------------------------------------------------------------------
ID1 | A | RetVal1 | RetVal2 | RetVal3 | [BLANK] | RetVal5 | RetVal..n
ID2 | A | [BLANK] | ... | ... | ... | ... | ...
ID3 | C | [BLANK] | ... | [BLANK] | ... | ... | [BLANK]
ID..n | B | ... | ... | ... | ... | ... | RetVal..n

[BLANK] - means that there was no record for that RptDate for the ID

Conditions:
- The number of records per ID varies and depends on the number of RptDates covered by @DateFrom and @DateTo
ID1 can have records for 02/26/2008, 02/25/2008, 02/22/2008
ID2 can have records for one, two or all of the same dates.
etc...

Thanks.

View 5 Replies View Related

Import Excel With Varying Worksheet Name Using DTS

Jun 6, 2007

Hi Everyone,



I'm trying to create a DTS package that will let me import an Excel file. The user will be able to name the file the same name every time. But can the DTS package read a different worksheet name each time? Right now, if I use the Excel connection object in DTS designer, it wants to hard code the worksheet name.



Thanks,

Eric

View 1 Replies View Related

Varying Ddl Permissions For A User By Schema

May 18, 2007

Is it possible to set up the permissions to not allow a specific user to create schemas, but to allow that user to create tables and procedures and functions in one schema, and to create procedures and functions but not tables, in a different schema within the same database?



View 3 Replies View Related







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