Measure Weirdness

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Specifying a default date value for SPROC parameter Might I need to CAST or CONVERT the variables Also, the startdate and enddate are datetime fields. Tag: Measure Weirdness
  - 2
    - Can't get at first row of input buffer...why?! hey, thanks for the quick reply. I have to say I don't really know about the internal workings of things...I'm new to SSIS, SQLServer and .NET...BUT, what I do know is that within that Sub I can in fact advance through the rows.....I do this like so: Do While row.NextRow() someVariable = Row.whatever_Input_Column_Ive_defined_in_the_components_input Loop If I use the above construct, then it does loop through the rows...NextRow() must return a False when there are no more rows and then the loop stops. Perfect...except I miss the first Row... I'm assuming this is because NextRow is called before i even get into the loop.... I must be doing something wrong....but what Thanks!! andy Tag: Measure Weirdness
  - 3
    - Exporting temporary table to excel spreadsheet Yes, I could put all the final information in a view. Is this going to help I'm a new in SQL and I'm not familiar with all the intricacies of the environment. Thanks for the help. Lauran Tag: Measure Weirdness
  - 4
    - How can I get a row count of CTE recordset? Yes, use Mark's answer - it's most definitely the one you want. The great thing about this is that you won't find yourself having a massive performance hit from doing it this way. It will do it in the same pass. Rob Tag: Measure Weirdness
  - 5
    - add a reference in scriptComponent That's not the only thing that you need to do. Read this: VSA requires DLLs to be in the Microsoft.Net folder (but not all the time) ( http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-VSA-requires-DLLs-to-be-in-the-Microsoft.Net-folder-_2800_but-not-all-the-time_2900_.aspx ) -Jamie Tag: Measure Weirdness
  - 6
    - How to Drill Down a Parent Child Hierarchy in Reporting Services 2005 ? Hi, I found something : Code Block As I mention, I am using Reporting Services and I have a Cube with a parent child dimension. When I use the dimension in a MDX statement I am not able to retrieve the parent of the current member because I don’t have any hierarchy. I don’t want to define a fixed hierarchy because can always change. The only way to retrieve it is to create a member like it follows: with member [Measures].[Cost_Parent] as iif ( [Cost].[Cost]. CurrentMember . Level . ordinal >1 ,[Cost].[Cost]. CurrentMember . Parent . Name ,[Cost].[Cost]. CurrentMember . Name ) Select [Measures].[Cost_Parent] on 0 ,[Cost].[Cost].allmembers on 1 From Cube Is there a better way to do it But with this MDX Statement it is only possible to get one drill down area. Does somebody know if it is possible to enable a drill down for the whole hierarchy on every level Or it is possible to get all elements of the parent child hierarchy from level 1 as one calcualted member, then another calculated member which includes all members from level 2 .... JF Tag: Measure Weirdness
  - 7
    - how to properly write the mdx? Hi, Adding NON EMPTY. It will remove Empty Values in Query Output. WITH MEMBER [Measures].[TotalCount] AS [Measures].[Case Activity Count] SELECT { [Measures].[TotalCount] } ON 0, NON EMPTY { [Activity].[Phone] } ON 1 FROM [Case Activity Summary]; Bhudev Tag: Measure Weirdness
  - 8
    - error when running SSIS package as a job I am running into an error when running a package using a scheduled job under SQL Server Agent account. I am getting following error: Date 6/12/2007 4:19:15 PM Log Job History (VistaODSFeed) Step ID 0 Server 006-DEVSQL2005 Job Name VistaODSFeed Step Name (Job outcome) Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message The job failed. Unable to determine if the owner (ACI\snasir) of job VistaODSFeed has server access (reason: Could not obtain information about Windows NT group/user 'ACI\snasir', error code 0x6ba. [SQLSTATE 42000] (Error 15404)). ACI\snasir is not the account under which SQL Server Agent service runs. However ACI\snasir has sa priveleges. not sure why am I getting ACI\snasir in the error and not the account under which sql server agent runs. ideas Tag: Measure Weirdness
  - 9
    - formatting in sql server in sql server o/p Tag: Measure Weirdness
  - 10
    - Out of memory while querying causes wrong data to be returned Sorry, misprint, I am already running sp 4. Tag: Measure Weirdness
  - 11
    - MDX Calculations Script updates Thanks for the explanations Darren. As you rightly guessed, I would like to allow power users to modify centrally stored calculations. However, BI Studio is overkill for my target population (financial controllers). Right now, when they want to modify calculation formulas, they submit a request to their IT department, along with the formulas to modify. The process works but there is definitely a loss of flexibility. I was hoping for another way to process those modifications.. Thx. Tag: Measure Weirdness
  - 12
    - OnInformation Event Firing Twice? Thanks for those pointers !. Well, it seems that the events are fired once for the package and another at the data flow task level. Once I give a value for the package.Name property I was able to figure this out. Now I have to find how can I disable oninformation events for the data flow task. My understanding is, all events other than the events specific to pipeline (like OnPipelineRowsSent, OnPipelinePostPrimeOutput etc., ) will propogate up wards to the package level. So is it safe to assume that I will not need to log those events in the DFT Thanks Tag: Measure Weirdness
  - 13
    - [RESOLVED]Getting only multiple records for all customers The following query will work for You..(multiple records for all customers - customers who have multiple records) Select * from Orders Where CustomerId in (Select CustomerId from Orders Group By CustomerId Having Count(ProductID) > 1) and ProductId is NOT NULL Tag: Measure Weirdness
  - 14
    - SQL Server 2000 and ASP.NET connection problem Are you sure that " 172.16.3.111 " is the name of your instance This doesn't sound right. Also, is the instance on a local or remote machine If it's remote, then you need to make sure that the server's firewall is properly configured. Here is a KB for configuring SS2K for remote connections: http://support.microsoft.com/kb/841252/ Il-Sung. Tag: Measure Weirdness
  - 15
    - sql server security problem... sweet deal. that worked on my mini test package. i'll give it a bash with the main package thanks for that! Tag: Measure Weirdness
  - 16
    - Combining Output PDF files we're trying to do this on the report server with out having to add another process. Is there any good tutorials for writing a rendering extension I've heard its difficult. Tag: Measure Weirdness
