How to enable Email delivery for Subscriptions

Hello,
Grateful if someone tell me the steps that needs to be carried out for
enabling the Email delivery "Report Server Email" option when creating a new
report subscription.
Currently, I do not see this option except Printer delivery and File delivery.
Thanks,
KaushikYou need to configure your server for email. You have several options on how
to do this. The easiest (and requires no email client on the server) is to
send it on to another email server that actually sends the email.
BOL: E-mail Setting (Reporting Services Configuration
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/cdad1529-bfa6-41fb-9863-d9ff1b802577.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kaushik" <Kaushik@.discussions.microsoft.com> wrote in message
news:5A97EFC4-2A84-413A-A40E-4E96D07207EC@.microsoft.com...
> Hello,
> Grateful if someone tell me the steps that needs to be carried out for
> enabling the Email delivery "Report Server Email" option when creating a
> new
> report subscription.
> Currently, I do not see this option except Printer delivery and File
> delivery.
> Thanks,
> Kaushik

How to do weighted average in Cube Calculation?

I am struggling with creating a weighted average calculation in my SSAS 2005 cube. I have a calculated member that computes the average Height of my products. This works out fine when I slice that measure up by Product Code. However, when I use it in another calculation, it appears to be defaulting to the ALL member.

Using the SQL Profiler connected to SSAS I was able to extract the following MDX when I got a Pivot Table in Excel set up the way I want initially:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Product].[Products - Product Code]})})), {[Measures].[Cut Jobs - Avg Piece Height]})

ON COLUMNS

FROM [DN DWH Cube]

The MDX above gives me a breakdown of each product code and it's average height.

Now what I need to do for another calculation is for each product code, take the sum of the average height * number of parts / total number of parts.

For example, say for a given dimensional slice (date or job number, production run, etc...) I have the following data:

Prod Avg Height Num Parts

A 10 100

B 7 500

C 23 225

The resulting formula should look perform the computation like this:

((10 * 100) + (7 * 500) + (23 * 225)) / 825 = 11.72

Wheras the actual average of the heights is 24.6

How do I write the MDX in a cube calculation so that I can use this weighted average value in other calculations?

Thanks in advance!

Is "Avg Height" a cube or calculated measure - if calculated, what are the underlying cube measures and formula? I'm assuming that "Num Parts" is a cube measure, of course.

|||

Avg Height is a calculated measure defined as:

[Measures].[Product Height] / [Measures].[Fact Count]

|||

In that case, if you want a weighted average just over Product Codes (as opposed to weighted average at the fact level), you could try something like:

Code Snippet

Sum(existing [Dim Product].[Products - Product Code].[Products - Product Code],

[Measures].[Avg Height] * [Measures].[Num Parts])

/ [Measures].[Num Parts]

|||

Thanks Deepak. That worked perfectly. One question, in the MDX you wrote, how is this

[Dim Product].[Products - Product Code].[Products - Product Code]

different from this:

[Dim Product].[Products - Product Code]?

|||

The naming pattern is: Dimension->Hierarchy->Level, so [Dim Product].[Products - Product Code] is a hierarchy, and

[Dim Product].[Products - Product Code].[Products - Product Code] is a level of that hierachy.

How to do weighted average in Cube Calculation?

I am struggling with creating a weighted average calculation in my SSAS 2005 cube. I have a calculated member that computes the average Height of my products. This works out fine when I slice that measure up by Product Code. However, when I use it in another calculation, it appears to be defaulting to the ALL member.

Using the SQL Profiler connected to SSAS I was able to extract the following MDX when I got a Pivot Table in Excel set up the way I want initially:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Product].[Products - Product Code]})})), {[Measures].[Cut Jobs - Avg Piece Height]})

ON COLUMNS

FROM [DN DWH Cube]

The MDX above gives me a breakdown of each product code and it's average height.

Now what I need to do for another calculation is for each product code, take the sum of the average height * number of parts / total number of parts.

For example, say for a given dimensional slice (date or job number, production run, etc...) I have the following data:

Prod Avg Height Num Parts

A 10 100

B 7 500

C 23 225

The resulting formula should look perform the computation like this:

((10 * 100) + (7 * 500) + (23 * 225)) / 825 = 11.72

