Showing posts with label function. Show all posts
Showing posts with label function. Show all posts

Friday, March 30, 2012

Problems with the aggregate function on a SP1 AS 2005 installation

We are using the Aggregate function in order to calculate totals for month members on the row axis. This calculated member is supposed to calculate Totals for every measure on the column axis.

The problem is that this works fine with simple calculated members like the division of two measures. But If a calculated member includes a time function like YTD or Parallelperiod aggregate returns null.

If I use Sum on a calculated member with YTD everything works fine.

This must be a bug?

Regards

Thomas Ivarsson

Hi Thomas,

Could you provide more details of how you're using Aggregate() - I suspect that this behavior might be by design? Aggregate() didn't support calculated measures in AS 2000 - and, according to Mosha, in AS 2005, Aggregate() works with a calculated measure by "switching solve orders":

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

>>

Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it. I.e. If the current measure is Ratio: Sales/Cost, and we are computing Aggregate({USA, Canada}), then instead of error, it will return Aggregate({USA, Canada},Sales)/Aggregate({USA, Canada},Cost).

>>

This will work with ratios, since the numerator and denominator will first be aggregated, then divided. But in your scenario, I'm not sure that the time functions can be applied after aggregation.

|||

Hi Deepak. Thank's for the reply. I have been away for a few days but here is an explanation.

We use aggregate for totals like this: Aggregate(Existing Intersect({{Extract({[DaysThisMonth]},[Time].[Time_Calendar])}},{[DaysThisMonth]}))

[DaysThisMonth] is a set with the days fo the current month. This set is also used for the rows and the total by the aggregate function calculates the totals for different measures on column axis.

If I have a calculated member like [Measures].[Sales]/[Measures].[Budget] everythin works fine with the total for [DaysThisMonth]. But if I use a calculation like Sum(YTD(), [Measures].[Anything] I will get null for the total.

If you have any ideas I will be grateful.

Kind regards

Thomas Ivarsson

|||

Thomas,

Since I'm not sure what's going on between Existing, Intersect and Extract, so I'm using that part as is.

For a calculated measure like you cited: Sum(YTD(), [Measures].[Anything]), it seems that Sum() across [DaysThisMonth] is not the aggregation you'd want - perhaps it should be computed for the last day? In that case, you could scope the calculation of the [DaysThisMonth] member separately for such YTD measures, like:

Create Member CurrentCube.[Time].[Time_Calendar].[DaysThisMonthAgg] as

Aggregate(Existing Intersect({{Extract({[DaysThisMonth]},[Time].[Time_Calendar])}},{[DaysThisMonth]}));

Scope ({[Time].[Time_Calendar].[DaysThisMonthAgg]}, {SetOfYTDMeasures});

this = ([Measures].CurrentMember,Tail(Existing

Intersect({{Extract({[DaysThisMonth]},[Time].[Time_Calendar])}},{[DaysThisMonth]})).Item(0).Item(0));

End Scope;

|||

Thank's Deepak. Existing, Intersect and Extract are generated by ProClarity professional 6.1. I have also tried with only Aggregate({[DaysThisMonth]},[Measures].Currentmember) with the same null-result for calculated measures with MDX-time-functions.

Aggregate is used for a total of each of the days within the last month({DaysThisMonth}).

I will try your example.

Kind regards

/Thomas

|||

Deepak.

Here is the complete MDX: It is a simplified cube from the Adventure Works projekt.

WITH MEMBER [Due Date].[CalendarYearDate].[All].[ Grand Total] AS 'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Due Date].[CalendarYearDate].[Year].&[2004], [Due Date].[CalendarYearDate].[Month] ) }, [Due Date].[CalendarYearDate] ) } }, { DESCENDANTS( [Due Date].[CalendarYearDate].[Year].&[2004], [Due Date].[CalendarYearDate].[Month] ) } ) )', SOLVE_ORDER = 1000 SELECT { [Measures].[Sales / Product Cost], [Measures].[Sales Amount], [Measures].[Sales YTD Aggregate], [Measures].[Sales YTD Sum], [Measures].[TestParallelPeriod] } ON COLUMNS ,

{ ( [Due Date].[CalendarYearDate].[All].[ Grand Total] ), { DESCENDANTS( [Due Date].[CalendarYearDate].[Year].&[2004], [Due Date].[CalendarYearDate].[Month] ) } } ON ROWS

FROM [Adventure Works Sales Cube]

WHERE ( [Customer].[CustomerGeography].[All] )

These are my calculated members:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales YTD Aggregate]

