Conditionally Parameterizing A SQLDataSource

Oct 31, 2006

I'm pararmeterizing a SQLDataSource, but I've encountered a problem.  The users want all records returned if no ID is provided.  So when they first hit the page, they will see all records, then they can filter down to one ID if needed.  However, if I add a selectparameter of ID, I have to default it to a value, which won't give the users all records.
 Suggestions?

View 1 Replies


ADVERTISEMENT

Parameterizing SQL Connection

Jun 9, 2006

I'd like to know what's the best practice in parameterizing SQL connection in SSIS so that we can move from Dev to QA and to Production easily.

Thanks,

Tommy

View 3 Replies View Related

Parameterizing Allow Nulls Columns

Jul 21, 2006

Here's a question I though would be common but can't find an answer to! 
My select statement, which pulls from SQL Server tables, has a column which allows nulls.  When I try to add a parameter to this column, it no longer returns rows that have null in that column when I test it with the default value of %.  I want it to return all the records.
SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @Engineer)
Parameter is;ConvertEmptyStringToNull = TrueDefaultValue = %Direction = InputName = EngineerQueryStringField = EngineerSize = 0Type = Empty
When I run this I get no records!  Isn't % suppose to return anything including nulls?

View 6 Replies View Related

Parameterizing Multi Valued Parameter In Data Set

Aug 16, 2007

Hi,
I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC.
To consider a sample code:

EXEC('select * from country where country_key in ('+@country+')')

where @country is a multi-valued report parameter.

Regards,
Emil

View 7 Replies View Related

SQL UPDATE Conditionally

Jul 20, 2005

This is probably a stupid question, but here goes: I am trying toupdate a table differently based on what is in one of the columnswithin the table. For example, I want to set Fld1 to be "On" if Fld2is "A", and I want to set Fld1 to be "Off" if Fld2 is "B". This iseasy to do in two SQL statements, but I was wondering if there's someway to do it in a single SQL statement. Thus far, I have had no luckin figuring out how to do this. Any ideas would be appreciated!!

View 1 Replies View Related

Aggregating Conditionally

Mar 7, 2008

I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!

Sample dataset (select * from cs_view):

Gender | Program | Student | Class_Section | Heads | Credits
------ | ------- | ------- | ------------- | ----- | -------
Female | English | Elena | Phys 101-b | 1 | 4
Female | English | Elena | Hist 101-c | 1 | 4
Female | English | Elena | Engl 101-a | 1 | 4
Female | English | Elena | Engl 105-b | 1 | 4
Male | History | Rich | Phys 105-a | 1 | 4
Male | History | Rich | Engl 101-c | 1 | 4
Male | History | Rich | Hist 101-b | 1 | 4
Male | History | Jacob | Phys 101-a | 1 | 4
Male | History | Jacob | Hist 101-b | 1 | 4
Male | History | Jacob | Engl 101-c | 1 | 4
Male | History | Jacob | Phys L-101-a | 1 | 0

Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.

Desired table:
Gender Program Heads Credits
------ ------- ----- -------
Female English 1 16
Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.


Thanks for taking the time...
tom

View 1 Replies View Related

Get Field Conditionally

Feb 27, 2007

Hi folks

I think this may be an increadibly simple one, but it's been driving me up the wall. I have a dataset that contains some summary information about a group of companies which i'd like to represent in a report. The dataset looks like this:

Company Lead_Type Count

Company1 Internal 2
Company2 Internal 3
Company3 Internal 5
Company2 External 10
Company1 External 7
Company3 External 3
etc...

I want to show this in a report as follows:

Internal External
Company1 2 7
Company2 3 10
Company3 5 3

I can't figure out how to select a specific field from the dataset (e.g. Company = Company1 and Lead_Type = Internal). This sort of thing must be done all the time, I'm sure I'm missing something fairly obvious. Any posts back gratefully received.

Thanks

Guy.

View 1 Replies View Related

Conditionally Execute A Task

Sep 7, 2007


Hallo

Is there a way to conditionally execute a task?

I got a task €śMail Send€ś and I would like to execute it, just if the variable X (my message source) has a value.

I try in expressions €śDisable€? = len(@[Benutzer::Msg] ) > 5 ? false : true, but it does not work.

View 3 Replies View Related

Exit The Package Conditionally

Jan 4, 2007

