Expression Question

Dec 18, 2007

I'm somewhat new to SSRS. I'm working on a report that needs to show the average transaction $. I tried an expression with AVG, but this is based on the total dollars (orders) divided by the number of orders. That worked, but what I need to have is the total net dollars (orders less returns) divided by the number of orders (not including returns).
I've tried a subquery, and expressions based on the totals shown on the report (that didn't work, I either get an error and it doesn't run, or it says "error" on the report.
The View (joins of six tables, two of which are union joins of the order and return detail lines and of the headers) upon which this is based has detail lines for each order, that need to be aggregated. The returns are distinguished from the orders by negative quantities and order number prefixes.
Is it possible to even do this? If so, please give detailed steps.

View 4 Replies

URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But

Feb 8, 2007

Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1

"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"

Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works

However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur

(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)

View 4 Replies View Related

Reporting Services :: Running Value Expression Within Lookup Expression In SSRS?

Oct 28, 2015

I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".

I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.

Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.

This is the lookup expression that I added for the 2nd column.

=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").

I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.

(processing): (SortExpression ++ m_context.ExpressionType)

View 7 Replies View Related

Which Expression To Use ?

May 27, 2008

well I have this code. I connect to database and submit data.
now i dont know which expression to use to validate if data is submited redirect to next level else show some error.
protected void Button1_Click1(object sender, EventArgs e) { SqlConnection con = new SqlConnection(@"somecode");
SqlCommand comm = new SqlCommand(); comm.Connection = con; comm.CommandText = "insertuser"; comm.CommandType = CommandType.StoredProcedure;some code... comm.Connection.Open(); comm.ExecuteNonQuery(); con.Close(); }
i dont wanna validate the fields as i can do it using field validator i wanna check if data is inserted in database.

View 3 Replies View Related

Only One Expression Can Be Specified

May 28, 2008

Hi

When I try and create this procedure, I get the error below

CREATE PROCEDURE GetUserSearchTypesCount
-- OUTPUT parameter to hold the count.
@UserID int,
@ReturnVal int OUTPUT
AS

SET @ReturnVal = (SELECT TOP 100 percent searchType, COUNT(searchtype) AS SearchCount
FROM ttracksearchresults
WHERE userid=@UserID
GROUP BY searchtype
ORDER BY searchtype)

GO

Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

View 3 Replies View Related

Expression In SET

Feb 7, 2007

I need a statement like:UPDATE tblQuestions SET QuestionNumber = QuestionNumber +1 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
(Required output is clear from statement itself)

I tried with
UPDATE tblQuestions SET QuestionNumber = EVAL(QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
and
UPDATE tblQuestions SET QuestionNumber = (QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1

I've just started working with SQL. Please suggest correct approach.

View 3 Replies View Related

Expression

Feb 1, 2008

I have the following code in the expression builder for a ExecuteSQL Task in the Control Flow.
"INSERT dbo.StgFileList SELECT ' " + @[User::MyFile] + " ' "

When I evaluate it, it renders as INSERT Staging.StgXMLFileList SELECT ' '

What am I doing wrong in the expression builder?

View 5 Replies View Related

Expression Help

Feb 20, 2008

Is it possible to nest expressions within a script in a similar fashion to how they're nested within the expression editor?

I have a requirement to strip trailing digits from a string field, so that only two decimal places are left.

e.g.
1.12345 becomes 1.12
12345.6789 becomes 12345.67

I would do this in the expression editor but I need to use Script component because there are a multitude of conditions that need to be evaluated first which I will do inside a Select Case statement.

I'm wrestling with code that looks like

Newratefield = ratefield.Substring(1, Findstring(ratefield, ".", 1) + 2)

but VB doesn't like the 'Findstring(ratefield, ".", 1) + 2)' part.

Any suggestions on what I can do here?

View 3 Replies View Related

Expression

Jan 9, 2007

Hi,

In RunningValue expression, can we able to put the DataSet name.

Thanks

View 17 Replies View Related

Expression

Jul 27, 2007

Hi,
In ssis I would like to map a column from table a to a column in table b
Currently this is how I am doing it using sql.
I believe I have to use a derived column but not sure how to implement if statement inside it.
Thanks

case

when len([Column 5]) = 0 then ''

when lower([Column 5]) = 'unknown' then ''

when isdate([Column 5]) = 1 then substring(ltrim([Column 5]), 1, 4) + substring(ltrim([Column 5]), 6, 2) + substring(ltrim([Column 5]), 9, 2)

else

null

end,

View 4 Replies View Related

The Value Expression

Dec 20, 2006

The Value expression for the textbox ��Name�� uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set....what does this mean...this an error that I have when i try and build my report i get this error for some reason...how do i fix this???

View 4 Replies View Related

Need Help With Expression

Sep 28, 2007

Hi,

I need to replace a field that might contain a certain character with another should the value occur.
To explain

If i get a hash # in the field, then I need to replace the whole field with a 0 since its a numeric field

I'm just a bit of a noob still with expressions

Regards

View 3 Replies View Related

Expression Sum And Iif Help?

Aug 17, 2007

Hi All,

SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))

