Calculated Measure - Unique Requirement

Hi Guys,

I have unique Requirement my database has following things:-

Dimensions

Activity (Each Item belongs to Activity)

Item

Measures

Quantity - 2005

Quantity - 2006

Total Invoice Value - 2005

Total Invoice Value - 2006

Calculated Measure

Delta Price (Difference Between 2005 & 2006 Price)

Price - 2005 (Value/Quantity)

Price - 2006

Basically how it happened is Price was calcuated at PartNumber and then at Activity level. My finance Department now wants calculation of Delta Price for Activity from PartNumber i.e.

Quantity - 2005 * Price - 2005 * Delta Price at (Part Number Level) Sum this and Divided by Value - 2005

I am giving following definition in calculated member -

Sum({[Part Number]},[Measures].[Quantity 2005] * [Measures].[Net Net Unit Price AED 2005] *[Measures].[Delta Net Net Unit Price - AED])

But what this does is again takes value at Activity level and does the calculation, can somebody help me to achieve this

Regards,

Kaushal

Hi Kaushal. I'd like to help, but Ithink I need more information first. If you can answer a few questions I think we can help.

(1) What is the structure of your Activity dimension? Are Part Numbers in the Activity dimension? Perhaps you can provide a brief example?

(2) Your calculated member definition states: "...Sum this and Divided by Value - 2005". Yet, your calculated member doesn't have the division operator (/). Does the calculated member need the division?

(3) Please provide a brief example of the "wrong" answer you get, and the "correct" answer you expect.

Thanks.

PGoldy

|||

(1) What is the structure of your Activity dimension? Are Part Numbers in the Activity dimension? Perhaps you can provide a brief example?

Yes PartNumber is part of Activity Dimension

(2) Your calculated member definition states: "...Sum this and Divided by Value - 2005". Yet, your calculated member doesn't have the division operator (/). Does the calculated member need the division?

Yes

(3) Please provide a brief example of the "wrong" answer you get, and the "correct" answer you expect.

Activity

BT001

Part Number

Quantity

Value

Net Price

Delta Price

A

100

9000

90

0.1

B

120

480

4

0.4

C

140

700

5

0.13

D

50

100

2

0.16

E

70

350

5

0.28

480

10630

22.14583

0.17

Wrong Value

1807.1

10630 * 0.17

Right Value

1297

SUMPRODUCT(C4:C8,E4:E8)

i.e. Value * Delta Price at PartNumber level.

Regards,

Kaushal

|||

Your issue appears to be that you are getting a product of sums, not a sum of the products.

To force the calculation to do the product first and then sum up you would do something like the following:

Sum(
Descendants([Activity].CurrentMember,[Activity].[Part Number])
,[Measures].[Quantity 2005] * [Measures].[Net Net Unit Price AED 2005] *[Measures].[Delta Net Net Unit Price - AED]
)

This will effectively get all the relevant part numbers, perform the calculation and then sum up the results of the calculation.

|||

Thanks Darren - I was a little behind following up.

PGoldy

|||

Thanks Darren,

It was great help, can you tell me if Part Number is not a level of Activity can i apply the same formula in someother way.

Regards,

Kaushal

|||

Paul: No worries, I think it is one of the strengths of these forums in that we can all chip in and contribute :)

Kaushal: You could probably apply a similar technique, but maybe not that exact formula if Part Number was on a different dimension. It really depends on how you want the logic to work and how your cube is structured. It could be as simple as replacing the references to the Activity dimension or it could get more complicated. I really don't have enough information to be able to answer that question fully.

If you are using SSAS 2005, you have a couple of other options open to you in addition to doing a straight calculated measure. One approach which would probably be ideal in this situation is to use a Measure Expression, these expressions get evaluated at the fact table grain and then get aggregated up and are very fast.

If your logic gets a bit more complicated another approach in SSAS 2005 could be to use a scoped assignment, this is very similar to a straight calculated measure, but potentially more efficient as you can restrict the subcube over which the calculation is perform.

