Dynamic Expression

I would like to display calculated field on a group header.
The nature of the calculation must be passed as a parameter to the report.
For example lets assume that my report lists X,Y pairs.
On the break I would like to display the value of
Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
It is not practical to prepare an expression for every possible option and
use a selector as X,Y are also dynamically selected values from the query.
So I need a dynamic way to define the expression based on input parameters.
Is Custom code is the way to go ? How do I access the Parameters collection,
and the Report's data from Custom code ?
Any Ideas ?
Thanks.I think that custom code will be needed. You can pass the values you need
(Parameters, Report Data) in as parameters to a function.
"NL" wrote:
> I would like to display calculated field on a group header.
> The nature of the calculation must be passed as a parameter to the report.
> For example lets assume that my report lists X,Y pairs.
> On the break I would like to display the value of
> Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> It is not practical to prepare an expression for every possible option and
> use a selector as X,Y are also dynamically selected values from the query.
> So I need a dynamic way to define the expression based on input parameters.
> Is Custom code is the way to go ? How do I access the Parameters collection,
> and the Report's data from Custom code ?
> Any Ideas ?
> Thanks.
>
>
>
>
>
>|||How does one create a custom function that manipulates the headers of a
report based on parameter values?
"John W" wrote:
> I think that custom code will be needed. You can pass the values you need
> (Parameters, Report Data) in as parameters to a function.
> "NL" wrote:
> > I would like to display calculated field on a group header.
> > The nature of the calculation must be passed as a parameter to the report.
> >
> > For example lets assume that my report lists X,Y pairs.
> > On the break I would like to display the value of
> > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> >
> > It is not practical to prepare an expression for every possible option and
> > use a selector as X,Y are also dynamically selected values from the query.
> > So I need a dynamic way to define the expression based on input parameters.
> >
> > Is Custom code is the way to go ? How do I access the Parameters collection,
> > and the Report's data from Custom code ?
> >
> > Any Ideas ?
> >
> > Thanks.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||The previous question was asking about display a calculated field on a group
header. You can do this by adding a field to the group header that references
a function defined in VB.NET code in the Report Properties Code tab.
Code sample:
Public Function GetDoubledValue(ByVal NumToDouble As Int) As String
Return CStr(NumToDouble * 2)
End Function
Field Reference:
=Code.GetDoubledValue(4)
"Leneise44" wrote:
> How does one create a custom function that manipulates the headers of a
> report based on parameter values?
> "John W" wrote:
> > I think that custom code will be needed. You can pass the values you need
> > (Parameters, Report Data) in as parameters to a function.
> >
> > "NL" wrote:
> >
> > > I would like to display calculated field on a group header.
> > > The nature of the calculation must be passed as a parameter to the report.
> > >
> > > For example lets assume that my report lists X,Y pairs.
> > > On the break I would like to display the value of
> > > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> > >
> > > It is not practical to prepare an expression for every possible option and
> > > use a selector as X,Y are also dynamically selected values from the query.
> > > So I need a dynamic way to define the expression based on input parameters.
> > >
> > > Is Custom code is the way to go ? How do I access the Parameters collection,
> > > and the Report's data from Custom code ?
> > >
> > > Any Ideas ?
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >

Dynamic Criteria for a Stored Procedure