Wheras the actual average of the heights is 24.6

How do I write the MDX in a cube calculation so that I can use this weighted average value in other calculations?

Thanks in advance!

Is "Avg Height" a cube or calculated measure - if calculated, what are the underlying cube measures and formula? I'm assuming that "Num Parts" is a cube measure, of course.

|||

Avg Height is a calculated measure defined as:

[Measures].[Product Height] / [Measures].[Fact Count]

|||

In that case, if you want a weighted average just over Product Codes (as opposed to weighted average at the fact level), you could try something like:

Code Snippet

Sum(existing [Dim Product].[Products - Product Code].[Products - Product Code],

[Measures].[Avg Height] * [Measures].[Num Parts])

/ [Measures].[Num Parts]

|||

Thanks Deepak. That worked perfectly. One question, in the MDX you wrote, how is this

[Dim Product].[Products - Product Code].[Products - Product Code]

different from this:

[Dim Product].[Products - Product Code]?

|||

The naming pattern is: Dimension->Hierarchy->Level, so [Dim Product].[Products - Product Code] is a hierarchy, and

[Dim Product].[Products - Product Code].[Products - Product Code] is a level of that hierachy.

How to do weighted average in Cube Calculation?

I am struggling with creating a weighted average calculation in my SSAS 2005 cube. I have a calculated member that computes the average Height of my products. This works out fine when I slice that measure up by Product Code. However, when I use it in another calculation, it appears to be defaulting to the ALL member.

Using the SQL Profiler connected to SSAS I was able to extract the following MDX when I got a Pivot Table in Excel set up the way I want initially:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Product].[Products - Product Code]})})), {[Measures].[Cut Jobs - Avg Piece Height]})

ON COLUMNS

FROM [DN DWH Cube]

The MDX above gives me a breakdown of each product code and it's average height.

Now what I need to do for another calculation is for each product code, take the sum of the average height * number of parts / total number of parts.

For example, say for a given dimensional slice (date or job number, production run, etc...) I have the following data:

Prod Avg Height Num Parts

A 10 100

B 7 500

C 23 225

The resulting formula should look perform the computation like this:

((10 * 100) + (7 * 500) + (23 * 225)) / 825 = 11.72

Wheras the actual average of the heights is 24.6

How do I write the MDX in a cube calculation so that I can use this weighted average value in other calculations?

Thanks in advance!

Is "Avg Height" a cube or calculated measure - if calculated, what are the underlying cube measures and formula? I'm assuming that "Num Parts" is a cube measure, of course.

|||

Avg Height is a calculated measure defined as:

[Measures].[Product Height] / [Measures].[Fact Count]

|||

In that case, if you want a weighted average just over Product Codes (as opposed to weighted average at the fact level), you could try something like:

Code Snippet

Sum(existing [Dim Product].[Products - Product Code].[Products - Product Code],

[Measures].[Avg Height] * [Measures].[Num Parts])

/ [Measures].[Num Parts]

|||

Thanks Deepak. That worked perfectly. One question, in the MDX you wrote, how is this

[Dim Product].[Products - Product Code].[Products - Product Code]

different from this:

[Dim Product].[Products - Product Code]?

|||

The naming pattern is: Dimension->Hierarchy->Level, so [Dim Product].[Products - Product Code] is a hierarchy, and

[Dim Product].[Products - Product Code].[Products - Product Code] is a level of that hierachy.

How to do Running Total on a field

I am creating a Summary report and on one of the fields "Resume Exists" has values of Yes or No. I am trying to do a summary by BU on this field to say how many resumes don't exist. So I would like it to total how many No's are in the Resume Exists field.

I was thinking of having the field on the detail row, but hide it, then do a summary on the footer.

I have not been very successful and was wondering if anyone has done this and if you can provide me an example. Any help would be greatly appreciated.

If you just want to get a total per group (BU), you don't need to use running total. Just add an aggregate on the group level like this: =Sum(IIF(Fields!ResumeExists.Value="No", 1, 0)). However, if you want to have a running total in the detail row to show how many No's you've encountered so far in each BU, you need to use the running value function: =RunningValue(IIF(Fields!ResumeExists.Value="No", 1, 0), Sum, "BUGroup").