Calling sp in an expression

Hi All,
Is it possible to call a stored procedure (or a dataset that is based on an
sp) from the expression property in a report?
Can the "Code" tab be used to call the stored procedure and that "code" be
referred to from the expression property in a report (if the above is not
possible)?
This is what I am doing (or want to do):
I have a custom assembly that I don't want to put in the
MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in the
folder of my choice and under my control since the folder structure of
Microsoft for SQL Server 2005 (CTP July version) has changed.
So I created an Assembly in SQL Server 2005 by referring to this DLL. I
created a stored procedure that calls the assembly. Now I want to call this
stored procedure from the expression property of the report since I will be
passing the arguments from here.
Any help is appreciated. Thanks.No. The only place that a stored procedure can be called (unless you use a
custom assembly which is what you are trying to get away from) from is by
creating a dataset. The dataset query parameter mapping can use an
expression. If you have just a single record coming back then you can use
First aggregate to refer to it in your report. I do this all the time. Not
sure if that solves your problem or not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> Hi All,
> Is it possible to call a stored procedure (or a dataset that is based on
> an
> sp) from the expression property in a report?
> Can the "Code" tab be used to call the stored procedure and that "code" be
> referred to from the expression property in a report (if the above is not
> possible)?
> This is what I am doing (or want to do):
> I have a custom assembly that I don't want to put in the
> MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> the
> folder of my choice and under my control since the folder structure of
> Microsoft for SQL Server 2005 (CTP July version) has changed.
> So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> created a stored procedure that calls the assembly. Now I want to call
> this
> stored procedure from the expression property of the report since I will
> be
> passing the arguments from here.
> Any help is appreciated. Thanks.|||Thanks for you prompt reply.
Can you please explain with an example - may be this will work.
"Bruce L-C [MVP]" wrote:
> No. The only place that a stored procedure can be called (unless you use a
> custom assembly which is what you are trying to get away from) from is by
> creating a dataset. The dataset query parameter mapping can use an
> expression. If you have just a single record coming back then you can use
> First aggregate to refer to it in your report. I do this all the time. Not
> sure if that solves your problem or not.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> > Hi All,
> > Is it possible to call a stored procedure (or a dataset that is based on
> > an
> > sp) from the expression property in a report?
> >
> > Can the "Code" tab be used to call the stored procedure and that "code" be
> > referred to from the expression property in a report (if the above is not
> > possible)?
> >
> > This is what I am doing (or want to do):
> > I have a custom assembly that I don't want to put in the
> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> > the
> > folder of my choice and under my control since the folder structure of
> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >
> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> > created a stored procedure that calls the assembly. Now I want to call
> > this
> > stored procedure from the expression property of the report since I will
> > be
> > passing the arguments from here.
> >
> > Any help is appreciated. Thanks.
>
>|||Which part is confusing to you? Mapping a query parameter to an expression?
Using the First aggragate?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> Thanks for you prompt reply.
> Can you please explain with an example - may be this will work.
> "Bruce L-C [MVP]" wrote:
>> No. The only place that a stored procedure can be called (unless you use
>> a
>> custom assembly which is what you are trying to get away from) from is by
>> creating a dataset. The dataset query parameter mapping can use an
>> expression. If you have just a single record coming back then you can use
>> First aggregate to refer to it in your report. I do this all the time.
>> Not
>> sure if that solves your problem or not.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> > Hi All,
>> > Is it possible to call a stored procedure (or a dataset that is based
>> > on
>> > an
>> > sp) from the expression property in a report?
>> >
>> > Can the "Code" tab be used to call the stored procedure and that "code"
>> > be
>> > referred to from the expression property in a report (if the above is
>> > not
>> > possible)?
>> >
>> > This is what I am doing (or want to do):
>> > I have a custom assembly that I don't want to put in the
>> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
>> > in
>> > the
>> > folder of my choice and under my control since the folder structure of
>> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >
>> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
>> > created a stored procedure that calls the assembly. Now I want to call
>> > this
>> > stored procedure from the expression property of the report since I
>> > will
>> > be
>> > passing the arguments from here.
>> >
>> > Any help is appreciated. Thanks.
>>|||Mapping a query parameter to an expression.
"Bruce L-C [MVP]" wrote:
> Which part is confusing to you? Mapping a query parameter to an expression?
> Using the First aggragate?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> > Thanks for you prompt reply.
> >
> > Can you please explain with an example - may be this will work.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No. The only place that a stored procedure can be called (unless you use
> >> a
> >> custom assembly which is what you are trying to get away from) from is by
> >> creating a dataset. The dataset query parameter mapping can use an
> >> expression. If you have just a single record coming back then you can use
> >> First aggregate to refer to it in your report. I do this all the time.
> >> Not
> >> sure if that solves your problem or not.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> >> > Hi All,
> >> > Is it possible to call a stored procedure (or a dataset that is based
> >> > on
> >> > an
> >> > sp) from the expression property in a report?
> >> >
> >> > Can the "Code" tab be used to call the stored procedure and that "code"
> >> > be
> >> > referred to from the expression property in a report (if the above is
> >> > not
> >> > possible)?
> >> >
> >> > This is what I am doing (or want to do):
> >> > I have a custom assembly that I don't want to put in the
> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
> >> > in
> >> > the
> >> > folder of my choice and under my control since the folder structure of
> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >> >
> >> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> >> > created a stored procedure that calls the assembly. Now I want to call
> >> > this
> >> > stored procedure from the expression property of the report since I
> >> > will
> >> > be
> >> > passing the arguments from here.
> >> >
> >> > Any help is appreciated. Thanks.
> >>
> >>
> >>
>
>|||RS makes this concept murky because it automatically creates a Report
Parameter for every query parameter. However, you can map query parameters
to the same report parameter or to an expression. For instance, let's say I
have two datasets, they both need from and to dates. Let's further say that
the query parameters are named differently. For instance @.FromDate and
@.StartDate. Reporting services will create two report parameters. However
you can map both @.FromDate and @.StartDate to the same report parameter.
Click on the ... in the dataset tab, Parameters tab. On the left is the
query parameter on the right is what it is mapped to. That is where you
would map both to the same report parameter. OK, now, mapping to an
expression, you can click on the combo box and set expression.
For instance this calls some code behind report:
=Code.SomecodeOfMine(Parameters!Param1.Value)
This sets to the current day/time
=Now
Etc. Your expression can be pretty much anything.
When you pick expression you go to the expression builder.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:74430973-4547-419F-AFFC-29CA4CE68667@.microsoft.com...
> Mapping a query parameter to an expression.
> "Bruce L-C [MVP]" wrote:
>> Which part is confusing to you? Mapping a query parameter to an
>> expression?
>> Using the First aggragate?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
>> > Thanks for you prompt reply.
>> >
>> > Can you please explain with an example - may be this will work.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No. The only place that a stored procedure can be called (unless you
>> >> use
>> >> a
>> >> custom assembly which is what you are trying to get away from) from is
>> >> by
>> >> creating a dataset. The dataset query parameter mapping can use an
>> >> expression. If you have just a single record coming back then you can
>> >> use
>> >> First aggregate to refer to it in your report. I do this all the time.
>> >> Not
>> >> sure if that solves your problem or not.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> >> > Hi All,
>> >> > Is it possible to call a stored procedure (or a dataset that is
>> >> > based
>> >> > on
>> >> > an
>> >> > sp) from the expression property in a report?
>> >> >
>> >> > Can the "Code" tab be used to call the stored procedure and that
>> >> > "code"
>> >> > be
>> >> > referred to from the expression property in a report (if the above
>> >> > is
>> >> > not
>> >> > possible)?
>> >> >
>> >> > This is what I am doing (or want to do):
>> >> > I have a custom assembly that I don't want to put in the
>> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to
>> >> > be
>> >> > in
>> >> > the
>> >> > folder of my choice and under my control since the folder structure
>> >> > of
>> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >> >
>> >> > So I created an Assembly in SQL Server 2005 by referring to this
>> >> > DLL. I
>> >> > created a stored procedure that calls the assembly. Now I want to
>> >> > call
>> >> > this
>> >> > stored procedure from the expression property of the report since I
>> >> > will
>> >> > be
>> >> > passing the arguments from here.
>> >> >
>> >> > Any help is appreciated. Thanks.
>> >>
>> >>
>> >>
>>