AS Aggregate(YTD([Due Date].[CalendarYearDate]),[Measures].[Sales Amount]),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Sales Amount] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales YTD Sum]

AS SUM(YTD([Due Date].[CalendarYearDate]),[Measures].[Sales Amount]),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Sales Amount] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales / Product Cost]

AS IIF([Measures].[Total Product Cost]<> 0,[Measures].[Sales Amount]/[Measures].[Total Product Cost],Null),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Total Product Cost], [Sales Amount] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[TestParallelPeriod]

AS (ParallelPeriod([Due Date].[CalendarYearDate].[Year],1,[Due Date].[CalendarYearDate].Currentmember),[Measures].[Sales Amount]),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Sales Amount] },

VISIBLE = 1 ;

The problem is that I get null for the calculated members with MDX time functions. Clearly the two ytd members would only return sums of cumulative values that does not make sense. But the Parallelperiod member would be interesting.

Regards

/Thomas

Friday, March 23, 2012

Problems with restore

I am trying to restore a database and when I try to use the GUI restore
function it just hangs. I can restore through query analyzer without a
problem, and all other functions of Enterprise Manager are working correctly.
I have the same problem from any machine I connect with through Enterprise
Manager.
Has anyone else had this problem before? I am on SP3a of SQL Server2000
with Windows 2000, but have never had a problem before.
Thanks...Dan,
haven't seen this problem, but you might want to open up another Enterprise
Manager and run a sp_who2 to try to see if this sheds any light on the
blocking process.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I have also experienced a similar problem - thus my searching here.
Anyway - I have found that if I wait long enough it WILL come back and
show me the restore options.
I find this issue when I select the DB and right click and select ALL
TASKS and Select RESTORE. That is where it hangs. I was trying to
restore a 15GB DB last night and it took 13 minutes for the restore
options to finally appear.
I suspect it has something to do w/ either the size of the actual DB or
size of log file or size / quantity of log files / backups is has
retained.
We only keep about 24 hours of logs / backup files in their origianl
location where SQL dropped them. Maybe it's hanging on trying to find
them but they are not there.
Still looking for a solution to bringing up the restore options quicker
than what is current.
Good luck -|||Well - Problem solved for my issue.
Recap - my issue was SQL hanging for some time while performing ALL
TASKS / RESTORE via Enterprise Manager.
I searched and found a reference to a stored procedure called:
sp_delete_backuphistory
To use it in Query Analyzer simply enter:
EXEC sp_delete_backuphistory '2006-10-01'
where '2006-10-01' is the date to be used to delete prior backup
history records.
When you try to RESTORE - it appears SQL goes out to lunch to query all
the backup history records. If you purge / delete old history that is
not needed SQL will be much more responsive to poping up the restore
option dialogue window. In my case it went from waiting 13 minutes
down to a mere 8 seconds.
again - good luck -

Tuesday, March 20, 2012

Problems with long execution time

