I can add two reportitem controls, ie reportitems!begbal.value + reportitems!deposits.value, without a problem. However, when I add the 3rd reportitem control to the expression, ie + reportitems!withdrawals.value, some really funky arithmetic occurs. All of these controls I am referring to are in the same group footer.
Any help out there would be greatly appreciated.
Hi
I need to access value of perticular row that is populated in table component from a textbox outside table.
hi,
this is sanjeev,
i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
this is my code
{
Package pkg = new Package();
string str = (string)entry.Key;
pkg.Name = str;
alEntity = (ArrayList)entry.Value;
ConnectionManager conMgr;
Executable chPackage;
TaskHost executePackageTask;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//string PackagePath = @"c:Genesis.dtsx";
//p = app.LoadPackage(PackagePath, null);
p = new Package();
p.LoadFromXML(parentPackageBody, null);
p.Name = str;
//Sequence seqContainer;
IDTSSequence seqContainer;
//seqContainer = (Sequence)p.Executables["Extract Genesis Data"];
seqContainer = ((Sequence)p.Executables[0]);
string packageLocation = @"Geneva Packages";
conMgr = p.Connections["SQLChildPackagesConnectionString"];
foreach (string val in alEntity)
{
if (seqContainer.Executables.Contains("Load_" + val) == false)
{
chPackage = seqContainer.Executables.Add("STOCK:ExecutePackageTask");
executePackageTask = (TaskHost)chPackage;
executePackageTask.Name = "Load_" + val;
executePackageTask.Description = "Execute Package Task";
executePackageTask.Properties["Connection"].SetValue(executePackageTask, conMgr.Name);
executePackageTask.Properties["PackageName"].SetValue(executePackageTask, packageLocation + ddlApplication.SelectedItem.Text + @"" + executePackageTask.Name);
}
}
app.SaveToXml(Server.MapPath("../SynchronizeScript/Packages/" + ddlApplication.SelectedItem.Text + @"") + str + ".dtsx", p, null);
}
please let me know what is the wrong in my code.
thanks in advance.
regards
sanjeev bolllina
sanjay.bollina@gmail.com
Is there a way to hide the value of an aggregated value in a matrix report when a row is hidded?
Something like IIF(ReportItems!TextBox1.Hidden,True,Flase)
The above gives me an error saying that Hidden is not a member of ReportItem.
I want to do is display the sum of textboxes in a group so I can have a subtotal for every group.
I tried these things:
Sum(ReportItems!txtbudget.Value).
I can't use Fields collection because the textbox value is from custom code so there are no Fields.
I searched for options on this and one option was supposed to be adding a field to the dataset. I tried this and I get "An error occured on the report server" when building the report before I even reference the field in the report.
I even wrote code to do the sum and passed in the ReportItem. I then get the error about the grouping scope.
I want the payroll sum to be the subtotal of each column. The budget values along with some other columns are from custom code that makes database calls (which was a workaround for another problem).
Object
Object Title
Budget
Current Spent
YTD Spent
4100
EMPLOYEES
$10,000.00
$11,307.45
$40,000
4105
EMPLOYEES - TEMPORARY
$2,000.00
$506.00
$7,000
4200
EMPLOYEE BENEFITS
$13,000.00
$2,354.74
$30,000
Payroll
I am having a problem viewing my footer on all pages of my report. I have created a page footer that reads
="The " & ReportItems("textbox213").Value
I am getting the value on the first page only and then only getting "The" on the rest.
My "PrintOnFirstPage" and "PrintOnLastPage" are both marked True in the Page Footer properties. I am completely confused. This is not the only textbox in my footer I have two others both which print on all pages, but neither of them use the ReportItems.
Can anyone help on this?
Thanks....Cin
I've added a hidden field to my report so I can show the value in the page header using ReportItems. However it only works for the first page - the value is blank on subsequent pages. Does the hidden field need to appear on all pages of the report? Would I need to make it a hidden field in a repeated list or table?
View 8 Replies View RelatedI am trying to create a report which uses a table. I have typed manually each cell.
In the subtotal lines, on rows, I have used ReportItems expression. On rows it is working. However, on column, the similar ReportItems expression, instead of adding the values, it is concatenating.
Could you please help?
Regards,
Ajit.
Hi All,
Is there anyway that I can add two reportitems which are in different scopes?
Thanks
Is there a way to make some elements on a report appear only when printed/exported ?
I recall this is possible in MS Access forms/reports, but am unable to find it in RS.
I'm thinking of Disclaimers, Company Logo etc...
I followed the instructions and the textbox named Color did indeed display alternate colors on the odd rows.
However, when I tried to propogate the color to the whole row using the expression =ReportItems!color.Value for the value of the backgroundcolor property, it was ignored.
So I tried some custom code:
Public Function GetColor()
Dim retValue as String
retValue = Me.ReportItems!Color.Value
return retValue
End Function
The syntax parser didn't like ReportItems either.
I first tried it without the Me object and I got a message about requiring an object so I tried Me.
Has anyone got another idea?
Thanks,
IanO
Hi,
May I know how can I make a simple calculation in reporting services using below formula:-
Column
B
C
D
E
Row
Group
January
February
Amt
Calc
Amt
Calc
3
a
100
=B3/B3
50
=D3/D3
4
b
200
=B4/B3
10
=D4/D3
5
c
100
=B5/B3
30
=D5/D3
6
d
150
=B6/B3
80
=D6/D3
I have build above table in reporting services. Row a, b, c, d are all belong to the same reportitems. However, I need to make a division using B3 as a based, where group = a.
I tried to make an expression with logic but it doesn't work:
Calc = reportitems!Amt.value / reportitems!Amt.value where reportitems!Group.Value='a'
Please help!! Thanks in advance!!
Hi,
Here is my problem,
I want to sum a textbox in my table group using =Sum(Reportitems!txt_erreur_cmt.Value,"table1_Group3")
but i always the same error as :
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
But my sum is the table group footer.
I have no ideas how do that sum.
Does anyone can help me ?
Best regards
I have a sum on a reportitems cell in my header:
=Sum(ReportItems!textbox1.Value)
When I run the report, it looks excellent. My issue is when I export it. When I export to Excel, it looks just like it did.
When I export to PDF, it gives me a total per page, not for the report.
Does ReportItem behave differently when rendered between excel and PDF? Or is it because I am putting a SUM on a ReportItems cell?
Hi,
I'm trying to get a data value to appear in the report footer on each page of my report. Using the technique described in various text books etc. I have placed a hidden text box in the report body, and the footer references this text box value.
The report is 4 pages long when I print preview it. The hidden field was initially placed near the top of the report body, so it is there on page 1 in print preview and the footer works on page 1. Pages 2,3,4 (where the hidden field ain't) just gets #Error in the footer.
If I simply move the hidden field further down in the report body so that now its on page 2 in print preview, then guess what - the footer now works on page 2 but errors everywhere else!
Why are ReportItems not known across the whole report? This is maddening. Does anyone know how to make a simple hidden text box value, that is referenced by the page footer, work so that every instance of the page footer shows the correct value?
thankyou
rob
A report I maintain has an extensive footer that appears on each page, populated with information I've stashed into invisible cells in the table's header. This works great, except in rare circumstances where the report includes a page that doesn't display the table at all--in which case the report errors when it attempts to print ("Object reference not set to an instance of an object").
Due to the requirements of the report, I cannot guarantee that the table will appear on every page, every time. Is there a way I can safely refer to the reportItems collection, in case of Nulls? I'll be okay with blank values in the footer in the cases where the page has no reportItems to work with, but I can't have the report blow up when printing.
Thanks in advance,
Jeff Lautenschlager
Hi,
I have two RDL files, one is main.rdl, the other one is sub.rdl.
In the body of main.rdl, I threw in a subreport that links to sub.rdl. Follow by the subreport is a table, in which has a textbox called mytitle. In the page footer, I added one text box that references to mytitle by using ReportItems!mytitle.Value.
When this report rendered in HTML or PDF format, it worked fine. However, when exporting to excel format, it failed. I figured it is because there isn't a ReportItem in the subreport called mytitle. I tried adding a dummy textbox into the subreport and called it mytitle, but again, no luck.
Did anyone ever encounter the same problem? Is there a work around way?
Thanks,
Hi,
Im currently writing a report in SSRS which requires to take data in the fields, manipulate and perform functions on it then display it within its own textbox. For example the field Cost is manipulated so that a column will show the cost depreciated after 5 years.
Cost Final Cost
500 250
1000 634
700 500
Footer: Totals 1384
My problem is that within each group (as the data is grouped by its type) I need to have a sum of the Final Cost data. As this is NOT a field I cannot use =Sum(Fields!FinalCost.Value, "group_1") but rather I need to use =Sum(ReportItems!FinalCost.Value, "group_1"). I know that SSRS does not allow this, but after trying to find an answer going extensively through google and many forums, I am not able to find a solution on how to sum up the Final Cost column.
If anyone has any ideas on how to resolve this issue I will be greatly thankful,
Cheers,
Ben
i want to use datebase wich suport all latin languages but i cant find the collection
esspeacily
eng,
german,
france and Turksh
I recently migrated a database from server A to server B. The backup jobs I am trying to build on server B are failing because of the following error:
--------------
[Microsoft SQL-DMO] Error 21776: [SQL-DMO]The name 'WinDat' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.
-------------
How do I add this database to the databases collection so it will be recognized?
Thanks in advance for your time and help.
actuallu the earlier obviously worked but now i am fetching values from 2 colums and in collection we can pass 2 strings i did that.but then how to increment the collection c.will just post the code .
query fetching 2 records
can add only 1
NameValueCollection c = new NameValueCollection();
cmd2.CommandText="select Pname ,HoursWorked from TimeSheet1 ts , ProjResource pr,ProjectDetails pd where ts.rid = pr.rid and ts.pcode=pr.pcode and pd.Pcode = ts.Pcode and pr.Rid = '" + Ridtxt.Text + " '";
cmd2 .Connection = con1;
con1.Open();
SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
c.Add(dr["pname"].ToString(), dr["hoursworked"].ToString());
}
as earliar
supose c# 12
asp 11
only c# getting added
even though loop is while dr.read()
{
}
actually how to increment key as in c both strings are occupied
hi evryone
I have a question!
in my ASP.NET page I use a SqlDataAdapter object for working on my database
first I declare it as a global vaiable(SqlDataAdapter objDataAdapter;)
then I construct it in my Page_Load procedure like this
(objDataAdapter=new SqlDataAdapter(strSql,objConnention))
then when I want to use it in my other procedures an error tells me that your object does not exist
for solving this error I should construct my objDataAdapter in evry procedure using it.
but I think such variables should not be erased from Heap automatically(with Garbage Collection)
because it has a reference to Stack. I mean the global variable...
any opinion ?
Thanks
I am collecting companyID's form a data grid, I want to pass the selected values to a sproc via a variable. Any idea on the syntax?
this works using a query string within my code
WHERE (dbo.Promotions.ExpirationDate > GETDATE()) AND (dbo.Promotions.CompanyID IN (" + selectedCompanies + "))
this doesn't within my sproc
WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (@SelectedCompanies))
I also tried
WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (SELECT @SelectedCompanies))
and
WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (' + @SelectedCompanies + '))
Thanks
hi , what is the script to change the unicode collection in sql server 7.0
Ahemd
I'm having trouble obtaining errors raised in a stored
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server
database:
CREATE TABLE TestTable
(
TestInt int
)
go
INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)
This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.
Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON
declare @TestInt int
declare @ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable
open TestCursor
Fetch next from TestCursor into @TestInt
While @@fetch_status<>-1
Begin
select @ErrMsg = 'Error ' + convert(char, @testint)
raiserror(@ErrMsg, 16, 1)
raiserror(@ErrMsg, 16, 1)
Fetch next from TestCursor into @TestInt
end
Close TestCursor
DeAllocate TestCursor
return
END
This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:
Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"
Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute
For Each oErr In cn.Errors
MsgBox oErr.Description
Next
End
End Sub
When this code is executed, only two message boxes appear
with the message "Error 1".
Any help on this matter would be greatly appreciated :)
If I have N different xml document formats and I want to store those xml data files as typed xml in one table (one column), can I do it using xml schema collection (by adding schema file to schema collection for each document type and assigning xml schema collection to this column)?
Is this possible using xml schema collection? Or did I miss something about xml schema collection usage scenario?
Thank you!
I need to feed head office sql server with the data from regional servers. Servers are spread through all continents
Data input done locally on Head office server as well and plus need to ship data from other servers.
So clarify this - Head office server is not standby one. Mirroring is out of the picture, I think..
Initially, I thought ship a log every 15 min and restore on Head office server but is this going to create an issue for the local data processing?
Any bright ideas welcome!
Is it possible to address the whole set of user stored procedures in one command?
For example, I want to GRANT EXEC on all sprocs in my db; can I do it in one command or do I have to do the GRANT for each sproc individually?
Precedence Constraint Editor Values:
Expression Operation: Expression and Constraint
Value: Failure
Expression: @GotRecs == 1
Radio button: Logical AND. All contraints must evaluate to True
Stored Procedure has an IF (that contains a SELECT) statement that checks for records if no records are returned processing stops. I SET the parameter after the BEGIN that follows the IF statement.
The step that contains the above code is step #3. I placed the constraint between step #3 and step #4 (Data Flow task) just Exports to a Flat File.
When I clicked the TEST button in the Precedence Constraint Editor I got the following msg:
Error at Constraint 3: The variable €śGotRecs€? was not found in the variables collection. The variable might not exist in the correct scope.
I have to create a CLR Proc for Reporting Services report that will use SharePoint (MOSS 2007) assemblies, so my datasource won't be a database, but the SharePoint object model that returns a collection.
How can I return these data (it's not necessary to be a collection exactly) without having to create a temp table just to return the SqlDataReader?
Thanks.
And sorry for my previous message, I just forgot that I was posting a message at the Global MSDN forums.
It is possible that Data Collection can cause massive increasing MB/sec to tempdb ? I cannot find connection with tempdb and I set cash file, but on same disk.
Or it can be something different? Last two weeks what I checked was Read/Write MB/s to tempdb increasing progressively.
One time it was about 20MB/sec
After it was reseting and again 1MB/sec..
What I checked , External company which install SQL Server made one file for tempdb, next week or during breaktime(it will be possible), I would like make 8files next weekend work.
Now I saw that TempDB mdf was still increased, but using was just 8-10%
We need to copy about 25 databases whenever we get a new softwaredelivery. Currently, the process is to manually use the DTS databasecopy wizard.Doing some research, I created a DTS package to copy databases. Butthis was not going to work since I have to hard code the databasenames in the package. Back to RTFMing, I discovered the DynamicProperties Task. So now I setup a .bat file that calls dtsrun and viaglobal variables passes in the source and target database names to thecopy db DS package.I did a simple test against a test database that I have and it worked.But when I tried it against some "real" databases I get the followingerrors. I have searched and googled but I have not found anything.Help???DTSRun OnError: Copy SQL Server Objects, Error = -2147199728(80045510)Error string: [SQL-DMO]The name 'tdb_grabowy1' was not found in theDatabases collection. If the name is a qualified name, use [] toseparate various parts of the name, and try again.Error source: Microsoft SQL-DMOHelp file: SQLDMO80.hlpHelp context: 1131
View 2 Replies View RelatedI'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.
View 1 Replies View Related