RowCount or Equivalent

I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?

Sorry I am not understanding your question.

RowCount does not exist, use CountRows instead, then What does 'reversing the image' means.

If you are after counting rows returned by some reports parameters, something along these lines should help.

=iif(CountRows("SM") <= 0,"Your query returned no result.",(iif(CountRows("SM") >= 64500,"Report is too large to open in Excel. Please use CSV Export then use another database like Access. " & "Number of rows returned: " & CountRows("SM") , "Number of rows returned: " & CountRows("SM") )))

Philippe

RowCount or Equivalent

I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?

Sorry I am not understanding your question.

RowCount does not exist, use CountRows instead, then What does 'reversing the image' means.

If you are after counting rows returned by some reports parameters, something along these lines should help.

=iif(CountRows("SM") <= 0,"Your query returned no result.",(iif(CountRows("SM") >= 64500,"Report is too large to open in Excel. Please use CSV Export then use another database like Access. " & "Number of rows returned: " & CountRows("SM") , "Number of rows returned: " & CountRows("SM") )))

Philippe

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.

Row numbering unpredictable

Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/Assuming CustomerName is unique:
select
(select count (*)
from #tmp t1
where t1.CustomerName <= t2.CustomerName) as rownum
, *
from
#tmp t2
order by
t2.CustomerName
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/|||you could create the table first with an ID column, then insert into
it. I suspect (though have no evidence) that the select into #tmp with
an id column created then is having issues with the order by|||(cseemeuk@.googlemail.com) writes:
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
people that it's guaranteed to work. Whether that really is true, I'm not
completely convinced of, but fairly. In any case, SELECT INTO is *not*
guaranteed to work that way, so stay away from it.
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|||Thanks - works perfectly. The INTO was the problem - appears to be no
guaranteed order to the IDENTITY(bigint, 1,1)
All sorted
Cheers,
Chris Smith
http://www.cswd.co.uk/|||The order is not guaranteed when you use SELECT INTO.
See
http://support.microsoft.com/defaul...kb;en-us;273586
For a list of paging options see
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
> Hi,
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
> (in ADO :-P)
> This is SQL 2000 SP3 (pah!)
> Cheers,
> Chris Smith
> http://www.cswd.co.uk/
>|||One would think this type of thing,so common and important,
would have a kb or something written by MS.Are you aware of any
link?If none exists I would ask you to kindly request something in
'writing'.Key points of an enterprise database should not be rattling
around just in someone head! :)
Clarity,clarity and nothing but clarity.
Regards from:
www.rac4sql.net
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A28BF07D031Yazorman@.127.0.0.1...
> (cseemeuk@.googlemail.com) writes:
> Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
> BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
> people that it's guaranteed to work. Whether that really is true, I'm not
> completely convinced of, but fairly. In any case, SELECT INTO is *not*
> guaranteed to work that way, so stay away from it.
>
> --
> 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|||Steve Dassin wrote:
> One would think this type of thing,so common and important,
> would have a kb or something written by MS.Are you aware of any
> link?If none exists I would ask you to kindly request something in
> 'writing'.Key points of an enterprise database should not be rattling
> around just in someone head! :)
> Clarity,clarity and nothing but clarity.
http://support.microsoft.com/defaul...kb;en-us;273586
Do not assume that article means that all INSERTs will always cause
IDENTITY to be generated in a predetermined order. There are at least
some situations where that doesn't work - whether by design or a bug I
can't say.
Perhaps the safest course is to assume that you cannot control the
IDENTITY sequence with ORDER BY. In my view the wisest and most logical
solution is to use other methods like the ROW_NUMBER function for
example.
I can think of at least two good reasons for not using IDENTITY the way
proposed by the KB. Firstly IDENTITY is normally intended as an
arbitrary surrogate key - using the values in any "meaningful" way is a
compromise you don't need and is something it just isn't designed for.
Secondly, this supposed behaviour of an "ordered" INSERT looks contrary
to the set-based nature of an INSERT statement. Whether or not it works
today, it seems undesirable to assume that it should always work that
way in future. One would hope and expect that the engine could optimise
out any redundant sorting in INSERT...SELECT queries. That seems to be
what happens in some cases today and maybe it will happen more often in
future versions due to improvements in the optimiser. Just some things
to bear in mind.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Anyone got any ideas on how to solve that other than client side paging <
<
The basic principle of a tiered architecture is that display is done in
the front end adn NEVER in the database. Why are you sing violating
40 years of Software Engineering?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144803667.948395.290650@.i40g2000cwc.googlegroups.com...
<<
> The basic principle of a tiered architecture is that display is done in
> the front end adn NEVER in the database. Why are you sing violating
> 40 years of Software Engineering?
>
Forty years of a life sentence is enough.Time to let the innocent free.
Convicted on trumped up,unsubstantiated and false charges.In other words,
NONSENSE.
The thread:
Monday, April 10, 2006 9:48 PM
microsoft.public.sqlserver.programming
Re: Membership Timeline Spanning
contains a response that further clarifies things:
"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net