MS SQL Server :: Inscope Evaluates To False On Second Row Group (totals Column)

Reporting Services :: Calculating Grand Totals From Group Totals

May 9, 2015

I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.

Example:
...
....
Group1 Value
10
20
Sub Total 1: 30

Group2 Value
15
25
Sub Total 2: 40

Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?

View 5 Replies View Related

Is There A Way To Group Sub Totals?

Nov 14, 2007

Hi,
Is there a way to display the sum of a group of a field?
I've created a group, but when I put the expression of SUM(Field) in the group footer, it gives me the total of Field for the whole dataset.
Is there a way I can display the just the Totals of the Groups?

so if my data looks like :

a | 1
a | 2
a | 3
b | 4
b | 5

b | 6

I want to display :

a | 1
a | 2
a | 3
Total a | 6
b | 4
b | 5

b | 6

Total b | 15

but instead, when i add the SUM expression into the footer group, I get :

a | 1
a | 2
a | 3
Total a | 21
b | 4
b | 5

b | 6

Total b | 21

View 8 Replies View Related

Minimizing Penalty (weighted Sum Of False Positives Plus False Negatives)

May 25, 2006

I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.

How can I enforce a different penalty for a false positive versus a false negative ? (I am assuming that in some sense the mining algorithms can then minimize the total penalty).

View 5 Replies View Related

Group Totals On Last Page - How?

Mar 27, 2007

I have a report that groups by dept #, job code and earnings code.

9999 Administration

033 Secretary

200 Regular Pay 44.00 1000.00

300 Sick Pay 8.00 25.00

400 Overtime 3.00 75.00

8888 Janoitorial

055 Janitor

200 Regular Pay 24.00 800.00

300 Sick Pay 4.00 15.00

400 Overtime 1.00 45.00

On the last page of my report I want to sum the earnings totals by earnings number. For Example:

Totals

200 Regular Pay 68.00 1800.00

300 Sick Pay 12.00 40.00

400 Overtime 4.00 120.00

Can this be done?

View 5 Replies View Related

Group Footer With Totals

Nov 30, 2007

I have a tabular report with grouping on the following fields:

Grouped rows: MDC, DrgDesc, ChronicOther
Detail row: Cases

The desired output follows:

MDC1
DrgDesc1
Chronic 50
Other 25
Total cases for DrgDesc1= 75

DrgDesc2
Chronic 20
Other 33
Total cases for DrgDesc2 = 53

etc....

I have everything working up to the Total cases for each DrgDesc. I tried adding a group footer to the Cases row, however this sub-totals for Chronic and Other rather than summing them together. Example is below.

MDC1
DrgDesc1
Chronic 50
50
Other 25
25
DrgDesc2
Chronic 20
20
Other 33
33

etc....

How can I achieve the desired result?

View 3 Replies View Related

Problem With Group Totals And Counts

Sep 21, 2007

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

View 8 Replies View Related

T-SQL (SS2K8) :: GROUP BY CUBE Aggregation - Pivoting On 2 Totals

Aug 1, 2014

I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:

SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]

[Code] .....

Basically this is used to work similar to a Pivot table in excel. My data will look as follows:

Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317

The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8

If I change the FROM select clause to use SUM instead of COUNT

SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)

It will return the correct Total amount but not the right numbers for the Attempt groupings...

View 1 Replies View Related

Reporting Services :: Group Totals From Report Items

May 18, 2015

I have a table with a row group "Sales Area" that lists customers per sales area. There is one column with the sales per customer and another column with the planned sales per customer.A third column "Under Plan" is a simple calculation that compares the two Report Items of the sales to the plan and puts a 1 there if plan is higher. My issue is how to get the total of the group "Sales Area", to display the group total of all customers that are under plan. SSRS doesn't let me use aggregate functions on group totals;Unfortunately I cannot pre-calculate the "Under Plan" figure in the query, since this example is a simplified overview (the customers is a distinct count for example...)

View 5 Replies View Related

Reporting Services :: Adding Group Totals In SSRS

Oct 27, 2015

I have a field on my report that uses the following expression to determine the commission amount for each order line. It works correctly to get the commission amount for each line, however, I need to get a total of the commission amount for each Salesperson.

My report is grouped in the following manner:

Salesperson, Type of Sale, Invoice Number, then the detail invoice line items (where the formula below reside). How can I get the totals for the Salesperson and the Type of Sale?

