Search results for query: *

Thanks jjefferson, it's a start but surprised there's no easy way of doing what should be a common function, nor is there much information on the web.

Hello, I'm struggling to find or write a SQL or .net function or SSRS expression to calculate working days between a CreatedDate and ClosedDate, the function must include bank holiday dates. I'm working from a database where creating SQL functions are not permitted, however I can embed the SQL...

I'm getting the same error even after the change ; with aCTE AS( select corraddr as txtField from dbo.househ ) ,bCTE AS ( select '<Address><ad>'+replace(CAST(txtField as VARCHAR(MAX)),CHAR(13),'</ad><ad>')+'</ad></Address>' as varcharField FROM aCTE ) ,cCTE AS ( select varcharField...

,cCTE AS ( select varcharField as xmlField FROM bCTE ) I changed to the above statement and had the error below; Msg 9506, Level 16, State 1, Line 1 The XMLDT method 'nodes' can only be invoked on columns of type xml.

Hi SabinUE, I've re-written as; ; with aCTE AS( select corraddr as txtField from dbo.househ ) ,bCTE AS ( select '<Address><ad>'+replace(CAST(txtField as VARCHAR(MAX)),CHAR(13),'</ad><ad>')+'</ad></Address>' as varcharField FROM aCTE ) ,cCTE AS ( select CAST(varcharField as XML) as...

I tried that but it's put all data in first column. and returned the error; Msg 9421, Level 16, State 1, Line 1 XML parsing: line 1, character 29, illegal name character

Thanks, where do i include the table and column? I tried the below but returned an error; with aCTE AS( select CAST(corraddr as TEXT) from dbo.house as txtField ) ,bCTE If there were more than 5 carriage returns what would happen? Thanks again, Javedi

Hi SabinUE, Just tested it and it really looks good. The text in my initial post was formatted incorrectly, i wanted it to be in columns not rows. Is this something easy to do and also will it work with a column from a table to split lots of addresses? Thanks for your help. Javedi

Hello, I have searched the forum and the internet but have not found a simple solution to splitting a column with data type TEXT where there is a carriage return. I'd like to use charindex, substring, len but don't know how to combine them to look for data in between the carriage returns (CR)...

Hello, I have a dataset which I plan to use in a matrix, but only want to be able to show two values in the parameter from a field that has lots more values, but still return all rows associated with the unique ticket number. Has anyone any ideas how this can be achieved? Thanks, Javedi

Hello I've searched the web but found no joy. I have a chart and would like to add an additional data label to show percentage. I can only switch between a count and a percentage on the same data label, so does any one know of a way i can add another data label, or have both a count and...

Thanks Dhulbert, Can you elaborate on Option 1 please? I'm using one database table for each of the two queries each with grid tables in ssrs. I don't understand how to get the parameter to switch between the grid tables, so only displaying one. So the parameter should show "OPEN" and "CLOSED"...

Hello In SSRS 2008, I have two different tables and would like a parameter to switch between them. Does anyone know how to do this? Thanks, Javedi

The dashes were put in by me to seperate the columns and there are around 1200 records. CSV would be fine, it would open in excel. Thanks, Javedi

Thanks drlex, Good article, is there no simpler way to convert the caption values to columns and the answers values to rows for each ticket number?

Hello I have the sample data below that I am trying to flatten from a view on SQL Server 2008. I was hoping there is an easy way to do this but I can't find any after Googling. The problem is with rows 4,5 and 6 which need to be in one column under Non-Reusable Item Details. Any idea's on how...

Hello I have four datasets (in a matrix grid) in a report and becuase they all return different data based on the parameter values broken down by month (columns), they are not aligned properly and do not look nice. I was wondering if there is a way i can keep the columns consitent, to show the...

Thanks xlbo, the results were accurate as only 2 cases out of 24 were out of time.

I tried the below but get the same result = sum(IIF(Count(Fields!TN.Value)= 0, 0,1-(Sum(Fields!MoreThan1Day.Value))/Count(Fields!TN.Value)))

Here's an example, where the Total colum should show 35%. one column Jun 2012 10 0.00% 10 10.00% 4 25.00% Total 24 8.33%