Any true public variables for custom code section?

I have been searching the newsgroup for showing the group sum in the
footer section. This isn't the same as a normal group sum as the
amount shown in the textbox is using the value straight out of the
dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
wanted to show the sum of the group amounts in the footer.
I thought I had found a solution by calling a function to store the
sum and then show the total by calling the same function with a
parameter to return the accumulated amount. Unfortunately I hit
another brick wall with this approach because even though I declared a
public variable outside the function (also tried a static within the
function), the accumulated amount only remains within the scope of
the group calling the function. When the footer section calls the same
function the accumulator is 0 instead of the accumulated amount for
the group.
My question, how do you declare and access a true public variable?
Here's an example of what is happening:
The group amount only needs to be displayed within the group, but
summed at the end of the report
Report
--
Group1 (grouped by MbGUID)
Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
Footer
Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
placeholder
Dataset columns
--
MbGUID,SizeTtl,Year,Month,Week,MaxSize
Data
--
1,120,2006,1,2,80
1,120,2006,1,3,90
1,120,2006,1,4,92
2,130,2006,1,3,87
2,130,2005,1,4,120
The output with the custom code
--
MbGuid Total
1 120
2 130
Report Total: 0
Obviously the Report Total should show 250 instead of 0.
Custom Code Section
--
Public ttl As Double = 0
Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
'Static Ttl AS Long
If pMode = "A" Then
If Ttl = Nothing Then
Ttl = 0
End If
Ttl = Ttl + pNbr
' Runtime error stating parameter is read only for the below
statement
'Report.Parameters!pSizeTtl = pNbr
AddGroupTotal = pNbr
ElseIf pMode = "T" Then
AddGroupTotal = Ttl
End If
End Function
MarkI'm also having the same problem.
I'm attempting to build a public array in the details section of a table and
then perform calculations against that, now filled array in the group footer.
The problem is that when I call my custom AddToArray code in the details
section and then return the ubound value of the array, it works fine in the
details section but when it gets to the footer the array is empty again.
Anyone have any suggestions?
"Mark" wrote:
> I have been searching the newsgroup for showing the group sum in the
> footer section. This isn't the same as a normal group sum as the
> amount shown in the textbox is using the value straight out of the
> dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
> wanted to show the sum of the group amounts in the footer.
> I thought I had found a solution by calling a function to store the
> sum and then show the total by calling the same function with a
> parameter to return the accumulated amount. Unfortunately I hit
> another brick wall with this approach because even though I declared a
> public variable outside the function (also tried a static within the
> function), the accumulated amount only remains within the scope of
> the group calling the function. When the footer section calls the same
> function the accumulator is 0 instead of the accumulated amount for
> the group.
> My question, how do you declare and access a true public variable?
> Here's an example of what is happening:
> The group amount only needs to be displayed within the group, but
> summed at the end of the report
> Report
> --
> Group1 (grouped by MbGUID)
> Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
> Footer
> Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
> placeholder
> Dataset columns
> --
> MbGUID,SizeTtl,Year,Month,Week,MaxSize
> Data
> --
> 1,120,2006,1,2,80
> 1,120,2006,1,3,90
> 1,120,2006,1,4,92
> 2,130,2006,1,3,87
> 2,130,2005,1,4,120
> The output with the custom code
> --
> MbGuid Total
> 1 120
> 2 130
> Report Total: 0
> Obviously the Report Total should show 250 instead of 0.
>
> Custom Code Section
> --
> Public ttl As Double = 0
> Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
> 'Static Ttl AS Long
> If pMode = "A" Then
> If Ttl = Nothing Then
> Ttl = 0
> End If
> Ttl = Ttl + pNbr
> ' Runtime error stating parameter is read only for the below
> statement
> 'Report.Parameters!pSizeTtl = pNbr
> AddGroupTotal = pNbr
> ElseIf pMode = "T" Then
> AddGroupTotal = Ttl
> End If
> End Function
> Mark
>

Any Idea's on this !

