Row-Limit per Group

I need to sum() the 3 biggest values from a specific field of each group.
Using
SELECT sum(numbers) FROM table GROUP BY field
operates on every row and LIMIT only restricts the final result. What I need is a way to limit the rows per group.select sum(numbers)
from daTable as X
where ( select count(*)
from daTable
where numbers > X.numbers) < 3|||This only gives a single amount, i.e., the sum of the biggest three from the whole table.
To obtain the sums of the three biggest values from each group:SELECT field, sum(numbers)
FROM daTable as X
WHERE (SELECT count(*)
FROM daTable
WHERE field = X.field AND numbers > X.numbers) < 3
GROUP BY field|||well spotted, peter, you are quite right, i misunderstood the question

:)

Row numbers on export

HI

I have written a script to export data from customer table to another crm package, on the export they require the first column to be numbered 1 - .... say 1000 or how ever many rows there will be.

Is it possible?

Thanks

Rich

Are you talking about a batch count? Or does every row have to have a number?

If it's batch count then use DTS add a global variable, assign the count, then use the file system object to insert the count into the file after the export

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Okay after reading it again I think you want count for each row

In sql 2005 you can use ROW_NUMBER in 2000 you can use IDENTITY with a temp table

2000 version

SELECT IDENTITY(INT, 1,1) AS Rank ,*
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT * FROM YourTable
ORDER BY SomeColumn

SELECT * FROM #Ranks ORDER BY Rank

2005 version

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn) AS 'rownumber',*
FROM YourTable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

is there not a function i could use in the statement? , i am not going to use dts

thanks

|||

do a select count(*) from (your query here)

union all

your original query

example in pubs on SQL server 2000

select convert(varchar(30),count(*)), '','','','','','','',''
from authors
union all
select au_id, au_lname, au_fname, phone, address, city, state, zip, contract
from authors

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks Denis, i will go the temp table route

Row numbers in select statements

Is there an easy way to do get a row number in each row in a select
statement?
Something like:
select rownumber,description price from lineorder order by row number
Thanks,
TomTshad,
Yes.
See:
How to dynamically number rows in a SELECT statement.
http://support.microsoft.com/defaul...kb;en-us;186133
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||Why can't the presentation tier do this? It's the only place that HAS to
loop through each row, one by one. Now you're forcing the database to do it
too, and performance can only suffer for it.
http://www.aspfaq.com/2427
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eV$hgGZ2FHA.636@.TK2MSFTNGP10.phx.gbl...
> Tshad,
> Yes.
> See:
> How to dynamically number rows in a SELECT statement.
> http://support.microsoft.com/defaul...kb;en-us;186133
I tried that:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2
then r.ItemName end,Price,PurchaseQty,Price * PurchaseQty
order by 1
But in my 8 rows returned I got (1,1,1,1,1,1,2,2)'
Is the Joins causing me a problem?
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OUJZsHZ2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Why can't the presentation tier do this? It's the only place that HAS to
> loop through each row, one by one. Now you're forcing the database to do
> it too, and performance can only suffer for it.
Actually, it can.
But that is an extra step,as I am binding it to a datagrid.
Tom
> http://www.aspfaq.com/2427
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
My philosophy is that rules of database normalization apply only to physical
tables and not to query results.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||> My philosophy is that rules of database normalization apply only to
> physical tables and not to query results.
FWIW, my objection to doing this in the database has nothing to do with
normalization at all, but with the extra work required (whether using a
subquery, or copying all the data to a separate table first). A simple
counter at the presentation layer is the very least impact on performance,
since it has to loop through all rows and display them one by one anyway.
A|||This assumes that the result is being consumed in such a way that the
developer can add the additional computed column. It may be exported from
DTS to a table or file, executed only in Query Analyzer and pasted into
email, or bound to a datagrid.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utST6ik2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> FWIW, my objection to doing this in the database has nothing to do with
> normalization at all, but with the extra work required (whether using a
> subquery, or copying all the data to a separate table first). A simple
> counter at the presentation layer is the very least impact on performance,
> since it has to loop through all rows and display them one by one anyway.
> A
>|||> This assumes that the result is being consumed in such a way that the
> developer can add the additional computed column. It may be exported from
> DTS to a table or file, executed only in Query Analyzer and pasted into
> email, or bound to a datagrid.
Yep, that's why I asked, "why can't the presentation tier do this?" and did
not say "ONLY the presentation tier can do this!"|||But do you think that only the presentation tier *should* do this?
;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhisP5k2FHA.1572@.TK2MSFTNGP10.phx.gbl...
> Yep, that's why I asked, "why can't the presentation tier do this?" and
> did not say "ONLY the presentation tier can do this!"
>

