How to do this conditional(maybe?) query

I'm trying to figure out how do a particular query.

Given that I know how to get the following results from a query (details below):

client1, MT
client2, WA
client3, MT
client3, WA
client3, ID
...

How do I fashion a query that, when a client is listed in more than one state (like client3), the query will only return a single record (instead of three) but print 'MULTIPLE' instead of listing the specific states.

<details>
Here's the set up. I have a table of clients (tblClients), a table of US states (tblStates) and 'linking' table (tblClientState) to id which states a particular client operates in. The reason for the linking table is that a client can operate in one or many different states.

The tables look like this:

tblClients:
client_id, client_name

tblStates:
state_abbrv, state_name

tblClientState:
client_id, state_abbrv

An example query I'm working with is to return a list with the client_name and the state_abbrv.

SELECT client_name, state
FROM tblClients JOIN tblClientState ON tblClients.client_id = tblClientState.client_id

This would return a result like this:

client1, MT
client2, WA
client3, MT
client3, WA
client3, ID
...

Again, here's my question:

How do I fashion a query that, when a client is in more than one state (like client3), the query will only return a single record (instead of three) but have it say 'MULTIPLE' instead of listing the specific states.

It seems like I might be able to use some conditions in my SELECT statement but I can't figure out how to make it all happen.
</details>

Thanks!

Eric LundTry using CASE:

SELECT client_name,
CASE WHEN COUNT(*) = 1 THEN MAX(state) ELSE 'MULTIPLE' END
FROM ...
GROUP BY client_name;|||Wow! Perfect!

That's just the kind of simple, sweet answer I was hoping I would get out of this group!

I was able to take your suggestion and build on it a little bit (because of course, my example was a little over simplified) and I got just EXACTLY what I was looking for.

Thanks a LOT. I really appreciate it!

Eric

How to do this calculation?

I have 3 tables , these table contain field as follow

- Dim_Product

- DecVolume

- Dim_Category

- DecVolume

- Fact_SalesTrx

- DecUnitPrice

If i would like to calculate a Price Per Unit with this formula, how should i do it?

Price Per Unit for specific Unit of Measurement (UOM)= (Unit Price/ UOM for that product) * UOM for that Category

I had try to do in the Calculation tab of the cube, but the result generate is weird (= Infinity).

This is the script that i had try

--

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Price Per Unit]

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume])*[Dim Category].[Dec Volume],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Dec Unit Price] },

VISIBLE = 1 ;

--

Appreciate some one can leave some clue here for me to go on...

Thanks

Woon How

You can do that inside datasourceview as named calculation!

NamedCalculation_PricePerUnit = (UnitPrice/DecVolume)*DecVolume

Try it.

Helped?

regards!

|||

Assuming that the "Value Column" Source and DataType have been correctly configured for the [Dim Product].[Dec Volume] and [Dim Category].[Dec Volume] attributes, you could try .MemberValue, like:

Code Snippet

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume].MemberValue)

* [Dim Category].[Dec Volume].MemberValue,

|||

Thanks for your hlp

Do you mean:

"Value Column" - mean insert some data in? (I'm had put some value for this column)

"Data Type: - mean set the data type ? )(I'm created it in design and set it to decimal)

I try had you approach and result return "#VALUE!". and i highlight the cell, it mention type mismatch.

Any idea?

Thanks In Advance Deepak Puri..

|||

Thanks PedroCGD,

If doing in this way, i would need to create another 2 columns into the Fact_SalesTrx Tables (decVolume Of Product,DecVolume Of Category)

Would that be a redundacy? since i had the columns in the Dim_Product, Dim_Category. And what should be the best way to design this, sorry im new in SSAS, so a lot of stuff im not sure.

|||

It's easier to illustrate this with a sample Adventure Works query - note that the .MemberValue of [Promotion].[Min Quantity] and [Product].[Reorder Point] attributes is only meaningful at the appropriate granularity of those dimensions:

Code Snippet

With

Member [Measures].[WeightedQuantity] as

iif(IsEmpty([Measures].[Order Quantity]) or

[Promotion].[Min Quantity].MemberValue = 0,

Null,

([Measures].[Order Quantity] /

[Product].[Reorder Point].MemberValue)

* [Promotion].[Min Quantity].MemberValue),

FORMAT_STRING = '#,0.0'

select

{[Measures].[WeightedQuantity]} on 0,

Non Empty [Product].[Product].[Product]

* [Promotion].[Promotion].[Promotion] on 1

from [Adventure Works]

How to do this ?

I want to do a win app or web app that has many reports, I do not want to use CR Service, I want to try SQL Server 2005 Reports, please is anyone who can tell me how to do this, or can send me any link telling me anything in this directon ?

I really appreciate your help !

Sincerely

The first place to start is the Microsoft site.

http://www.microsoft.com/sql/technologies/reporting/default.mspx

|||Hi,
Thnx Brad
Can u suggest me any book speaks about creating reports in VS.NET (2003 or 2005) using SQL Report Services (SQL 2000 or 2005) .
I value your help!

Best regards|||For RS 2000 the book "Microsoft Reporting Services in Action" by Teo Lachev is very good.

how to do this "between dates" query?

Hi,

I have a query as follows:

SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @.Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded')
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)