I'm inside a "dataflow" and

i have a conditional spliter

 

one of the condition say "ok"

and the the other says "not ok"

 

when  my flow goes to the "not ok" condition

I want to Abort  the entire package execution

I want to make it sure that the package is terminated

and could not  triggers the next control in the control flow.

 

i want to kill/ abort the execution of all my SSIS processes

for the entire package within the current dataflow

 

 

View 3 Replies View Related

Conditionally Display Parameters

Oct 30, 2007

Is it possible with SSRS to conditionally display parameters.

Example, we have dimension concepts that we would like to use in reporting on an Either/Or basis

1. Choose Country

then choose either

2. Geography - Country>Region>Market>Submarket
3. Division - Country>Division>Submarket


The users would like a choice of reporting by Geography or Division

So, choosing Geography will then lead to cascading parameters of Region>Market

Choosing Division would lead to a Division paramter only

Is it possible to do this with SSRS?

View 3 Replies View Related

Conditionally Hidden Groups

Oct 9, 2006

Hi,

I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.

View 2 Replies View Related

Conditionally Grouping Different String Values Together

Jan 29, 2008

 I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3.  890/EG4.  012/AS5.  Trainee6.  Contractor 7.  Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then  every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups. 

View 4 Replies View Related

Stored Procedure Conditionally Using @@Identity

Feb 21, 2006

Im having a play using @@identity

I want to insert some data into a table

Get the AutoID value on the inserted data

then insert some other data into another table
and set its id value to the captured @@identity

My question
I only want to do the second insert only if
@FieldValue2 contains a value ie not empty

below is a basic procedure without conditonal part



Code:

CREATE PROCEDURE conditional_Insert

@FieldID As int,
@FieldValue As Nvarchar(50),
@FieldValue2 As Nvarchar(50)
AS

declare @id int

set nocount on

INSERT INTO Table1(fieldID,fieldvalue)
VALUES( @FieldID, @FieldValue)

set @id = @@identity

INSERT INTO Table2(fieldID2, FieldValue2)
VALUES(@id, @FieldValue2)

select @id
GO

View 2 Replies View Related

Possible To UPDATE Multiple Fields Conditionally?

Nov 14, 2013

I have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)

Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.

I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?

Code:
CREATE TABLE #myTable (
YrStart INT,
MthStart INT,
YrEnd INT,
MthEnd INT);

[code]....

View 4 Replies View Related

Can A Trigger On Table Fire Off Conditionally

Oct 29, 2014

Is there a way to make a sql table trigger fire off conditionally on calling app?We have this vendor app database which also have in-house custom built app operates on them. Some one are thinking using triggers to audit second part of the app activities, but want to skip the vendor app part of operation for at least performance reason.To my knowledge, the answer is no. An insert trigger will fire off whenever there is an insert. It doesn't provide a mechanism to check which app/process first, then conditionally fire or not fire.

View 6 Replies View Related

Conditionally Showing/hiding A Parameter

Jul 16, 2007

I have a report that in most cases takes 4 parameters. In a few special cases, though, it takes 5 parameters. I have my code modified enough such that it can handle this exception, however I cannot figure out how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

View 3 Replies View Related

Conditionally Extraction Of Data From A View

Mar 19, 2007

Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file

I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Any suggestions will ge greately appreciated
IgorB

View 6 Replies View Related

How To Conditionally Count Distinct Values?

Mar 17, 2008

Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.

departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...

I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.

My report would produce the following output considering the above dataset:






Merchandise Number of customers who used a coupon
--------------------------------------------------------------------------------------------------------------
Department 1 2

Class 1 2

Barcode 123 2
Barcode 789 1
Department 2 0

Class 7 0

Barcode 456 0


I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.





View 3 Replies View Related

Conditionally Hide 2 Out Of 6 Rows In A Matrix?

Jul 26, 2007



I just spent about 30mins searching through the forums for this and saw several posts, but I didn't find a straight answer that seems like it would work for my report. How can I add only 2 data rows to a group (to hide them via the group's visible properties) and keep the rest of the rows outside of the group, but still in the same column (vertical area), as shown:

This is the view of all rows:








Cat1
Cat2

Product A
Data 1
#
#


Data 2
#
#


Data 3
#
#


Data 4
#
#


Data 5
#
#


Data 6
#
#

Product B
Data 1
#
#