Row Numbers for Groups

I am trying to number a group and I am using the following to do so:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
However, I have one little problem. How do I clear out the value and start
over? This is what I want my report to look like:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
1. Group 2 Header
Detail
Instead I get:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
3. Group 2 Header
Detail
Any suggestions?Assuming your Group 1 is called "Group1", you can use a RunningValue with an
explicit scope specified:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"wbarron" <wbarron@.discussions.microsoft.com> wrote in message
news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
>I am trying to number a group and I am using the following to do so:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> However, I have one little problem. How do I clear out the value and
> start
> over? This is what I want my report to look like:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 1. Group 2 Header
> Detail
> Instead I get:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 3. Group 2 Header
> Detail
> Any suggestions?
>|||Thanks! After I posted, it dawned on me that I needed to specify the scope.
Thanks for the response.
Wendy
"Robert Bruckner [MSFT]" wrote:
> Assuming your Group 1 is called "Group1", you can use a RunningValue with an
> explicit scope specified:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "wbarron" <wbarron@.discussions.microsoft.com> wrote in message
> news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
> >I am trying to number a group and I am using the following to do so:
> >
> > =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> >
> > However, I have one little problem. How do I clear out the value and
> > start
> > over? This is what I want my report to look like:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> >
> > Instead I get:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 3. Group 2 Header
> > Detail
> >
> > Any suggestions?
> >
> >
>
>

Row Numbers for Group

I have a table with 2 Groups and I want to number only Group2 with row
numbers. I put in a RowNumber(Nothing) function, and i'm getting something
like this:
Group 1
11 Group 2
14 Group 2
28 Group 2
Group 1
35 Group 2
etc...
What can I do to get the numbers to show up correctly?
Thanks!problem solved!
=RunningValue(Fields!fieldname.Value, CountDistinct, Nothing)
"jmann" wrote:
> I have a table with 2 Groups and I want to number only Group2 with row
> numbers. I put in a RowNumber(Nothing) function, and i'm getting something
> like this:
> Group 1
> 11 Group 2
> 14 Group 2
> 28 Group 2
> Group 1
> 35 Group 2
> etc...
> What can I do to get the numbers to show up correctly?
> Thanks!sql

Row Numbers for a View

I've been given a task that I believe is, basically, impossible, but
I'd like to see if there's a way to do it.

What my boss wants me to do is to create a view, in SQL Server 2000,
that will provide not only a row number field of some sort, but that
will produce sequential ordering for arbitrary selects and orderings.
So, if my data is a table with values from A thru D and my user does
SELECT data FROM vwTable, the result would be:

Row Data
-- --
1 A
2 B
3 C
4 D

But is they did SELECT data FROM vwTable ORDER BY data DSC, they would
get

Row Data
-- --
1 D
2 C
3 B
4 A

And if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), they
would get

Row Data
-- --
1 B
2 C

In SQL 2005, of course, this would be fairly trivial since I could use
the ROW_NUMBER function. In 2000, though, it seems to be utterly
impossible. My boss, however, is convinced that there must be some way
to create a calculated field to do it.

I'll be cursed if I can figure out a way to do so.

Any suggestions would be appreciated.>> In 2000, though, it seems to be utterly impossible. My boss, however, is
>> convinced that there must be some way to create a calculated field to do
>> it.

Paste the following in Google search box:
"dynamically number rows site:support.microsoft.com"

--
Anith|||Where do you want to show the data?
Use Front End application to do this