Well I have to implement Mail Merge ...user defined Letters in short...
basically like this..
The user can enter any custom letter with Fields we will provide them to put
into the letter ...We will them store this in database(right now thinking on
storing in HTML format).
For eg Letter template would be like
To,
<Name>
<Address1>
Hello <Name>
Thank you
Regards,
<MyName>
<MyPhone>
When the user then selects to generate a letter selecting this Template,then
we need to subsitute appropriate fields and generate the letters for all the
selected user clients by the user.
Please how can i achieve this with RS...any suggestions welcomed...
Thank uUnfortunately, this is not really supported in Reporting Services. We had
planned to add support for rich text (paragraphs, justification, formatting,
etc.) in SQL 2005 but ran out of time.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
> Well I have to implement Mail Merge ...user defined Letters in short...
> basically like this..
> The user can enter any custom letter with Fields we will provide them to
> put
> into the letter ...We will them store this in database(right now thinking
> on
> storing in HTML format).
> For eg Letter template would be like
> To,
> <Name>
> <Address1>
> Hello <Name>
> Thank you
> Regards,
> <MyName>
> <MyPhone>
> When the user then selects to generate a letter selecting this
> Template,then
> we need to subsitute appropriate fields and generate the letters for all
> the
> selected user clients by the user.
> Please how can i achieve this with RS...any suggestions welcomed...
> Thank u
>|||Thanks for the reply !!
Well i am ready to give up on Rich text formatting here...but how can I
actually achieve the working of such templates as letters.How can i do this
with RS.
Is Inner Html supported in RS.
Thank u
"Brian Welcker [MSFT]" wrote:
> Unfortunately, this is not really supported in Reporting Services. We had
> planned to add support for rich text (paragraphs, justification, formatting,
> etc.) in SQL 2005 but ran out of time.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
> > Well I have to implement Mail Merge ...user defined Letters in short...
> > basically like this..
> > The user can enter any custom letter with Fields we will provide them to
> > put
> > into the letter ...We will them store this in database(right now thinking
> > on
> > storing in HTML format).
> > For eg Letter template would be like
> >
> > To,
> > <Name>
> > <Address1>
> >
> > Hello <Name>
> > Thank you
> >
> > Regards,
> > <MyName>
> > <MyPhone>
> >
> > When the user then selects to generate a letter selecting this
> > Template,then
> > we need to subsitute appropriate fields and generate the letters for all
> > the
> > selected user clients by the user.
> > Please how can i achieve this with RS...any suggestions welcomed...
> >
> > Thank u
> >
> >
>
>|||You can't - embedded HTML is part of the rich text feature that was cut. If
you think about it, since we support multiple output format, we would have
to parse the HTML and represent it in other formats (Excel, PDF, etc.)
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:F8F014D9-7D93-4BFA-97CB-A1F990BADA83@.microsoft.com...
> Thanks for the reply !!
> Well i am ready to give up on Rich text formatting here...but how can I
> actually achieve the working of such templates as letters.How can i do
> this
> with RS.
> Is Inner Html supported in RS.
> Thank u
>
> "Brian Welcker [MSFT]" wrote:
>> Unfortunately, this is not really supported in Reporting Services. We had
>> planned to add support for rich text (paragraphs, justification,
>> formatting,
>> etc.) in SQL 2005 but ran out of time.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
>> news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
>> > Well I have to implement Mail Merge ...user defined Letters in short...
>> > basically like this..
>> > The user can enter any custom letter with Fields we will provide them
>> > to
>> > put
>> > into the letter ...We will them store this in database(right now
>> > thinking
>> > on
>> > storing in HTML format).
>> > For eg Letter template would be like
>> >
>> > To,
>> > <Name>
>> > <Address1>
>> >
>> > Hello <Name>
>> > Thank you
>> >
>> > Regards,
>> > <MyName>
>> > <MyPhone>
>> >
>> > When the user then selects to generate a letter selecting this
>> > Template,then
>> > we need to subsitute appropriate fields and generate the letters for
>> > all
>> > the
>> > selected user clients by the user.
>> > Please how can i achieve this with RS...any suggestions welcomed...
>> >
>> > Thank u
>> >
>> >
>>

Any help solidifying the following?

I am trying to create a table structure that would allow users to define custom formulas for computing 1 or more values for any given number of variables.

Table Structure

FORMULA
ID | Descr
-----
1 | Rectangle

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * V

FORMULA_VARIABLE_VALUE
fkID | Variable | Value
-------
1 | X | 10
1 | Y | 10
1 | Z | 2

In the above, notice on FORMULA_VARIABLE the field named expression. If this is NULL, this will be a parameter that must be specified by the user, else, this value from the expression must be evaluated using the variable values.

Notice the Volume expression uses the expression for the variable A. This is where the fun begins...

