(Newbie) Trying to get rid of NonEmptyCrossJoin

Hi everyone,

As I have said before, I am new to MDX. Any help much appreciated.

I am trying to substitute the Exists() function for the NonEmptyCrossJoin() function, but Exists is not producing the results that I would expect. The test query that I am running is:

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS

FROM [AS Test1]

WHERE

NonEmptyCrossJoin({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]},

{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]})

This is not ideal code, but performs correctly, only listing members with start and end dates with the appropriate values. I tried to substitute that query for this:

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS

FROM [AS Test1]

WHERE

Exists(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}),

NonEmpty({[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]}))

This query effectively ignores the second set (i.e. the End Date set), listing all values with start dates in the given range. Can anyone tell me what I am doing wrong?

Any help much appreciated.

Edit: Adding a measure group at the end of the Exists function and/or removing the NonEmpty functions do not correct the problem.

Not sure whether you've read this blog entry, which discusses different scenarios in which NECJ was used. The closest equivalent is Exists() with measure group, but it depends on the scenario in which NECJ was being used:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx

>>

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

...

NonEmptyCrossJoin(set1, set2, ..., setN, K) is equivalent to Exists(set1*...*setK, set(K+1)*...*setN, "measuregroupname")

where "measuregroupname" is the name of the measure group to which the current measure belongs.

...

>>

So, in your case, it should be like:

Exists({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}

* {[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},, "measuregroupname")

|||

Hi Ed,

First of all, why are you trying to replace NECJ with Exists rather than NonEmpty - are you setting the NullProcessing property anywhere? I think you're better off using the NonEmpty function here.

Secondly, in my experience you should always include a measure somewhere in either of the sets you pass into NonEmpty - especially if you have multiple measure groups in your cube. If you don't then I think what happens is that you remove all the items in your set which are empty for all measures in the cube, which can be confusing when you're only showing one measure and can also slow your query down.

So... does the following do what you want?

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN},[Measures].[Value]) ON ROWS

FROM [AS Test1]

WHERE

(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]} *

{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},[Measures].[Value]))

HTH,

Chris

|||Thank you very much Chris and Deepak for your replies. I'll give those approaches a try.

(local) Alias does not function in an virtual sql server?

When I try to connect to (local) server (in Query Analyzer for example) on an
virtual sql server, the application tries to connect to the active node
instead, resulting in an error message (SQL Server does not exist or access
denied).
Is it possible to configure this alias to connect to the virtual sql server?
Furthermore I recognized, that DTS-Packages with (local)-Data Sources still
work on the virtual server. On a second, similar installed server, this
results in the same error message.
Any thoughts?
Martin Saalmann
There is an Environment Variable you can use in order to run the SQLDIAG
utility on the active node. I'm not sure if it uses the (local) or the "."
designation, but it would be worth a shot.
Type set _CLUSTER_NETWORK_NAME_=SQL NETWORK NAME, and then press ENTER.
NOTE: The SQL NETWORK NAME is the SQL Server virtual name for a named
instance. This is only the first part of the name. For example, if the
instance name is VSQL2\INST2, the SQL NETWORK NAME is VSQL2.
INF: How To Run SQLDIAG On a Clustered/Virtual SQL Server
http://support.microsoft.com/kb/233332/EN-US/
Sincerely,
Anthony Thomas

"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
|||Martin,
you could create an Alias on each Node in the Client Network Utility with
name (local) and then the real name to point at the SQL Virtual Name
Andy.
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
> an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
> access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
> server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
> still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>