Madhivanan|||Anith Sen wrote:
> >> In 2000, though, it seems to be utterly impossible. My boss, however, is
> >> convinced that there must be some way to create a calculated field to do
> >> it.
> Paste the following in Google search box:
> "dynamically number rows site:support.microsoft.com"

Thanks, however, while that is a good way to derive row numbers in a
select statement, unfortunately it isn't quite what my boss is asking
me to do. She wants a view that will produce row counts in a
calculated field regardless of the order that the user uses to select
the data.

I would prefer to require the user to generate the row numbers in their
selects, wjhich wouldd allow for the solution you offered.
Unfortunately, that isn't what I've been tasked to do.|||Madhivanan wrote:
> Where do you want to show the data?
> Use Front End application to do this

SQL Reporting Services.|||On 27 Mar 2006 16:32:09 -0800, Andrew Lias wrote:

>I've been given a task that I believe is, basically, impossible, but
>I'd like to see if there's a way to do it.
>What my boss wants me to do is to create a view, in SQL Server 2000,
>that will provide not only a row number field of some sort, but that
>will produce sequential ordering for arbitrary selects and orderings.
>So, if my data is a table with values from A thru D and my user does
>SELECT data FROM vwTable, the result would be:
>Row Data
>-- --
>1 A
>2 B
>3 C
>4 D
>But is they did SELECT data FROM vwTable ORDER BY data DSC, they would
>get
>Row Data
>-- --
>1 D
>2 C
>3 B
>4 A
>And if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), they
>would get
>Row Data
>-- --
>1 B
>2 C
>In SQL 2005, of course, this would be fairly trivial since I could use
>the ROW_NUMBER function. In 2000, though, it seems to be utterly
>impossible. My boss, however, is convinced that there must be some way
>to create a calculated field to do it.
>I'll be cursed if I can figure out a way to do so.
>Any suggestions would be appreciated.

Hi Andrew,

The way you describe it here, it's impossible. That holds true for both
SQL Server 2005 and SQL Server 2000. Even ROW_NUMBER() won't help you.

If you need the row numbers to match the order specifiede on the select
and if you want to skip numbers for rows not included in the select,
you'll have to add row numbering logic on the SELECT statement. If you
add row numbers in the view, the numbers won't change if you exclude
some rows or choose a different order when selecting from the view.

Just to prevent misunderstanding - it is NOT impossible to get the
result sets you require. But it's only possible by extending the SELECT
with some row numbering logic. Either using ROW_NUMBER() if you're using
SQL Server 2005, or by using either a correlated subquery or a self-join
and a GROUP BY if you're using SQL Server 2000.

--
Hugo Kornelis, SQL Server MVP|||Andrew Lias (anrwlias@.gmail.com) writes:
> Thanks, however, while that is a good way to derive row numbers in a
> select statement, unfortunately it isn't quite what my boss is asking
> me to do. She wants a view that will produce row counts in a
> calculated field regardless of the order that the user uses to select
> the data.

Time to get a new boss?

What she is asking for is not possible. You would have to package the
user's SELECT statement somehow, so you can modify to add the row-number
column. As Hugo pointed out, this is the same on SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>>SQL Reporting Services

Cant you make use of Recordnumber feature such as the one available in
Crystal reports?

Madhivanan|||Erland Sommarskog wrote:
> Andrew Lias (anrwlias@.gmail.com) writes:
> > Thanks, however, while that is a good way to derive row numbers in a
> > select statement, unfortunately it isn't quite what my boss is asking
> > me to do. She wants a view that will produce row counts in a
> > calculated field regardless of the order that the user uses to select
> > the data.
> Time to get a new boss?
> What she is asking for is not possible. You would have to package the
> user's SELECT statement somehow, so you can modify to add the row-number
> column. As Hugo pointed out, this is the same on SQL 2005.