Data 2
#
#


Data 3
#
#


Data 4
#
#


Data 5
#
#


Data 6
#
#

And this is the view I'm seeking in some cases:








Cat1
Cat2

Product A
Data 1
#
#


Data 4
#
#


Data 5
#
#


Data 6
#
#

Product B
Data 1
#
#


Data 4
#
#


Data 5
#
#


Data 6
#
#

View 4 Replies View Related

Conditionally Formating Subtotal Output

May 22, 2007

I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.

View 1 Replies View Related

How To Limit Conditionally Records Exported

Aug 24, 2006

I apologize if I'm posting this question in the wrong forum.

I have a query that exports data from an Oracle database to an Excel spreadsheet. The application that executes the query is Computer Associates Eureka Report writer. I do not have direct access to the database or any of its objects. With the data in Excel, I run a VBA macro-driven inventory report. The Excel spreadsheet row limitation is not a problem presently, however, the query exports a lot of extraneous data that I don't need. I actually export about a dozen columns of data however I'm only listing 4 in the example below.

Example:

CaseNum ActionDate ActionType ComplCd
1029901 09/08/2006 F 0
1029901 09/11/2006 C 0
1029901 08/18/2006 C 1
1029901 08/17/2006 F 1
1029901 08/01/2006 F 1

When the ComplCd = 1 I only want the query to export one row of data for each CaseNum. Is
there a way to code my query to accomplish this?

ugabulldog

View 1 Replies View Related

Conditionally Extend Query With Clauses

Jan 14, 2008

Howdy folks, first time poster.

In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?


-- The basic query
SELECT column
FROM table
WHERE clause

-- Additional clause, only appended to query
-- if expression evaluates to true
IF @parameter <> default_value
BEGIN
AND additional_clause
END


Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?


-- The basic query

SELECT column
FROM table
WHERE clause

-- Additional clause, should only effect result
-- set when the parameter is not default_value
AND table.column =
CASE @parameter
WHEN default_value THEN
-- identity, table.column=table.column,
-- should have no effect other than just a long query
table.column
ELSE
@parameter
END

View 8 Replies View Related

Conditionally Count Rows In A Table

Aug 26, 2007

I am building a Table Report where I need to "count" the number of cells in a column conditionally.


I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1
ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1
ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

View 4 Replies View Related

Join: Conditionally Include Data From Sub Rows?

Nov 2, 2004

Having one invoice table (ord) and one 'person' table (actor) I would like to include the name of the person who is responsible for an invoice: SELECT Ord.OrdNo, Ord.Selbuy, AC.Nm AS 'Responsible', AC.EmpNoFROM OrdLEFT OUTER JOIN Actor AS AC ON Ord.Selbuy = AC.EmpNoWHERE ord.ordno = 23505 This works perfectly fine if Ord.Selbuy has a corresponding value in Actor:|26914 |21|Yvonne| 21| or if there is no corresponding value in Actor:|26914 |21|NULL| NULL| But what if Ord.Selbuy=0? Then I end up with 3285 rows from Actor! This happens because Actor.EmpNo=0 is allowed. Persons which have never been employed or used to be employed gets Actor.EmpNo=0.Can I create a SELECT statement which only returns data from the INVOICE row if Ord.Selbuy=0?|26914 |21|NULL| NULL| p.s. I'm not able to change table structure/behavior of update procedures, because the tables/code belong to a "bought from the shelf"-business system.

View 2 Replies View Related

Having Rows With Some Null Values Returned Conditionally

Aug 8, 2012

This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:

We are in the process of converting Access database to MSSQL with web form front ends.

I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.

I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.

Here is a sample of the data:

Code:
SQL> select * from test;

COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too

7 rows selected.

Now, if I have this SQL run, it will return only rows that have no nulls in any columns:

Code:
select
col1,
col2,
col3,
col4from test
where
col1 like'th%'
and col2 like '%'
and col3 like '%'
and col4 like '%';

COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is test too

Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:

Code:
select
col1,
col2,
col3,
col4from test
where
(col1 like'th%' OR col1 is null)
and (col2 like '%' OR col2 is null)
and (col3 like '%' OR col3 is null)
and (col4 like '%' OR col4 is null);
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too

The idea is to only select the first 4 rows in the above example.

I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.

Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?

View 9 Replies View Related

View That Conditionally Counts Rows With Reset