i write this expression inside the group footer row, it give a #error when generating report.

please help

Cheers

Nick

View 5 Replies View Related

Expression.Like For Numbers?

Aug 26, 2007

Hi,
Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?
Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.
Cheers,
koekie

View 3 Replies View Related

Boolean Expression In SQL

Jun 30, 2005

Hello,In VB/VB.NET, I can use an expression as such to evaluate true/false:Dim blnValue As Boolean = (SomeObject.Property = "Some value")Can I do this in T-SQL?declare @var bitset @var = ?Thanks.

View 2 Replies View Related

SQL Expression Needed

Jan 12, 2006

Hello, I need to create a table where a column's data should have a hypen inserted after the second character. The table will accept a large input of rows, so I think it would be better to use a SQL expression or formula on the column to provide the hyphen on insert rather than doing an update statement later. There is no way of knowing how many characters will follow the hyphen.
So for example... 888888 would become 88-8888
or 5555555555555555 would become 55-55555555555555
Any help would be great!

View 4 Replies View Related

Regular Expression

Jan 10, 2007

Hi Guys

I have a table in which one of the column name is "NAME".
I have to write query in which column is not null and if there is something then check for 'trust' or 'trustee' in the string. and if found don't pull that record. skip it

who do i do these. Any help please.

View 2 Replies View Related

CONCAT + Expression

Mar 2, 2006

how can i CONCAT 2 columns & () ?

SELECT CONCAT(A,B) AS C From myTAble

but I want to get A (B)
dog (red)

thank you

View 2 Replies View Related

Regular Expression?

Mar 25, 2008

HiI'm looking to return rows from a table where a column is LIKE a keyword. This is my current code:SELECT DISTINCT business_name FROM Table WHERE business_name LIKE @keyword + '%' ORDER BY business_name ASCThe problem with this is that if a business is called "The White Ship Inn", and the keyword to match is "White Ship" it will not match. If i put another wild card in front of @keyword then this finds too many matches - i need it to match against columns which either start with the keyword, or have a whitespace in front of them. Is there some kind of T-Sql i can use or will i have to go down the regex route (using this (http://www.codeproject.com/KB/mcpp/xpregex.aspx)) ?cheers

View 2 Replies View Related

Regular Expression..

May 6, 2004

Does SqlServer support Regular expression?
I want to know if it's avilable or not...
And if it's not available, I want to know any other efficient way to validate string.. manipulating Regular expression style validation.

Thank you all...

View 6 Replies View Related

Only One Expression Can Be In SELECT

Feb 2, 2014

The full error is this: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

My Code:
SELECT Customers.CustomerID, Customers.EmailAddress, Customers.LastName, Customers.FirstName,
(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers
ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID),
(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers
ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID)
FROM Customers JOIN Addresses
ON Customers.CustomerID = Addresses.CustomerID;

I am trying to test a query I am going to use to create a view with all of a customers addresses (billing and shipping).

View 5 Replies View Related

Is It Possible To Format An Expression?

Sep 12, 2005

I have an expression based upon 2 fields in a view that I want to be able to divide and then multiply by a number. Whenever I do the division portion of the calculation all I get are 0 & 1's. I was hoping to use the percentage in other calculations.
Is there something similar to FORMAT(X/Y,0.00)?

(vwLbrPosSum.PosMin/vwMinByEmp.TotMin) x (a)vwLbrPosSum.PosMin or (b) 2400 or (c)vwMinbyEmp.TotMin - 2400
If the person worked more than 40 hrs I could allocate the OTMins to the different positions in labor reporting.

Thanks in advance for your assistance.

View 2 Replies View Related

Expression To Find 0

Dec 4, 2007

Hi i'd like to create a expression to find if the first character is 0 and if it is then don't display it, but if the first charactar is not 0 then its ok, am trying to do this within a drived column in SSIS

View 14 Replies View Related