=IIF(Fields!PartIsSerialized.Value=True, (Sum(Fields!OrderLineSubtotal.Value)/Fields!Quantity.Value),Sum(Fields!OrderLineSubtotal.Value))
*
IIF(
Fields!TransactionType.Value Like "*USED*", (Parameters!CommissionRateUsed.Value*.01),

[Code] ....

View 2 Replies View Related

Sql 2000: Does Float Column Allow 0 When Allow Null=false

Oct 11, 2007

Hi,
I have a float column with Allow Nulls= false
Is it normal that when I try to set the column to 0, i get the error:

Cannot insert duplicate key in object

Please note that the column is part of a primary key and that there is no record with the value of the column 0 in the table in question.

Thank you.

View 5 Replies View Related

Report Services 2000 Totals On Group Are Incorrect. Summing Duplicate Values

Dec 19, 2007

Hello Everyone
I've created a report with a simple dataset that is similar to this

City , RequestID, Request Amount, ClaimID, ClaimAmount
El Monte 791 52,982.00 2157 41,143.75
El Monte 3691 11,838.00 3140 8,231.14
El Monte 3691 11,838.00 3141 990.00
El Monte 3691 11,838.00 3142 2,615.00

So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:

For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.

I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.

View 6 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName   Clm ABC-X

X

Page2:

GroupName   Clm ABC-Y

Y

Page3:

GroupName   Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

Sum Totals Per Column

May 27, 2002

--Select Capital_Amount + Interest_Amount + Insurance_Amount + Admin_Fee
--from Loan Where loan_no = '9110001AA667'
--Select top 3* from loan

Select sum(Capital_Amount) As Capital_Amount from Sales
Select * from Sales

How can l run these two Queries in the same view. l want to display what l have in the salesNew View and at the same time sum all the amount columns.But l'm not
being successful. Is it achievable or l have to have two separate views?

CREATE View SalesNew
AS
SELECT DISTINCT
tr.Transaction_Date,
tr.Loan_No,
n.Store,
n.User_Issued,
n.LoanBook As Company,
p.Product,
n.Capital_Amount,
n.Interest_Amount,
n.Insurance_Amount,
n.Admin_Fee,
n.Total_Amount
FROM Transaction_Record tr
INNER JOIN Loan n
ON tr.loan_No = n.loan_No AND tr.loan_No = n.loan_No
INNER JOIN Product p ON n.product = p.product

--2nd query

Select n.loanbook As Company,Sum(n.Capital_Amount) As Capital_Amount,
Sum(n.Interest_Amount) As Interest_Amount,
Sum(n.Insurance_Amount) As Insurance_Amount,
Sum(n.Admin_Fee) As Admin_Fee,
Sum(n.Total_Amount) As Total_Amount
From Loan n
Group By n.loanBook with RollUp

View 2 Replies View Related

Calculat Running Totals Column

Jul 13, 2006

Hi I have a table with the following structure:

UserID__DateJoined

I want to produce a query that shows on a month/year bases the number of users joined for each month AND show a running total of membership, I'm almost there....just got a problem getting the running totals:

this is what I have so far:

SELECT DATEPART(MONTH, DateJoined) AS Month, DATEPART(YEAR, DateJoined) AS Year, COUNT(UserID) AS NuAccounts
FROM Accounts_Users
GROUP BY DATEPART(MONTH, DateJoined), DATEPART(YEAR, DateJoined)
ORDER BY DATEPART(YEAR, DateJoined), DATEPART(MONTH, DateJoined)

How do I produce a column that shows the running total of accounts?

thanks for any help

View 6 Replies View Related

Reporting Services :: Row Group Totals On A Drill Down Report Builder Report

Oct 28, 2015

I have a report builder drill down report. I have row groups with totals. It looks like the attached. The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is

= Switch (
MID(Fields!protocol_id.Value,1,7)="THERAPY",
Sum(IIF(Fields!status.Value = "CO", CDbl(Fields!TX_CO.Value), Nothing)),
MID(Fields!protocol_id.Value,1,7) = "GENERAL" and MID(Fields!program_id.Value,1,6)
= "INTAKE", Sum(IIF(Fields!status.Value = "CO",

[code]...

Is there any way to not display the expression in the Total columns unless the report is expanded?

View 2 Replies View Related

Daily Totals In Column Format Using A Stored Proc

Mar 27, 2008

I am usings transact sql..

Using a stored proc. I need a generic starting point. I am trying to get order categories ie. oranges, bananas, pears on the horizontal and then the daily totals vertically for each day of the month. Just say the # under ea is the total of boxes received daily. The total under each category would be determined by the receipt date. Also
I need a total category for each fruit under each as month-date and year-to-date. This should be updated daily. Obviously my actual prog is more complicated then this but I just need an idea where to start.
Product Oranges Bananas Pears Total Fruit
03/1/08 1 2 5 8
03/2/08 0 2 1 3
03/3/08 4 0 1 5
mtd
ytd

View 2 Replies View Related

Reporting Services :: Sort Report By Totals Column

Jan 4, 2012

I've created an SSRS report in Report builder and I'm displaying it in SharePoint 2010. What I would need to do is to sort according to my totals -column. My report structure is as follows:

The first and column second columns are the ID and the name of a customer. There are over 35.000 consolidated customers in the database.

The third column is the column which I would like my report to be sorted upon. It gives the sum (Amount_EUR) of all different productlines sold to the customer.

The fourth column in this report design model is the product groups sold to that customer. There over twenty of product groups visible when Running the report.

This is what my report looks like when finished (you'd have to scroll a lot to the right if I took the whole of the report so I cut only a part of it to be visible):

I tried to add a sorting option from the reports Tablix properties and adding this code as the sorting function:

=SUM(Fields!Amount_EUR.Value)

But I'm getting an error: "A sort expression for the tablix 'Tablix1' includes an aggregate function. Aggregate functions cannot be used in data row sort expressions."

Ok, so no aggregate functions aren't allowed to the sort.

If I just put:

=Fields!Amount_EUR.Value and sort by that from Z to A(biggest to largest) it doesn't have any effect. The report is still sorted alphabetically by the ParentID.

I've also tried some other code bits, but they all seem to be saying that I couldn't put an aggregate function into the sort expression....but my Total -column is already calculated with a Sum -function....

Is it possible sort my report based on that Total -column? If yes, how? I'm using SSRS 2008

View 4 Replies View Related

Running Totals In A Report Builder Column Chart

Mar 9, 2006

I've created a Report Builder Column chart with months as the x-axis
(category) and revenue as the y-axis (value). This correctly displays
the revenue for each month, but I'd like to display a running total of
the revenue for the year.

I'm currently displaying:
Jan $2
Feb $1
Mar $4

I want to display:
Jan $2
Feb $3
Mar $7

I know this is possibly using the RunningTotal formula in Reporting
Services, but I'd like to do this in Report Builder so the users can
change and create their own graphs with running totals. Is there a way to do this in Report Builder?

Thanks.

View 1 Replies View Related

Year (date) Totals Comparing To Column Values In Same Table?

Jan 31, 2014

how to write condition for self table year records, such 2012 name and acctno match with 2013 name and acctno then total, provided below,

create table #tab1 (MasterKey int, AcctNo varchar(12),name varchar(25), SumaofShares numeric, request_dat datetime )
--drop table #tab1
insert into #tab1 values (1000, 100,'Tom', 2500, '10/01/2012')
insert into #tab1 values (1001, 101,'Bat', 1550, '08/11/2012')
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
insert into #tab1 values (1003, 103,'Vat', 1750, '04/15/2012')
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')

[code]....

i would like to get 4 columns output

how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values,

MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key

so the request is

need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below

1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts UN Veriverted'
2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts Veriverted'
3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1)
then '2012 and 2013 accts NONVERT'
4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 last year accounts'
5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1)
then '2012 This year accounts'

for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as

insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')

for ex 2)

insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')

for ex 4) 2012 records there is not match acctno and name in 2013 recods

insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')

for ex 5) 2013 records there is no match of name and acct no with 2012 records

insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013')
insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013')
insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013')
insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')

Expected Results (just for format)

AcctTypeDescription,SumofShares, OtotalutStand
'2012 and 2013 accts UN Veriverted',2700,234
'2012 and 2013 accts Veriverted' ,2890,234
'2012 and 2013 accts NONVERT' ,4533,325
'2012 last year accounts' ,2334,567
'2012 This year accounts' ,2222,877

View 9 Replies View Related

Trying To Get Daily Totals From Cumulative Totals In A Pivot

Oct 2, 2006

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00

etc.

As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:

Date_WorkDay_GasSales_EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00

etc.

To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:

1_2_etc.

300.00 95.00 etc.

So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])

as

(

SELECT

datename(month, cag.date),

cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]

FROM CAG INNER JOIN

Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by cag.date, cag.WorkDay

)

select * from SalesCTE

pivot

(

sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers? Thanks in advance!!!

P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

View 12 Replies View Related

Conditional Split - Expression Evaluates To Null

Sep 5, 2007

Hi everyone!

I'm using a conditional split to discriminate modified records. My expression looks like this:
col1_source != col1_dest || col2_source != col2_des.....and so on. I use OLE DB Command afterward to update modified records.

It all works fine if no columns evaluate to null. If any (source or dest.) evaluates to null, component fails.

Any tips how to solve a problem?

It has to work like this:

If colX_source is null and colX_dest is not null --> Update
If colX_source is not null and colX_dest is null --> Update
If both colX_source and colX_dest are null --> No update

p.s. i apologize if a similar thread exists, I haven't found something of use to me.

View 13 Replies View Related

SQL 2012 :: SSRS Average Column Group Value For Row Group

Feb 28, 2014

I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:

Year
Month
Date

And the following row groups:

Region
Product
SubType (hidden, data at the date level is summed to Product)

At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)

View 0 Replies View Related

Getting Sum Of Certain Groups Using Inscope

May 16, 2008

In my report I have 3 row groups and two column groups as follows, where measure is number of hours for a given project.

2008/05 2008/06 ........
Demand | Allocated Demand | Allocated
- Project 1 1500 1000

- Department1 500 400

- Employee1 200
- Employee2 200

- Department2 1000 600

- Employee3 300
- Employee4 300

+ Project 2 1200 1000
-------------------------------------------------------------------------------------------------------------------------

Grand Total 2700 2000

My question is, is this possible using a Matrix? I have used matrix in some of my reports but have always used automatic/generic SUM feature it provides.

Note carefully that there are no values for Employees in Demand Column Group. Basically I would have to SUM Department groupings to get the Project level total and then SUM all Project Groupings to get the grand total. The Allocation column is okay since the Employees have values in that column. Can this be done using InScope( ) or some other function rather than simply doing the Generic SUM of Rows and Columns of the matrix which would probably throw error when trying to sum a blank value in demand column.

Thanks in advance.

View 2 Replies View Related

InScope For Two ColumnGroups

Aug 7, 2007

Hello!
I have the following structure

Task | User | Value 1 | ..... | Value N | InScope Task | InScope User
--------------------------------------------------------------------------------------------------------------
Task1 User1 0 5 true false (!)

User2 5 3 true false (!)
Subtotal 5 8 true false
Task2 User1 1 5 true false (!)
User2 4 5 true false (!)

Subtotal 5 10 true false
Total 10 18 false false

The last two columns show in which row group the rows are. I have two groups "Task" and "User". I need to define for my purposes if the value are belong to user rowgroup. Unfortunatley =InScope("User") always return false. How I can define if the row value belongs to "User" row group??

View 2 Replies View Related

Inscope Problem

Apr 20, 2007

Hello,

I got a matrix with two group columns, G1 and G2

I have totals for the innermost group G2

Article Size Stock Sales S/S

Shirt 31 1 2 2

33 2 1 0.5

...

Total 3 3 (I dont want a value here)

So i did =IIF(INSCOPE("G2"),Sale/Stock,nothing)

The problem is that all the size lines are considered outside the G2 group so i get nothing in all rows insted of only have nothing on the total.

I have installed SP2 of SSRS 2005

If i use Inscope("G1") it works, but i need to work with the G2

Is this a bug? Or am i doing something wrong?

Thank you

View 3 Replies View Related

SS2005 Standard (RTM && SP1 CTP1) Evaluates Sub-queries In Wrong Order

Mar 24, 2006

We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.

However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.

My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ?
I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.

However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.

CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))

INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')

-- Note Lack of any non-numeric rows
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and
-- then querying that also gives the same error

DROP TABLE #Problem

I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!

View 5 Replies View Related

Update Trigger: COLUMNS_UPDATED Evaluates To True For All String Fields

Jul 14, 2006

I have a trigger that writes changes to an audit table. In the case of Updates, I only want to write out the fields that have actually changed. My code for COLUMNS_UPDATED evaluates to true for all varchar fields, even when they haven't changed. All other scenarios appear to be working correctly.

WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1