Hello,
We have recently released a new version of our gallery function where we
give the users the possibility to upload images, videos and sound files. We
moved about 15 million objects from the earlier version of the application.
Each day about 150- to 200 thousand objects are uploaded and 100 thousand
objects are deleted.
One of the features in the new versions is the search functionality. The
users can mark their content with tags that are searchable via a full text
index. Each day about 1.5 million queries are executed against the full text
index and about the double against the table that the index is based on.
The full text query uses CONTAINSTABLE and gets 100 rows from the index.
These rows is joined with the table that the index is based on via a INNER
JOIN and four other tables that is needed to present all information about
the object (one INNER JOIN and three LEFT OUTER JOIN).
The problem we have come over is that queries take extremely long time to
execute (up to five minutes). Under a beta period we ran the application with
the same amount of data but with less queries against the tables, under this
period we don’t discovered any problems. After the release the msftesql.exe
consumed 100 % CPU. From the start we were using INFLECTION, after removing
that the process isn’t constantly using 100% CPU but is still peaking at full
CPU usage.
We are using Sql Server 2005. The machine running the Sql Server has two AMD
Opteron 64 bit Dual Core 2.4 GHz with 16 GB of RAM.
My question is if anyone has some input of how to proceed with the
identification of the problem? What limitations of the amount of queries
(both selects and changes of the content) have a full text index? Dose anyone
has some documentation/literature about this subject to recommend?
Thanks in advance!
Regards,
Karl
When you remove full-text from the query what is your performance like. I
strongly suspect that your problem is that you are running into a lazy spool
which will make your SQL FTS performance extremely slow. You will need to
re-write your queries to remove lazy spool.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"karl_a" <karl_a@.discussions.microsoft.com> wrote in message
news:F02AAEAF-0F3E-4847-8210-2D36EF61EC9A@.microsoft.com...
> Hello,
> We have recently released a new version of our gallery function where we
> give the users the possibility to upload images, videos and sound files.
> We
> moved about 15 million objects from the earlier version of the
> application.
> Each day about 150- to 200 thousand objects are uploaded and 100 thousand
> objects are deleted.
> One of the features in the new versions is the search functionality. The
> users can mark their content with tags that are searchable via a full text
> index. Each day about 1.5 million queries are executed against the full
> text
> index and about the double against the table that the index is based on.
> The full text query uses CONTAINSTABLE and gets 100 rows from the index.
> These rows is joined with the table that the index is based on via a INNER
> JOIN and four other tables that is needed to present all information about
> the object (one INNER JOIN and three LEFT OUTER JOIN).
> The problem we have come over is that queries take extremely long time to
> execute (up to five minutes). Under a beta period we ran the application
> with
> the same amount of data but with less queries against the tables, under
> this
> period we don't discovered any problems. After the release the
> msftesql.exe
> consumed 100 % CPU. From the start we were using INFLECTION, after
> removing
> that the process isn't constantly using 100% CPU but is still peaking at
> full
> CPU usage.
> We are using Sql Server 2005. The machine running the Sql Server has two
> AMD
> Opteron 64 bit Dual Core 2.4 GHz with 16 GB of RAM.
> My question is if anyone has some input of how to proceed with the
> identification of the problem? What limitations of the amount of queries
> (both selects and changes of the content) have a full text index? Dose
> anyone
> has some documentation/literature about this subject to recommend?
> Thanks in advance!
> Regards,
> Karl
|||Laxy spool might indicate the wrong join algorithm selection.
You might consider the following workarounds:
1) insert the results of containstable into a temptable (e.g. clustered on
ftkey) and then join the temptable with the rest of your query.
2) You mentioned CONTAINS returns 100 rows. Is that by virtue of data
distribution or due to top_n_by_rank parameter? If you're not using the
latter, I strongly recommend doing so.
3) If there's a specific query plan that you find optimal (e.g. the one you
observed in testing), and the one you're getting now is different, you can
consider using the OPTIMIZE FOR hint to hint the CONTAINSTABLE parameter
value that produced the optimal plan.
Hop this helps,
Regards,
-Denis.
"Hilary Cotter" wrote:

> When you remove full-text from the query what is your performance like. I
> strongly suspect that your problem is that you are running into a lazy spool
> which will make your SQL FTS performance extremely slow. You will need to
> re-write your queries to remove lazy spool.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "karl_a" <karl_a@.discussions.microsoft.com> wrote in message
> news:F02AAEAF-0F3E-4847-8210-2D36EF61EC9A@.microsoft.com...
>
>

