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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

IN SSAS 2005, total count is incorrect for a partial month

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


IN SSAS 2005, when I navigate Cube->browser and I drag my fields "Day of week" to rows and couple of measures namely "M1" and "M2" to columns

and applied a filter "hierarchydate" in the filter section(eg partial month Jan 10 to Jan31), I get the total count incorrect. I also have the following observations

1) for example "Instead of giving Sunday "Sub total" for that partial month i.e "Jan 10 to Jan31", it is displayed with all the Total Sunday's count for all the years

2) IN Cube-> browser , it is working fine, if I apply the filter in "select dimesion" section instead of applying filter in "drop filter fields section"

Is there any such kind of issue in SSAS 2005?

Please reply me ASAP if you have any information

View Complete Post

More Related Resource Links

Count of Rows - Records between month - T-SQL 2005


Hi Below is my Scenario.

Record                       Start Date                           END Date

  1                              01/02/2008                          04/05/2009

  2                             01/25/2008                           06/19/2009

  3                              02/02/2008                   

Kerberos between MOSS 2007 and SSAS 2005


I realize this is probably going to be one of those vague questions that I am not going to get much help on here, but I thought I'd give this a shot before we go the MS Incident route on monday.

We have tried to setup Kerberos between MOSS 2007 AND SSAS 2005 to no avail.  We have been through the knowledge base articles outlining the setup multiple times with all the experts on MOSS and Security here where I work.  We've used other materials we have on kerberos here.  But the end result is that the double hop is not happening.  We are trying to connect three ways: excel services, ssrs 2005 in integrated mode, and Sharepoint KPI's (using analysis services).  In every case the connection is not happening.

Other details are that the ssrs integrated mode seems to be setup right because I do get a report (albiet all it has is a connection error message).  Excel services works fine if I use the unattended service account, but when I switch the odc file to windows (should cause kerberos to kick in) it fails.  When I try to add a kpi to the kpi list it can't retrieve a list of kpi's from ssas.

In all cases I am the user trying to perform these operations, and I have total access to the cube -- I'm the developer.  I have no problems connecting to the cube directly through excel, so the security at that end passes t

Shared dataset in SSRS 2008 R2 running on SSAS 2005

I am trying to create a report in SSRS 2008 R2 using SSAS 2005 as a datasource. My dataset has a date parameter. When I create an embedded dataset in my report everything works fine, but when I create a shared dataset with the exact same mdx query, I get the following error: An error occurred during local report processing. The definition of the report 'xxxx' is invalid. The Value expression for the report parameter 'DateCalendarHierarchy' contains an error: [BC30034] Bracketed identifier is missing closing ']'. Does anybody have any ideas? Is this a bug? Thanks, Louis

Plot Running total charts from SSAS Cube

Environment: I am using SQL Server 2008, I have installed SSIS, SSAS and SSRS. Objective: Create a Effort V/S Defect plot in SSRS 2008 Approach: 1. Created the SSAS cubes with Effort and Defects data by timeline (rollup of data based on Week ending date) 2. Created SSRS project and linked the dataset to SSAS cube. But i am unable to plot a Effort (X-Axis) v/s Defects (Y-Axis). As soon as i drag the measures into the query designer in dataset properties, the effort rollup will break. Following is the data that has been created in SSAS. I need to plot a graph of Hours v/s defects (Running total). Please guide me on how to plot the graph using SSRS 2008 from the already created SSAS cubes Week Of Hours Defects 10/24/2009 8   11/7/2009 63   11/14/2009 68   11/21/2009 80.5   11/28/2009 139   12/5/2009 221.25   12/12/2009 131.75   12/19/2009 124.5   12/26/2009 61.5   1/2/2010 73   1/9/2010 153.5   1/16/2010 149.5   1/23/2010 196   1/30/2010 163   2/6/2010 155.5   2/13/2010 178   2/20/2010 138   2/27/2010 161.5   3/6/2010 189   3/13/2010 191.9   3/20/2010 240.5 9 3/27/2010 260.2 5 4/3/2010 214.5 13 4/10/2010 274.6 24 4/17/2010 200.5 15 4/24/2010 227 9 5/1/2010 237.05 18 5/8/2010 190.5 12 5/15/2010 156.25 6 5/22/2010

SSAS Calendar Week commencing with split at month

Hi all, Wonder if someone can help. Our CFO wants to see a cube calendar that shows week commencing but stops at the end of month. For example if the dates are Monday  28th   Tuesday  29th   Wednesday 30th   Thursday 1st    Friday  2nd Saturday 3rd Sunday   4th So for this example the week commencing is the 28th of Month 1 and Thursday is week commencing 1st of the next. I am having a problem seeing how I can do this. If I add the weeks to a standard calendar I get the week commencing 28th which includes the 7 days, naturally. I cannot help feeling I am missing something obvious. Cheers