the results look like this

WeekRetailTotal

MonthRetailOrderNo

SalesWeek

SalesYear

￡397.55

8

3

2002

etc etc for each week in a year and then it goes onto the next year.

What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.

I've tried to do a WHERE DATEPART(wk, OrderDate) > @.StartDate AND DATEPART(wk, OrderDate)
< @.EndDate AND YEar(OrderDate) > @.StartYear AND YEAR(OrderDate) < @.EndYear

But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.

I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.

Any ideas?

Thanks

It is common for Date query questions to receive the suggestion of using a Calendar table to assist. In this case it would help as you could turn your Year/Week pairs into dates and then do a simple date range comparison.

Try this approach, using only date functions:

Where DateDiff( wk, DateAdd(yy, @.StartYear - 1900 , '1/1/1900' ), OrderDate ) >= @.StartWeek

And DateDiff(wk, DateAdd(yy, @.EndYear - 1900, '1/1/1900' ), OrderDate ) < @.EndWeek

The first comparison needs to be >= because the first day of the year is in the first week of the year -- the comparison gets off by one. The end should be okay. I used DateAdd to get the first day of each year, you could use another approach like string concatenation.

|||

There is native BETWEEN-AND syntax support in SQL Server. The bigger issue here is you need to consider day of week, i.e. you want the date range to be between the first day of the start year/week and the last day of the end year/week (inclusive on both ends):

Here is my approach (working sample against Northwind database):

DECLARE @.StartWeek INT, @.EndWeek INT, @.StartYear INT, @.EndYear INT

SET @.StartWeek = 50;

SET @.EndWeek = 2;

SET @.StartYear = 1996;

SET @.EndYear = 1997;

DECLARE @.StartDate DATETIME, @.EndDate DATETIME;

SET @.StartDate = DATEADD(week, @.StartWeek-1, '1/1/' + CAST(@.StartYear AS VARCHAR(4))) -- get into the @.StartWeek-th week

SET @.StartDate = DATEADD(day, 1-DATEPART(weekday, @.StartDate), @.StartDate) -- get the First Day (Sunday by default) of that week

SET @.EndDate = DATEADD(week, @.EndWeek-1, '1/1/' + CAST(@.EndYear AS VARCHAR(4))) -- get into the @.EndWeek-th week

SET @.EndDate = DATEADD(day, 7-DATEPART(weekday, @.EndDate), @.EndDate) -- get the Last Day (Saturday by default) of that week

--SELECT @.StartDate, @.EndDate

SELECT orderdate, DATEPART(year, orderdate) AS Year, DATEPART(week, orderdate) AS Week, DATEPART(weekday, orderdate) AS DayOfWeek FROM orders

WHERE OrderDate BETWEEN @.StartDate AND @.EndDate

|||thanks guys! - I'll give it a go!!

How to do this

