Basic "RANK" question: how to stop null records appearing.

i have a query that runs just fine, returning the turnover per sector, where the sector has non-null turnover.

I'd like to add ranking into the query, but when I do, I now get ALL sectors, with those sectors having null turnover all ranking equal bottom.

This kinda makes sense, but essentially I'd like to exclude the null turnover ones. I've tried various combinations of nonempty, NON EMPTY and Exists without success. What's the answer to this, and more importantly, how should I be thinking this through to get the right answer myself?

1. First query: i have defined the ranking measure, but not used it. I get only non empty sectors.

WITH

member measures.[turnover rank] as rank([SECTOR3].[SECTOR3 ID].CurrentMember, [SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS)

SELECT {[Measures].[TURNOVER]} on columns,

nonempty {[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS} ONROWS

FROM [WmCube4]

where [TIME].[Date].&[2007-06-04T00:00:00]

2. Second query: i include the ranking measure in the output - now null records appear.

WITH

member measures.[turnover rank] as rank([SECTOR3].[SECTOR3 ID].CurrentMember, [SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS)

SELECT {[Measures].[TURNOVER],measures.[turnover rank]} on columns,

nonempty {[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS} ONROWS

FROM [WmCube4]

where [TIME].[Date].&[2007-06-04T00:00:00]

Not sure how ranking could occur without using a 3rd parameter for Rank() - but you can return null sectors with empty turnover, like:

member measures.[turnover rank] as

iif(IsEmpty([Measures].[TURNOVER]), Null,

rank([SECTOR3].[SECTOR3 ID].CurrentMember,

[SECTOR3].[SECTOR3 ID].[SECTOR3 ID].ALLMEMBERS,

[Measures].[TURNOVER]))

|||

works perfectly, thanks!

Barcode not printing

I have barcode printing formula in my report. When the report runs,it
does not print the barcode , just prints the barcode text with some additional characters in the begining and end of the text.Is there any file missing in cryatal to print that barcode ? If yes then whats the name of this file ?Are you using formula to print barcode inside report file?? :confused:
If you want to use 1-D barcode, why don't you use barcode font.sql

backupset

Here is my situation:
I have a stored procedure that runs full, differential and
log backups for a particular database.
Each backup has unique name based on the type, day of the
week and the time (e.g. Full_Backup_Monday_12-30).
Thus, backups are kept for one week.
When I review the backup set table, I see the name of my
backup, but in many cases I see an old date for the backup
and start and completion dates. However, when I review
the server, the newer backups are present.
My questions are:
Why isn't the backupset table being updated with the most
recent information?
When is the backupset table updated?
Thanks,
MichaelHi Michael,
Could you create a database backup as following steps?
1. Expand a server group, and then expand a server.
2. Expand databases, right-click the database, point to all tasks, and then
click backup database
3. Type the backup set name in the Name box and Select Database -complete
4. Under Destination, click Tape or Disk, and then specify a backup
destination.
If no backup destinations appear, click Add to add an existing backup
device or to create a new one.
5. Click Ok to create (Don't select the Schedule check box)
6. Check to see the backupset table again.
Is the backupset table updated immediately?
According to my test, the backupset table is updated on my side. When a
real backup finishes, a new record will be inserted in the table. Does it
work on your side? Generally, we do not recommended to directly query
system table. Could you tell me your detailed scenario?
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Michael Shao
Microsoft Support Engineer
| Content-Class: urn:content-classes:message
| From: "Michael" <michael_schall@.unionsanitary.com>
| Sender: "Michael" <michael_schall@.unionsanitary.com>
| Subject: backupset
| Date: Mon, 7 Jul 2003 16:51:46 -0700
| Lines: 22
| Message-ID: <06c801c344e2$b9f90bf0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNE4rn5YC8MduACSZi2OM0YFuqZFQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23185
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here is my situation:
|
| I have a stored procedure that runs full, differential and
| log backups for a particular database.
|
| Each backup has unique name based on the type, day of the
| week and the time (e.g. Full_Backup_Monday_12-30).
| Thus, backups are kept for one week.
|
| When I review the backup set table, I see the name of my
| backup, but in many cases I see an old date for the backup
| and start and completion dates. However, when I review
| the server, the newer backups are present.
|
| My questions are:
| Why isn't the backupset table being updated with the most
| recent information?
|
| When is the backupset table updated?
|
| Thanks,
| Michael
|