Our database utilizes some custom functions and .dll's to perform IRR
calcuations. The IRR calculation takes two steps.
1. The first step is to create a view which preps the cash flows and
pulls out what you want to analyze. ("Ad Hoc Query")
2. The second step is to create a stored procedure that references the
Ad Hoc Query and performs the IRR calculation ("IRR sp"). The IRR sp
has three important parameters:
a. a start date. If the start date is null, then spGetPerformance
performs the calculation from the earliest date.
b. an end date. This is typically set to the most recent quarter
end date.
c. @.ShowTransactions. This allows us to audit the constituents of
the calculation. 0 = return a single record that is the IRR. 1 =
return all the constituents such that a user could transfer the
recordset to excel and perform the IRR calculation to double check that
spGetPerformance is performing well and there are no errors in data
entry for the constituents.
A sample of the IRR sp is written below:
ALTER PROCEDURE sp10_IRRSinceInceptionByInvestor
AS
BEGIN SET NOCOUNT ON
exec spGetPerformance @.AdHocQueryName =
'sp10_view_IRRSinceInceptionByInvestor',
@.StartDate = NULL, @.EndDate =
'6/30/2005', @.ShowTransactions = 0
END
---
Now I have about 30 of these IRR sp and they all have EndDates that are
the same or somehow systematically related (e.g. 5 are set one year
earlier, 5 are set 3 years earlier, etc.).
Is there a way to write a script, stored procedure, something else to
change the @.EndDate criteria for each IRR sp in a programatic way?
Now, I need to open each IRR sp and manually change the @.EndDate
parameter. I do have a table of IRR sp names and dates, but I am a
complete SQL novice, so I can't even so where to go next. I tried to
put dlookups in the criteria section but that doesn't seem to work.
Ryan(Ryan.Chowdhury@.gmail.com) writes:
> Now I have about 30 of these IRR sp and they all have EndDates that are
> the same or somehow systematically related (e.g. 5 are set one year
> earlier, 5 are set 3 years earlier, etc.).
> Is there a way to write a script, stored procedure, something else to
> change the @.EndDate criteria for each IRR sp in a programatic way?
> Now, I need to open each IRR sp and manually change the @.EndDate
> parameter. I do have a table of IRR sp names and dates, but I am a
> complete SQL novice, so I can't even so where to go next. I tried to
> put dlookups in the criteria section but that doesn't seem to work.
Nothing is impossible, and of course you could write a program to
do this. But for 30 procedures, I seriously doubt that it's worth the
effort. Had you said 300, it had been a different issue.
I will have to admit that I did not fully understand the setup, but if
you need to change the dates, maybe the scheme is flawed in some way.
Rather than hard-coding the values, maybe EndDate should be a parameter?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, I was told to run the IRR sp as an exec statement. I don't know
how to incorporate the EndDate as a parameter, although is it not a
parameter in the sp code above? It is just hard coded as 6/30/2005
right now.|||(Ryan.Chowdhury@.gmail.com) writes:
> Erland, I was told to run the IRR sp as an exec statement. I don't know
> how to incorporate the EndDate as a parameter, although is it not a
> parameter in the sp code above? It is just hard coded as 6/30/2005
> right now.
If I understand your description correctly, these procedures are somehow
generated. The scheme appeared strange to me, and may not be the best one.
But since I only know a fraction of what is going on in your organisation,
I can't say exactly how this should be changed.
If you think that having these 30 procedures that you need to change is
causing manageability problems, you should bring it up internally and
propose that a better solution to be developed.
As for the question you actually asked: yes, you could automate that change,
but, no, it would not be worth the effort.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dynamic calculation

Hi,
I get different values and a calculation from a query and need to bring them
all together to make another calculation.
@.PRICE,@.TIME,@.CALC,@.TOTAL
@.PRICE = 3
@.TIME = 5
@.CALC = '* .5'
@.TOTAL = (@.PRICE @.CALC) * @.TIME
I tried to use the exec command, but couldn't get it to work
How can I do this using Transact SQL?
Thank you,
MosheProbably you can make use of CASE expressions to do this, assuming you
know something about the types of optional calculations. Example:
SET @.total =
CASE @.calc_type
WHEN 1 THEN @.price*@.time*0.5
WHEN 2 THEN @.foo*@.bar*0.25
WHEN ... etc
END
If you think you will be forced to use dynamic code then there should
be no reason why you can't do it with EXEC. That's not necessarily the
approach I would recommend but maybe if you post some actual code
rather than pseudo code we could help you fix it.
David Portas
SQL Server MVP
--|||Seems to me you're trying to use dynamic SQL - don't quite know why, since
what you need can be done much more efficiently without dynamic SQL, but
still...
Read more here:
http://www.sommarskog.se/dynamic_sql.html
For a more efficient solution, please provide more information.
ML|||SET @.TOTAL = (@.PRICE @.CALC) * @.TIME
HTH, jens Suessmeyer.|||Hi Moshe,
I've done something very similar with a financial research app i wrote for a
client, they specify a couple of hundred dynamic formula that i then need to
calculate on the fly.
Basically you can use sp_executesql and get the output...
declare @.nsql nvarchar(4000)
set and declare... @.PRICE,@.TIME,@.CALC,@.TOTAL
SET @.PRICE = 3
SET @.TIME = 5
SET @.CALC = '* .5'
SET @.nsql = '@.TOTAL = (@.PRICE ' + @.CALC + ') * @.TIME'
EXEC sp_executeSQL @.nsql,
N'@.PRICE int, @.TIME int, @.TOTAL
decimal( 10, 2 ) OUTPUT',
@.PRICE, @.TIME, @.TOTAL OUTPUT
PRINT @.TOTAL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Moshe Allen" <mosheallen@.hotmail.com> wrote in message
news:dmerbr$hba$1@.news2.netvision.net.il...
> Hi,
> I get different values and a calculation from a query and need to bring
> them all together to make another calculation.
> @.PRICE,@.TIME,@.CALC,@.TOTAL
> @.PRICE = 3
> @.TIME = 5
> @.CALC = '* .5'
> @.TOTAL = (@.PRICE @.CALC) * @.TIME
> I tried to use the exec command, but couldn't get it to work
> How can I do this using Transact SQL?
> Thank you,
> Moshe
>