Hi All,
I have a table Called MailList with 2 columns namely MailId and Message.
There can be many messages related to one email id in the table. I need to
consolidate all the messages into a single message and send the mail to that
id.
(It may or may not exceed the limit of 8000 chars).
For Example,
MailId Message
a@.x.com XXXXXXXXXXXXXXXXXXXXXX
a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
c@.z.com BB
a@.x.com CCC
I need to concatenate the messages of the first, second and fifth record
into one single message and send the mail to a@.x.com. Similarly for the othe
r
two.
How can I do this. Please help me at the earlist.
Sevugan.CYou have to do this in a loop. Wheter you do it in a temporary table or
within a cursor belongs to you coding style.
HTH, Jens Suessmeyer.|||use northwind
-- your problem looks like this --
select supplierid, productname
from products
-- your problem looks like this --
-- solutions--
create proc concatenate
(
@.supplierid Varchar(10)
)
as
select IDENTITY(int, 1,1) AS ID_Num,
Productname into #tempx from products where
supplierid=@.supplierid
declare @.ctr int
declare @.concatenated varchar(5000)
declare @.maxrecord int
select @.maxrecord=max(id_num) from #tempx
select @.concatenated=''
select @.ctr=0
while @.ctr <> @.maxrecord
begin
Select @.ctr=@.ctr+1
Select @.concatenated =@.concatenated+Productname
from #tempx where id_num=@.ctr
end
select @.concatenated
--test solution --
exec concatenate 7
try to improve from there...
thanks,
joeydj
"Sevugan" wrote:

> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to th
at
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the ot
her
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||Which is the PK? You want message to be XXX...YYYY...CCC or
CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
helpful for us.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to
> that
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the
> other
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||It is a temporary table. The Message is the primary key.
I want the message to be xxx...yyy...ccc...
--
Sevugan.C
"Perayu" wrote:

> Which is the PK? You want message to be XXX...YYYY...CCC or
> CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
> helpful for us.
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
>
>|||Hi
My problem here is I need to store more than 8000 characters which is not
permitted in VARCHAR variable.
If the no.of records in the table are more than 8000 chars how can I store
that?
--
Sevugan.C
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> use northwind
> -- your problem looks like this --
> select supplierid, productname
> from products
> -- your problem looks like this --
> -- solutions--
> create proc concatenate
> (
> @.supplierid Varchar(10)
> )
> as
> select IDENTITY(int, 1,1) AS ID_Num,
> Productname into #tempx from products where
> supplierid=@.supplierid
> declare @.ctr int
> declare @.concatenated varchar(5000)
> declare @.maxrecord int
> select @.maxrecord=max(id_num) from #tempx
> select @.concatenated=''
> select @.ctr=0
>
> while @.ctr <> @.maxrecord
> begin
> Select @.ctr=@.ctr+1
> Select @.concatenated =@.concatenated+Productname
> from #tempx where id_num=@.ctr
> end
> select @.concatenated
> --test solution --
> exec concatenate 7
>
> try to improve from there...
> --
> thanks,
> --
> joeydj
>
> "Sevugan" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

>From your vague narrative, you need a column that gives you the message
order.
Finally, you need to learn RDBMS and *basic* tiered architecutre
concepts. This is a display issue and shjould be done in the front
end, never in the database.|||Why can't you use Text column?
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
> Hi
> My problem here is I need to store more than 8000 characters which is not
> permitted in VARCHAR variable.
> If the no.of records in the table are more than 8000 chars how can I store
> that?
> --
> Sevugan.C
>
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||If message is the PK, so you have no control which one will be the first
one.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:B76CCD95-E2F9-4315-BA77-D2B9C614CE76@.microsoft.com...
> It is a temporary table. The Message is the primary key.
> I want the message to be xxx...yyy...ccc...
> --
> Sevugan.C
>
> "Perayu" wrote:
>|||How would I pick the value from that text column to make use of that in the
xp_sendmail procedure call.
Exec Master..xp_sendmail @.message = <how to bring the value of the text
column here>
Sevugan.C
"Perayu" wrote:

> Why can't you use Text column?
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
>
>

How To Do this

I created some DropDownList's Dynamically based on the records in the Products table of Northwind Database Ie
Dim StrConn As String = ConfigurationManager.ConnectionStrings("....").ConnectionString
Dim MyConn As New SqlConnection(StrConn)
Dim MyAdapter As SqlDataAdapter
Dim MyDataSet As DataSet
Dim MyComm As SqlCommand
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim StrSelect As String = "Select ProductId,ProductName From Products"
MyAdapter = New SqlDataAdapter(StrSelect, MyConn)
MyDataSet = New DataSet
MyAdapter.Fill(MyDataSet, "Products")