That's what I thought. I just wanted to be extra sure that there
wasn't some tricky way to do this before I went back to her and said
that it simply could not be done the way that she was asking.|||if you can use a stored procedure instead of a view, you could select
the data INTO a temp table in the "correct order", alter the table to
add an identity column, and return that ordered by identity.
before someone gets excited, there isn't a GUARANTEE this will work
forever in future versions of SQL, but it probably will.|||Doug (drmiller100@.hotmail.com) writes:
> if you can use a stored procedure instead of a view, you could select
> the data INTO a temp table in the "correct order", alter the table to
> add an identity column, and return that ordered by identity.
> before someone gets excited, there isn't a GUARANTEE this will work
> forever in future versions of SQL, but it probably will.

There is no guarantee that it will work any version of SQL Server. In fact
for a result set of any size, I would not expect it to work.

What is guaranteed to work, at least in SQL 2005, is if you have a
table with an IDENTITY table, and perform an INSERT with an ORDER BY.

Note that this does not apply to SELECT INTO with the IDENTITY function
and ORDER BY. In that case, there is *no* guarantee.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Your answer is better - have the identity already there.

But, at least we "solved" the problem!!!!|||Hey Andrew

Nothing is impossible, maybe I have read too fast but here is how I would do it.
Sounds like your boss just wants row numbering on your result set.
In reporting services use this expression

=RowNumber("DataSetName")

That would be like using Crystal's RecordNumber

Hope this helps

Row numbers etc.

I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.

Does this sound like something that can be done within SSRS?

I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.

John,

You might be able to do this in the code window of the reports, but I think it might be simpler for you to do this in your MDX.

Here's a sample query (based on the FoodMart cube) that gives a rank for Store Sales and a rank for a made up member of Negative Store Sales, to show that the rankings work independently:

Code Snippet

with

member [Measures].[Negative Store Sales]

as [Measures].[Store Sales] * -1

member [Measures].[Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Store Sales])

member [Measures].[Negative Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Negative Store Sales])

select

{[Measures].[Store Sales Rank]

, [Measures].[Store Sales]

, [Measures].[Negative Store Sales Rank]

, [Measures].[Negative Store Sales]} on columns

, [Store].[Store Name] on rows

from [Sales]

Will that work for you?
Jessica

|||

Thanks Jessica.

I played around yesterday and came up with this MDX code, which does almost everything I need (I think):

with

SET [Sales Rank 2006] AS

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

MEMBER [Measures].[Sales Rank] AS

RANK([Dim Customer].[Customer Name].CurrentMember, [Sales Rank 2006])

SELECT NON EMPTY

{[Measures].[Sales Rank], [Measures].[Sales]} on 0,

NON EMPTY [Dim Customer].[Customer Name].MEMBERS on 1

from [Heidtman DW]

WHERE [Date Shipped].[Year].[2006]

I'm not sure what to do with it. Do I create calculate members in my cube? Do I create a calculated member in my data set in SSRS? It doesn't seem like I can use this code directly to do either. I use year as a parameter in my reports. Will SSRS run the ranking code against the set of data (year) chosen at run time? Or do I have to create rankings by each year in my data set and store them somewhere?

Thanks again.

|||

If you think there will be multiple queries/reports that will need to use sales rank, I would put the calculated measure in your cube. If this is a one time reporting requirement, I would put it in the query.

As for getting the rank in a calculated measure, there are a few modifications I would make. Since you are passing the year in as a parameter to your query, you will not need to directly specify it in your calculated measure. Once you slice on the year and use your calculated measure, it will rank over the slice you have specified. Also, if you pass the measure into the rank function, you don't need to order your set beforehand.

So to use your example of customers, I would create a calculated measure that looks similar to this:

Code Snippet

with

member [Measures].[Sales Rank Over Customers] as

Rank([Dim Customer].[Customer Name].CurrentMember, [Dim Customer].[Customer Name], [Measures].[Sales])

Then you can use [Measures].[Sales Rank Over Customers] in your query on the same axis as your sales measure. As long as your [Dim Customer].[Customer Name] hierarchy is on the other axis, and you are slicing on the date, that should work for you.

-Jessica

|||

That builds an akward data set, increases the processing time by several orders of magnitude, and doesn't produce a rank result.....back to the drawing board.

Could I just create a named set in my cube that was:

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

This, to me, would order the customers by sales from highest to lowest? Then all I need to do is somehow assign an integer to each customer in the ordered list, counting from 1 to n? I don't need to rank them as they're already sorted in the order I'm looking for?