Calculations tab - MSAS 2005

Hi

Pretty new to this so bear with me:)

I have created a cube which has atm a single value measure and 5 dimensions which enable me to look at totals of the value measure in various ways - quite pleased I've managed to get this far without too many headaches.

I now need to create a new measure which will be the existing value measure didvided by a subset of the existing value measure. So lets say each value belongs to a group numbered 1-10. I need to have a new value which is 'value' divided by the total of values belonging to group 1. Hope thats clear :)

1. I think I need to create a new calculated member in the calculations tab of MSAS 2005, is that right?

2. Can I have an example of syntax how to do this? I know SQL very well but MDX not at all so knowing what I want but not how to get at it is very frustrating.

Thanks for any help you can give.

Wayne

Hi

Ad 1) Yes

Ad 2) Assuming that your "group" is an attribute in one of your dimensions, the following should work.

CASE WHEN ([Measures].[Value],[Dimension 1].[Group].[Group 1]) = 0
THEN NULL
ELSE [Measures].[Value]/([Measures].[Value],[Dimension 1].[Group].[Group 1])
END

And also you should set the "Non empty behavior" of the new calculated measure to [Measures].[Value].

|||

Couple of comments:

1) IIF is often preferable to CASE for performance reasons

2) For this calculation it is not possible to set NON_EMPTY_BEHAVIOR correctly in general case. Setting it to [Measures].[Value] will cause wrong results when [Measures].[Value] is 0 (as opposed to NULL). If you are sure it is never 0, then probably it will work.

|||

Thanks for those replies.

I've created a calculation but I'm not quite getting what I want yet. I'll flesh out my scenario a bit & maybe you can help some more :)

I am working with accounts data & each value in my cube is held against an accounting code. Each accounting code belongs to a hierarchy of groups which is 3 deep - I call them lvl1, lvl2 and lvl3.

I have data for many businesses which spans several years.

In order to compare one business' performance to another I need to divide each value in the cube by each ￡1000 of income for that business in that year. The income values have a lvl3 code of 'INCOME'. This is the bit I am having trouble with.

So my calculation as it stands atm is:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].RatioVal

AS

CASE WHEN ([Measures].[Val],[Tbl Acc Codes].[Acc Lvl3 Code].[INCOME]) = 0

THEN 0

ELSE [Measures].[Val] /(([Measures].[Val],[Tbl Acc Codes].[Acc Lvl3 Code].[INCOME]) / -1000)

END

,

VISIBLE = 1 ;

When I look at the cube browser this seems to be giving me a ratioval on most of my group total lines but not for every value in the cube as I would like. I'm thinking that the income total is perhaps being calculated depinding on the position of each Val within the cube, when what I need is for the calculation to use the same income total for each business/year every time. So if a business has a total income of ￡25,000 for 2005 I want to divide every value in my cube for that business/year by 25.

Any help you can offer much appreciated & sorely needed :)

Thanks