For Each Dr As DataRow In MyDataSet.Tables("Products").Rows
Dim Drp As New DropDownList
Drp.ID = "Drp" & Dr.Item(0)
Drp.DataSource = MyDataSet.Tables("Products")
Drp.DataTextField = "ProductName"
Drp.DataValueField = "ProductId"
Drp.DataBind()

PlcConterol.Controls.Add(Drp)
Next
End Sub
I tried doing the samething using the DataReader but it creates only one DropDown Ie

While MyReader.Read
Dim Drp as New DropDownList

Drp.Id="Drp"

Drp.DataSource=MyReader

Drp.DataTextField="ProductName"

Drp.DataValueField="ProductId"

Drp.DataBind

End While
Thanks

Hi websyd,

I'm not quite sure why you need to put the databinding process in a loop.

Are you trying to put every row into the dropdownlist item collection? If so, please forget about the loop. When you have set the DataSource, DataTextField and DataValueField, the binding process will put everything into the collection automatically.

So please change to

Drp.DataSource = MyDataSet.Tables("Products")
Drp.DataTextField = "ProductName"
Drp.DataValueField = "ProductId"
Drp.DataBind()

And for DataReader, you can use

Dim Drp as New DropDownList
Drp.Id="Drp"
Drp.DataSource=MyReader
Drp.DataTextField="ProductName"
Drp.DataValueField="ProductId"
Drp.DataBind

HTH.

|||

Hi thanks 4 d response. I looped because i needed to create the DropDowns based on the number of rows in the table. So if i remove the code from the loop i will get only one DropDownList as your code suggests.

With the DataReader, again this will create only one DropDownList but i need to create to more than one.

Thanks

|||

Hi Websyd,

The DataReader only created one DropDownList because the DataReader can only be used once in data binding.

This is because when bound, the control will have the DataReader read to the end to populate the items. As you know, the DataReader is forward-only and cannot be used again for reading. That's why only one control is filled with data.

How to do these joins

Pardon my newbieness.
I have four tables to join. I'll just call them Table1, Table2, Table3
and Table4.
Table1 is the main table. I want to do outer joins pointing from fields
in Table1 to fields in each of the other tables. So all records in
Table1 regardless of whether they match in the other tables.
todSelect t1.* from Table1 t1
LEFT JOIN Table2 t2
ON t1.SomeiD = t2.someID
LEFT JOIN Table3 t3
ON t1.SomeiD = t3.someID
...
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--
"Tod" <todtown@.swbell.net> schrieb im Newsbeitrag
news:1115757100.280539.176950@.o13g2000cwo.googlegroups.com...
> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>|||select * from
Table1 left join Table2 on Table1.fld1 = Table2.fld1
left join Table3 on Table1.fld1 = Table3.fld1
left join Table4 on Table1.fld1 = Table4.fld1
"Tod" wrote:

> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>|||Conceptually. each "Join" is a join between only two "Relations", or
"Resultsets". When you have more than two tables in a From Clause, and,
therefore, you have two or more joins. the second "Join" that takes place ca
n
be thought of as a Join between the intermediate resultset created by the
first join, and the third table. So the answer to your question depends on
what order, and what exactly, you wish to Join in this second Join... (and
then the third Join...)
Two possibilities exist:
You could Join Tables B to A, using Outer Join syntax, and then Join C to
that resultset, also using Outer Join SyntAX...
From TableA
Left Outer Join Table B On .....
Left Outer Join Table C On ......
Or 2) you might be wishing to Join the COmbined Inner Join of Tables B & C
to Table A. In this case you would be joining B & C FIrst, and then Joining
THAT resultset to TableA using Outer Join Syntax
From TableA
Left Outer Join (Table B Join Table C On ....)
On ....
This approach might be used to get ALL Customers, (even the ones with no
Invoices), plus the data from a Invoices and connected Invoice Details table
s
, but only include invoices that have details...
"Tod" wrote:

> Pardon my newbieness.
> I have four tables to join. I'll just call them Table1, Table2, Table3
> and Table4.
> Table1 is the main table. I want to do outer joins pointing from fields
> in Table1 to fields in each of the other tables. So all records in
> Table1 regardless of whether they match in the other tables.
> tod
>