How to assign an integer?

Thanks.

Row numbers etc.

I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.

Does this sound like something that can be done within SSRS?

I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.

John,

You might be able to do this in the code window of the reports, but I think it might be simpler for you to do this in your MDX.

Here's a sample query (based on the FoodMart cube) that gives a rank for Store Sales and a rank for a made up member of Negative Store Sales, to show that the rankings work independently:

Code Snippet

with

member [Measures].[Negative Store Sales]

as [Measures].[Store Sales] * -1

member [Measures].[Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Store Sales])

member [Measures].[Negative Store Sales Rank]

as Rank([Store].CurrentMember, [Store].[Store Name], [Measures].[Negative Store Sales])

select

{[Measures].[Store Sales Rank]

, [Measures].[Store Sales]

, [Measures].[Negative Store Sales Rank]

, [Measures].[Negative Store Sales]} on columns

, [Store].[Store Name] on rows

from [Sales]

Will that work for you?
Jessica

|||

Thanks Jessica.

I played around yesterday and came up with this MDX code, which does almost everything I need (I think):

with

SET [Sales Rank 2006] AS

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

MEMBER [Measures].[Sales Rank] AS

RANK([Dim Customer].[Customer Name].CurrentMember, [Sales Rank 2006])

SELECT NON EMPTY

{[Measures].[Sales Rank], [Measures].[Sales]} on 0,

NON EMPTY [Dim Customer].[Customer Name].MEMBERS on 1

from [Heidtman DW]

WHERE [Date Shipped].[Year].[2006]

I'm not sure what to do with it. Do I create calculate members in my cube? Do I create a calculated member in my data set in SSRS? It doesn't seem like I can use this code directly to do either. I use year as a parameter in my reports. Will SSRS run the ranking code against the set of data (year) chosen at run time? Or do I have to create rankings by each year in my data set and store them somewhere?

Thanks again.

|||

If you think there will be multiple queries/reports that will need to use sales rank, I would put the calculated measure in your cube. If this is a one time reporting requirement, I would put it in the query.

As for getting the rank in a calculated measure, there are a few modifications I would make. Since you are passing the year in as a parameter to your query, you will not need to directly specify it in your calculated measure. Once you slice on the year and use your calculated measure, it will rank over the slice you have specified. Also, if you pass the measure into the rank function, you don't need to order your set beforehand.

So to use your example of customers, I would create a calculated measure that looks similar to this:

Code Snippet

with

member [Measures].[Sales Rank Over Customers] as

Rank([Dim Customer].[Customer Name].CurrentMember, [Dim Customer].[Customer Name], [Measures].[Sales])

Then you can use [Measures].[Sales Rank Over Customers] in your query on the same axis as your sales measure. As long as your [Dim Customer].[Customer Name] hierarchy is on the other axis, and you are slicing on the date, that should work for you.

-Jessica

|||

That builds an akward data set, increases the processing time by several orders of magnitude, and doesn't produce a rank result.....back to the drawing board.

Could I just create a named set in my cube that was:

ORDER

(

NONEMPTY([Customer Name].[Customer Name].members),

[Measures].[Sales], BDESC

)

This, to me, would order the customers by sales from highest to lowest? Then all I need to do is somehow assign an integer to each customer in the ordered list, counting from 1 to n? I don't need to rank them as they're already sorted in the order I'm looking for?

How to assign an integer?

Thanks.

Rounding very small negative numbers to string