Monday, March 12, 2012

problems with getElementById and Select Box

Hi
I have a problem with a javascript function. I have a select box in my
form called "sale_status" this has two options "pending" and "sold".
When "sold" is selected it should automatically show the "sale_date"
field. The function checkStatus is called onChange().
Problem: my show and hide functions work fine, but when I alert
status.value I get "undefined". can anybody tell me what I'm doing
wrong (the id is definitely correct). I'm sure it's just something
stupid that I've done.
here's my function:
function checkStatus(id){
status = document.getElementById(id);
alert(status.value);
if(status.value=="sold"){
alert('h');
show("sale_date_field");
}
else{
hide("sale_date_field");
}
}
Many Thanks
Benoops, sorry, wrong group.

problems with getElementById and Select Box

Hi
I have a problem with a javascript function. I have a select box in my
form called "sale_status" this has two options "pending" and "sold".
When "sold" is selected it should automatically show the "sale_date"
field. The function checkStatus is called onChange().
Problem: my show and hide functions work fine, but when I alert
status.value I get "undefined". can anybody tell me what I'm doing
wrong (the id is definitely correct). I'm sure it's just something
stupid that I've done.
here's my function:
function checkStatus(id){
status = document.getElementById(id);
alert(status.value);
if(status.value=="sold"){
alert('h');
show("sale_date_field");
}
else{
hide("sale_date_field");
}
}
Many Thanks
Benoops, sorry, wrong group.

problems with getElementById and Select Box

Hi
I have a problem with a javascript function. I have a select box in my
form called "sale_status" this has two options "pending" and "sold".
When "sold" is selected it should automatically show the "sale_date"
field. The function checkStatus is called onChange().
Problem: my show and hide functions work fine, but when I alert
status.value I get "undefined". can anybody tell me what I'm doing
wrong (the id is definitely correct). I'm sure it's just something
stupid that I've done.
here's my function:
function checkStatus(id){
status = document.getElementById(id);
alert(status.value);
if(status.value=="sold"){
alert('h');
show("sale_date_field");
}
else{
hide("sale_date_field");
}
}
Many Thanks
Ben
oops, sorry, wrong group.

Problems with float results in calculation.

I have a stored procedure that uses the LOG10 function to calculate a logarithmic average. The first part of the calculation takes 1 divided by the number of results achieved per group. This always results in something like 0.2 or 0.3. Now, the problem is that SQL Server rounds off the result to the nearest 0, which always results in a result of 0. This then causes problems with the LOG10 function which results in a "Domain error" message. Anyone know of a way I can tell SQL to keep the value of 0.3 or 0.2 when it is calculated and not do a rounding? I've tried to convert the result of the calculation to float or real, but this did not help. Anyone know how to fix this?

Thanks,

Bcs you are doing integer division.

use the following sampel to fix yours..

Code Snippet

Select

1/Count(*) [1/Count(*)], --Wrong, Integer Division

1.0/Count(*) [1.0/Count(*)], --Partially Correct

1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression

Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer

From

Sysobjects

|||Thanks a lot. It helped.

Friday, March 9, 2012

Problems with float results in calculation.

I have a stored procedure that uses the LOG10 function to calculate a logarithmic average. The first part of the calculation takes 1 divided by the number of results achieved per group. This always results in something like 0.2 or 0.3. Now, the problem is that SQL Server rounds off the result to the nearest 0, which always results in a result of 0. This then causes problems with the LOG10 function which results in a "Domain error" message. Anyone know of a way I can tell SQL to keep the value of 0.3 or 0.2 when it is calculated and not do a rounding? I've tried to convert the result of the calculation to float or real, but this did not help. Anyone know how to fix this?

Thanks,

Bcs you are doing integer division.

use the following sampel to fix yours..

Code Snippet

Select

1/Count(*) [1/Count(*)], --Wrong, Integer Division

1.0/Count(*) [1.0/Count(*)], --Partially Correct