How to do the sorting sequence in numbering order

dear folks,
I would like to know that how to do the number sorting in numbering sequence ?
In order perform a sort you need to use the ORDER BY clause. If the datatype
is a numeric datatype the sort will be performed as a numeric sort ie. 3
comes before 21. However if the dataype is a character datatype a character
sort will be performed ie. 21 comes before 3. Below is an illustration of
this:
CREATE TABLE nums
(
i INT,
data VARCHAR(20)
)
INSERT nums SELECT 1, 'a'
INSERT nums SELECT 3, 'b'
INSERT nums SELECT 21, 'c'
INSERT nums SELECT 2, 'd'
SELECT *
FROM nums
ORDER BY i
Returns :
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
CREATE TABLE chars
(
i VARCHAR(10),
data VARCHAR(20)
)
INSERT chars SELECT '1', 'a'
INSERT chars SELECT '3', 'b'
INSERT chars SELECT '21', 'c'
INSERT chars SELECT '2', 'd'
SELECT *
FROM chars
ORDER BY i
Returns :
i data
-- --
1 a
2 d
21 c
3 b
(4 row(s) affected)
If the column your are attempting sort stores the number as a character then
you will need to convert the number to a numeric datatype:
ie.
SELECT *
FROM chars
ORDER BY CONVERT(INT, i)
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
- Peter Ward
WARDY IT Solutions

How to do the sorting sequence in numbering order

dear folks,
I would like to know that how to do the number sorting in numbering sequence
?In order perform a sort you need to use the ORDER BY clause. If the datatyp
e
is a numeric datatype the sort will be performed as a numeric sort ie. 3
comes before 21. However if the dataype is a character datatype a character
sort will be performed ie. 21 comes before 3. Below is an illustration of
this:
CREATE TABLE nums
(
i INT,
data VARCHAR(20)
)
INSERT nums SELECT 1, 'a'
INSERT nums SELECT 3, 'b'
INSERT nums SELECT 21, 'c'
INSERT nums SELECT 2, 'd'
SELECT *
FROM nums
ORDER BY i
Returns :
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
CREATE TABLE chars
(
i VARCHAR(10),
data VARCHAR(20)
)
INSERT chars SELECT '1', 'a'
INSERT chars SELECT '3', 'b'
INSERT chars SELECT '21', 'c'
INSERT chars SELECT '2', 'd'
SELECT *
FROM chars
ORDER BY i
Returns :
i data
-- --
1 a
2 d
21 c
3 b
(4 row(s) affected)
If the column your are attempting sort stores the number as a character then
you will need to convert the number to a numeric datatype:
ie.
SELECT *
FROM chars
ORDER BY CONVERT(INT, i)
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
- Peter Ward
WARDY IT Solutions

How to do the sorting sequence in numbering order

dear folks,
I would like to know that how to do the number sorting in numbering sequence ?In order perform a sort you need to use the ORDER BY clause. If the datatype
is a numeric datatype the sort will be performed as a numeric sort ie. 3
comes before 21. However if the dataype is a character datatype a character
sort will be performed ie. 21 comes before 3. Below is an illustration of
this:
CREATE TABLE nums
(
i INT,
data VARCHAR(20)
)
INSERT nums SELECT 1, 'a'
INSERT nums SELECT 3, 'b'
INSERT nums SELECT 21, 'c'
INSERT nums SELECT 2, 'd'
SELECT *
FROM nums
ORDER BY i
Returns :
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
CREATE TABLE chars
(
i VARCHAR(10),
data VARCHAR(20)
)
INSERT chars SELECT '1', 'a'
INSERT chars SELECT '3', 'b'
INSERT chars SELECT '21', 'c'
INSERT chars SELECT '2', 'd'
SELECT *
FROM chars
ORDER BY i
Returns :
i data
-- --
1 a
2 d
21 c
3 b
(4 row(s) affected)
If the column your are attempting sort stores the number as a character then
you will need to convert the number to a numeric datatype:
ie.
SELECT *
FROM chars
ORDER BY CONVERT(INT, i)
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
- Peter Ward
WARDY IT Solutions

