Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

Conditional Data Inserting

Hello,

My project import data from à text file to a database. I'm able to import the whole file but i want to import only if data are younger than the one i have in database.

How can i do ? Where can i see a tutorial about it ?

thanks a lotDo both the source rows and the database rows have a date that can be compared. If so then you could use either a lookup or a mergejoin to get the two dates into the same data flow (by lookup or join on some key that indicates the rows are matched) and then use a conditional split to send the rows you want to update to a OLEDB command transform with the appropriate update statement (or if you do indeed want to insert the data not update then just send it to an OLEDB destination).

Thanks,
Matt|||There's a tutorial here that compares merge join and lookup components. Its not exactly the same as your situation but it may help!!

-Jamie|||Thanks it is very useful !

But i'm still blocked because in some case i need to insert, and in other case i need to update...|||Ash has a great post on upserts here:
http://sqljunkies.com/WebLog/ashvinis/archive/2005/06/15/15829.aspx
Give that a shot.
Thanks,

Concurrent SQL Tranasactions Fail

Hi all,
I am having trouble trying to get an efficient way of checking if a
value pair exists in a table, and if not inserting the value pair and
returning the new id in the identity column. The old method I have used
has been fine for some time but the table has grown to a very large
size and the current code is causing a bottleneck.
The table consists the three columns, An identity column and two
nvarchar(15) columns. Instead of replicating the nvarchar(15) columns
over the database I have placed them in a lookup table and use the ID
from the identity column to reference the nvarchar values. I do store
multiple languages in the database so an nvarchar is necessary.
I admit the current code I use below is poor, but all of the
enhancements I have tried to make have resulted in the system failing,
as multiple copies of the procedure run and two (or more) procedures
try and insert the same data into the table (there is a unique
clustered index on the two varchar columns, and the identity column is
the primary key).
As our server application is multithreaded, it is highly likely
(certain) that two or more of these procedures will be running at once
so I have to ensure data integrity whilst checking and updating. Im
just not sure that a TABLOCKX,HOLDLOCK lock hint is the best way.
CREATE PROCEDURE dbo.CreateNewContextItem
@.CONTEXT NVARCHAR(15),
@.DATAITEM NVARCHAR(15),
@.ID INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRANSACTION
SET @.ID = (SELECT id FROM contextitem WITH (TABLOCKX,HOLDLOCK) WHERE
context = @.CONTEXT AND dataitem=@.DATAITEM)
IF @.ID IS NULL
BEGIN
INSERT INTO
contextitem (context,dataitem)
VALUES
(@.CONTEXT,@.DATAITEM)
IF @.@.ERROR<>0
GOTO Errhandler
SET @.ID = Scope_Identity()
IF @.@.ERROR<>0
GOTO Errhandler
END
COMMIT TRANSACTION
RETURN 0
Errhandler:
ROLLBACK TRANSACTION
RETURN 50063 -- application specific error code
GO
Is there a way of speeding up this procedure whilst still allowing
multiple copies of it to be run at once safely? Any help would be
greatly appreciated.
Thanks in advance.How about UPDLOCK instead of the TABLOCKX hint?
ML
http://milambda.blogspot.com/|||That seems to have worked a treat!
Thanks for your help ML