1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression

Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer

From

Sysobjects

|||Thanks a lot. It helped.

Wednesday, March 7, 2012

Problems with conditional format - DATEADD function

I've been struggling with this problem for over a week now. I have a report
that gives me product renewal dates, in the table I want to highlight rows
depending on what month the renewal date falls into.
Sound pretty straightforward but its been anything but!
In my query I select the renewal dates I want using: >= DATEADD(mm, 1,
GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
However when I use the same DATEADD statement to conditionally format the
table I get the error: Argument 'Interval' is not a valid value. For some
reason it doesn't like anything I put for the Month interval in the
expression. I've tried every permatation I can think of for month including
putting "" round it but it doesn't like it - code is below
=IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
"Yellow",
IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
"Green",
IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
"Blue", "Gray"
)))
Any help would be really appreciated!!!On Jan 14, 6:05=A0am, Jack <J...@.discussions.microsoft.com> wrote:
> I've been struggling with this problem for over a week now. I have a repor=t
> that gives me product renewal dates, in the table I want to highlight rows=
> depending on what month the renewal date falls into.
> Sound pretty straightforward but its been anything but!
> In my query I select the renewal dates I want using: >=3D DATEADD(mm, 1,
> GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
> However when I use the same DATEADD statement to conditionally format the
> table I get the error: Argument 'Interval' is not a valid value. For some
> reason it doesn't like anything I put for the Month interval in the
> expression. I've tried every permatation I can think of for month includin=g
> putting "" round it but it doesn't like it - code is below
> =3DIIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
> "Yellow",
> IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
> "Green",
> IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
> "Blue", "Gray"
> )))
> Any help would be really appreciated!!!
Try DateInterval.Month instead of "mm" e.g. DateAdd(DateInterval.Month,
2,Today())
That should do the trick.
Happy reporting!
toolman|||I actually found the answer elsewhere - I should have been using "m" instead
of "mm".
Haven't tried your solution but I'm sure it works so thanks as well!
"toolman" wrote:
> On Jan 14, 6:05 am, Jack <J...@.discussions.microsoft.com> wrote:
> > I've been struggling with this problem for over a week now. I have a report
> > that gives me product renewal dates, in the table I want to highlight rows
> > depending on what month the renewal date falls into.
> >
> > Sound pretty straightforward but its been anything but!
> >
> > In my query I select the renewal dates I want using: >= DATEADD(mm, 1,
> > GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
> >
> > However when I use the same DATEADD statement to conditionally format the
> > table I get the error: Argument 'Interval' is not a valid value. For some
> > reason it doesn't like anything I put for the Month interval in the
> > expression. I've tried every permatation I can think of for month including
> > putting "" round it but it doesn't like it - code is below
> >
> > =IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
> > "Yellow",
> > IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
> > "Green",
> > IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
> > "Blue", "Gray"
> > )))
> >
> > Any help would be really appreciated!!!
> Try DateInterval.Month instead of "mm" e.g. DateAdd(DateInterval.Month,
> 2,Today())
> That should do the trick.
> Happy reporting!
> toolman
>

Saturday, February 25, 2012

Problems with an insert

Ok, I've been looking at my code for a while now and need some help. All this function should do is take the incoming ProductID and String of ChildIDs, then insert a record for each ProductID and ChildID into my table

I have 2 problems with my function right now:

1) Why is my function creating zeros in the first pastthrough the loope for new ProductID passed to my function?


ProductID RelatedProductID
105744 0
0
105744
...and then it's fine

The problem is that the @.str variable is returning zero in the first iteration in my loop

SET @.str = substring(@.tmpstr, 1, @.pos - 1

2) It's inserting dup ChildIDs as you'll see below. You'll see I want to perform 2 inserts (with 2 different relationship types of 2 and 3) because for each parent / child we need a reverse insert with relationshiptype as the third column...don't ask me why, we just do.
Example incoming ChildIDs string looks like this (and this string is not always a fixed length): 105234,105235,105236,105237,105238,105239,105240,105241,105242,105243,105244,105245,105246,105247,105248,105249,105250,105251,105252,105253,105254,105255