Hi,
I'm trying to round a very small negative number, e.g. -.024, to a string.
If I round -.024 to 1 place, I get 0.0
However, when converting to string, I get .-0
Is there some combination of functions to get rid of the minus sign in such
a situation, or do I have to put in an IF condition to check if number < .05
and change it to ABS?
Below is code that is resulting in -0.
Thanks.
Alan
DECLARE @.Value float(53)
DECLARE @.ValueRounded float(53)
SET @.Value = -.024 -- Any negative number < .05
SELECT @.Value
SET @.ValueRounded = ROUND(@.Value, 1)
SELECT @.ValueRounded
SELECT CONVERT(CHAR(6), @.ValueRounded)What about this?
DECLARE @.Value float(53)
DECLARE @.ValueRounded decimal(6,1)--convert to decimal
SET @.Value = -.024 -- Any negative number < .05
SELECT @.Value
SET @.ValueRounded = ROUND(@.Value, 1)
SELECT @.ValueRounded
SELECT CONVERT(CHAR(6), @.ValueRounded)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Alan Z. Scharf wrote:
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in suc
h
> a situation, or do I have to put in an IF condition to check if number < .
05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)|||Menace,
Thanks very much. I figured there must be a way.
Alan
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149092714.650915.239590@.j55g2000cwa.googlegroups.com...
> What about this?
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded decimal(6,1)--convert to decimal
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Alan Z. Scharf wrote:
string.
such
.05
>|||Is this what u need?
DECLARE @.Value float(53)
SET @.Value = -.025
SELECT ABS(Floor(@.Value))
Regards
Sudarshan Selvaraja
"Alan Z. Scharf" wrote:

> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in suc
h
> a situation, or do I have to put in an IF condition to check if number < .
05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
>
>|||BTW, FLOAT is an approximate representation. You would probably be better
off representing your data as a NUMERIC type.
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:Od%23oc2MhGHA.3756@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in
> such
> a situation, or do I have to put in an IF condition to check if number <
> .05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
>
>|||>> I'm trying to round a very small negative number, e.g. -.024, to a string
. <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.

Rounding Values

Hi,

How can I round a value to the next int number like all values > 1 and < 2 I need to round to 2 and on and on...to all numbers

So If I have 2.1 it's 3 if I have 2.9 it's 3 ...and so on...

Thanks

There is a built-in function: CEILING().|||Thanks.. It worked

Rounding up+

I need to round numbers.
it can be 1.1 or 1.2 or 1.9 it doesn't matter it always need to round up to whole number

select ceiling (1.1)

select ceiling (1.2)

select ceiling (1.9)

|||thank you

Rounding error when summing 4 numbers to 0

When I sum the 4 numbers (-550.83, 1690.65, 550.83, -1690.65), it should add up to zero. It displays correctly to 2 decimal places. I know that they are stored internally as a double. However, I use the number as a denominator in a calculated member and the test for zero fails. I have to use bounds such as > -00000001 and <0.00000001. The result is a very large number in billions if the filter is not used in the calculation.

Has anyone come across this or got a better suggestion? I can also use the VBA round function.

Thanks

If these are always going to be decimal numbers, as in the example above, does the changing the data type to "Currency" help?

http://msdn2.microsoft.com/es-es/library/ms129408.aspx

>>

SQL Server 2005 Books Online

DataType Element (ASSL)

Defines the data type of the associated element.

...

The values for DataType are defined in the System.Data.OleDb.OleDbType enumeration. However, only the enumeration values in the following table are valid in the DataType element.

Value	Description
BigInt	A 64-bit signed integer. This data type maps to the Int64 data type in Microsoft .NET Framework and the DBTYPE_I8 data type in OLE DB.
Bool	A Boolean value. This data type maps to the Boolean data type in the .NET Framework and the DBTYPE_BOOL data type in OLE DB.
Currency	A currency value ranging from -263 (or -922,337,203,685,477.5808) to 263-1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. This data type maps to the Decimal data type in the .NET Framework and the DBTYPE_CY data type in OLE DB.

...

>>

|||

Thanks for the info and a possible solution. I have to bring it in as a double.

The source fact table is in account dimension format with only one measure DECIMAL (24,12). The measure can be pounds shipped, gross sales, or some marketing dollar allocation to 12 places. If I used currency, I would lose significant precision due to rounding to 4 decimal places.

I think the structure of my client's source fact table is already determining how I manage this. I wish SSAS would allow you to set the source format such as DECIMAL (24,12). I have the calculated measure isolated in one area, but it may impact performance when the client drills down hierarchies. Here is the code I used for the calculated measure.

IIF(([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]) > -0.000000001 AND
([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]) < 0.000000001,
0, ([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]))