SQL Check Expression

Mar 12, 2008

I'm using SQL 2000 Enterprise Manager Diagram Check Constrains dialog ???????

How do I CHECK Expression so that cust_zip data is:
- all numbers
- 5<=length<=10

Thanks for any help...

View 1 Replies View Related

Filter Expression

Jul 23, 2005

HiI have some data that need to be filtered based on a SET of Id's.If it's about a single ID, then i would pass it as a parameter in astored procedure and use it within the the WHERE Clause, but here thoseID's are determined in run time and I can't simply create a a storedprocedure for an unknown amount of ID's.I looked into the SQL Server 8.0 Manual but had no examples how to usethe Function Filter.Generaly, how can filter some records based on a set of ID's?Best regards

View 1 Replies View Related

Regular Expression?

Sep 6, 2005

I need to remove all the [ and ] in every sql stmt where they are usedto close encircling numeric value, fyi, all these sql stmt areconverted from an Access db. For instance,select *from XYZtblwhere fieldA = [1] or fieldA = [2] or fieldA = [3]to beselect *from XYZtblwhere fieldA = 1 or fieldA = 2 or fieldA = 3-- and of course I'd have written in as follows, but that's not-- the pointselect *from XYZtblwhere fieldA IN (1,2,3)Thanks.

View 5 Replies View Related

Using Alias In An Expression

Jul 20, 2005

HiI tried to use the alias a1 for the column f1 like this, but it fails:create table a (f1 int not null primary key)insert into a (f1) values (2)select f1 a1 from a where a1 = 2Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'a1'.In my real situation f1 is a "pretty complex" calculation, and a1 is analias of that.I wanted to use the alias in the query instead of repeating the calculationseveral times.If this use of alias is not legal, then is there a way to get around this?TIAGunnar V�yenliEDB-konsulent asNORWAY

View 2 Replies View Related

Expression Problem

Apr 14, 2008

Hello,

I need to pass a parameter that has multiple values depending on a picklist. I'll show you an schema of what I need:

Parameters:

label value
Open 11

Closed -1
Others >0 and <11

Of course there's no problem with "open" and "closed", but I don't know how to build an expression for "others". Can you help me?

Thanks in advance, regards

View 5 Replies View Related

Expression Question

Mar 11, 2008

Can anyone tell me if I am doing something wrong

I am creating a sql string passing some parameters in the expression field of a string variable(defined at the data flow task level)

I have the following in the expression field

"select * FROM TDATA where CODE ='4' and item ='5' and ddate > (Current_date - " + @[User :: DateOffset] + ")"

DateOffset is a package level variable that is defined as an integer that has a value of 7.

I keep getting a "The expression for variable "SqlStr" failed evaluation. There was an error in the expression."

If I just have the simple query "select * FROM TDATA where CODE ='4' and item ='5' " the expression works fine

Can someone tell what I am doing wrong

Thanks in adv

View 14 Replies View Related

How To Use An Expression In A SQL Task

Nov 7, 2007

Hi

Does anyone know if there is a Tutorial That I can Use, that would explain me how to use an Expression in a SQL Task, as I need to create a Table by using a T-SQL Script and pass the Table name into the Variable and cannot come right using Variables.

I have been given advice that an expression would solve my problem.

Please Help

Thanks In advance

View 4 Replies View Related

Expression Builder

Dec 1, 2007

Hi All,
I want to build following code into expression builder . I don't know how to write that things into expression builder. so can you help me out from this problems.

1.Daily stock Inventory by stores " & FormatDateTime( date() , vbshortdate )
2. " Weekly stores Queue Productivity for " & date()-8
3.Customer Data Entry Monthly Report for stores " & Month( now()-27) & "/" & Year(now()-27)
4."Customer of stores " & FormatDateTime( now() , vbshortdate ) & " - stock"
5.customers opens account " & FormatDateTime( now() -8, vbshortdate ) & " (STOCK1)

Thanks
Aric

View 6 Replies View Related

DATEPART Expression

Apr 1, 2008

Hi - Can anyone tell me what the first day of the week (i.e. equal to 1) for the DATEPART("dw",<date>) expression in SSIS. Or can I specify the first day of the week somehow?

It looks like Monday=1 which seems a little odd as the equivilent T-SQL DATEPART has Sunday=1.

Thanks!

View 5 Replies View Related

Do A CASE With A Expression

Aug 16, 2007

I know i can do a "IF" using expressions, the question is, can i do a CASE?

Thanks!

View 3 Replies View Related