How could I assign rank.

I have the below query

select [FI NAME], round (sum(FIWORKING.GROSPFT), 0)as FIGROSS from from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]
order by sum (FIWORKING."GROSPFT") DESC

Which returns below

name1 65784
name2 32586
name3 37892

based on this, I would like to be able to set a rank (aka 1 , 2 , 3, etc)
for NAME1, NAME2, etc

I would like to store this in table GROSPFTRANK

That Table looks like

name1 1
name2 2

I haven't been able to figure out the SQL to do this.

Thanks for an help

ChrisYou can create a table with an identity column. Insert into that table using your query and the ranking will automatically be applied.|||There are several ways to skin this cat.

One method is to make the second field in your destination table an incrementing identity column, and then just insert your ordered data into it.

A second method would be to create a temporary table with an autoincrement column and load your data into it prior to storing it in your permanent table.

Or you could use this sql statement, which runs two totals and then counts the number of records in the second set which are less than the value in the first set:

Insert into GROSPFTRANK ([FI NAME], [RANK])
select [FIWORKINGOUTER].[FI NAME], count([FIGROSS].[FI NAME])
from
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGOUTER
inner join
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGSUB
on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)

Note that this assigns two [FI NAME] values the same rank if they have the same summary value. If you want unique ranks based on, say, the alphabetical order of [FI NAME], join the two subqueries with this ON statement:

on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)
or (FIWORKINGOUTER.FIGROSS = FIWORKINGSUB.FIGROSS
and [FIWORKINGOUTER].[FI NAME] < [FIWORKINGSUB].[FI NAME])

blindman

How can you programatically export from Reporting Services via C# using VS.NET 2003 + SQL Server

Hi,

I'm not sure if this is possible, i've been looking round various sites for the past couple of days and have only found information on how to export reports in Reporting Services 2005.

What i am wanting to do is create a front end in which the user can specify where the report is to be exported to and the click a button which will export the report to that location into a pdf format.

ThanksYOu can simply get the report as a byte[] which is the return type of the Render method and save this as a filestream to the designated path.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the reply. However, which object has the Render method?

I only have reporting services for visual studio 2003.|||

A simple one can be found here: http://support.microsoft.com/?kbid=875447

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

How can you programatically export from Reporting Services via C# using VS.NET 2003 + SQL Se

Hi,

I'm not sure if this is possible, i've been looking round various sites for the past couple of days and have only found information on how to export reports in Reporting Services 2005.

What i am wanting to do is create a front end in which the user can specify where the report is to be exported to and the click a button which will export the report to that location into a pdf format.

ThanksYOu can simply get the report as a byte[] which is the return type of the Render method and save this as a filestream to the designated path.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the reply. However, which object has the Render method?

I only have reporting services for visual studio 2003.|||

A simple one can be found here: http://support.microsoft.com/?kbid=875447

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

How can we get the round value of a number by MDX?

Hi, all,

How can I get the round value of a number (e.g 4.95, I want to get its closest value of 5), how can I achieve this by MDX?

Thanks a lot and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

go to menu report -> report properties and select code. then paste following code there.

Public Function Round(d as double) AS Double
Dim roundValue AS Double
roundValue =System.Math.Round(d)

If (System.Math.Abs(d - roundValue) - 0.5>0) Then
If (roundValue Mod 2) = 0 Then
roundValue = roundValue + 1
End If
End If

RETURN roundValue
End Function

then in ur designer

Code.Round(Field!Amount.value)

think this wil b ok
|||

Hi, thanks for your help. But i dont understand as what i am trying to do is for the KPI in cube designer, i want to get a round value for the KPI.

Thanks.

With best regards,

Yours sincerely,

|||

Helen,

In MDX you can achieve Rounding by using VBA!Round Function as following

WITH MEMBER [Measures].[My Rounded Measure]

AS VBA!ROUND([Measures].[My Measure],0)

SELECT

{

[Measures].[My Rounded Measure]

} ON 0,

NON EMPTY{

[Customer].[Customer].[Customer]

} ON 1

FROM [My Cube]

Bhudev