I have produced the following using the code below. If you could please look at it and let me know if there are any ways to make this more effective, efficient, and stable. That would be greatly appreciated.

The code is a first time run-through!

USE Northwind
GO

CREATE TABLE FORMULA
(
FormulaID int NOT NULL,
Descr char(30)
)
GO

CREATE TABLE FORMULA_VARIABLE
(
fkFormulaID int NOT NULL,
Dimension char(30),
Variable char(10),
Units char(5),
Expression char(255)
)
GO

CREATE TABLE FORMULA_VARIABLE_VALUE
(
fkFormulaID int,
Variable char(10),
Value float
)
GO

INSERT INTO FORMULA (FormulaID, Descr)
SELECT 1, 'Rectangular'
GO

INSERT INTO FORMULA_VARIABLE (fkFormulaID, Dimension, Variable, Units, Expression)
SELECT 1, 'Side 1', 'X', 'IN', NULL UNION ALL
SELECT 1, 'Side 2', 'Y', 'IN', NULL UNION ALL
SELECT 1, 'Height', 'Z', 'IN', NULL UNION ALL
SELECT 1, 'Area', 'A', 'SI', 'X * Y' UNION ALL
SELECT 1, 'Volume', 'V', 'I3', 'A * Z'GO
GO

INSERT INTO FORMULA_VARIABLE_VALUE (fkFormulaID, Variable, Value)
SELECT 1, 'X', 10 UNION ALL
SELECT 1, 'Y', 10 UNION ALL
SELECT 1, 'Z', 2
GO

CREATE PROCEDURE usp_BuildExpressions
@.iFormula int
AS

CREATE TABLE #TempFormulaResults
(
Dimension char(30),
Value float
)

DECLARE @.cDimension char(30), @.cOldExp varchar(255), @.cNewExp varchar(4000), @.cVariable char(5)

DECLARE OldExpCursor CURSOR FOR
SELECT Dimension, Expression FROM FORMULA_VARIABLE WHERE fkFormulaID = 1 AND Expression IS NOT NULL
OPEN OldExpCursor

FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Iterate through expresions, build new cursor where dimension is not equal
-- Replace expression variable with expressions
DECLARE NewExpCursor CURSOR FOR
SELECT Variable, Expression FROM FORMULA_VARIABLE
WHERE fkFormulaID = @.iFormula AND Expression IS NOT NULL AND NOT (Dimension = @.cDimension)

OPEN NewExpCursor
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(RTRIM(@.cOldExp), RTRIM(@.cVariable), RTRIM(@.cNewExp))
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
END
CLOSE NewExpCursor
DEALLOCATE NewExpCursor

-- Get the variable values, replace values in expression and calcluate result
DECLARE @.fValue float, @.cVarName char(5)
DECLARE ValueCursor CURSOR FOR
SELECT Variable, Value FROM FORMULA_VARIABLE_VALUE
WHERE fkFormulaID = @.iFormula

OPEN ValueCursor
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
WHILE(@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(@.cOldExp, RTRIM(@.cVarName), CONVERT(VARCHAR, @.fValue))
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
END

DECLARE @.cSelect nvarchar(4000), @.param nvarchar(4000), @.Eval float

SET @.cSelect = 'SET @.fResult = ' + @.cOldExp
SET @.Param = '@.fResult float OUTPUT'

EXEC sp_executesql @.cSelect, @.Param, @.Eval OUT
INSERT INTO #TempFormulaResults (Dimension, Value) VALUES (@.cDimension, @.Eval)
FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
CLOSE ValueCursor
DEALLOCATE ValueCursor
END

CLOSE OldExpCursor
DEALLOCATE OldExpCursor

SELECT * FROM #TempFormulaResults
DROP TABLE #TempFormulaResults
GO

EXEC usp_BuildExpressions 1

DROP TABLE FORMULA
GO
DROP TABLE FORMULA_VARIABLE
GO
DROP TABLE FORMULA_VARIABLE_VALUE
GO
DROP PROCEDURE usp_BuildExpressions

Any thoughts?

Mike BOn flaw I see, but I am not sure how to fix is if the "nested" variables are more then 2 deep. What if I used Volume from above in a different formula?

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * Z
1 | 1/2 Vol | v | V / 2

Now after executing the stored proc usp_BuildExpressions, the expression for 1/2 Vol would not work and end up looking like
A * 2 / 2.

It will stop short of replacing all the variables with the appropriated nested expressions.

Any ideas?

Mike B