Search results for query: *

Thanks. Should have thought about the cross join Doh! 'The world isn't round - it's bent!' Spike Milligan

I have a calendar dimension and I want to see 2 attributes in my query list: [Calendar].[Date].[Date] [Calendar].[Week No].[Week No] It complains about them not having the same dimensionality. How can I force it to display both in my query output? TIA 'The world isn't round - it's bent!'...

Nice tool. Thanks for that. Have another star. 'The world isn't round - it's bent!' Spike Milligan

Great. That looks better. I just need to find out why I get different results when I browse using the same members using an Excel pivot table or the cube browser in SSAS on the one hand, and this query on the other. Thanks, 'The world isn't round - it's bent!' Spike Milligan

Thanks, that showed me how the data is. You are right, the data is sparse and I get lots of nulls and the error codes appear in weeks with data following weeks with null entries. (Although other queries via SSAS cube browser or Excel show more data than is shown by the query - weird). Is it...

Now I'm confused. When I ran your code, after editing the '6-' bit it ran ok in SSMS with Sunday as the end of the week. When I run the following code, which uses a date column from my calendar table, Saturday is the last day of the week. select [Date] ,CONVERT(VARCHAR(12)...

FYI The code almost worked. I noticed that the code was recognising Sunday as the last day of the week (which is what we want) but was giving Sunday the Saturday date as the week end date. So I changed the '6 - (DATEPART(dw, @StartDate)' to be '7 - (DATEPART(dw, @StartDate)'. Also, does SQL...

Thanks. Using the following code to test it before putting it in the Calculation script: WITH MEMBER [Measures].[Gross Sales Weekly % Movement] AS (([Calendar].[YMWD].CURRENTMEMBER, [Measures].[Gross Sales]) - (PARALLELPERIOD([Calendar].[YMWD].[Week No],1, [Calendar].[YMWD].CURRENTMEMBER)...

Thanks, I will give that a try. I should have said that the calendar table comes from an ERP system which generates the dates and other attributes as per the requirements for that system. But I can modify it in the DWH using this script. I have found a way of adding user defined code in the...

I need to calculate a percentage movement between 2 periods. I'm trying to use the following code: WITH MEMBER [Measures].[Gross Sales Weekly % Movement] AS ({[Calendar].[YMWD].[Week No], [Measures].[Gross Sales]} - { PARALLELPERIOD( [Calendar].[YMWD].[Week No],1,[Calendar].[YMWD].[Week...

(I know my name says 'MIS' after it, but it's wrong and I can't see where to change it). Thanks, but I'm not sure of the SQL to identify the last day of the week to get it's date and insert it against the record. Rather, the ETL tool I use has limited capabilities that way (I probably could...

I have a calendar dimension in the data warehouse that has (amongst other columns) Date and Week Number. For every record I want to add the week end date to the dimension in the OLAP cube. I assume the parent hierarchy needs to be a hierarchy from the Calendar dimension. Does that hierarchy...

Thanks. I'm very rusty on this, so thanks for the tip - and speed of response. 'The world isn't round - it's bent!' Spike Milligan

It's been a long time since I used Crystal. I think this is possible to do, but if it is, I can't remember how to do it. I can't use an OLAP report or a cross tab because of the way the data needs to be presented. I have a few descriptive columns, then I need columns for January to December...

Answered my own question. Use the Script object and define it as transforming. Make the script look like this. Public Class ScriptMain Inherits UserComponent 'Declare a variable scoped to class ScriptMain Dim counter As Integer Public Sub New() ' This method gets called only...

Anyone know the syntax and function to get SSIS to populate a derived column with a self incrementing index? I'm using the Derived Column tool but can't find an index or rowcount/rownumber function. Thanks. 'The world isn't round - it's bent!' Spike Milligan

Forgot to say, the review was slightly wrong (in case you find it) - RSinteract CAN read OLAP. 'The world isn't round - it's bent!' Spike Milligan

RSInteract (rsinteract.com)just got a decent write up in one of the SQL mags (can't remember which). You can download an eval copy. 'The world isn't round - it's bent!' Spike Milligan

The issue was, having run an extraction that failed, how could I check the value of the variable passed to that extraction so that it only extracted the transactions not previously extracted (i.e. the highest transaction number in the data warehouse before the last extraction ran). However, it...

I am incrementally extracting data. I use a variable to store the highest number in an index column and pass it to the Script task. My extraction failed after 900 rows. I am assuming the data did not come into the data warehouse in numerical sequence. How can I check to see what was the variable...