- Next
  - 1
    - SQL2005 SP2 Dec CTP Sharepoint AddIn install problem In Central Admin, can you check if you can find the feature under Site Settings -> Site Collection features and if present make sure that it is activated. Thanks, Sharmila Tag: Measure Weirdness
  - 2
    - Reporting Services Instance not found How did you get it to work I have the same problem. Tag: Measure Weirdness
  - 3
    - SSIS - Several Bugs in creating a simple task Jamie Thomson wrote: -clipped- That kinda makes sense does it not The whole point of encrypting it is so no-one can get at it. That's my understanding anyway. In a sense, I totally agree, however in I'm not trying to access the contents, and I have the permissions I need to backup/restore. The task is meant to transfer a database. Whether an object is encrypted or not should be irrelevant to the task. The transfer works in offline mode, but in online mode I get the above stated error. I'm not trying to access/modify it, I need to move it, and I have the SQL permissions to do so. After some thought, I could increase the Minimum Error Count to 2, but my package will break if any more Ecrypted Stored Procs are added. I'd have to add another script that counts the number of encrypted procs and sets the Min Err count manually. Again, it seems like a LOT of work to copy a database, and if it's a known issue, should there have been functionality built into the xfer database task to compensate I should just be able to drop-and-go with the xfer database task. Any intricacies related to the mechanics of performing the move should be handled by the task itself, and that should include encrypted procs. Jamie Thomson wrote: -clipped- I've never used Copy SQL Server Objects Task but can you not loop over your objects using ForEach Loop's SMO Enumerator (or other) and move them one by one. Well that's really the main issue. The TablesList is a StringCollection property. Properties that are objects can't be set with expressions. This means that variables that are objects have little value other than to pass objects between script tasks. The ForEach loop sets a property on every iteration. There's no way for me to, within the same package, programmatically create a list and pass the list to the Copy SQL objects task. The only way for me to do this would be to create another package that manipulates this package programatically. I'm assuming there was a good reason why the Script Task doesn't have visibility outside of itself other than variables It doesn't make sense to me why the script task can't programatically change properties of other tasks at run-time. Combined with the property/object/expression limitation, any properties within SSIS that are not strings or numbers can ONLY bet set at design time, unless you create another package that alters your package programatically. That's not what I wouldhave expected from SSIS, or MS for that matter. It's a LOT of work to perform mostly simple tasks. I want to build a list of objects, and pass that list to the Copy SQL objects task. I would assume I could just create a variable, drop my list in that variable, and then assign that variable to a property with an expression. That seems logical and simple. I guess the point I'm trying to make is that if it was a design requirement that the tasks should only communicate with each other through variables, then limiting expressions to strings and numbers greatly imhibits the ability of SSIS, negating a lot of the value SSIS and variables provide. Also, if it was a known limitation that expressions couldn't assign obejcts to properties, the next logical step would be to allow the scripts to programatically change properties. Disallowing changing properties at run-time completely because of the limitations of the expression language (which arguably shouldn't even exist) or because someone chose to limit the visibility of the script object seems like a decision made in err. Jamie Thomson wrote: What else would you like to store I haven't yet come across anything that cannot be stored in vars. In theory, you should be able to store and assign any object type that can also be a Task Property. As of right now, you can't programatically alter any properties unless they are a number or string, because of the limitation. Jamie Thomson wrote: Yeah alot of people don't like this but there are good justifications for it. Quite simply, the expression syntax is optimised for what it is provided for. Greasy fast speed. And faster than .Net. Hopefully someone from MSFT will reply will teh proper justification. Not that I don't believe you, but it's hard for me to believe speed was the driving factor. .NET is compiled. An expression syntax would be interpreted, unless SSIS compiles it (which again, would seem like they're re-inventing the wheel). I would assume that once a package is run for the first time, all of the .NET would be compiled and cached. At that point I can't see it being slower at all than an interpreted expression syntax. If they are compiling the syntax, it's hard for me to believe that the minute amount of speed in using a custom-compilation would offset the functionality provided by enabling the .NET framework. Jamie Thomson wrote: It is a problem yeah. The reason is that the scripting environment is VSA - something completely seperate to SSIS. VSA only supports VB.Net. The inclusion of scripting was late in the dev cycle for SSIS v1 and hence they didn't have time to build something that used C#. Given the choice between having VB.Net or nothing at all I know what I'd got for. In the next version VSA is expected to get replaced with VSTO and this will give full C# support. In theory, wouldn't adding C# support to VSA also implicitly add C# support in 2005 Is there a reason they're moving to another framework rather than just adding C# support to VSA I also found a couple of bugs so far. Where do I report them. If I try and set the ListTables property from the VS property window, when I hit ADD, I get a "Could not find a ctor for the String type" error. When adding an item to the project, if I click around and then press cancel, I get some "could not find proxy" error or something like that. Thanks for your help so far Jamie. Tag: Measure Weirdness
  - 4
    - The SELECT permission was denied on the object 'tblName', database 'dbName', schema 'dbo' Thanks a lot Raul. Your advise pointed me in the right direction. I'd previously never set the User Mapping controls in SQL Server Management Studio to allow the NT AUTHORITY\NETWORK SERVICE account datareader & writer access to the db. It now works like a charm! Cheers! Tag: Measure Weirdness
  - 5
    - Substring and Patindex having an User Defined Function Hi All, Can we use the SUBSTRING or PATINDEX function with a User Defined Function as one of the parameters Something like this: Code Block Select SUBSTRING ( Select * from DB.dbo.Function( 'Parameter' ), PATINDEX ( '%\%' , Select * from DB.dbo.Function( 'Parameter' ), 8) Mannu. Tag: Measure Weirdness
  - 6
    - Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string? Then how should I approach this Script task doesn't work for my case What other way should I use Thanks! Tag: Measure Weirdness
  - 7
    - DTS Code works only with VS 2003 but not with VS 2005 ? Thanks very much. Tag: Measure Weirdness
  - 8
    - i want know the difference between "SQL Server 2005 -Std Edition" and "SQL Server 2005 Std license" thank u. but which one i will go for that. can ypu give me the details. Tag: Measure Weirdness
  - 9
    - Report Definition >> There's no way AFAIK to fetch N rows but only display N-M rows. Yes, actually there is <s>. You do need to give yourself appropriate information from the data source, but this doesn't necessarily mean limiting the rows! (because you might have another data region that shows a different count, in the same report, from the same data). Here's a reporting query from my favorite ASP.NET add-on table (ELMAH error handler): Code Snippet SELECT ErrorId, Application, Host, Type, Source, Message, [User], StatusCode, TimeUtc, Sequence, AllXml FROM ELMAH_Error ORDER BY ErrorId ... now suppose we add a column, as follows: Code Snippet SELECT ErrorId, Application, Host, Type, Source, Message, [User], StatusCode, TimeUtc, Sequence, AllXml, Row_Number() OVER (ORDER BY ErrorID) AS ItemID FROM ELMAH_Error ORDER BY ErrorId (you can add a counter like this different ways in other data sources) ... Now we can add the following filter to a table (or whatever): Code Snippet =Fields!ItemID.Value <= 8 ' or whatever maximum you have in mind, ' for example you can say: =Fields!ItemID.Value <= =Parameters!MyMax.Value ... and readers please note, if you have trouble when you follow these instructions: When I built this example I had to CInt() (cast) on both sides of the filter expression (including the literal 8!) to get it to work the first couple of times. It may be because I was building the query interactively for the dataset, and the designer doesn't support this clause so, while it processes the SQL, it is not sure about the data type of the result. IAC it does work. >L< Tag: Measure Weirdness
  - 10
    - Access To Dts.Variables Causes Exception Hey all...I'm pretty new to SSIS packages and things are coming along nicely. My problem is accessing variables in script tasks. I've created two variables (package scope, strings, readonly = false) - aDetailFiles and strDetailFile Within a Foreach Loop I loop through a folder. I've added a script task and within that I try the following: Dts.Variables("User::aDetailFiles").Value = Dts.Variables("User: trDetailFile").Value.ToString() + "|" + Dts.Variables("User::aDetailFiles").Value.ToString() Everytime this script executes I get the following errors: at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index) at ScriptTask_feac87c947ce4431a4fee0ba0e13631d.ScriptMain.Main() in dts://Scripts/ScriptTask_feac87c947ce4431a4fee0ba0e13631d/ScriptMain:line 25 I have searched this error message but the only thing I found was to set the ReadWrite variable property but I don't know where to set that. Expresion property set as false also. Any ideas Thanks. Tag: Measure Weirdness
  - 11
    - Property DefaultSchema is not available for Database [DatabaseName] I do understand the situation you described but in a well design group structure, a user will exist in only one group for a specific database. The group and schema structure will usually be matched. Otherwise it will become difficult to manage security efficiently. Therefore it should be possible to assign a Default Schema to a group. The way it is now, if you have 100 users in a specific group instead of having to add only that group with a Default Schema, we now have to manage 100 different users within the database. If the default schema for these users have to change, there is a high probability that some will be forgotten. I believe Microsoft has a good approach to security by using Domain\Group to which we give privileges and then we had the targeted users to the group. Why should this approach stop at SQL Server Tag: Measure Weirdness
  - 12
    - Cannot insert duplicate key That will not work because my primary key is made up of 3 columns. The remaining 47 columns may make the record unique. However, I only want 1 record for each primary key. Tag: Measure Weirdness
  - 13
    - RS Integration with SharePoint 3.0 - Query with userid parameter When integrating SQL Reporting Services 2005 with Sharepoint Services 3.0, and you have a SRS report that runs over a view with the UserID as a parameter (to filter the records the user has access to), will the SharePoint ReportViewer passover the UserID to RS and RS to SQL Sharepoint security with Reporting Services stops at Item level or does it impersontate down to SQL Should authenticating be Integrated or SQL Should the userid in SQL be exact match with Sharepoint Or are they mapped somewhere Thank You!! Tag: Measure Weirdness
  - 14
    - SQL 2000 - All Client Tools Thanks Euan Tag: Measure Weirdness
  - 15
    - Printing in Reporting Services hangs No - this is not the same issue - none of the fields are very long (all less than 40 characters). And it happens when I print any of the reports. Any other ideas Tag: Measure Weirdness
  - 16
    - SUM in Report Larry, Thanks was it, but is sum all value, for sample if there are 2 value how 14 i'll have a result 28, and i wanted 2, get it, Can you help me your first anwers is correct after i mark how response valid. Tkank you a lot! Tag: Measure Weirdness

JohnBGood

have a measure that is a running total based on a formula:

SUM({NULL:[Time].CurrentMember}, [Actual Work] / [Estimated Work] * [Planned Cost])

When this measure is rolled up for all time (for example 2007) the calculation is correct. For individual weeks or months, the calculation is not correct. See any issue here

Top