Rounding decimals for numbers AFTER the AVG func. Please take a l

My query returns numbers to the report, these are already rounded (this is
made to happen in query as they are cast to int).
After I get these integers to my report, I tend to group them, and AVG a
group set. If there is a lot of the numbers in the equation, the total tends
to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
putting d or n in the custom format properties for this textbox, I also try a
number of the default formatting options. Even I try to use the <format> tag
in the XML. All to no effect.
The only thing I can think is that I tend to write out a '%' after the
number (to show it's a percentage, it's purely cosmetic). Here is a line of
the AVG function, and how I am using it . I would really appreciate it if
anyone knew what I might be goofing up on here!
=iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")Did you try just using the following expression:
=Avg(Fields!DoneIt.Value)
and then just use formatcodes like P, P0, P1, etc.?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Swift" <MattSwift@.discussions.microsoft.com> wrote in message
news:EA1C086B-CA02-4314-8CED-970B2D72B5EB@.microsoft.com...
> My query returns numbers to the report, these are already rounded (this is
> made to happen in query as they are cast to int).
> After I get these integers to my report, I tend to group them, and AVG a
> group set. If there is a lot of the numbers in the equation, the total
> tends
> to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
> putting d or n in the custom format properties for this textbox, I also
> try a
> number of the default formatting options. Even I try to use the <format>
> tag
> in the XML. All to no effect.
> The only thing I can think is that I tend to write out a '%' after the
> number (to show it's a percentage, it's purely cosmetic). Here is a line
> of
> the AVG function, and how I am using it . I would really appreciate it if
> anyone knew what I might be goofing up on here!
> =iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")|||In your report, go to [ Report Properties ] from your [ Report ] menu.
On the [ Code ] tab, paste this in...
--[ BEGIN CODE ]--
Public Function ConvertToInt(ByVal x_obj As Object) As String
Return String.Format("{0:#}", x_obj)
End Function
--[ END CODE ]--
Now, change your textbox's formula to:
=ConvertToInt(Avg(Fields!DoneIt.Value)) & "%"
Andrew Bruderer
"Robert Bruckner [MSFT]" wrote:
> Did you try just using the following expression:
> =Avg(Fields!DoneIt.Value)
> and then just use formatcodes like P, P0, P1, etc.?
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt Swift" <MattSwift@.discussions.microsoft.com> wrote in message
> news:EA1C086B-CA02-4314-8CED-970B2D72B5EB@.microsoft.com...
> > My query returns numbers to the report, these are already rounded (this is
> > made to happen in query as they are cast to int).
> >
> > After I get these integers to my report, I tend to group them, and AVG a
> > group set. If there is a lot of the numbers in the equation, the total
> > tends
> > to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
> > putting d or n in the custom format properties for this textbox, I also
> > try a
> > number of the default formatting options. Even I try to use the <format>
> > tag
> > in the XML. All to no effect.
> >
> > The only thing I can think is that I tend to write out a '%' after the
> > number (to show it's a percentage, it's purely cosmetic). Here is a line
> > of
> > the AVG function, and how I am using it . I would really appreciate it if
> > anyone knew what I might be goofing up on here!
> >
> > =iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")
>
>

rounding decimal points in an expression

This sounds so simple but yet don't know why it doesn't work.

i've got two decimal numbers in columns

closingUnits = 25093.53640

closingAmt = 59110.33

i use a derived column transformation to generate a string column which is the division of those two above.

mystrfield = closingAmt /closingUnits = 2.355599

what i want is cast it to just 4 decimal points when i run the expression below it cast it to 4 decimal points but it doesn't do any rounding.

the value i get is 2.3555 when i should get 2.3556

(ISNULL(closingAmt ) || closingUnits == 0) ? "0.0000" : ((closingAmt / closingUnits) < 1 && (closingAmt / closingUnits) > -1 ? "0" : "") + (DT_WSTR,15)(DT_DECIMAL,4)(closingAmt / closingUnits)

Found the answer,

silly me, it's ROUND duh!!!

http://msdn2.microsoft.com/en-us/library/ms141721.aspx