May 22, 2015

I have a table sorted by vendor, then item, then Status date with a QC Pass date and Fail date.

I need a SQL 2008 view that counts how many consecutive times a Vendor/Item has passed QC.

When it fails, the count resets to zero, then begins incrementing again.

I need to know how to generate the last column (Count).

I have tried using a ROW_Number() OVER(Partition BY, Order By...) command in the view, but I cannot seem to make it work right.

VendorItemStatusDatePassDate Faildate Count
10056322010-05-092010-05-091
10056322012-12-152012-12-152
10056322013-05-252013-05-253
10056322014-11-172014-11-174

[Code] ....

View 5 Replies View Related

How Do You Conditionally Fire A Trigger (mimic Replication)

Oct 19, 2006

Does anyone know how to do the following. I'm trying to mimicreplication with triggers.I have 2 databases, each have these 2 tables.1.USERSID intNAME varchar(20)2.CHANGESTABLE varchar(20)TYPE varchar(10)col1 varchar(20)col2 varchar(20)On the USERS table I have a for insert trigger. Whenever a new user isadded the trigger puts an entry into the CHANGES table such as("USERS", "INSERT", "1", "Fred")I now have an application (vb.net) that monitors the CHANGES table onserver1. If it finds an entry it determines the table using the TABLEcolumn and performs the necessary insert and deletes the entry fromCHANGES. Now the problem is server2 also has an for insert trigger onthe USERS table so it puts an entry into CHANGES on server2. As youcan imagine this goes around in a loop.What I was hoping for was someway of saying, "I'm inserting from myapplication so don't do the trigger".Any ideas gratefully appreciated.Steve.

View 9 Replies View Related

Conditionally Format Subtotal For Matrix Report

Apr 3, 2006

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

View 7 Replies View Related

Conditionally Adding A Column To My Custom Component

Feb 9, 2006

Hi,

I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'.

Depending on its setting, I want to include (or not include) a column in the output.

Any advice on how to go about doing this (with some sample code) would be much appreciated!

Here's how I'm declaring the property in ProvideComponentProperties()
IDTSCustomProperty90 IncludeErrorDesc =  ComponentMetaData.CustomPropertyCollection.New();
IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
IncludeErrorDesc.Name = "Some Name";
IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
IncludeErrorDesc.Value = Convert.ToBoolean(false);

Thanks in advance

-Jamie

 

View 4 Replies View Related

Disabling Scheduled Report Delivery Conditionally?

Sep 16, 2007

Hi,

I am currently planning on using Reporting Services for delivery of reports, some of which in turn are reliant on an import from FTP via SSIS.

In the event that the FTP fails I would like the delivery of just the dependant reports to be cancelled for just that day.

Any advice on the best way of achieving this?

Cheers,

Martin

View 3 Replies View Related

Reporting Services :: Conditionally Turn On Action?

Nov 14, 2010

I want use an action to launch another report when clicking on a textbox however I only want to do it under certain circumstances. Strangely I don't see an option to put an expression on the "Action" property. Why is this?

Is there any other way to conditionally set the Action?

[URL]

View 15 Replies View Related

Paging Issue On Conditionally Set Second Table Visibility

Aug 26, 2006

Hi. I am having a problem with a 2005 report that has two tables and is designed thru visual studio. The second table is set to page break before printing and also to repeater headers on new pages. It works fine just like that. However, if I place a condition on the visibilty of the table based on an input report parameter it does not page break before rendering the table. In fact it does not page break at all leaving me with an extremely long last page. The visiblity parameter does properly toggle. This is all occurring when rendering the report to the screen using the windows reportviewer. When I export to PDF it looks fine.

Any help would be great.

View 9 Replies View Related

Displaying SELECT Query Results Conditionally...

Apr 7, 2008


Hello all,

I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:

SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla
FROM tableX....
WHERE call_status=open.....


I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.

At the moment the SELECT query above gives me the following results:

Call Ref sla

10001 In SLA
10002 Breach
10002 In SLA
10003 In SLA
10004 In SLA
10005 Breach
10005 In SLA
... ...


What it should look like is this....

Call Ref sla

10001 In SLA
10002 Breach
10003 In SLA
10004 In SLA
10005 Breach
... ...


Please let me know if anyone has the answers or any clues to this! thanks.

View 5 Replies View Related







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