How To Handle NULL Dates In Formulas?
Mar 19, 2008
Re: SQL Server Reporting Services
I have welders who have active dates and inactive dates, and I need to create an "isactive" column in the report. My formula will be pretty obvious to most of you. However, if the user does not input an inactive date, that means none has been given and therefore the welder is currently active. The problem is that there seems to be no way to check if inactive date is null or not in the formula.
Please help! Thanks!
View 11 Replies
ADVERTISEMENT
Mar 13, 2004
I test my the now function and it is getting the right date. When I try to send that to the sql database I have it turns it into 1/1/1900. Does anyone know why this is happening, I have tried everything Here is my code:
sql = "Insert into tblguestbook(date, name, city, state, email, Url, Comments)Values ('"
sql = sql & Request.Form(Now) & "','"
sql = sql & Request.Form("nametxt") & "','"
sql = sql & Request.Form("citytxt") & "','"
sql = sql & Request.Form("statetxt") & "','"
sql = sql & Request.Form("emailtxt") & "','"
sql = sql & Request.Form("urltxt") & "','"
sql = sql & Request.Form("commentstxt") & "');"
View 1 Replies
View Related
Sep 10, 2007
Do I have to convert dates for a .NET developer so they can capture ms?
Or is there a method they are suppose to be using
I had to remove my modified date check to check for data collisions because they can't pass back microseconds.
What's the deal
View 4 Replies
View Related
Jul 20, 2005
I am a developer who works with MS SQL Server a lot, but I'm far froman expert. I am revamping an appointment scheduling system thatallows for appointments to reoccur daily, weekly, monthly and yearly.Currently, I am saving the appointment date as the initial appointmentdate. Then when I want to check for appointments, my stored proc doesdoes a select on the appropriate records fitting certain critieria(like only appointments for this doctor, at this location, etc). OnceI have these records I cycle through them calling the DateAdd() andDateDiff() functions to see if the appointment is reoccuring duringthe dates I'm looking for.Here's is a mock up of what I'm doing. I know cursors are a huge hitperformance-wise (especially how they are used in this scenario) andwant to get a way from this, but I can't figure out how to getreoccuring appointments to work. Any help is appreciated. Thanks.sp_GetAppointments(@StartDate, @EndDate)set @DateToCheck = @StartApptDatewhile @DateToCheck <= @EndApptDatebegin--Start a cursorDECLARE RepeatCursor CURSORFORWARD_ONLY STATIC FORselect ApptDate from ApptTable where DoctorID = 1 and--Check if it repeats daily((repeat = 1 andDateAdd(d,DateDiff(d,ApptDate,@DateToCheck),ApptDa te) =@DateToCheckand DateDiff(d,ApptDate,@DateToCheck) >0)--Check if it repeats weeklyor (repeat = 2 andDateAdd(wk,DateDiff(wk,ApptDate,@DateToCheck),Appt Date) =@DateToCheckand DateDiff(d,ApptDate,@DateToCheck) >0)CLOSE RepeatCursorDEALLOCATE RepeatCursorset @DateToCheck = DateAdd(d,1,@DateToCheck)end
View 4 Replies
View Related
Jul 23, 2005
I already asked this question; however, I am giving all the detailsnow:We get large files(millions of records) and we need to load it into ourtables using import export wizard. Some of the fields in the file canbe Null and so we are forced to create table with fields that allowNulls with default ''. However when we insert data into these tablesit puts Null in those fields even though we have a default '' (I do notthink we have any work around for that; do we?)Finally we need to go through each field and update it to '' if it is aNull and that takes LOT OF TIME.If (select count (*) from <tablename> where <columname> is Null) >0BeginUpdate <tablename>set <columnName> = ''where <columnName> is NullendPlease let me know if there are any work arounds for this crisis ?Thank you very much in advance!
View 1 Replies
View Related
Oct 16, 2006
My way:
add column (boolean) to speicify whether is null or not in drived column component, i feel that's a little difficult
any better ideas? thanks
View 6 Replies
View Related
Nov 11, 2006
Using asp.net 2.0 and visual studio 2005. The question is regarding the following ER diagram: I've made Firstname, lastname, buildingID and RoomNum all required fields. I've got a modified GridView that displays all of the table Faculty columns. It's been modified so the BuildingID and DepID are resolved to their actual field names and displayed in a DropDownList. In the dropdown list I used for inserting (a seperate DetailsView control), I manually inserted an item into the Department dropdownlist which had the text "-- Select a Department --" with a value of -1. MS SQL didn't like that -1 value so I wrote the following code to fix it: protected void dsFaculty_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
if (e.Command.Parameters["@DeptID"].Value.ToString() == "-1")
{
e.Command.Parameters["@DeptID"].Value = null;
}
} That means of course DeptID is null, which is ok. The problem arises is when I try to edit that row in the GridView. I get the an error 'ddlDepartment' has a SelectedValue which is invalid because it does not exist in the list of items.Parameter name: valueIdeally, I'd like to make the dropdown list in the GridView show "-- None --" for the DeptID if it comes across a null value. I already tried playing around with the Command.Parameters in the dsFaculty_Selected function, but it didn't work. Ideas?
View 1 Replies
View Related
May 15, 2007
Hi all I am trying to populate a page with data from a SQL DB however one field is null. The problem is this is causing a StrongTypingException and I am not sure how I should handle this to stop the apllication crashing. I am trying to assign a bit value from a SQL Db to checkboxI have tried putting if (Convert.IsDBNull(contentRow.pag_status) == false){//Do what I want}
but this still throws the exception
Can anyone help!?
View 5 Replies
View Related
Feb 19, 2008
Hi,
In my Excel file, The Application date column contains empty for some rows. In SSIS I am using one Data Conversion to that Application Date column to change it as Date[dt_Date]. This data conversion is giving error Conversion failed. In Sqlserver table, I declare ApplicationDate column datatype as DateTime.
I want to keep those empty date values as Null in Sqlserver.
I tried the IMEX=1 property still it is not working. How to solve this error?
Thanks in advance.
View 1 Replies
View Related
Jul 5, 2007
I have a web application. In some instances, I have a need to send guid parameter as null while making a sql query to SQL 2005. My question is as how to handle this null guid on .net and sql side.
Thanks! in advance.
View 2 Replies
View Related
Aug 17, 2005
If myDateTimeColumn contains a <NULL> value. How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types.
I am curious as to what others are doing to handle this?
View 6 Replies
View Related
Jun 18, 2004
I have a checkbox on my webform that is bound to a bit field in my SQL table. I'm fine as long as I've got the bit field set to 0 or 1, but if the field is NULL, the checkbox throws an exception during the databind.
Is there any way to handle this without removing the data binding and manually setting the value (ie: some way to intercept it before the exception gets thrown and then setting the field value in the dataset)?
Thanks!
View 1 Replies
View Related
Jan 12, 2007
VS05
How are dates removed / nullified?
I have a SQL datetime field that is being editted via a datatable / adapter - The table structure defines the datetime as a date.
From here I want to remove the date - i.e. write DBNull back to the database. Setting the Date to Nothing or .minvalue results in a min date exception and DBNull cannot be cast to the datetime either.
R
View 3 Replies
View Related
Feb 8, 2008
Hi, I have a database field for completion dates - until a task is completed, there is no date and at the moment I have null values in this field.My problem arises when searching the records. I have a search form which passes parameters via a query string to a SqlDataSource. The SqlDataSource has the CancelSelectOnNullParameter set to true, so if any fields on the search form are left blank, they are ignored. For other dates, my sql query contains something like (Job.EnteredAt < ISNULL(DATEADD(d, 1, @EntTo), '2099-01-01')) This approach doesn't return any records for the completion date as there is no date to compare to the '2099-01-01'.Can anyone give me any tips on how I should handle this? I'm willing to change my structure, search page or sql query!Thanks, Neil
View 1 Replies
View Related
Aug 4, 2007
My users want to be able to enter nothing in a date field.
I'm using asp.net v2, vb.net, and VS 2005 for my application. I'm not sure what to do or what code to write to allow the user not to enter a date and keep from hitting the sqldatetime overflow error.
I could use some help.
Thanks
View 4 Replies
View Related
Apr 2, 2007
Hi EveryoneVery new to .net and currently dipping my toes in the water with a small application, but getting to the point -
I have a form which has somel text fields that expect a date but which are not a required field so in other words the user can leave them blank.
The code behind page stores the information using a stored procedure which I add parameters to in the following fashion -
SqlParameter userdate = new SqlParameter();userdate .ParameterName = "@dtdate";userdate .SqlDbType = SqlDbType.DateTime;userdate .Direction = ParameterDirection.Input;userdate .Value = dtdate.Text.ToString();cmd.Parameters.Add(userdate);
Now if I leave the text field dtdate blank I receive an error because the above expects a date.
If I remove the line userdate .SqlDbType = SqlDbType.DateTime; I don't recieve an error but my stored procedure saves the date as 01/01/1900 or similar.
I believe this is because in my stored procedure the paramger dtdate is defined as @dtdate datetime
Obviously I want to have it so that if the user leaves the text field empty then no date is saved in the database and was wondering how other people tackle this scenario.
View 3 Replies
View Related
Jan 16, 2007
I have been looking for the answer to this for a while, but probably haven't found the right place or query. I want to achieve the following:Table 1Table 2
DateShiftDateShiftData
---------------------------------------------------------------------------------
1/1/200711/1/20072Some data
1/1/200721/2/20073Some more data
1/1/20073
1/2/20071
1/1/20072
1/1/20073
and generate:
Table 3
DateShiftData
--------------------------------------------------------
1/1/20071NULL
1/1/20072Some data
1/1/20073NULL
1/2/20071NULL
1/1/20072NULL
1/1/20073Some more data
This way, the information can be displayed and show that some of the entries were not entered for the dates with NULL. Thanks for the help, -Syn
View 2 Replies
View Related
Sep 21, 2006
I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?
Thanks.
View 4 Replies
View Related
Jul 20, 2005
Looking for a decent source that has some examples of column formulas aswell as a list of column formula functions that can be used and how to usethem.Hard time finding something online. Any suggestions or references would begreatly appreciated.Fairly new to this.Thanks
View 1 Replies
View Related
Jan 24, 2008
ive come from a crystal background and im having difficulty writing reports.
An example of the problem im having is with saved formulas (crystal calls them formulas)
eg
i would create a forumula called Difference
it would be something like this
=DateDiff("d",Field1, Field2) save that and then it was stored
then in a text box i would reference it like so.
=iif( Difference <= 23,"Too New","Just Right")
is this possible in RS??? as i have reports i need to convert from crystal to RS, and the iif statements are becoming huge, especially when you reference the same formula in the expression multiple times.
bit long winded i know, and i hope this makes sense, but i am witts end with this issue
thanks in advance
Jonny
View 3 Replies
View Related
Sep 4, 2007
im moving over from a crystal background, in crystal i was able to create custom formulaes and use them somewhere in the report, can this be done in RS, if so how?
thanks
Jonny
View 1 Replies
View Related
Dec 9, 2005
Sorry, I made a mistake. How can I delete this message?
View 3 Replies
View Related
Apr 28, 2004
I'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?
---- Jake
View 4 Replies
View Related
Jun 25, 2006
I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.
Thanks
View 1 Replies
View Related
Jan 16, 2008
I'm creating a report that is designed to be exported to Excel so that the end user can manipulate the data. There are two main columns that I'm concerned with - TimeTaken and OTTimeTaken (for overtime).
Our application does not track OTTimeTaken so it's default will just be 0, but I need the excel file to have a formula that automatically adds the TimeTaken to the OTTimeTaken in a third cell for the total number of hours as that value will be added by the A/R department for invoicing.
I thought adding this would give me what i need:
="=SUM(C" & RowNumber(Nothing) & ": D" & RowNumber(Nothing) & ")"
when exporting to excel, it dumps it in as plain text, requiring that the user double click on the cell in order to "activate" the formula.
Is there a way to have SRS create this formula so that it works in excel rather than requiring the additional user interaction?
Thanks!
View 4 Replies
View Related
Dec 27, 2007
Trying to get the blank out of my report and replace with (0). My formula is SUM(Loan Amount). If there is nothing to sum there is a blank space. Would like it to have a (0) in it.
View 1 Replies
View Related
Jul 23, 2005
I'm working with a DB design that seems to me to be rather complex.This is a very slimmed down version of what I'm doing, but I believe itis enough to get my question resolved.Here is my layout.These 4 tables are used to generate a questionaire.Survey OrderID========= ==========SurveyID OrderIDOrderID QuestionGrpIDQGrp Questions============= =============QuestionGrpID QuestionIDQuestionID QuestionTextThe following two tables are used to calculate a report that is sent tothe customer.RawData=========================OrderIDQuestionIDValue is string but is Cast as decimal for numeric formulasMetrics==============================================QuestionGroupID | ReportText | Formula | MetID==============================================2 | % Support Staff of Total | OP21/(OP21+OP22+OP23) | 1The OP references are questionIDsNow to calculate the result for the report we programatically parse theformula creating a temp table (table name = Temp & orderID & _ &QuestionID) with OrderID and OPxx as the field names. We create onetable for each question.We then use dynamic SQL again to calculate the result using the aboveformulaSELECT OP21/(OP21+OP22+OP23) FROM Temp5_21, Temp5_22, Temp5_23 WHERETemp5_21.OrderID = Temp5_22.orderID AND Temp5_22.OrderID =Temp5_23.OrderIDThis select is used to create a single table of calculated values.This table is in turn used to tell the customer how they compare toother customers. Percentile, Mean, Median, Std Dev, and a few others. Idon't claim this part of the project, but I'm not sure how I might havedone it, had it been assigned to me.MY PROBLEM!!!Sometimes a 0 is valid data and is the denominator of a devisioncalculation. Since this is so dynamic and it might be difficult todetermine when division is used. I need a way to default divide by 0execptions to NULL. This DB is on a hosted server.Thanks for bearing with me,Greg Kelley
View 2 Replies
View Related
Aug 5, 2007
I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.
For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given
2
4
3
6
0
Average: 5
What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?
I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.
Any help will be appreciated.
View 1 Replies
View Related
Apr 27, 2007
Hi,
I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.
Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:
Code Snippet
=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value
This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.
99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...
I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.
Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;
- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)
- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)
Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.
And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.
Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.
*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...
Thanks in advance for any advice
View 4 Replies
View Related
Dec 3, 2007
Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
View 10 Replies
View Related
Jul 20, 2010
I need to have a report that will have formulas in excel. I understand that the limited support for exporting excel formulas has been dropped in SSRS 2008. Is there any way to work around this and still output formulas?obvioulsy the way to do it in previous versions of SSRS are not working (using report items). I tried to add my formulas in my dataset (I can know which excel cells I need in the formulas) but they end up being displayed as text. I then have to get in the excel file and enter and exit each cell so that it "becomes" a formula.
View 11 Replies
View Related
Aug 29, 2007
Hello all, I´m a beginner on AS2005 (but I know pretty well AS2000), I migrated a cube from AS2000 to AS2005 but in this cube I have a Custom Rollup Formula on two levels of my Time dimension (month and week), an example of the week level formula is the next:
iif( [Time].CurrentMember.Name = [Time].CurrentMember.NextMember.Name,
null,
iif( [Time].CurrentMember.Name = [Time].CurrentMember.PrevMember.Name,
Sum({[Time].CurrentMember.Children,[Time].CurrentMember.PrevMember.Children}),
Sum([Time].CurrentMember.Children)
)
)
<<this formula overrides the week aggregations>>
and I really need this but I cannot find on AS2005 where to do the same thing, someone can tell me where can I define Custom Rollup Formulas and how?.
Thanks in advance!
View 10 Replies
View Related
Jan 18, 2008
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
View 2 Replies
View Related
