.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

12 months total from last active period at year level

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server
 

Hi all,

I need to calculate last 12 months at a year and month level.
Using Adventure works as an example this works fine for when a month is chosen using the below mdx.


WITH MEMBER MonthLevel
AS
SUM( {
(
OPENINGPERIOD([Date].[Calendar].[Month]).LAG(11))
:
OPENINGPERIOD([Date].[Calendar].[Month])}, [MEASURES].[Internet Sales Amount])

select {[Product].[Product].&[486]} on rows,
{MonthLevel} on columns
from [Adventure Works]
WHERE
[Date].[Calendar].[Month].&[2003]&[12]

However I cant create a member for the year level to go back 12 months e.g. the following mdx at first glance would seem to be correct and brings the same results.

WITH MEMBER YearLevel
AS
 [MEASURES].[Internet Sales Amount]

select {[Product].[Product].&[486]} on rows,
{YearLevel} on columns
from [Adventure Works]
WHERE
[Date].[Calendar].[Calendar Year].&[2003]

The issue is when the year is not complete e.g. if we were in the 6 month of 2003 only then YearLevel would be incorrect as it yields a YTD value and not the last 12 months total.

Is there an easy way to sum up 12 months from the last active period when the where clause is at a year level (last active period being the highest period in the year when a sale was made)?




View Complete Post


More Related Resource Links

Calculating Full Year Total

  
Im creating a sales report in SSRS by pointing it at a cube in SSAS.  Im creating calculated members to aggregate my data in various ways so they can be shown on the report.   I have a FACT table containing sales figures and various dimension tables including a server created time dimension.   Ive managed to create pretty much everthing I need (e.g. Current YTD, Prior year YTD).  What I now need is a full year figure to compare current year sales to.   Below is what Im aiming for i.e. the total of 122712 from 2009 appearing against every month in 2010.   Fiscal Year Fiscal Month Count Full Prior Year sales 2009 Apr-09 9805     May-09 9942     Jun-09 8986     Jul-09 9696     Aug-09 8912     Sep-09 10097     Oct-09 11713     Nov-09 10434     Dec-09 8820     Jan-10 10821     Feb-10 11844     Mar-10 11645     Total 122712   2010 Apr-10 3517 122712   May-10 1669 122712   Jun-10 2518 122712   Jul-10 3306 122712   Ive been bashing my head against a brick wall with this one so any help would be gratefully received!

security top total and current level

  
the locig of data is Country 1 - City1 - Company1 -> Nett Income Country 1 - City1 - Company2 -> Nett Income Country 1 - City1 - Company3 -> Nett Income                                                  Total Income   How can i achive the following logic  - the user (role) Company2 have to see only Total for City 1 (Company1+Company2+Company3), just total and Country 1 - City1 - Company2 -> Nett Income I can use dimension data and check Country 1 - City1 - Company2 but in this case he/she can't see totals for City 1 any ideas ?  

getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


Total Sales and Total Last Year Sales

  

I have this code below that works fine. But since i have filtered the query to only show 10 specific items. I only get the total yr and ly sales for the specific items. I want to get the Total Sales for all items. Is there a way i can create a set or a measure that would show the Total sales no matter what the item number. Without having to take the filter off the report. Please help!

 

Also I want to add that I want to display ranking on the sales for the selected items only. So this is why i did the report this way.

 

 

 

WITH

MEMBER [Measures].[LY Sales] AS (ParallelPeriod([Time].[Fiscal Period].[Year], 1, [Time].[Fiscal Period].CurrentMember), [Measures].[Vet PL Gross Sales Amt])



SELECT 

NON EMPTY{[Measures].[Vet PL Gross Sales Amt] ,  [Measures].[LY Sales]}

     ON COLUMNS