IF ((@TriggerType = 'I') OR (@TriggerType = 'D') OR (@TriggerType = 'U' AND substring (COLUMNS_UPDATED(),@char, 1) & @bit > 0))
BEGIN..........

END

END

Ideas? Thanks!

View 1 Replies View Related

SSRS - Using Inscope With A Matrix

Jun 15, 2015

I have a simple matrix. The row group is schoolname. The column group is tweek. and the data field is thours (sum(thours)). on the right side of my matrix, I want to display the Average hours for all the week columns. SSRS can do a sum very simply, but when I use the avg function, I get erroneous results. Do I somehow use the inscope function in the data field ?? I just don't know. and I don't understand how to use the inscope function.

View 2 Replies View Related

Using InScope() For Customize Subtotal

Jan 22, 2008

Hello All:

I have the following data:

2000 2001

Child Teen Adult Child Teen Adult
Region1 25 40 35 33 55 12
Region2 50 10 40 20 10 70

Total 75 50 75 53 65 82

and I need the following data

2000

Child Teen Adult Child%
Region1 25 40 35 25/(25+40+35)
Region2 50 10 40 50/(50+10+40)

Total 75 50 75 75/(75+50+75)

I was able to get the Child% column and Total row, except for the cell (75/(75+50+75) ) using InScope() operator.
Can any one help me in this regard.

Thanks,
Vishnu

View 4 Replies View Related

Bug With InScope() And Some Custom Code

May 30, 2006

Hi guys,

i was developing some custom code to do a running total in a matrix, and i have noticed some odd behaviour with the InScope function. I am doing year on year reporting, so i have two row groups on my matrix: the first is on month (matrix2_Calendar_Month), the second on year (matrix2_Calendar_Year).

I needed to total the number of days covered by the months i was reporting on, so i wrote some very standard code to do this, along with an expression in that column of the matrix:

=IIf(
InScope("matrix2_Calendar_Year"),
CStr( Round( Sum(Fields!Sales.Value / (24 * Code.AddDays( CStr(Fields!Calendar_Month.Value), CInt(Fields!Calendar_Year.Value))), 2)) ,
Code.getBounds()
)

Code.AddDays() calculates and returns the number of days in the month of that year on that row. Code.getBounds simply returns the lower and upper bounds of the array, plus its contents (so i can inspect them). This is what is returned in the report:

Month / Year

Sales

Capacity

% Capacity

Avg $/h

February

2006

3842

7706

49.86%

2.86

2007

0

0

0.00%

0

March

2006

4949

8692

56.94%

3.33

2007

0

0

0.00%

0

April

2006

5160

8154

63.28%

3.58

2007

0

0

0.00%

0

May

2006

3309

8348

39.64%

2.22

2007

0

0

0.00%

0

Total

17259

32900

52.46%

0-8*28,28,31,31,30,30,31,31,28

If you look at the output in the total row, you will see that Code.AddDays() has been called one extra time at the end,  with Feb 2006 as its parameters, thus adding an extra 28 days to the running total. Why is Code.AddDays called on the total row, when i should be out of the scope of both the row groups? (Note: this happens for whichever row group i use in the InScope check in the expression).

Here is the custom code used for all this:

Dim numDays()

Public Function AddDays(ByVal month As String, ByVal year As Integer) As Integer
    Dim thisMonth As String

    Dim upper As Integer
    upper = 0
    On Error Resume Next
    upper = UBound(numDays) + 1
    ReDim Preserve numDays(upper)

    thisMonth = CStr(year) & "-" & month & "-01"
    numDays(upper) = DateDiff("d", CDate(thisMonth), DateAdd("m", 1, CDate(thisMonth)))
    AddDays = numDays(upper)
End Function

Public Function TotalDays() As Integer
    Dim lower As Integer
    Dim upper As Integer

    lower = 0
    upper = 0
    On Error Resume Next
    lower = LBound(numDays)
    upper = UBound(numDays)

    TotalDays = 0
    Dim ii As Integer
    For ii = lower To upper
        TotalDays = TotalDays + CInt(numDays(ii))
    Next
End Function

public function getBounds() as string
getBounds = Cstr(LBound(numDays)) & "-" & CStr(UBound(numDays)) & "*" & Join(numDays, ",")
end function

sluggy

View 5 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

Transact SQL :: Select Row With Max (column Value) Group By Another Column

May 19, 2015

i dont't know how to select row with max column value group by another column. I have T-SQL

CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (

[code]....

View 3 Replies View Related