Calculated field in Footer...running total

Hello,

How do I add unique values on the report? For example say I have this in my report:

Customer: Food Purchased: Amount:

Judy Cat Food $12

Sarah Dog Food $13.50

Diane Rabbit Food $17

Jason Dog Food $16

Tammy Dog Food $15

In the footer of the report I want to print a summary box that looks like this:

Product: Number Purchased: Total:

Cat Food 1 $12

Dog Food 3 $44.50

Rabbit Food 1 $17

How do I do this?

Thanks!

group on the food purchased fields and do a count on the group.|||

I'm not an expert at this. It is easy to say what do to. Can you tell me how to do it?

|||

Hello,

I don't think there is a way to have a summary like that in the footer of your report. But this will add your group totals, then you can just hide the details if you need.

Right click on the row handle for your detail row and select 'Insert Group'. In the box that pops up, give the group a name (or leave the default), select the Food Purchased field as your expression and select the box for 'Include group footer'. This will create two new rows in your table, a header and footer. In one of your footers textbox's, enter this to get the count of the group:

=CountRows()

If you don't want to see the details and just want the summary, click the row handle and change the Visibility -> Hidden propery to True. This will hid your details and only show the group footers.

Hope this helps.

Jarret

|||I have one question if the data of your main report is not fit in one page then the footer will repeat on next page. Does it ok with your requirement ?|||

I am converting this report from a crystal report. So yea, in the report now it is in a Report Footer and it only appears on the last page. This is how I need it because I was told to make the SQL Report look exactly like the CR. Unfortunately it seems like SRS doesn't give you a really nice option of doing Running Total fields like in Crystal. In Crystal they created a running total field and said "count all that are Dog Food" and then another running total that said "Count all that are Cat Food." I am not sure if putting it on the group footer will work because I need the report to display as it is. And to just say =CountRows() seems like it will count all rows and return 5. But I haven't tried it.

I created another report and put all the footer stuff in a table footer. And the table footer only appeared on the last page. I would like to put all this information in the table footer not the report footer. Sorry if I didn't explain that too well.

I am very surprised that SRS doesn't have an easy way to do this.

|||

Hello,

Using =CountRows() will give you the number of rows in that scope, so if you put this in your group header/footer, it will show the number of rows in that group. If you place it in the table header/footer, you will get the total number of rows in the table.

Jarret

|||

How do I put the conditional on? Currently it is counting all rows in the database for each type of food. What if I want to say "only count the rows that are on the report" (or only records that happened in the past week)?

|||

This worked in the table footer!

=Count(iif(Trim(Fields!FoodPurch.Value)="Dog Food",Fields!FoodPurch.Value, Nothing))

Calculated Cell Option Not Visible in Cube Editor

Hi Guys,

I am using SQL SERVER Analysis Services 8.0.760, i have an unique problem. In my Cube Editor i am not able to see Calculated Cells Options. Right now i am able to see following options:-

Dimensions

Measures

Calculated Members

Actions

Named Sets

This is very urgent requirement can somebody help?

Regards,

Kaushal

I think that calculated cells is onlyn available in Analysis Services enterprise or developer edition. Perhaps you are using the standard edition?

HTH

Thomas Ivarsson

Calculate/create Row Number without identity

How do I output a row number for a table solely for the purpose of
querying for a unique row?

In my problem, the table from a legacy system does not have a primary
key, so it limits various querying I'd like to do that identifies
uniqueness in the table.

The problem is that since I'm using DTS to simply copy the table to
SQL, I don't want to create identity rows.chrispycrunch (chrispycrunch@.gmail.com) writes:
> How do I output a row number for a table solely for the purpose of
> querying for a unique row?
> In my problem, the table from a legacy system does not have a primary
> key, so it limits various querying I'd like to do that identifies
> uniqueness in the table.
> The problem is that since I'm using DTS to simply copy the table to
> SQL, I don't want to create identity rows.

Not sure why the use of DTS would preclude the use of an IDENTITY row,
but then again I have no experience of DTS. After all, IDENTITY seems
perfect in this case.