((cdate("1/1/2001")+30) as task_due_date (Not working)

I have the below function written in VB, and everything works fine
EXCEPT that the sql code is not executing correctly. The date field is
in the table always shows "12/1/1899 11:59:17 PM" no other date. I'm
should have the date of the input into the function + or - the integer
in the task_due_days field.

for example (cdate("1/1/2002")+30) as task_due_date

What am I doing wrong?

Function SetTasks(trans_id As Long, trans_type As Integer, event_date
As Date)

Dim task As String

task = "Insert into tbl_tasks
(trans_id,task_name,Task_due_date,comments) SELECT (" & trans_id & ")
as trans_id,task_name,(cdate(" & event_date & ")+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=" &
trans_type

Debug.Print task

DoCmd.RunSQL (task)

End Function

the actual SQL code is....

Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

ANY HELP IS GREATLY APPRECIATED!(stoppal@.hotmail.com) writes:
> the actual SQL code is....
> Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
> SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
> task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

Apparently you are not using SQL Server, as there is no cdate function
in SQL Serever.

I can tell what the problem is though: 1/1/2001 = 0 with integer division,
and with floating-point division you get 0.0005. Since you got
11:59:17, I guess that in whatever you are using, you have floating-
point division. (In SQL Server you would get integer division here.)

So you need to delimit the date string. In SQL Server that would be
'1/1/2001'. But it looks a bit likely you are using Access, in which
case maybe ## is better. But you better ask in comp.databases.ms-access
it you are uncertain.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

"Static variable" in function

Hi!

I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?

Thank you!

No. There is no such functionality in TSQL. Your approach sounds fine. Alternatively, you can define a scalar UDF that returns the constant value instead of storing it in the table.

"Must declare the scalar variable" in table-valued function

Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = @.arTemp.Insurer AND
Pims.NABP = @.arTemp.NABP AND
Pims.NumRx = @.arTemp.NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@.arTemp".

I don't understand why SQL thinks @.arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

Not sure of the why, but aliasing the table variable seems to satisfy the compiler.

Code Snippet

UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = t1.Insurer AND
Pims.NABP = t1.NABP AND
Pims.NumRx = 1.NumRx
)

FROM @.arTemp t1;

|||Immediately after posting that I found the workaround -- enclose @.arTemp in brackets where it is used to qualify a column.

So
ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = [@.arTemp].Insurer AND
Pims.NABP = [@.arTemp].NABP AND
Pims.NumRx = [@.arTemp].NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

works -- at least it passes the syntax check.
I think this is a SQL bug.

"Aggregate" function in reporting services

I have the problem of aggregating semi-additive measures correct in higher levels in a matrix control of reporting services. I use the "Microsoft SQL Server Analysis Services" data provider. I have a calculated measure saved in the AS2005 cube which looks like:

[Measure].[Net Sales (kg)] = iif( [Measures].[Sales Volume KG] = 0, 0, [Measures].[Net Sales (RLG)] / [Measures].[Sales Volume KG] )

In the matrix, I have a 4 level Product Dimension on the rows (4 groups matrix1_level1 ..... matrix1_level4) and the visibility of the groups is toggled by each upper level, so I can drill in the product dimension. I place this measure in the data part of the matrix with (sum(Fields!Net_Sales__kg_.Value)). On the leave level all values ar correct. But on each other level, I see the sum of the sublevels.

After a lot of pain researching, I found that I have to use the "aggregate" function instead of the "sum" function, because aggregate uses the aggregating which comes from the data provider and the "Microsoft SQL Server Analysis Services" data provider is able to do that. Thats all of documentation I found. I have played around with this function, but always when I use it, I get no values on all levels. I tried the "scope" parameter of the aggregat function, nothing changed.

I tried aggreagate(Fields!Net_Sales__kg_.Value, "matrix1_level1"), aggreagate(Fields!Net_Sales__kg_.Value, "SalesCube") (SalesCub is the name of my Dataset in Reporting Services I use), aggreagate(Fields!Net_Sales__kg_.Value, "matrix1") and last but not least aggregate(Fields!Net_Sales__kg_.Value), all is the same, no values.

It seems to me, that the aggragate function returns "Nothing", because than, according to the Online Help, no values will be shown. But according to some blogs and forum entries, the "Microsoft SQL Server Analysis Services (AdomdClient)" Data provider should deal with aggregate. Have I do some special entries in the "Advanced..." Settings of the data provider?

Can someone tell me, what I'm doing wrong, and did someone know a good documentation (with examples) for the aggregate function on the internet. The Help-file doc is very, very poor!!!

Thanks
Hans

I have solved it now by programming code under report properties and do my own aggregation. But I think this should be done automatically by the "aggregate" function. Does no one know a good site of documentation and what data providers can use them and how?

Thanks
Hans

|||

Can you please add some more details about your custom aggregation? I have a very similar problem and I need to work around it somehow...

Thanks,

Efi

|||

Hi Efi,

I wrote a little Function in the Code-part of the Report Properties like:

Public Function CalcLevelSum(fname as string, svkg as double, svm2 as double, sMeasure as double) as double
Dim sret as double

if fname = "AVWeight" then
sret = iif(svm2 = 0, 0, svkg * 1000 / svm2)
elseif right(fname, 3) = "kg)" then
sret = iif(svkg = 0, 0, sMeasure / svkg)
elseif right(fname, 3) = "m2)" then
sret = iif(svm2 = 0, 0, sMeasure / svm2)
else
sret = sMeasure / 1000
end if

return sret

End Function

And in the cells I changed the generated Sum-Formula to

=Code.CalcLevelSum(Parameters!SelectedMeasure.Value, Sum(Fields!Measures_Ym2SalesVolumeKG.Value), Sum(Fields!Measures_Ym2SalesVolumeM2.Value), Sum(CDbl(Fields!Measures_Ym2Amount.Value)))

With this approach, I calculate on each hierarchie level the devided values by myself. I have done a further standardisation in my cube: all per kg-values have the letters (kg) at the end of the Measure-Name, and all m2 Values (m2). So I can easily determine if the value must be devided or not. The parameters are only the sums which I need for deviding.

One necessary thing is the CDbl Casting of the measure Value. If you do not cast it with CDbl, you get an error!

Hans

"Aggregate" function in reporting services

I have the problem of aggregating semi-additive measures correct in higher levels in a matrix control of reporting services. I use the "Microsoft SQL Server Analysis Services" data provider. I have a calculated measure saved in the AS2005 cube which looks like:

[Measure].[Net Sales (kg)] = iif( [Measures].[Sales Volume KG] = 0, 0, [Measures].[Net Sales (RLG)] / [Measures].[Sales Volume KG] )

In the matrix, I have a 4 level Product Dimension on the rows (4 groups matrix1_level1 ..... matrix1_level4) and the visibility of the groups is toggled by each upper level, so I can drill in the product dimension. I place this measure in the data part of the matrix with (sum(Fields!Net_Sales__kg_.Value)). On the leave level all values ar correct. But on each other level, I see the sum of the sublevels.

After a lot of pain researching, I found that I have to use the "aggregate" function instead of the "sum" function, because aggregate uses the aggregating which comes from the data provider and the "Microsoft SQL Server Analysis Services" data provider is able to do that. Thats all of documentation I found. I have played around with this function, but always when I use it, I get no values on all levels. I tried the "scope" parameter of the aggregat function, nothing changed.

I tried aggreagate(Fields!Net_Sales__kg_.Value, "matrix1_level1"), aggreagate(Fields!Net_Sales__kg_.Value, "SalesCube") (SalesCub is the name of my Dataset in Reporting Services I use), aggreagate(Fields!Net_Sales__kg_.Value, "matrix1") and last but not least aggregate(Fields!Net_Sales__kg_.Value), all is the same, no values.

It seems to me, that the aggragate function returns "Nothing", because than, according to the Online Help, no values will be shown. But according to some blogs and forum entries, the "Microsoft SQL Server Analysis Services (AdomdClient)" Data provider should deal with aggregate. Have I do some special entries in the "Advanced..." Settings of the data provider?

Can someone tell me, what I'm doing wrong, and did someone know a good documentation (with examples) for the aggregate function on the internet. The Help-file doc is very, very poor!!!

Thanks
Hans

I have solved it now by programming code under report properties and do my own aggregation. But I think this should be done automatically by the "aggregate" function. Does no one know a good site of documentation and what data providers can use them and how?

Thanks
Hans

|||

Can you please add some more details about your custom aggregation? I have a very similar problem and I need to work around it somehow...

Thanks,

Efi

|||

Hi Efi,

I wrote a little Function in the Code-part of the Report Properties like:

Public Function CalcLevelSum(fname as string, svkg as double, svm2 as double, sMeasure as double) as double
Dim sret as double

if fname = "AVWeight" then
sret = iif(svm2 = 0, 0, svkg * 1000 / svm2)
elseif right(fname, 3) = "kg)" then
sret = iif(svkg = 0, 0, sMeasure / svkg)
elseif right(fname, 3) = "m2)" then
sret = iif(svm2 = 0, 0, sMeasure / svm2)
else
sret = sMeasure / 1000
end if

return sret

End Function

And in the cells I changed the generated Sum-Formula to

=Code.CalcLevelSum(Parameters!SelectedMeasure.Value, Sum(Fields!Measures_Ym2SalesVolumeKG.Value), Sum(Fields!Measures_Ym2SalesVolumeM2.Value), Sum(CDbl(Fields!Measures_Ym2Amount.Value)))

With this approach, I calculate on each hierarchie level the devided values by myself. I have done a further standardisation in my cube: all per kg-values have the letters (kg) at the end of the Measure-Name, and all m2 Values (m2). So I can easily determine if the value must be devided or not. The parameters are only the sums which I need for deviding.

One necessary thing is the CDbl Casting of the measure Value. If you do not cast it with CDbl, you get an error!

Hans

"Aggregate" function in reporting services

I have the problem of aggregating semi-additive measures correct in higher levels in a matrix control of reporting services. I use the "Microsoft SQL Server Analysis Services" data provider. I have a calculated measure saved in the AS2005 cube which looks like:

[Measure].[Net Sales (kg)] = iif( [Measures].[Sales Volume KG] = 0, 0, [Measures].[Net Sales (RLG)] / [Measures].[Sales Volume KG] )

In the matrix, I have a 4 level Product Dimension on the rows (4 groups matrix1_level1 ..... matrix1_level4) and the visibility of the groups is toggled by each upper level, so I can drill in the product dimension. I place this measure in the data part of the matrix with (sum(Fields!Net_Sales__kg_.Value)). On the leave level all values ar correct. But on each other level, I see the sum of the sublevels.

After a lot of pain researching, I found that I have to use the "aggregate" function instead of the "sum" function, because aggregate uses the aggregating which comes from the data provider and the "Microsoft SQL Server Analysis Services" data provider is able to do that. Thats all of documentation I found. I have played around with this function, but always when I use it, I get no values on all levels. I tried the "scope" parameter of the aggregat function, nothing changed.

I tried aggreagate(Fields!Net_Sales__kg_.Value, "matrix1_level1"), aggreagate(Fields!Net_Sales__kg_.Value, "SalesCube") (SalesCub is the name of my Dataset in Reporting Services I use), aggreagate(Fields!Net_Sales__kg_.Value, "matrix1") and last but not least aggregate(Fields!Net_Sales__kg_.Value), all is the same, no values.

It seems to me, that the aggragate function returns "Nothing", because than, according to the Online Help, no values will be shown. But according to some blogs and forum entries, the "Microsoft SQL Server Analysis Services (AdomdClient)" Data provider should deal with aggregate. Have I do some special entries in the "Advanced..." Settings of the data provider?

Can someone tell me, what I'm doing wrong, and did someone know a good documentation (with examples) for the aggregate function on the internet. The Help-file doc is very, very poor!!!

Thanks
Hans

I have solved it now by programming code under report properties and do my own aggregation. But I think this should be done automatically by the "aggregate" function. Does no one know a good site of documentation and what data providers can use them and how?

Thanks
Hans

|||

Can you please add some more details about your custom aggregation? I have a very similar problem and I need to work around it somehow...

Thanks,

Efi

|||

Hi Efi,

I wrote a little Function in the Code-part of the Report Properties like:

Public Function CalcLevelSum(fname as string, svkg as double, svm2 as double, sMeasure as double) as double
Dim sret as double

if fname = "AVWeight" then
sret = iif(svm2 = 0, 0, svkg * 1000 / svm2)
elseif right(fname, 3) = "kg)" then
sret = iif(svkg = 0, 0, sMeasure / svkg)
elseif right(fname, 3) = "m2)" then
sret = iif(svm2 = 0, 0, sMeasure / svm2)
else
sret = sMeasure / 1000
end if

return sret

End Function

And in the cells I changed the generated Sum-Formula to

=Code.CalcLevelSum(Parameters!SelectedMeasure.Value, Sum(Fields!Measures_Ym2SalesVolumeKG.Value), Sum(Fields!Measures_Ym2SalesVolumeM2.Value), Sum(CDbl(Fields!Measures_Ym2Amount.Value)))

With this approach, I calculate on each hierarchie level the devided values by myself. I have done a further standardisation in my cube: all per kg-values have the letters (kg) at the end of the Measure-Name, and all m2 Values (m2). So I can easily determine if the value must be devided or not. The parameters are only the sums which I need for deviding.

One necessary thing is the CDbl Casting of the measure Value. If you do not cast it with CDbl, you get an error!

Hans

"Aggregate" function in reporting services

I have the problem of aggregating semi-additive measures correct in higher levels in a matrix control of reporting services. I use the "Microsoft SQL Server Analysis Services" data provider. I have a calculated measure saved in the AS2005 cube which looks like:

[Measure].[Net Sales (kg)] = iif( [Measures].[Sales Volume KG] = 0, 0, [Measures].[Net Sales (RLG)] / [Measures].[Sales Volume KG] )

In the matrix, I have a 4 level Product Dimension on the rows (4 groups matrix1_level1 ..... matrix1_level4) and the visibility of the groups is toggled by each upper level, so I can drill in the product dimension. I place this measure in the data part of the matrix with (sum(Fields!Net_Sales__kg_.Value)). On the leave level all values ar correct. But on each other level, I see the sum of the sublevels.

After a lot of pain researching, I found that I have to use the "aggregate" function instead of the "sum" function, because aggregate uses the aggregating which comes from the data provider and the "Microsoft SQL Server Analysis Services" data provider is able to do that. Thats all of documentation I found. I have played around with this function, but always when I use it, I get no values on all levels. I tried the "scope" parameter of the aggregat function, nothing changed.

I tried aggreagate(Fields!Net_Sales__kg_.Value, "matrix1_level1"), aggreagate(Fields!Net_Sales__kg_.Value, "SalesCube") (SalesCub is the name of my Dataset in Reporting Services I use), aggreagate(Fields!Net_Sales__kg_.Value, "matrix1") and last but not least aggregate(Fields!Net_Sales__kg_.Value), all is the same, no values.

It seems to me, that the aggragate function returns "Nothing", because than, according to the Online Help, no values will be shown. But according to some blogs and forum entries, the "Microsoft SQL Server Analysis Services (AdomdClient)" Data provider should deal with aggregate. Have I do some special entries in the "Advanced..." Settings of the data provider?

Can someone tell me, what I'm doing wrong, and did someone know a good documentation (with examples) for the aggregate function on the internet. The Help-file doc is very, very poor!!!

Thanks
Hans

I have solved it now by programming code under report properties and do my own aggregation. But I think this should be done automatically by the "aggregate" function. Does no one know a good site of documentation and what data providers can use them and how?

Thanks
Hans

|||

Can you please add some more details about your custom aggregation? I have a very similar problem and I need to work around it somehow...

Thanks,

Efi

|||

Hi Efi,

I wrote a little Function in the Code-part of the Report Properties like:

Public Function CalcLevelSum(fname as string, svkg as double, svm2 as double, sMeasure as double) as double
Dim sret as double

if fname = "AVWeight" then
sret = iif(svm2 = 0, 0, svkg * 1000 / svm2)
elseif right(fname, 3) = "kg)" then
sret = iif(svkg = 0, 0, sMeasure / svkg)
elseif right(fname, 3) = "m2)" then
sret = iif(svm2 = 0, 0, sMeasure / svm2)
else
sret = sMeasure / 1000
end if

return sret

End Function

And in the cells I changed the generated Sum-Formula to

=Code.CalcLevelSum(Parameters!SelectedMeasure.Value, Sum(Fields!Measures_Ym2SalesVolumeKG.Value), Sum(Fields!Measures_Ym2SalesVolumeM2.Value), Sum(CDbl(Fields!Measures_Ym2Amount.Value)))

With this approach, I calculate on each hierarchie level the devided values by myself. I have done a further standardisation in my cube: all per kg-values have the letters (kg) at the end of the Measure-Name, and all m2 Values (m2). So I can easily determine if the value must be devided or not. The parameters are only the sums which I need for deviding.

One necessary thing is the CDbl Casting of the measure Value. If you do not cast it with CDbl, you get an error!

Hans

'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral,

I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :

The Hidden expression for the table ‘table1’ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Here is the code :

************************************************************************

Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string

i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xx\mydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()

for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j

rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function

******************************************************************

Why do i get the error message ?, is this related to Code Access Security issues with .net framework. if yes

how do i set the Security so the report server or report manager allows embedded custom code to be executed. Any advice ?

Chi

To set code access security for your custom code, see the following article: http://msdn2.microsoft.com/en-us/library/aa237680(SQL.80).aspx

-Albert

|||

Thanks Albert, i have one more question, do i have to grant permission even if the code that i use is embedded in the report .I mean i did not create a custom DLL. In other words do i have to grant permission to the code even if it's embedded in the report, but in my code i reference System.Data.SqlClient.

Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string

i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xx\mydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()

for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j

rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function

chi

|||

Sorry, I didn't read your post carefully enough. Yes, you need to set code access security for the custom code in your report. The instructions are in the following article: http://msdn2.microsoft.com/en-us/library/aa237693(SQL.80).aspx. You need to modify the permissions for the "Report_Expressions_Default_Permissions" code group in the policy file.

-Albert

#error when referencing System.Data and Microsoft.AnalysisServices.AdomdClient

Hi,

I created a function in the Report Properties -> Code Section that uses System.Data and Microsoft.AnalysisServices.AdomdClient.

In the reference section I have made references to these Assemblies. When I preview my report on the preview tab, everything works fine. But when I deploy it to the server I get #error in the text box which calls the function.

Any ideas?

Thanks in advance.

You may want to carefully read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=460220&SiteID=1

Basically, it is not working in the report server environment due to missing code access security permissions. I believe teh AdomdClient requires FullTrust permissions.

-- Robert

|||

Thanks. The "Expression host assembly" only had execution persmissions. I created a custom assembly and granted it FullTrust.

Thanks again for pointing me in the right direction.

|||

Hi there. When I created my custom assembly, the only way that I was able to get it to work was to use an OLE db connection instead of the Adomdconnection. I had followed these steps here:

http://support.microsoft.com/Default.aspx?kbid=842419

The reason was that I didn't know what type of permission to assert in my code for an Adomdconnection since there wasn't an 'AdomdConnectionPermission' object. Has anyone got this to work with an Adomdconnection object?

My second question is that when my report executes the functions in the assembly, they execute with the credentials of the server identity. Is there a setting in any config file that I can change so that the assembly can impersonate the current user and execute the functions with the user's credentials?

Thanks.

#error when referencing System.Data and Microsoft.AnalysisServices.AdomdClient

Hi,

I created a function in the Report Properties -> Code Section that uses System.Data and Microsoft.AnalysisServices.AdomdClient.

In the reference section I have made references to these Assemblies. When I preview my report on the preview tab, everything works fine. But when I deploy it to the server I get #error in the text box which calls the function.

Any ideas?

Thanks in advance.

You may want to carefully read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=460220&SiteID=1

Basically, it is not working in the report server environment due to missing code access security permissions. I believe teh AdomdClient requires FullTrust permissions.

-- Robert

|||

Thanks. The "Expression host assembly" only had execution persmissions. I created a custom assembly and granted it FullTrust.

Thanks again for pointing me in the right direction.

|||

Hi there. When I created my custom assembly, the only way that I was able to get it to work was to use an OLE db connection instead of the Adomdconnection. I had followed these steps here:

http://support.microsoft.com/Default.aspx?kbid=842419

The reason was that I didn't know what type of permission to assert in my code for an Adomdconnection since there wasn't an 'AdomdConnectionPermission' object. Has anyone got this to work with an Adomdconnection object?

My second question is that when my report executes the functions in the assembly, they execute with the credentials of the server identity. Is there a setting in any config file that I can change so that the assembly can impersonate the current user and execute the functions with the user's credentials?

Thanks.