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/HR
3. 890/EG
4. 012/AS
5. Trainee
6. 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
ADVERTISEMENT
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
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
Jun 12, 2007
Hello All,
I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
Bob001
Bob
Robert005
Doug053
Doug100
Douglas
Barney001
Frank
I want to group it up as...
Bob
Doug
Barney
Frank
And then be able to summarize the results in the matrix.
Thanks in advance for any help
-Clint
View 13 Replies
View Related
Aug 30, 2013
I want to get the summation of the amount column against all the distinct values of Description fields. May be the "Where" (applied on Tdate Column) Clause unable to contains all the Unique(Distinct) values from the single Column (Description).
Table Structure :
CREATE TABLE [dbo].[TransLine](
[TransID] [int] NULL,
[No] [int] NULL,
[Description] [varchar](50) NULL,
[Code] ....
Sample data : [URL] ....
View 5 Replies
View Related
May 7, 2007
I have two groupings defined in my table. I group by Owner and then by Priority. I'm struggling with the expression to capture the string values at the appropriate scope. Using the table below, I want to capture "Critical, High" for "Jack Daniels" and "Medium, Low, Informational" for "Jim Beam". The table has "grpOwner" and "grpPriority" defined on Fields!Owner.Value and Fields!Priority.Value respectively. Any help would be greatly appreciated!
Owner
Priority
Jack Daniels
Critical
High
Jim Beam
Medium
Low
Informational
View 3 Replies
View Related
Jan 22, 2006
What is the best way to build a SQL query to extract data from a table in this manner:
COLA COLB COLC DATE
01 01 01 04/15/1988
01 01 01 11/23/1997
01 01 01 02/02/2005
01 02 01 6/14/1990
01 02 01 10/30/1999
01 02 02 4/24/2001
How would I select Row 3, 5, and 6 above (In other words, from each set where COL A B & C are equivalent, grab the most recent date?)
Thanks!!!
View 1 Replies
View Related
Oct 12, 2007
Hi Fellows
I am trying to organize these information.the data come form two tables that are not relating, but I did a join and my primary key is the filed polygon. I have a list of points(geomseq) for each polygon but the number of points(geomseq) can change. I have this inofrmation in a data base.
geomseq polygon xc yc x1 y2
0 17 21 22.5 0 0
3 17 21 22.5 40 40
2 17 21 22.5 0 20
4 17 21 22.5 20 0
1 17 21 22.5 0 10
5 17 21 22.5 10 10
1 18 40 40.5 0 20
4 18 40 40.5 20 30
0 18 40 40.5 0 0
3 18 40 40.5 10 20
2 18 40 40.5 5 15
5 18 40 40.5 30 35
6 18 40 40.5 40 40
9 18 40 40.5 80 80
7 18 40 40.5 45 45
8 18 40 40.5 50 60
I want something like this
geomseq polygon xc yc x1 y2
0 17 21 22.5 0 0
1 17 21 22.5 0 10
2 17 21 22.5 0 20
3 17 21 22.5 40 40
4 17 21 22.5 20 0
5 17 21 22.5 10 10
0 18 40 40.5 0 0
1 18 40 40.5 0 20
2 18 40 40.5 5 15
3 18 40 40.5 10 20
4 18 40 40.5 20 30
5 18 40 40.5 30 35
6 18 40 40.5 40 40
7 18 40 40.5 45 45
8 18 40 40.5 50 60
9 18 40 40.5 80 80
regards and thanks in advance
Edwin
View 3 Replies
View Related
Nov 6, 2015
I have two tbles that have ItemName and their bill amount
a) tblLunch
which shows records like below
Invoice Item Amount
--------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 ICE CREAM 1000.00
b) tblDinner
which shows records like below
Invoice Item Amount
------------------------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 PASTA 1000.00
I want to perform a query that should SUM Amount Columns by Grouping the Item from both the tables, so we could get the following result
Item Amount
------------------------------
COFFEE 2000.00
TEA 4000.00
ICE CREAM 1000.00
PASTA 1000.00
View 3 Replies
View Related
Feb 6, 2007
I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.
Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2
What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Is this something that can be done with grouping and sorting?
Thanks,
Chad
View 1 Replies
View Related
Oct 18, 2001
There's some SQL below (T-SQL) & I'm wanting to have this result set
grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column.
The columns BCOM_ID contain unique values, but Venue_ID can have duplicate
values. I only want to get rows for one instance of the Venue_ID (per
BCOM_ID) - doesn't matter which instance but basically, no duplicates.
Oh yes, one of the columns is a Bit column.
Any ideas would be welcome & appreciated!
Many thanks,
Darren
darren@darrenbrook.fsnet.co.uk
SQL:-
SELECT Booking_Header.BH_ID,
Booking_Header.Booking_Header_Description,
Booking_Header.BStat_ID, Booking_Header.BT_ID,
Booking_Header.Tagged, Booking_Header.Status_Timestamp,
Booking_Header.Start_Date, Booking_Header.Days_Qty,
Proposal.PPL_ID, Proposal.PPL_Status,
Booking_Component.BCOM_ID,
Booking_Component.Component_Description,
Booking_Component.Venue_ID, Venue.Venue_Code,
Venue.Description, Address.Address_ID, Address.Town,
Booking_Status.BStat_Description,
Booking_Type.Type_Description
FROM dbo.Booking_Header INNER JOIN
dbo.Proposal ON
dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN
dbo.Booking_Component ON
dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER
JOIN
dbo.Venue ON
dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN
dbo.Address ON
dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN
dbo.Booking_Status ON
dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER
JOIN
dbo.Booking_Type ON
dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID
WHERE (dbo.Proposal.PPL_Status = 1) AND
(dbo.Booking_Header.BH_ID = 10)
Thanks,
Darren
View 2 Replies
View Related
Aug 4, 2014
I'm trying to divide two values from separate rows. Each row is a separate UNION statement.
2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)
In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)
What would be the best way to accomplish this?
View 1 Replies
View Related
Mar 26, 2008
I want to group by a fields value in a table. I have a purchase table where contracts and orders are stored in together. Now what I need is, I have to group only by the rows that contain an identical contract_no and order_no as those records represent the actual contract. the other rows contain order data-sets which need to be subtracted from the contracts values.
datasource:
contract_no order_no name amount
12 12 contract1 1,000,000
12 215 order215 50,000
12 460 order460 75,000
280 280 contract2 500,000
280 340 order340 10,000
280 410 order410 9,000
I want to display each order that took place via drilldown/hide function in the table, and calculate the amount from the contract that is left.
desired result report:
12 contract1 1,000,000
order 215 50,000
order 460 75,000
amount left 875,000
View 1 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Sep 16, 2004
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
View 2 Replies
View Related
Nov 26, 2007
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
View 1 Replies
View Related
Dec 10, 2004
Hello,
I am working with ASP.NET/VB and Microsoft SQL 2000 database.
I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book
Then I use this script in results.aspx to put the keywords in a string:
Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub
My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':
SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')
This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().
Can you tell me how to access the string values in the SQL and use it?
Thanks,
Miguel
View 2 Replies
View Related
Jul 6, 2006
SQL neophyte hoping this is an easy answer...
I'm trying to obtain an average for a set of string values. "Huh?" (In an investment portfolio, some securities have ratings, e.g., AAA or Aa2 or BBB, etc., and to obtain an overall rating for the portfolio you have to find a way to average the string values from each individual security.) I figured I'd assign a numerical value to each, average, and convert back to string. My first attempts have been a little futile...can anyone help?
View 10 Replies
View Related
May 27, 2008
Hi all,
I have table with id column of nvarchar..
Now,WHEN I RUN THIS QUERY,
select ID from <TABLE>,
IT RETURNS
erfq-1
erfq-10
erfq-100
erfq-1000
erfq-10000
erfq-1001
erfq-101
erfq-11
erfq-12
erfq-19
erfq-2
erfq-3
erfq-30
erfq-4
erfq-5
erfq-50
erfq-6
erfq-9
erfq-99
erfq-999
WHERE AS I NEED IT AS BELOW
erfq-1
erfq-2
erfq-3
erfq-4
erfq-5
erfq-6
erfq-9
erfq-10
erfq-11
erfq-12
erfq-19
erfq-30
erfq-50
erfq-99
erfq-100
erfq-101
erfq-999
erfq-1000
erfq-1001
erfq-10000
How Can I achive this one ?
Thanks in Advance
Dana
View 7 Replies
View Related
Oct 18, 2007
HI!
I do a select and concatinating the answers into a one column table @tab(string).
Is it possible to format these values like this:
GENAV DELAV TOTIP RES
12 myvalues www.notech.com 1
1 starthere 192.168.0.2 1
125 or here www.hereistomanychar.s 0
max 3 max 10 max 22 max 1
the first column max 3 characters the second column max 10 char...
I want to do this to get a good view of the data i the table @tab
Here is the select:
INSERT INTO @tab
SELECT Convert( varchar, GENAV)+ ' ' + Convert( varchar, DELAV)+ ' ' + Convert( varchar, TOTIP) ' ' + Convert( varchar, RES)
from dbo.MOVE
where DATE between @starttime and @theEndTime
With this select it looks like:
12 myvalues www.notech.com 1
1 starthere 192.168.0.2 1
125 or here www.hereistomanychar.s 0
This is very complicated to read!
View 6 Replies
View Related
Dec 30, 2003
This is a portion of my stored procedure in T-SQL
CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)
AS
SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)
SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)
*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])
The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?
thx in advance...
View 3 Replies
View Related
Jun 14, 1999
I currently have a need to dynamically build an sql statement that always returns a single value when executed. The sql statement is always the same except the database name reference in the statment.
What I need is to be able to capture that value for later use in the procedure. Since this is a stored procedure, I can't use the "USE" statment to switch databases and I haven't been able to figure this out using the Execute statement. I can execute the string, but I can't capture the value.
I'm simply trying to execute the same set of sql statements in a stored procedure without hardcoding database names or build an identical stored procedure in all our databases.
Any help is appreciated.
View 2 Replies
View Related
Apr 8, 2004
I am not close to an sql server today and this question was posed to me. can someone hook me up?
" I want to query a column of values and place them into a single string seperated by commas" (as a function)
Table a
123
456
789
321
654
987
output
123,456,789,321,654,987
thanks in advance
View 14 Replies
View Related
Oct 25, 2006
How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?
View 10 Replies
View Related
Jun 5, 2014
I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be
column header US
with value Project/Prescription sale
next column header NL
with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,
[Code] ....
View 9 Replies
View Related
Jul 29, 2015
Why can i get the numeric values from this string?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be different...
View 6 Replies
View Related
Jan 10, 2014
I have a program which feeds back information to a table within my database. The annoying thing is that only 1 of the table cells has all of the information I need so essentially I have a large string of data I need to retrieve values from.I retrieve my cell with a simple select details from alerts where ID = (alert number).The results I have are as follows:
[INFO] Dashboard Output Information:
[LastUpdated]
Time=09:59
[LastProcessed]
LastProcessed=*2911
Updated=09:59
[LastReceived]
LastReceived=#10
Updated=09:59
As you can see I have a list (its larger but this is a sample), the only thing I need are the values for each section so for last processed i want the 2911 so I can use it on my dashboard and the last received I want the 10 for that section.i have read about using substrings but am really not sure where to start and how I'd go about getting these values to then use elsewhere?
View 3 Replies
View Related
Apr 5, 2006
I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!
View 6 Replies
View Related
Nov 24, 2015
The data source is Fetch XML - so all the values are string.I need to sum the values but getting an error:
Warning 1 [rsAggregateOfNonNumericData] The Value expression for the textrun ‘Textbox25.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.I tried converting it to INT but it returns a large number which does not match what I'm expecting.
View 10 Replies
View Related
Dec 7, 2007
Any idea why this expression would not work in SSRS?
Based on a expression on a group textbox I get 0 records that match:
=Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0)
I've evaluated in the proc & there should be a count of 29 records.
I found a work around by using this in the stored proc & I get my record count:
ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END
and changed the expression in SSRS to this & it works:
=sum(Fields!ACCRUAL_CODE.Value)
Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?
View 3 Replies
View Related
Oct 17, 2007
SQL Server 2005.(SP2). MS SSRS;
I want to display some numbers in the same line as a concatenated string. For example a Customer may have multiple bills. These bill numbers are displayed in separate rows. I want to display them all on the same line.
Example of current display:
Customer Bill #
ABC Company 123
ABC Company 456
ABC Company 789 etc
I want this to display as below:
Cusotmer Bill #
ABC Company 123, 456, 789, etc.
Is this possible in SSRS. Please help me with the syntax.
Thanks in advance.
View 2 Replies
View Related