, NON EMPTY { ( (DESCENDANTS([Sell To Customer].[Geography].[RC Division].&[0], 

[Sell To Customer].[Geography].[Customer]) 

* [Sell To Customer].[City].[City].ALLMEMBERS 

* {[Brand].[Brands].[Item].&[230],

[Brand].[Brands].[Item].&[347],

[Brand].[Brands].[Item].&[349],

[Brand].[Brands].[Item]

getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


how to create calculated measure in cube that always gives value on Year level in Date hierarchy

  

Hello,

I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie


how to create calculated measure in cube that always gives value on Year level in Date hierarchy

  

Hello,

I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie


Total sales past 12 months

  

Hi

I want to do something as simple as Adding the previous 12 months sales to a row.

My tablix report is suppose to look something like this:

Year Filter: 2010

Months:           Months Sales           Sales Past 12 months
Jan                    12345                        67890
Feb                    23456                        78901
Mar                    34567                        89012
And so on

So ... in the third column, Sales Past 12 months, say for Mar 2010, the Sum should be something like:
Sales Mar 2010 + Sales Feb 2010 + Sales Jan 2010 + ... + Sales Apr 2009 = 89

sum the total year amount even if a time dimension filter exists

  

my need is to get the total of a year amount even if a time filter exists for the same year.

p.e.

I have [Measures].[Amount] and a [Time] dimension and an hierarchy like [Time].[YearMonthQuarter].

In excel I filter [Time] hierarchy and I only keep the first Quarter.

I need to see a measure that will give me the total of year amount (not filtered).

 

Thank you in advance

M. 

 


A Low Level Look at ASP.NET Architecture

  
ASP.NET is a powerful platform for building Web applications, that provides a tremendous amount of flexibility and power for building just about any kind of Web application. Most people are familiar only with the high level frameworks like WebForms and WebServices which sit at the very top level of the ASP.NET hierarchy. In this article I'll describe the lower level aspects of ASP.NET and explain how requests move from Web Server to the ASP.NET runtime and then through the ASP.NET HTTP pipeline to process requests.

AGE CALCULATION IN YEARS AND MONTHS

  
How to calculate childs age,but when i enter childs age as '03/27/2007' it returns 3 years where as actual age is 2 yr 11 months

Show grand total running total in GridView Footer

  
In many scenarios we need to display Running total as well as Grand total in GridView footer. In this post i will try to explain in an easy way that how we can display running total & Grand total in a GridView footer combindly. To explain this solution using an example here i use a sales order report. The report contains all sales order amount in a tabular way. I will use a GridView to display sales order amount and use GridView footer to display Running total & Grand total. Let we have a customer table with id and name column plus an orders table with OrderID,CustomerID,OrderDate & Amount. Now our goal is to show all customers order with amount as well as page wise running total & grand total. Note that running total is necessary when you enable paging in a GridView where as Grand total is always you can consider.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no

  

Hi,

 I keep getting the following error. I also added time out parameter in the connection stirng and it still did not help. Has any one faced similar issues.

Thanks in adavance.

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlCl

Automatically redirect to HTTPS and check if SSL is active

  

Hello,

I've set in the web.config:

    <!-- User Authentication -->
    <authentication mode="Forms">
      <forms loginUrl="~/Page/View/Content/Login.aspx" timeout="2880" requireSSL="true" />
    </authentication>


This works fine. If there is no ssl connection, the login form doesn't continue. But I would like to check if ssl is active (in codebehind) and, if it's possible, to automatically the user to the https page if he's currently on http. (I could do this job by manually redirect with page.response("https://url.../login.aspx"); but this is not really generic. For example if the webapp is moved to another domain it doesn't work anymore... I'm looking for something like: FormsAuthentication.RedirectWithSSL(); or so... :D)

Regards,
Chris 



How to set SearchRoot Path in Active Directory in this scenario

  

How to set SearchRoot Path in Active Directory in this scenario:

Functionality: We have scenario that 1<sup>st</sup> hit one LDAP server with some 'fixed user name' &' fixed password ' and filter data with specific User name(which given by user) getting 'User dn'.

After that we hit Next LDAP server based on the 'User dn' getting from 1<sup>st</sup> server.

(So, in my case 1<sup>st</sup> LDAP server works like as Load balancing server but functionalitywise it is different)

|-------------------------------------------------------------------------------|
|                                         

active directory exception unusual behaviour

  

Hi 
i have a small problem
i want user names from active directory for an auto completer type of service
the method is always throwing an exception 
-------
"searcher.FindAll()' threw an exception of type 'System.DirectoryServices.DirectoryServicesCOMException' System.DirectoryServices.SearchResultCollection 

Active Directory and .NET: paging the search result

  
Hi everybody!

I have a question about Active Directory in .NET
For my project I need to fetch results from Active Directory search page by page, because later I would need to bind it to the pageable DataGrid.

I tried to use .NET library DirectoryServices for that, but paging provided by this library is transparent to the user and is used only to increase the efficiency of searching, when results are too big. I.e. I cannot tell the DirectorySearcher to give me first page, then the next or previous page, it returns me all resulting pages in one bundle.

However, what I need is to explicitely get page after page directly from the AD searcher and to be able to go at least one page forward or backward.

The solution with copying all results to the DataBase and then do the paging is not accepeted by the clent, since it is too inefficient. And since it's a Web app, I cannot keep results in memory either.

I found some hints about COM Interface, but I could not find good and detailed examples or explanations. I aslo found this line of code: DirectoryServices.Interop.IDirectorySearch.ExecuteSearch(), but I don't know which libraries should i add to be able to compile this code.

If somebody could help me out with that problem, any suggestion is welcome, 'cause this issue is eating me alive :)

Thank you!
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend