Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

EndScope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon

Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

End Scope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon

Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

End Scope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon

Conditional FK Deletes

How would I use a Foreign Key to prevent deletions on the parent table?

For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.|||Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

Sorry if all this seems obvious, but I am a very green DBA...

TIA,

-Justin|||You never mentioned cascading - this is very important.

The answer is yes. SQL Server automatically sends this error to the calling application.

Take a look at the books online "Cascading Referential Integrity Constraints" article.

Concurrency puzzle (ODBC API related)

I hope this is not outside the scope of this newsgroup.

have been useing BulkAdd with ms-access with good success.
Until now that I;m switching to MSDE which is on a shared server.

The problem is that MSDE gives an error:
Invalid attribute/option identifier, sql state=HY092
which corresponds to:
SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.

but my BulkAdd routine explicitly has a call to:
SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)

I cannot imagine how the concurrency is readonly.
Could it be that I'm looking at the wrong place or the wrong attribute?

Thanks in adv.Ernesto (tsh@.mathematicuslabs.com) writes:
> have been useing BulkAdd with ms-access with good success.
> Until now that I;m switching to MSDE which is on a shared server.
> The problem is that MSDE gives an error:
> Invalid attribute/option identifier, sql state=HY092
> which corresponds to:
> SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.
> but my BulkAdd routine explicitly has a call to:
> SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)
> I cannot imagine how the concurrency is readonly.
> Could it be that I'm looking at the wrong place or the wrong attribute?

Without seeing your code, it is not possible to give a definitive answer.
(And it doesn't help that I have little experience of ODBC API programming.)

But checking the docs, my conclusion is that the SELECT statement for your
cursor is such that the cursor is not updatable. This could be due to
several causes: lack of primary key, use of expressions etc. Would need to
see the SQL code together with some information on the table to say more.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland ,
You are good!
I changed the query which created the table to create a primary key and it
SEEMS to solve the problem.
(It's a good thing having a primary key is ok in this case, else I would not
know how to "solve" this.)

I'm not completely out of the woods yet, but I think now there is another
unrelated problem.
Hopefully you'll keep reading if I am fooled and the problem is still there.

Now if you can only tell me how or where in the world can I learn these
things about the odbc api.

Not having found any good resource which explains these things, the way I
operate is by prayer only and geenrous people like yourself.

E|||Ernesto (tsh@.mathematicuslabs.com) writes:
> Now if you can only tell me how or where in the world can I learn these
> things about the odbc api.

The place to read about the ODBC API in general is the MDAC Books Online,
which comes with the MDAC SDK. Then there is a section in the SQL Server
Books Online which covers specifics for the SQL Server ODBC Driver.

For this particular issue, though, it's more of a general understanding
of databases in general, and in this particular case about cursors. I
know that Kalen Deleany has a chapter on cursors in her "Inside SQL
Server 2000". I doubt that she says much about ODBC, but the same issues
apply when you use cursors in T-SQL.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for the sources. I will look them up.
One more dumb question in case you can respond:

How would I do this if I did not want to add a primary key to the table?|||On Wed, 7 Jul 2004 14:20:02 -0400, Ernesto wrote:

> Thank you for the sources. I will look them up.
> One more dumb question in case you can respond:
> How would I do this if I did not want to add a primary key to the table?

You could add it to a temporary table that did have a primary key, then
execute an INSERT INTO command to transfer it to the permanent table.

However, a primary key is almost always a good idea. Some will argue that
without a primary key it isn't a table at all.|||>> How would I do this if I did not want to add a primary key to the
table? <<

Without a key, this is not a table; it is a punch card file with a
little class. You would return to what we used to call "EDP"
(Electronic Data Processing) in the late 1950's and throw away all 30
years of progress.

Well, you asked and that is the answer.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Concatinating child field

I'm developing an ASP.NET app with SQL back end. I'm trying to develop a use
r
interface that must reference information from two related tables and would
like to return the following (either in table, but preferably in datagrid):
ParentField1 ParentField2 Parent Field3 ChildField1Record1
ChildField1Record2
ChildField1Record3
ParentField1 ParentField2 ParentField3 ChildFeld1Record4
ChildFeld1Record5
ChildField1Record6 etc.
Is there anyway to write a TSQL function to concatinate the child records
into a dynamic field (there are about 400 parent records)
I could create a denormalized table that mimics the child table by has the
ParentID and ChildRecords concatinated that gets updated everytime the child
table changes, but that's just bad programming!
Any suggestions would be greatly appreciated.
Thanks in advance.well the question is not clear to me. but i guess u are trying to concatinat
e
the fields in the query
ucan do it as
select <field1> + <field2> + ..
FROM <Table>
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"AbeR" wrote:

> I'm developing an ASP.NET app with SQL back end. I'm trying to develop a u
ser
> interface that must reference information from two related tables and woul
d
> like to return the following (either in table, but preferably in datagrid)
:
> ParentField1 ParentField2 Parent Field3 ChildField1Record1
> ChildField1Record2
> ChildField1Record3
> ParentField1 ParentField2 ParentField3 ChildFeld1Record4
> ChildFeld1Record5
> ChildField1Record6 etc.
> Is there anyway to write a TSQL function to concatinate the child records
> into a dynamic field (there are about 400 parent records)
> I could create a denormalized table that mimics the child table by has the
> ParentID and ChildRecords concatinated that gets updated everytime the chi
ld
> table changes, but that's just bad programming!
> Any suggestions would be greatly appreciated.
> Thanks in advance.|||Hi Chandra,
Thanks for the response, and sorry for not being clear. Actually I am trying
to return contents of all records with the ParentID from the child table wit
h
the parent record from the parent table, so actually want to select
record1field1 + record2field1 + record3field1 from chldtable joining
parenttable without repeating the parent data for each record.
Regards,
AbeR
"Chandra" wrote:
> well the question is not clear to me. but i guess u are trying to concatin
ate
> the fields in the query
> ucan do it as
> select <field1> + <field2> + ..
> FROM <Table>
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "AbeR" wrote:
>|||>> Is there anyway to write a TSQL function to concatinate the child records
The terminology you used is a bit confusing.
Assuming that there is a one-to-many relationship that exist between the
tables, what you seem to be asking is to return a resultset with the single
value from the referenced table and all the values from the referencing
table as a contenated string. In general, such processing which demand heavy
looping and formatting of values should be done on the client side -- quite
often one should be able to leverage the functionality of a programming
language or a report-writer in such cases.
Doing this on the server often requires procedural logic, often disguised as
a user defined function or perhaps cursors. For some such workarounds, refer
to: http://tinyurl.com/aka4u
Anith|||Exactly. I was hoping not to create another dataset on the client system, bu
t
that's probably the best approach (While loops in C# within the app)...
DataSet1 will be read by the client and produce dataset two with all the
values from the referenced table as one string and then present in a datagri
d.
Many thanks for the advice and reference.
- AbeR
"Anith Sen" wrote:

> The terminology you used is a bit confusing.
> Assuming that there is a one-to-many relationship that exist between the
> tables, what you seem to be asking is to return a resultset with the singl
e
> value from the referenced table and all the values from the referencing
> table as a contenated string. In general, such processing which demand hea
vy
> looping and formatting of values should be done on the client side -- quit
e
> often one should be able to leverage the functionality of a programming
> language or a report-writer in such cases.
> Doing this on the server often requires procedural logic, often disguised
as
> a user defined function or perhaps cursors. For some such workarounds, ref
er
> to: http://tinyurl.com/aka4u
> --
> Anith
>
>