how to do the same thing in mssql as mysql?

in MySql:

SELECT * FROM table LIMIT 5,10

How to do this in MicroSoftSql?

What does this do ? Does it select 10 rows from row 5 onwards ? If so, you can do something like this

select top 5 * from

(

select top 15 from table ORDER BY id ASC

)

ORDER BY id DESC

assuming that you have an index on the column called id. Of course, you need to add a third query to flip them back to ascending order, if you require that.

|||

but what if I have more than 100000 records in the table?

if I want to get 50000-50005

select top 50000 * from

(

select top 5 from table ORDER BY id ASC

)

ORDER BY id DESC

so should it be very slow?

|||

I'm not sure how big our database was, but I used this trick to do paging on a system and it was fine. That was a few years ago, SQL Server 2005 may have some tricks up it's sleeve that I don't know and didn't find in the help just now.

|||

I would like to suggest Gates, Next Version MSSQL and Access append "Limit" sentence.

*^_^!

|||

http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

Looks like there's a new way to get row numbers. I thought there may be, but my initial search did not find it.

|||

oh... thanks,

but is there a way to support Access?

Because our develop system base on asp.net+Access

|||

*grin* then you should have asked about access to start with :-)

I'd expect my first solution to work with Access, but I couldn't be sure, I don't use it.

How to do the following sql query

How to do the following using sql statements?
I would like to be able to have the following table
Everytime I insert a new record the AutoIncrementKey field will increase by one
but I want the PriKey to be in the order of the datetime as shown below.
INSERT TABLE DateTime = '1/1/2001 12:00:00'
INSERT TABLE DateTime = '31/1/2001 8:00:00'
INSERT TABLE DateTime = '18/1/2001 4:00:00'
INSERT TABLE DateTime = '21/1/2001 3:00:00'
.........
.........
DateTime AutoIncrementKey PriKey(according to date)
1/1/2001 12:00:00 1 1
31/1/2001 8:00:00 2 4
18/1/2001 4:00:00 3 2
21/1/2001 3:00:00 4 3

How to do this?"Steve" <ngsteve@.my-deja.com> wrote in message
news:976e0586.0309040836.3ccd556a@.posting.google.c om...
> How to do the following using sql statements?
> I would like to be able to have the following table
> Everytime I insert a new record the AutoIncrementKey field will increase
by one
> but I want the PriKey to be in the order of the datetime as shown below.
> INSERT TABLE DateTime = '1/1/2001 12:00:00'
> INSERT TABLE DateTime = '31/1/2001 8:00:00'
> INSERT TABLE DateTime = '18/1/2001 4:00:00'
> INSERT TABLE DateTime = '21/1/2001 3:00:00'
> .........
> ........
> DateTime AutoIncrementKey PriKey(according to date)
> 1/1/2001 12:00:00 1 1
> 31/1/2001 8:00:00 2 4
> 18/1/2001 4:00:00 3 2
> 21/1/2001 3:00:00 4 3
> How to do this?

Only with extreme difficulty because inserting a row may change the PriKey
of every other row.

EG
after
INSERT TABLE DateTime = '1/1/2001 12:00:00'
INSERT TABLE DateTime = '31/1/2001 8:00:00'

you have

DateTime AutoIncrementKey PriKey(according to date)
1/1/2001 12:00:00 1 1
31/1/2001 8:00:00 2 2

then, after

after
> INSERT TABLE DateTime = '18/1/2001 4:00:00'

you have

DateTime AutoIncrementKey PriKey(according to date)
1/1/2001 12:00:00 1 1
31/1/2001 8:00:00 2 3
18/1/2001 4:00:00 3 2

Very, very ugly stuff.

Do do this you would have to run something like

update my_table t set prikey = (select 1+count(*) from my_table where
my_date < t.my_date)

in an update, insert and delete trigger.

Very ugly stuff, and very slow and 100% guaranteed to scale poorly.

David|||> Everytime I insert a new record the AutoIncrementKey field will increase
by one
> but I want the PriKey to be in the order of the datetime as shown below.

Why do you need to STORE this data? I would write a stored procedure or
view that calculated it at select time. As David points out, you would need
triggers to do this and it would certainly kill the performance of your app.

(Also, not sure why this was posted to comp.databases.paradox?)

