Calculated Measure using Datediff

Hello out there,

i am pretty new to SSAS and have to add a calculated measure that uses the Datediff function from T-SQL.

In detail I want to calculate the average Datediff between a selected Date and the Dates in the DB Table DimUsers.

For example:

UserID = 1 - FirstVisitDate = 05-01-2005
UserID = 2 - FirstVisitDate = 07-05-2005
UserID = 3 - FirstVisitDate = 07-19-2005
.
.
.

And now I want to display the average DateDiff in Days between those Datetimes and a selected Date (let's take 01-07-2006) for showing it in the Cube Browser.

I hope I explained it clear enough and someone please show me a detailled way to do that.
Thanks a lot in advance!
Greets
Claudio

Here's an Adventure Works query modified from an earlier thread - it computes the average difference of the Start Date of a Promotion from a given date (here it's 2005-01-01):

>>

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

CDate("2005-01-01"))),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

--

AvgDaysSinceStart
All Promotions 876.13
Discontinued Product 603.50
Excess Inventory 677.00
New Product 550.00
No Discount 1,310.00
Seasonal Discount 656.67
Volume Discount 1,280.00

>>

|||Hello Deepak,

wow thank you for your quick answer again!

It's almost what i need. How can I use a dynmaic value instead of CDate("2005-01-01")?

I use the Server Time Dimension and tried

AVG(existing [Dim User].[Dim User].[Dim User], DateDiff("d", [Dim User].[FIRST VISIT DATE].MemberValue, [Time].[Year - Half Year - Quarter - Month - Date].[Date].MemberValue))

that is not working.
|||

Well, it depends on how the dynamic value is defined. For example, if it should be the last date in the [Date] dimension:

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

Tail([Date].[Date].[Date]).Item(0).MemberValue)),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

|||Thats closer to what i am looking for. But how can I just use it full dynamic? I want to go to the Cube Browser and select a Date in the <select dimension> on top together with the calculated measure in the data area.

I guess (but i'm not sure) it's a similar approach as it is used in the Calculate measure "Growth in Customer Base" from Adventure Works

Case

When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0
Then "NA"

When IsEmpty
(
(
[Date].[Fiscal].CurrentMember.PrevMember,
[Measures].[Customer Count]
)
)
Then Null

Else (
( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] )
-
( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] )
)
/
( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] )

End

... is it?|||

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

[Date].[Date].MemberValue)),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

where [Date].[Date].[July 1, 2004]

|||Thanks a lot Deepak! That works perfect!
What should I do without all your help? You are great!

Greets
Claudio

Calculated Measure And Drill Through

I am getting the error below in Proclarity:

"Error Accessing Drill To Detail Information

xxxxxxx in dimension Measures is a calculated member."

Can AS2005 support drill through from a calculated member, if not is there a work around?

Thanks

The answer is pretty easy but you might not like it: The drillthough is not supported on the calculated measures or calculated members.

Not sure what would you expect from the drillthoguh into calculated measure or member. Calulated member could be totally unrelated to the facts in your cube. How would you drillthorouh into that? You can definitely can come up with simple case of calc measure being sum of two measures in the same measure group, but that is very narrow and not practical case.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Calculate Unit % by Category

I have a report that is grouped by Category and on each detail line I need to show the % Units Sold per Category. I can't figure out how to get the total Units Sold per Category before I write the individual lines. Can someone, please, walk me through this process?

Thank you very much!!!

ChrisRight click on the filed select summary and summary type as sum and Check the button "show as Percentage" and select again the filed

Calculate 2 textboxes?

I have 2 textbox in my detail table. I want to be able to minus textbox1 with textbox2. In each of those textbox is a subreport that contains a stored procedure. Here's my intended output.

Example:

ID Sales Cost Gross

123 $23.00 $15.00 $8.00

421 $20.00 $8.00 $12.00

Any ideas?

Did you try using the VBScripting capability of the report ?

|||

I hope that someone corrects me since I don't like my work around but...

You can not pull information out of a sub-report (I am pretty sure). The work around that I have been using is the create a view in SQL and then use the views to equate the total that I am looking for.

Good luck I hope that helps.

Calc % in crystal detail againt running total

Quick question.
I have a grand total being calculated in a formula, for each detail line I am trying to get a percentage of that number against a value in the detail. The problem is that the grand total is running and only calculates against the current running total not against the grand total when the report finishes. Any way around this?Hi!

1) Insert the Formula Field from Report Design Page

2) Named as Percentage and write the code to the formula field coding area

{table.numberfiled}/100

3) And Drag and Drop the Formula Filed on Details Section Where do u want place.

C# Master-Details (Separate Pages)

My question is setting up master detail pages using stored procedrues. In the tutorials C# Master-Details (Seperate Pages) example they use the following code for the master page for the navigation.

<asp:HyperLinkField HeaderText="View Details..." Text="View Details..." DataNavigateUrlFields="au_id"
DataNavigateUrlFormatString="DetailsView_cs.aspx?ID={0}" />

The call to the Details PageDetailsView_cs.aspxuses the following code

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT dbo.authors.au_id, dbo.titles.title_id, dbo.titles.title, dbo.titles.type, dbo.titles.price, dbo.titles.notes FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id WHERE (dbo.authors.au_id = @.au_id)"
ConnectionString="<%$ ConnectionStrings:Pubs %>">
<SelectParameters>
<asp:QueryStringParameter Name="au_id" DefaultValue="213-46-8915" QueryStringField="ID"/>
</SelectParameters>
</asp:SqlDataSource>

I can replicate the example calling the detail page but I am unable to make the detail page work when using a stored procedure as the asp:SQLDataSource. Using the above sql code as a stored procedure in the <SelectParameters> I am not able to return the data set using either asp:QueryStringParameter or asp:Parameter as I have built other forms using stored procedures and have tested the procedure and know that it works. Can someone point me in the right direction.

Thanks

The soulution works the same with stored procedures but you need to have

EnableSortingAndPagingCallbacks="false" not true

|||I spoke to soon, my previous post did not resole the issue of calling a stored procedure from the detail page. The master page passes the correct value to the detail but the procedure does not recognize it.