The product ID incoming that is passed to this function for example is 106731

Example of the double inserts and zeros that keep happening for a product:

106731 0
0 106731
106731
105234 ?- Double Child 105234 (which I do not want happening)
106731 105234 ?-
106731 105235
106731 105235
106731 105236
106731 105236
106731 105237
106731 105237
106731 105238
106731 105238
106731 105239
106731 105239
106731 105240
106731 105240
106731 105241
106731 105241
106731 105242
106731 105242
106731 105243
106731 105243
106731 105244
106731 105244
106731 105245
106731 105245
106731 105246
106731 105246
106731 105247
106731 105247
106731 105248
106731 105248
106731 105249
106731 105249
106731 105250
106731 105250
106731 105251
106731 105251
106731 105252
106731 105252
106731 105253
106731 105253
106731 105254
106731 105254
106731 105255
105253 106731
105251 106731
105249 106731
105247 106731
105245 106731
105243 106731
105241 106731
105239 106731
105252 106731
105254 106731
105237 106731
105235 106731
105250 106731
105248 106731
105246 106731
105244 106731
105242 106731
105240 106731
105238 106731
105236 106731
105234 106731 <-- Double ChildID 105234
105234 106731 <--
105236 106731
105235 106731
105244 106731
105243 106731
105242 106731
105241 106731
105240 106731
105239 106731
105238 106731
105237 106731
105254 106731
105253 106731
105255 106731
105252 106731
105251 106731
105250 106731
105249 106731
105248 106731
105247 106731
105246 106731
105245 106731

What I should be getting is this instead (example below: ProductID is in bold, ChildID is in blue):

ProductID RelatedProductID RelationshipType

106731 105234 3

105234 106731 2

106731 105235 3

105235 106731 2

106731 105236 3

105236 106731 2

106731 105237 3

105237 106731 2

106731 105238 3

105238 106731 2

106731 105239 3

105239 106731 2

106731 105240 3

105240 106731 2

…and so on


ALTER PROCEDURE [dbo].[Insert_Product_Child_Relationships]
-- Add the parameters for the stored procedure here
@.ProductID bigint,
@.ChildIDList ntext
AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.str nvarchar(4000),
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ','
WHILE @.textpos <= datalength(@.ChildIDList) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = ltrim(@.leftover + substring(@.ChildIDList, @.textpos, @.chunklen))
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.str = substring(@.tmpstr, 1, @.pos - 1)
INSERT INTO ProductRelationship VALUES(@.ProductID, convert(bigint, @.str), 3,'', GetDate(), 3, NULL, NULL, 1, 0)
INSERT INTO ProductRelationship VALUES(convert(bigint, @.str), @.ProductID, 2,'', GetDate(), 3, NULL, NULL, 1, 0)
SET @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.leftover = @.tmpstr
END
END

