How to format KPI values in KPI definition?

Hi, experts,

How can we format KPI values in the KPI value expression? (e.g. format KPI_Name with format of 2 decimal places?).

Hope it is clear for your help. I am looking forward to hearing from you shortly.

Thanks a lot in advance.

With kindest regards,

Yours sincerely,

Hi Helen! I do not think you can format KPI. You can use a calculated member as the value expression /source for the KPI and format it instead.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks for your help. I also found a strange problem that when I build the report with KPI values displayed in SSRS 2005, the values are with many decimal places instead of the formats I have already set up in the Analysis services cubes? (e.g, I formated the KPI values by using a calculated member in calculations with 2 decimal places, but the KPI values displayed on SSRS2005 are with many decimal places? e.g. 2.455879..?)

I have no idea what is going on. I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Hi Helen! I have seen the same behaviour in SSRS2005. You are aware of that Reporting Services do not show SSAS2005 KPI status and trends graphically? You will only see numbers.

SSRS2005 do not seem to use the formats from relational sources nore SSAS2005. I think you will have to apply formats once again in SSRS2005 like "### ####,##"

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your patient advices and help.

Yes, I am aware of this problem that SSRS2005 is not able to show KPI status and trend graphically.

With kindest regards,

Yours sincerely,

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_nullcount_of_not_nullcount(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!
<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegr oups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.
|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:

>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
>count(f2( fieldx ) ) AS count_of_not_null,
>count(*)
>from some_table
>
>that would return one row that would look like:
>
>count_of_nullcount_of_not_nullcount(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!
|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegr oups.com...
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.
sql

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_null count_of_not_null count(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:

>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
>from some_table
>
>that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.

How to find the NULL counts and non NULL counts?

Dear experts,
I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.
Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.
What I'm trying to eliminate is:
select count(*)
from some_table
where fieldx is null
and then running the next query:
select count(*)
from some_table
where fieldx is NOT null
What functions can I use to run a query such as:
select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table
that would return one row that would look like:
count_of_null count_of_not_null count(*)
25 75 100
I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?
Thanks a lot!<dba_222@.yahoo.com> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> Dear experts,
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
> What I'm trying to eliminate is:
> select count(*)
> from some_table
> where fieldx is null
> and then running the next query:
> select count(*)
> from some_table
> where fieldx is NOT null
>
> What functions can I use to run a query such as:
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
> that would return one row that would look like:
>
> count_of_null count_of_not_null count(*)
> 25 75 100
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?
SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table
Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.|||SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls
The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.
Roy Harvey
Beacon Falls, CT
On 31 Oct 2006 06:39:01 -0800, dba_222@.yahoo.com wrote:
>Dear experts,
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>What I'm trying to eliminate is:
>select count(*)
>from some_table
>where fieldx is null
>and then running the next query:
>select count(*)
>from some_table
>where fieldx is NOT null
>
>What functions can I use to run a query such as:
>select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
>from some_table
>
>that would return one row that would look like:
>
>count_of_null count_of_not_null count(*)
>25 75 100
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>Thanks a lot!|||Brilliant!
I really should have thought of that.
But it was a looong tedious day yesterday.
Thanks a lot!
Mike C# wrote:
> <dba_222@.yahoo.com> wrote in message
> news:1162305541.838434.188080@.f16g2000cwb.googlegroups.com...
> > Dear experts,
> >
> > I am finding a LOT of rows with NULL columns in the Sybase
> > tables I'm querying.
> >
> > Say, there is a table, with 100 rows.
> > 25 rows are NULL
> > 75 rows are NOT NULL.
> >
> >
> > What I'm trying to eliminate is:
> >
> > select count(*)
> > from some_table
> > where fieldx is null
> >
> > and then running the next query:
> >
> > select count(*)
> > from some_table
> > where fieldx is NOT null
> >
> >
> > What functions can I use to run a query such as:
> >
> > select count(f1( fieldx ),) AS count_of_null,
> > count(f2( fieldx ) ) AS count_of_not_null,
> > count(*)
> > from some_table
> >
> >
> > that would return one row that would look like:
> >
> >
> > count_of_null count_of_not_null count(*)
> >
> > 25 75 100
> >
> >
> >
> > I know there is the ISNULL function. But that converts the NULL
> > to an actual number. Could I use other functions in conjunction with
> > it?
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.

How to find out the PK from a table?

Dear all experts,

I need to find out some property of a tableAs following sql statement:
select mtable.name,mcolumn.name,mtype.name,mcolumn.is_identity
from sys.tables mtable, sys.all_columns mcolumn, sys.types mtype
where mcolumn.object_id = mtable.object_id
and mcolumn.system_type_id = mtype.system_type_id
and mtable.name in ('TestTable')
order by mtable.name,mcolumn.column_id

But there is no PK information.
Follwing sql statement shows the PK name of a table, but no composite info(which fields):
select mtable.name,mkey.name
from sys.key_constraints mkey, sys.tables mtable
where mkey.parent_object_id = mtable.object_id
and mkey.type = 'PK'
and mkey.name like 'TestTable'

How can I find which fields are PK in a table?thanks...
-Winson

try using

sp_pkeys 'tablename'

|||

And I try to write another way as follow:

select mtable.name as tablename,mcolumn.name as fieldname,mtype.name as fieldtype,mcolumn.is_identity,mindex.index_id as is_pk
from sys.tables mtable inner join sys.all_columns mcolumn on mtable.object_id = mcolumn.object_id
inner join sys.types mtype on mcolumn.system_type_id = mtype.system_type_id
left join sys.index_columns mindex on mcolumn.object_id = mindex.object_id and mcolumn.column_id = mindex.column_id
where mtable.name in ('TestTable')
order by mtable.name,mcolumn.column_id,mindex.key_ordinal

|||You can also query the system view INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAIN_TYPE = 'PRIMARY KEY' joining on the table name.|||

Also, take a look at

http://msdn2.microsoft.com/en-us/library/ms345522.aspx#_FAQ16