Please Put Me Out Of My Scoping Misery
May 31, 2007
Hi
I have a table with 2 groupings
OrderType
Month
so I might have
£
OrderType 1 1000
OrderType 2 2000
Month=Jan 3000
OrderType 1 4000
OrderType 2 5000
Month=Feb 9000
OrderType footers contain an experssion =sum(Fields!.ItemQty.value , "OrderType")
What is the correct scope to put have 'grand totals' in the Month grouping and what should i have in each of the group footers
(I've tried replacing "OrderType" with "Month" and putting the expression in both group footers but nothing works)
I've read various posts and articles on scope but I'm either totally misunderstanding the principle or getting my syntax wrong as I either get errors say the scopes wrong or blank values.
I'm not using group headers, only
Detail Line
OrderType group footer
Month group footer
Thanks
Steven
View 7 Replies
Jul 20, 2005
GreetingsI have been trying to write a script that will list out the size of eachuser table in a specified DB. I am running into a scoping problem whentrying to format my display. I am sure there are several ways to derive theresults. My method won't allow me toselect count(*) from @tab_varas it barks that I must declare the variable @tab_var.-- start of script Should be able to copy and paste into QAuse fin_temp2declare @tab_var varchar(50)declare @tab_count intdeclare @count intdeclare @count_val intselect@count = 0declare c cursor forselect name from [Fin_Temp2].dbo.sysobjectswhere type = N'U' order by nameopen cfetch c into@tab_var-- set @count_val = 'select count(*) from ' + @tab_var-- select count(*) from @tab_varset @count_val = exec ('select count(*) from ' + @tab_var) -- <<<===select @count,@tab_var,@count_val -- <<<=== What I wouldlike to do so results would be displayed on one linewhile @@fetch_status = 0beginset @count = @count + 1fetch c into@tab_varselect @count, @tab_varendclose cdeallocate c
View 3 Replies
View Related
Mar 14, 2007
Hi,
I'm having some trouble with some variables in my package.
A brief overview:
My package grabs all the data from an Excel sheet and based on several factors, divides the data up into rows and inserts them into a database.
The Excel sheet has certain ranges of cells that determine how I split the data (and therefore which table the data will go into).
My package is structured as follows:
I. Control Flow:
1. Data Flow Task:
a. OLE DB Source - grabs all data from Excel sheet
b. Script Component - adds a rowcount column to each row, determines the ranges of the various sets of data and sets some variables to the row numbers of the ranges.
c. Conditional Split - splits the data by comparing each row number to the variables with the range row numbers in them
d. Various Flat File destinations (to be replaced by something saving to a table).
The problem I am having is that the Conditional Split doesn't seem to get the correct values of the variables once they are changed in the Script Component.
I know that the Conditional Split is setup correctly as if I put the values for the variables in as defaults the Conditional Split works correctly .
Inside of "
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
" in my script component, as I'm adding the rownumber, I search for specific values to determine if the row represents the beginning or end of a range. If it meets the criteria, I take the row number and and save it to a public integer (of the Script Component)
After I've added my row numbers, inside of "Public Overrides Sub PostExecute()" I have the following:
Public Overrides Sub PostExecute()
Me.ComponentMetaData.FireInformation(0, "Changed Variable!!!", "FirstSalesRow: " + CStr(MyBase.Variables.StartRange1), "", 0, False)
'set our variables
MyBase.Variables.StartRange1= iStartRange1
MyBase.Variables.EndRange1= iEndRange1
Me.ComponentMetaData.FireInformation(0, "Changed Variable!!!", "FirstSalesRow: " + CStr(MyBase.Variables.StartRange1), "", 0, False)
End Sub
Now, when the Script component finishes and I go to the Execution results, I can see that the first "Me.ComponentMetaData.FireInformation()"
returns the default value of the variable, 0.
But the line below it is the second "Me.ComponentMetaData.FireInformation()" and it clearly shows the correct variable value.
I have checked that all of my variables in the Script Component are in the "ReadWriteVariables" property, all variables are "ReadOnly = False" as well and are scoped at the package level.
This has lead me to believe that the Conditional Split task is grabbing the value of the parameter prior to the begin of the Data Flow itself.
Is that correct?
If so, should I be able to work around this by having one Data Flow with a script component to set the variables and output an in memory dataset, then have a second Data Flow with the Conditional Split in it?
Please let me know if you need any more info to help.
Thanks!
View 2 Replies
View Related
