Expression Problem Count Disctinct

Nov 8, 2007

Hello all,

I have a problem to do a count disctinct on two different fields within reporting services using an expression.

In my dataset i have a field called Description and a field called Type.
I would like to do a count distinct for every Description with Type=0 and a count distinct for every Description with Type=1.

Can this be done with an expression within RS (and not within my sql query from my dataset)?

Greetings
Vinnie

View 7 Replies


ADVERTISEMENT

Disctinct Query Help

May 22, 2007

Can anyone tell me how to write this query correctly... Right now it is returning all of the results instead of just the results with unique emails...  SELECT     ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAddedFROM         ResultsWHERE     email IN  (SELECT DISTINCT email  FROM  Results)  Please help. 

View 6 Replies View Related

Disctinct Selections

Apr 11, 2006

Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column


for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

Alan

View 14 Replies View Related

Count Expression On Condition

Apr 25, 2008

Hi, i need help please!

I want to show a count of the following:
Field values = "CFG1" , "CFG2"

So i want to count the number of "CFG2" values there are.

I tried something like this but does not work.

=count(Fields!Task.Value="CFG2")

Please Assist!

Regards

View 11 Replies View Related

COUNT Expression Values

Feb 28, 2007

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.



Any info is helpful.

View 1 Replies View Related

SUM Of Report Field With Expression Which Has COUNT Aggregate Function

Jun 20, 2007

Hi everyone,



I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org.

Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report).



Report has three groups Org , Assessor and Unit.

Can someone please help me with this?

Appreciate help.

Thank you,

Ski





Org 1(Group1)

Unit Credits Units Assessed(# of Trainees) TotalCredits



Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4)

Assessor 2 Unit 2 1 2 2 (1*2)

Assessor 3 Unit 3 5 2 10 (5*2)

Unit 4 2 1 2

Assessor 4 Unit 5 10 2 20

--------------------------------------------------------------------------------------------------------

11 42 -----------------------------------------------------------------------------------------------------------



Org 2

Assessor 3 Unit 1 2 3 6

Assessor 4 Unit 6 1 10 10

--------------------------------------------------------------------------------------------------------

13 16

--------------------------------------------------------------------------------------------------------









View 4 Replies View Related

Reporting Services :: Count Of Lookup Expression Column In Report With Multiple Datasets?

Sep 27, 2015

My report has two data sets that hold inventory from two different departments.    

ds_DeptA and ds_DeptB

I have a table, that pulls the DeptB status of DeptA record and displays it. This returns empty when the lookup fails to make a match, which is fine.  Typically means DeptB does not have the record yet.   I need to count these empty (null) feilds and populate it in a Text box outside of the table.
 
I just can't figure out the syntax with multiple datasets. I can't use the lookup expression as part of the count expression since the count expression is not contained in a table that has a dataset. 

table: ds_DeptA
fields:
ID
Name 
date_set_to_DeptB
<<Expr>> =Lookup(Fields!ID.Value,Fields!DeptA_ID.Value,Fields!DeptB_Status.Value, "ds_DeptB")

View 3 Replies View Related

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

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct
sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

View 9 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

I use SQL 2000
I have a Column named Bool , the value in this Column is  0ã€?0ã€?1ã€?1ã€?1
I no I can use Count() to count this column ,the result would be "5"
but what I need is  "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is  2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.  
thank you very much
 

View 5 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.

View 5 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

Inserted Rows Count From SSIS Not Like Table Rows Count

Jun 25, 2007

Hi all



i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................



any ideas

View 6 Replies View Related

Count(*) Vs Count(columnname)

Aug 28, 2007

 Is there a difference in performance when using count(*) or count(columnname)?

View 10 Replies View Related

SQL Query Automatically Count Month Events B4 Today; Count Events Today + Balance Of Month

Mar 20, 2004

I would like to AUTOMATICALLY count the event for the month BEFORE today

and

count the events remaining in the month (including those for today).

I can count the events remaining in the month manually with this query (today being March 20):

SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))

Could anyone provide me with the correct syntax to count the events for the current month before today

and

to count the events remaining in the month, including today.

Thank you for your assistance in advance.

Joel

View 1 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







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