A|||Aaron Bertrand - MVP wrote:
> (Also, not sure why this was posted to comp.databases.paradox?)
Maybe because it's a paradox database and the queston should not have been
crossposted to the sql server groups ...|||First of all, the request for additional columns this table is redundant.
There is no information about the nature of the datetime column; is it
unique? If so, declare it as your primary key, there is no need for another
column.

CREATE TABLE tbl (
dt DATETIME NOT NULL PRIMARY KEY ) ;

If you need a numeric identifier, the prikey column will suffice & the
'autoincrementkey' makes little sense. What is the rule for serialization in
case of multi-row inserts? How do you even know the order in which row is
inserted ? A popular workaround used in t-SQL is to use an IDENTITY column
like:

CREATE TABLE tbl (
dt DATETIME NOT NULL PRIMARY KEY,
autoincr INT NOT NULL IDENTITY);

And you can have a view like:

CREATE VIEW (dt, col, incr)
AS
SELECT dt, ( SELECT COUNT(*)
FROM tbl t1
WHERE t1.dt <= tbl.dt) AS "intcol",
autoincr
FROM tbl ;

--
- Anith
( Please reply to newsgroups only )|||> > (Also, not sure why this was posted to comp.databases.paradox?)
> Maybe because it's a paradox database and the queston should not have been
> crossposted to the sql server groups ...

Well, I figured majority rules. :-)|||DateTime Mode
1/1/2001 12:00:00 1
31/1/2001 8:00:00 7
18/1/2001 4:00:00 3
21/1/2001 3:00:00 3
21/1/2001 5:00:00 3
22/1/2001 3:00:00 7
22/1/2001 8:00:00 7
23/1/2001 3:00:00 3
23/1/2001 9:00:00 5

What I actually want to do is just this, the insertion might not be in
any order or time,
for the above table,
get the total time for each mode Example
Mode 1 Duration = 31/1/2001 8:00:00 - 1/1/2001 12:00:00
Mode 7 Duration= 18/1/2001 4:00:00 - 31/1/2001 8:00:00 + 23/1/2001
3:00:00 - 22/1/2001 3:00:00
Mode 3 Duration= 22/1/2001 3:00:00 - 18/1/2001 4:00:00 + 23/1/2001
9:00:00 - 23/1/2001 3:00:00
Mode 5 Duration= CurrentTime Now - 23/1/2001 9:00:00
How should the sql statement be?
I actually wanted to get the starttime using the statement below but
how to get the endtimes?
Select DateTime, AutoIncrementKey Where Mode = 1
Select DateTime, AutoIncrementKey Where Mode = 7
Select DateTime, AutoIncrementKey Where Mode = 3
Select DateTime, AutoIncrementKey Where Mode = 5|||Steve,

I think you are not following the implications. The calculation you have
shown as :

>>
Mode 1 Duration =
31/1/2001 8:00:00 - 1/1/2001 12:00:00
Mode 7 Duration =
18/1/2001 4:00:00 - 31/1/2001 8:00:00 +
23/1/2001 3:00:00 - 22/1/2001 3:00:00
Mode 3 Duration =
22/1/2001 3:00:00 - 18/1/2001 4:00:00 +
23/1/2001 9:00:00 - 23/1/2001 3:00:00
Mode 5 Duration =
CurrentTime Now - 23/1/2001 9:00:00 <<

depends on the how the rows are being represented positionally in the table.
In other words, for you to decide which datetime value to be subtracted from
which other one, you have to rely on the position of a row in relative to
another. This cannot be, since the rows in a table are not ordered, it may
return different ordering of rows under various circumstances. You have 3
rows with mode 7 and 4 rows with mode 3. How do you decide which one should
be considered for Mode 7 & Mode 3 calculations in which order?

To do this reliably, you need to have a logical value ( loosely put, another
column which can represent the required sequence of datetime values on which
you do your calculations ) for each datetime value.

--
- Anith
( Please reply to newsgroups only )

how to do text replacements

I am trying to do a text replacement to reflect changes where I've
stored data.
A field, backup_archive_filename, contains the url path. I've since
changed the directory structure and wish to change whats stored in the
table.
Example:
\\10.0.12.110\SQLSafe\COGNOS-DEV\2005-08-29_2017m_51s_Diff_COGNOS-DEV_cm.safe
\\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Diff_TLS-D-AN001_Northwind.safe