Can SSAS 2005 run with Sql Server 2008 database engine?

Are there any known issues of running SSAS 2005 with SQL Server 2008 database engine?

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

SSAS 2008 and SSAS 2005

Olap cube created in SSAS 2008 and processed in SSAS 2008 gets same result of measure as the result counted by hand. However, the same cube processed in SSAS   2005 shows incorrect value. What can be the reason?

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

Excel 2007 connectivity to SSAS 2005 cube

Hello - I am using an excel 2007 odc file to connect to an analysis services 2005 cube (on windows server 2003 R2 OS). I receive the following error message   "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server" Have already attempted the following fixes but running out of ideas now 1. Added localeIdentifier to ODC file    <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=bisql;Initial Catalog=SLAM ACT BUD; LocaleIdentifier = 1033 </odc:ConnectionString> 2. Ensured that all dimensional attributes which are used in MDX calculations (named sets, calculated members) are set to "AttributeHierarchyEnabled  = True" Running out of steam so any help is greatly appreciated.

Sales Total, Get Curent Month

The date functions in sql is pretty complex to me. I want to get the sum of Grand Total for the current month. OrderDate is a DateTme Column. SELECT SUM(GrandTotal) FROM CompletedOrdersHistory WHERE OrderStatus='COMPLETED' AND OrderDate=@CurrentMonth;

SSRS 2005 with SSAS Parameter is not declared

Hello, I'm builind a mdx query to my report in ssrs 2005 with ssas 2005 all on SP3. I'm having the hardest time to add a custom parameter to the query, I add it to the dataser parameters tab ("..." button). Then when I try to use it in my query I get an error: The query uses a parameter, which is not declared. I've seen some examples on the goo.. internet but none seems to work. My query runs fine like this STRTOMEMBER("[dimension].[entity].&[16]") but it fails if I try to do this.. STRTOMEMBER("[dimension].[entity].&[" + @entityid + "]") Am I trying to do impossible? :) Any idea on how to get such filter to work? My filters are a bit complex that's why I can't simply use the "Parameter" checkbox in the query designer mode. Thanks!

SSRS 2005 conditional count in chart

Hi, I have a column chart in which I want to display the % of Invoiced Sales in 24 hours and % of Not Invoiced Sales in 24 hours. The data should be categorized by date, so for each date I can see the % of invoiced and not invoiced sales within 24 hours. My dataset contains: sales id, sales status (only invoiced are permitted and I took care of that in my query), date and I have the nbDays which is bound to a function that determinate if the sale is invoiced in 24h. So if the nbDays < 3 it's in 24h otherwise it's not. I want my chart to display for each date two column: 1- for invoiced in 24, 2- not invoiced in 24 h. So, I drag the date fields to Category fields area and sales id twice to Data fields area. The idea is to use one for invoiced in 24h and the other is for the other case.   So, I want to know how can I write a conditional expression to get this requirement done and make the data fields sales id displays data according to my condition: I tried this but it didn't work: =count(iif(fields!nbDays.value <3, 1,0))  and also the: =iif(fields!nbDays.value <3, count(fileds!salesid.value),0)   any suggestions will be welcomed and appreciated...

Trouble to connect report on SSAS 2005

Hello! I have deployed my first report (ssrs2008) using a ssas (2005) cube as data source but I can't get it to work. I setup the credentials to use Windows Authentication in the SSAS data source and when I open the report from the rs server it renders fine, however when I try it from a workstation I get the error below: The SSRS instance is not on the same server as SSAS. Any Clue? Thanks! Cannot create a connection to data source 'TestCube'. The connection either timed out or was lost. Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. An existing connection was forcibly closed by the remote host

How to Correlate Last weekday of previous Month and First Weekday of current Month ? - T-SQL 2005

Hi friends, below is my detail description. I woulld like to show busines users Previous Months Last Weekday in Drop Down List as a Parameter. Now, if they select Previous Months Last Weekday, then i need to pass First weekday of Current month. How can i correlate this things ? if you need more information, let me know.   Thanks.

How to Get Total Minutes From Hours in SQL Server 2005?


Hi All.........!

I have 10:45 Hours and Now I want to get Total Minutes Like 645 Minutes.

How Can I Get in SQL Server 2005?

Please Help Me................!

Thanks in Advanced..............!

How to get total record count in an OUTPUT Parameter


I couldn't get the total record count in a, in my stored proc, in a given scenario. Please help


My SP looks like this:

create proc RecordCount

       @CustomerID varchar(10),

       @RecCnt int OUTPUT


begin tran

if(CustomerID = 'ABC')

@RecCnt = select count(*) from Customers where CustomerID='ABC'



begin tran


@RecCnt = select count(*) from Customers where CustomerID='XYZ' and TranDate > '05/01/2009'




-- This is how I called my SP

declare @rc int

exec RecordCount 'XYZ', @RecCnt = @rc OUTPUT

select @rc

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