Schema (just enough to see what's going on)

ProductRelationship

-

ProductID (bigint)

RelatedProductID (bigint)

ProductRelationshipType (int)

It is doing that because you are adding a @.leftover to the beginning of the string, so the first record is nothing.

Change your SET @.tmpstr to:

--SET @.tmpstr = ltrim(@.leftover + substring(@.ChildIDList, @.textpos, @.chunklen))
SET @.tmpstr = ltrim(substring(@.ChildIDList, @.textpos, @.chunklen))|||Thanks Tom, I got so confused. Thanks much, it works great now!

Problems with Aggregate function in ProClarity

The filteremptyrowsand grand total doesn′t work for me in ProClarity 6.3 when I′m using aggregate function. I get the #Error measage in Grand Total.

Could you post the MDX that's generating the error? (View | MDX Editor)

Thanks,
Bryan

Monday, February 20, 2012

Problems using TOP and User Functions

Hi! I'm trying to create an User Defined function that gives me the top rows
of a table. I must be able to define wich is the top number of rows to be r
eturned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20), [TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription from Te
rritories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm doing
wrong? I'm using the Northwing DB, and I intend to get the first 5 entries,
for instance.You have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.html|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:

> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message967438.html
>

Problems using TOP and User Functions

Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
--
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.htmlYou have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message967438.html|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:
> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message967438.html
>

Problems using TOP and User Functions

Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message967438.html
You have to use a hard-coded value in a TOP clause in SQL Server 2000.
You'll be able to use expressions in SQL Server 2005. You didn't say what
determines the order. For example, is it the TOP (n) sorted by TerritoryID?
In that case, try:
select
o.TerritoryID
, o.TerritoryDescription
from
Territories o
join
Territories i on i.TerritoryID <= o.TerritoryID
and i.RegionID = o.RegionID
where
o.RegionID = @.reg_id
group by
o.TerritoryID
, o.TerritoryDescription
having
count (*) <= @.num
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"TechnoSpike" <TechnoSpike.1mdf1a@.mail.webservertalk.com> wrote in message
news:TechnoSpike.1mdf1a@.mail.webservertalk.com...
Hi! I'm trying to create an User Defined function that gives me the top
rows of a table. I must be able to define wich is the top number of
rows to be returned. I assume I could do this:
CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
RETURNS @.table table ([TerritoryID] [nvarchar] (20),
[TerritoryDescription] [nchar] (50))
AS
begin
if exists (select 1 from Region where RegionID = @.reg_id)
begin
insert into @.table select top @.num TerritoryID, TerritoryDescription
from Territories
where RegionID=@.reg_id
end
return
end
but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
doing wrong? I'm using the Northwing DB, and I intend to get the first
5 entries, for instance.
TechnoSpike
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message967438.html
|||You could use the SET ROWCOUNT command. This will limit the number of rows
returned from the query similar to the TOP function.
"TechnoSpike" wrote:

> Hi! I'm trying to create an User Defined function that gives me the top
> rows of a table. I must be able to define wich is the top number of
> rows to be returned. I assume I could do this:
> CREATE FUNCTION ListTopRegions (@.reg_id int, @.num int)
> RETURNS @.table table ([TerritoryID] [nvarchar] (20),
> [TerritoryDescription] [nchar] (50))
> AS
> begin
> if exists (select 1 from Region where RegionID = @.reg_id)
> begin
> insert into @.table select top @.num TerritoryID, TerritoryDescription
> from Territories
> where RegionID=@.reg_id
> end
> return
> end
> but it keeps saying : Incorrect syntax near '@.num'. Any idea what I'm
> doing wrong? I'm using the Northwing DB, and I intend to get the first
> 5 entries, for instance.
>
> --
> TechnoSpike
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message967438.html
>

Problems using Split function

Hi,
I'm trying to use the common text function "Split" to split the report name.
An example report name would be "REF001-Title of Report". I'd like to be
able to split this report name global into "REF001" and "Title of Report"
However I can't get my head around the way Reporting Services wants me to
write the expression.
I've started with the below, and know it somehow needs to be split into an
array of which I can use elements 0 and 1, but I don't know what syntax it
wants me to use.
=split(Globals!ReportName,"-")
Can someone please help.!?
JHYou just need to grab what element you want.
Example:
=Split("abacad", "a")(1)
returns "b"
Steve MunLeeuw
"JH" <JH@.discussions.microsoft.com> wrote in message
news:EE22A431-73B9-4CCD-921E-70503930E6C9@.microsoft.com...
> Hi,
> I'm trying to use the common text function "Split" to split the report
> name.
> An example report name would be "REF001-Title of Report". I'd like to be
> able to split this report name global into "REF001" and "Title of Report"
> However I can't get my head around the way Reporting Services wants me to
> write the expression.
> I've started with the below, and know it somehow needs to be split into an
> array of which I can use elements 0 and 1, but I don't know what syntax it
> wants me to use.
> =split(Globals!ReportName,"-")
> Can someone please help.!?
> JH