I want to change SQLSafe to SQLSafe\Diff or SQLSafe\Full depending when
there is either %Diff% or %Full% in the string to reflect the change in
the directory.

I wanted to do something like:
update backups_sets
SET backup_archive_filename = <<get first part>>+ 'SQLsafe\Diff' +<<get
last part>> where backup_archive_filename like '%_Diff_%'

I need a function for <<get first part>> like EXTRACTSTR(
backup_archive_filename, '\',3) would return '\\10.0.12.110\SQLSafe'. I
cant find a built in function that can pick apart fields based on a
seperator.
TIA
RobHi

Maybe something like:

UPDATE Mytable
Set URL = STUFF(url, charindex('\SQLSafe\',url),LEN('\SQLSafe\'),CASE WHEN
CHARINDEX('_Diff_',url) > 0 THEN '\SQLSafe\Diff\' ELSE '\SQLSafe\Full\'
END )

You may want to try this out with (this may wrap!):

SELECT STUFF(url, charindex('\SQLSafe\',url),LEN('\SQLSafe\'),CASE WHEN
CHARINDEX('_Diff_',url) > 0 THEN '\SQLSafe\Diff\' ELSE '\SQLSafe\Full\'
END )
FROM ( SELECT
'\\10.0.12.110\SQLSafe\COGNOS-DEV\2005-08-29_2017m_51s_Diff_COGNOS-DEV_cm.safe'
AS url
UNION ALL SELECT
'\\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Diff_TLS-D-AN001_Northwind.safe'
UNION ALL SELECT
'\\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Full_TLS-D-AN001_Northwind.safe'
) A

John

"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126800395.289507.197650@.g47g2000cwa.googlegr oups.com...
>I am trying to do a text replacement to reflect changes where I've
> stored data.
> A field, backup_archive_filename, contains the url path. I've since
> changed the directory structure and wish to change whats stored in the
> table.
> Example:
> \\10.0.12.110\SQLSafe\COGNOS-DEV\2005-08-29_2017m_51s_Diff_COGNOS-DEV_cm.safe
> \\10.0.12.110\SQLSafe\TLS-D-AN001\2005-08-29_2041m_11s_Diff_TLS-D-AN001_Northwind.safe
> I want to change SQLSafe to SQLSafe\Diff or SQLSafe\Full depending when
> there is either %Diff% or %Full% in the string to reflect the change in
> the directory.
> I wanted to do something like:
> update backups_sets
> SET backup_archive_filename = <<get first part>>+ 'SQLsafe\Diff' +<<get
> last part>> where backup_archive_filename like '%_Diff_%'
>
> I need a function for <<get first part>> like EXTRACTSTR(
> backup_archive_filename, '\',3) would return '\\10.0.12.110\SQLSafe'. I
> cant find a built in function that can pick apart fields based on a
> seperator.
> TIA
> Rob

How to do Text Mining using SSIS

Hi
All
Can anyone tell me how to do text mining using SSIS
I have a some financial document in text format and i want to mine those
Thnaks in advance
Satish

Can you be a bit more specific about your requirements?

Have you looked at the Term Extraction and Term Lookup components in SSIS?

-Jamie

How to Do Text Mining

Hi
All
Can anyone tell me how to do text mining using Analysis Servvices in sql server 2005
I have a some financial document in text format and i want to mine those
Thnaks in advance
Satish

The text mining tutorial here should be helpful: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/Links_LinkRedirector.aspx?id=689

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian|||

Ian Roof - MSFT wrote:

The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

Oooh... good to know. Well, okay then.

Thanks for the clarification, Ian.

So, I'm guessing this means that a table data region just doesn't offer much in the way of getting to a solution for an aggregate of an aggregate. Your suggestion (relevant to a matrix) seems like the closest thing to a simple solution of a nested aggregate that I've been able to find.

--Pete

|||One thing you may be able to try is to create a two Tables. One table that is hidden and uses the first function that does not have headers and footers, and your current table that only uses the second function. Make sure that the new, hidden table is above and to the left of your current table, so that it will be executed first.

Ian

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?