If there really is a problem for DTS, you make a two-stepper and have
DTS to park the data in a transient table, and insert from there into
the target table.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi. Are you sure that the original table has no duplicates? If there
are any
duplicate, you do not have unique rows. However, assuming you want to
establish an identity for future use, you could extract the old data
row-by-row
and set a unique identity of your own construction, and maintain that
with
any new rows that get added (and make sure to have a unique index on
that
value.
All in all, the identity column is made for this.
Joe Weinstein at BEA

Calculate New 'Unique' Records Each Day

Hi All,
If I have the following table and want to get the below results out,
what would be the best way of approaching this:
StartDate | TelNumber
01/11/05 | 1111
01/11/05 | 2222
01/11/05 | 3333
02/11/05 | 1111
02/11/05 | 4444
03/11/05 | 1111
03/11/05 | 5555
03/11/05 | 1111
03/11/05 | 6666
Each day I want to know the number of Unique TelNumbers across the
whole table each day.
So the answer I am looking for based on the above table, from the query
is:
01/11/05 -> 3 Unique TelNumbers
02/11/05 -> 1 Unique TelNumbers
03/11/05 -> 2 Unique TelNumbers
I've been messing around with Distinct but I can't seem to get it to
look at previous days.
Can anyone help?
Thanking you all in advance.
HPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
SELECT foobar_date, COUNT(DISTINCT tel_nbr)
FROM Foobar
GROUP BY foobar_date;
You might also want to learn about ISO-8601 dates.|||harrys@.gmail.com wrote:

> Each day I want to know the number of Unique TelNumbers across the
> whole table each day.
> So the answer I am looking for based on the above table, from the
> query is:
> 01/11/05 -> 3 Unique TelNumbers
> 02/11/05 -> 1 Unique TelNumbers
> 03/11/05 -> 2 Unique TelNumbers
> I've been messing around with Distinct but I can't seem to get it to
> look at previous days.
> Can anyone help?
DDL would indeed be nice. This is untested but I think it'll work:
select T1.StartDate, count(select distinct TelNumber from Table T2
where T2.StartDate = T1.StartDate and TelNumber not in (select distinct
TelNumber from Table T3 where T3.StartDate < T2.StartDate))
from Table T1
group by T1.StartDate
HTH,
Stijn Verrept.|||Thanks for the replies. As requested, just to be more specific.
I receive a file every day that is set up as below. I have been
requested to find out how many new callers there are each day that have
never called before:
I use BULK INSERT to put the table into SQL.
-- Insert Data Into Table --
BULK INSERT [MasterData]
FROM 'c:\a2.csv'
WITH (FIELDTERMINATOR = ',')
Table:
ID | StartDate | TelNumber |
1 | 01/11/05 09:32:02 | 012321111
2 | 01/11/05 09:35:18 | 012322222
3 | 02/11/05 15:50:32 | 012321111
4 | 02/11/05 19:10:46 | 012323333
5 | 03/11/05 11:10:11 | 012324444
6 | 03/11/05 17:46:25 | 012322222
7 | 03/11/05 06:34:42 | 012328888
So i have to work out how many new callers there are each day that have
never called in before. So the results from the above would be:
01/11/05 - 2 New Callers
02/11/05 - 1 New Callers
03/11/05 - 3 New Callers
- - - - - - - - - - - -
Celko- Your query gives me distinct callers each day, but it doesnt
take into account if they have already called on the previous day.
Stijn - I tried working/amending your query but just couldnt get it to
work. (Apologies if it's me being stupid!) Could you expand on how you
think that may solve my dilema?
This query will need to be run everyday!
Thanks for your assistance.
H|||Hi There,
See if this can help you.
Select T1.StartDate,(
Select Count(Distinct T2.TElNumber) From tmpData1 T2 Where
T2.StartDate =T1.StartDate
And T2.TelNumber Not In ( Select T3.TElNumber From tmpData1 T3 Where
T3.StartDate<T2.StartDate)
)
>From tmpData1 T1 Group By T1.StartDate
Here tmpData1 is the table Name
With Warm regards
Jatinder Singh|||Hi,
No joy!
I tried the following:
SELECT DATEPART(dd,T1.StartDate),(
SELECT Count(Distinct T2.CLI) From MasterData T2 Where
T2.StartDate =T1.StartDate
And T2.CLI Not In ( Select T3.CLI From MasterData T3 Where
T3.StartDate<T2.StartDate)
)
FROM MasterData T1 GROUP BY DATEPART(dd,T1.StartDate)
But I just get a list of zeros!
Exscuse my ignorance, but where can I found out more about this T1 T2
business? Had a look on google, but obviously I dont know what to
search on at the moment!
Thanks
H|||Just to add to this:
If I run the following:
SELECT convert(varchar(10), StartDate, 101) AS DayCol, COUNT(DISTINCT
CLI) AS CountCLI
FROM MasterData
GROUP BY convert(varchar(10), StartDate, 101)
ORDER BY convert(varchar(10), StartDate, 101)
It gives me the nuimber of unique telephone numbers EACH DAY.
Is there no way to creat a sub-query to calculate the number of unique
telephone numbers each day since the beggining?|||harrys@.gmail.com wrote:

> It gives me the nuimber of unique telephone numbers EACH DAY.
> Is there no way to creat a sub-query to calculate the number of unique
> telephone numbers each day since the beggining?
Well that is what my query should do LOL. The T1, T2, ... are just
aliases. If you can provide some DLL (create table, insert into table,
...) of the table and sample data you use, we can easily try out the
code and correct it if wrong. See this post for an example of good
DDL:
http://groups.google.com/group/micr...programming/br
owse_thread/thread/5d4fc158de977f3f
Kind regards,
Stijn Verrept.|||Stign,
Thanks for the reference link. I see what you mean now. And ackowledged
for future reference!
So on that basis, below are the details:
- - - - - TABLE
CREATE TABLE [dbo].[MasterData](
[CDRRef] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[CLI] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[AccessNumber] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[TransNumber] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[DurSec] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
- - - - - INSERT DATA
INSERT INTO [YourCallCDR].[dbo].[MasterData]
([CDRRef],[CLI],[AccessNumber],[TransNum
ber],[StartDate],[EndDate],[DurSec])
VALUES
(AFE5468ASD,0122233344,0777000000,077700
0000,10/01/05
09:00:02,10/01/05 09:05:05,5)
INSERT INTO [YourCallCDR].[dbo].[MasterData]
([CDRRef],[CLI],[AccessNumber],[TransNum
ber],[StartDate],[EndDate],[DurSec])
VALUES
(ADF8912GGE,0122233355,0777000000,077700
0000,10/01/05
10:18:05,10/01/05 10:18:32,27)
So on and so forth. . . .
Stign - I did try your query; Amended as follows:
SELECT T1.StartDate,
COUNT(SELECT DISTINCT CLI FROM MasterData T2
where T2.StartDate = T1.StartDate and CLI not in (select distinct
CLI from MasterData T3 where T3.StartDate < T2.StartDate))
from MasterData T1
group by T1.StartDate
But even after playing around with it, I receive the error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Hope the additional info helps someone help me solve this one!!
Thanks
H|||harrys@.gmail.com wrote:

> Hope the additional info helps someone help me solve this one!!
Indeed much better, only the data isn't correct. You can use this
great freeware program to generate some data that can be inserted:
http://www.rac4sql.net/objectscriptr_main.asp
Try this query:
SELECT T1.StartDate,
(SELECT COUNT(DISTINCT CLI) FROM #MasterData T2
where T2.StartDate = T1.StartDate and CLI not in (select distinct
CLI from #MasterData T3 where T3.StartDate < T2.StartDate)) as UniqueCLI
from #MasterData T1
group by T1.StartDate
It doesn't give any error.
Kind regards,
Stijn Verrept.