Computing And Assigning A Value To A Textbox From Other Data Regions
May 18, 2007
I am new to SSRS, so perhaps its a trivial question. I was wondering that since all controls have names in the report, is it possible to programatically access values of different textboxes, do some computation and then assign to another text box? I know how to do it using the Aggregate functions and operators, but am not sure if I can access values from textboxes within two different tables and assign the computed value to a third text box on the page (not belonging to any table or other control).
Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?
I'm looking for advice on how to approach a reporting task I've been given. The report needs to be grouped by one of the fields in a view. The view contains patient data. Within each group, the report has to show the results when the view is processed for patients under 18 and another set of results when processed for patients 18 or older. Should I create a subreport for each age group? How would I show both age group sections under the same group? I've heard that subreports are resource-intensive. Is there a better way? Data regions? How would that work?
My Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated
Regards Praveen John +91-9895074288 "Frankly, my dear, I don’t give a damn"
My Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated
Is it possible to nest list items that use different datasets? I am currently working on a report that consumes information from several different tables. Is there a way to achieve this using nested data regions?
Here are some of the items from the report-
IT Plan ---------- IT Plan Headers 1-10
IT Plan Projects ------------------- Project Headers
IT Plan Project Sponsors ------------------------- Sponsors 1-n
IT Plan Project consumers ---------------------------
IT Plan Project Survey
------------------ Survey Qs and Responses...
IT Plan Project Alignments to Intiatives -----------------------
Alignments 1-n
..... several other such subsections.
and then the information gets repeated for the next IT plan.
I thought it might be possible to nest data regions. After placing nested lists on my reports, when I try to associate data from other datasets onto to the nested list, the report designer UI only exposes aggregate functions like Count or First in the expression editor --> Fields collection. It does not let me associate other datasets.
If anybody can provide some details on the best approach to tackle a huge report like this, I would really apprecaite it. I am currently going with the Sub reports approach.
When we try to export to excel a SQL client report containing tables that were grouped based on some data in two tables of a dataset I am receiving the following error
"Data Regions within table/matrix cells are ignored".
I was using a single data table to contain my assortment of fields, but one of the text boxes often has to grow vertically, skewing the layout on the fields to the right of it. So, I moved those other fields to a data list right beside the table. It seemed to work perfectly, so I deployed it to our development server and tested it. When rendered from the server, the list gets bumped down to below the table.
So, after doing some research, I stuck the list into a rectangle and tried different things, like making sure the top of the rectangle was above the top of the table, etc. However, one or the other data region always gets bumped down to below the other--I can't get them to stay side-by-side when run from the SSRS server.
The final frustration is that when I print from the malformed 'preview' on the server, it comes out correctly on the printer! In other words, it looks different on the printer (correct) from how it looked when I hit print (incorrect) in the browser.
Apologies if this is a newbie question, but I'm stuck.
Hello all, for a project I am trying to implement PayPal processing for orders public void CheckOut(Object s, EventArgs e) { String cartBusiness = "0413086@chester.ac.uk"; String cartProduct; int cartQuantity = 1; Decimal cartCost; int itemNumber = 1; SqlConnection objConn3; SqlCommand objCmd3; SqlDataReader objRdr3; objConn3 = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); objCmd3 = new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3); objConn3.Open(); objRdr3 = objCmd3.ExecuteReader(); cartProduct = "Cheese"; cartCost = 1; objRdr3.Close(); objConn3.Close(); cartBusiness = Server.UrlEncode(cartBusiness); String strPayPal = "https://www.paypal.com/cgi-bin/webscr?cmd=_cart&upload=1&business=" + cartBusiness; strPayPal += "&item_name_" + itemNumber + "=" + cartProduct; strPayPal += "&item_number_" + itemNumber + "=" + cartQuantity; strPayPal += "&amount_" + itemNumber + "=" + Decimal.Round(cartCost); Response.Redirect(strPayPal);
Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query. So I want cartProduct = Title and cartCost = Price from the SQL query. How do I do this?
I have a table with first name, last name, SSN(social security number)and other columns.I want to assign group number according to this business logic.1. Records with equal SSN and (similar first name or last name) belongto the same group.John Smith 1234Smith John 1234S John 1234J Smith 1234John Smith and Smith John falls in the same group Number as long asthey have similar SSN.This is because I have a record of equal SSN but the first name andlast name is switched because of people who make error inserting lastname as first name and vice versa. John Smith and Smith John will haveequal group Name if they have equal SSN.2. There are records with equal SSN but different first name and lastname. These belong to different group numbers.Equal SSN doesn't guarantee equal group number, at least one of thefirst name or last name should be the same. John Smith and Dan Brownwith equal SSN=1234 shouldn't fall in the same group number.Sample data:Id Fname lname SSN grpNum1 John Smith 1234 12 Smith John 1234 13 S John 1234 14 J Smith 1234 15 J S 1234 16 Dan Brown 1234 27 John Smith 1111 3I have tried this code for 65,000 rows. It took 20 minute. I have torun it for 21 million row data. I now that this is not an efficientcode.INSERT into temp_FnLnSSN_grpSELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,(SELECT 1 + count(*)FROM distFLS AS c2WHERE c2.ssn < c1.ssnor (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =substring(c1.fname,1,1) or substring(c2.lname,1,1) =substring(c1.lname,1,1)or substring(c2.fname,1,1) =substring(c1.lname,1,1) or substring(c2.lname,1,1) =substring(c1.fname,1,1)))) AS group_numberFROM distFLS AS c1JOIN tu_people_data AS c3ON (c1.ssn = c3.ssn andc1.fname = c3.fname andc1.lname= c3.lname)dist FLS is distinct First Name, last Name and SSN table from thepeople table.I have posted part of this question, schema one week ago. Please referthis thread.http://groups.google.com/group/comp...6eb380b5f2e6de6
I have a Data Flow Script Component(Destination Type) and in the properties I have a read/write variable called User::giRowCount
User::giRowCount is populated by a Row Count Component previously in the Data Flow.
After reading http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97494&SiteID=1 it is very clear that you can actually only use variables in the PostExecute of a Data Flow Script Component or you will get an error "Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute."
What I need to do is actually create a file in the PreExecute and write the number of records = User::giRowCount as second line as part of the header, I also need to parse a read/write variable such as gsFilename to save me hardcoding the path
-they must go in the PreExecute sub --workarounds please-here is the complete script component that creates a file with header, data and trailer --Is there any workaround
Public Class ScriptMain Inherits UserComponent 'Dim fs As FileStream Dim fileName As String = "F:FilePickUpMyfilename.csv" 'Dim fileName = (Me.Variables.gsFilename.ToString)
Dim myFile As FileInfo = New FileInfo(fileName) Dim sw As StreamWriter = myFile.CreateText Dim sbRecord As StringBuilder = New StringBuilder
Public Overrides Sub PreExecute()
sbRecord.Append("RECORD_START").Append(vbNewLine)
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
Public Overrides Sub PostExecute() sbRecord.Append("RECORD_COUNT").Append((vbTab)) sbRecord.Append(Me.Variables.giRowCount.ToString).Append(vbNewLine) sbRecord.Append("RECORD_END").Append(vbNewLine) 'Now write to file before next record extract sw.Write(sbRecord.ToString) 'Clear contents of String Builder sbRecord.Remove(0, sbRecord.Length)
Hi I have a situation where i have a drop down list. The user selects a value from it and then a query behind the scenes is generated based on the value from the list. I want the result of the query to be diplayed in the textbox.I've checked a few examples but i don't really have a good understanding of how to generate this query and get the results to the textbox. I think you use the SQLDATASOURCE but still need some help...Can anyone help? Thanks.
Hi, I'm a newbie to to asp.net. I have been trying to get this done but have no idea how to go about to do it. I am using Visual Studio Express 2005 and SQL Server Express 2005. I have a database "RECORDS" and a table "NAME" with columns "NAMEID - int" and "NAMERECORDS - varchar(50)" I opened new website asp.net using vb. In the default I have one textbox (textbox1) and button (button1). I wish to insert a new NAME to the database whenever I click the Button1. I have created the INSERT command but unsure how to code it. I appreciate anyone can show me how to insert to the database whatever is being typed in the textbox whenever I click the button1. Thanks in advance.
hi there...i want to know how to get a specific data from a database and put it in a label? Like an example i have created a database that have "username", "account number"..example i want to put the account number of the username "kevin" into a text box (vs 2005)..
Hi All, I am working on a report, in this report a textbox is present, which is expected to show string. This string length is larger than what textbox can accommodate in single line. I want this textbox to show only right part of the string in case it can accommodate full string. After using the right alignment I assumed it would do show, however it does not work like that. It always shows the left part of string in case, string is bigger than width of textbox. Restriction from design: 1. Cannot increase the width of textbox. 2. Cannot use the Can Grow = true to show string in multiple lines.
Below is the example of my issue: Say there is a string: "Microsoft SQL Server Reporting Services". because the textbox width is not sufficient to see full string, I want to "Reporting Service". Which is of same size which column can accommodate. So I used Right alignment in textbox. however text box shows "Microsoft SQL Server". So, is there a solution to see required text in the textbox.
hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
i currently have multiple textboxes which input data, i'm wanting the textbox details to be sent to a database, here's how one of the textboxes look: <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' Visible="False" Width="300px"></asp:TextBox> the sqldatasource:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommunityDirConnectionString %>" InsertCommand="INSERT INTO email(Name, Department, Email, Subject, Body) VALUES (@Name, @Department, @Email, @Subject, @Body)" SelectCommand="SELECT email.* FROM email"><InsertParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Department" /><asp:Parameter Name="Email" /><asp:Parameter Name="Subject" /><asp:Parameter Name="Body" /></InsertParameters></asp:SqlDataSource> i'm wanting it to fire once the user selects a button, but i'm not sure what code needs to be placed behind the button?
I have a DataReader on a web page: <asp:SqlDataSource ID="ChangeInfoRecord" runat="server" ConnectionString.......... I want to display the value of one of the returned field in a text box. What is the correct syntax? I tried RolledFromDisplay.Text = ChangeInfoRecord("RolledFrom") but that isn't it.
i have a textbox which a user enters a numeric value i want it to use SqlDataSource and check if the value exists in any of the tables. in my text box the users would enter starting from '100000' or '200000' i want it to check the view that starts the # with '100000' and 2ed view starts '200000' With this i can check in one of the tables and make the selection. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>" SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)"> <SelectParameters> <asp:ControlParameter ControlID="txtReport" Name="ReportNumber" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
How can i make this possible ? i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?
I have a VERY simple program, it contains a datagrid, textbox and button I want it so when I insert something into the textbox and press the button it puts it into the database that is connected to the datagrid and displays it I have the following code and I cannot get it to work: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) SqlDataSource1.InsertParameters.Add("@Name", TextBox1.Text) End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head> <body><form id="Form1" action="Default.aspx" runat="server"> <table> <tr> <td> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>"DeleteCommand="DELETE FROM [Table] WHERE [ID] = @ID" InsertCommand="INSERT INTO [Table] ([Name]) VALUES (@Name)" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>"SelectCommand="SELECT [ID], [Name] FROM [Table]" UpdateCommand="UPDATE [Table] SET [Name] = @Name WHERE [ID] = @ID"> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> </td> </tr> <tr> <td align="center"> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </td> <td> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" /> </td></tr> </table></form> </body> </html>
Hello all.... I am trying to submit data from a form(textbox) to a sql table. but I am getting an error message "NullReferenceException was unhandled by user code" Can any help me with this? This is my code inProtected Sub btnSubmit_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim cnstr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()Dim pa1 As Data.SqlClient.SqlParameter = New Data.SqlClient.SqlParameter("Keyword", Data.SqlDbType.VarChar, 50, Data.ParameterDirection.Input) pa1.Value = Keyword.Text SqlHelper.ExecuteNonQuery(cnstr, Data.CommandType.StoredProcedure, "spNewRec", pa1)
i wanna know is there a way to retrieve data from the sql database and then instead of putting it in the datagrid, can i put a specific field of data to a textbox?
i mean just a data (for example, a username where the password match the username) in a textbox.
I'm having a DB designed for me, and I'm inspecting it and wondering what in general is the better way to do this.
We have a product, which we are counting "product views". The DB designer has created columns called "view_today" and "views_alltime".
I specified I wanted a normalized database, I'm thinking this is technically not normalized ? Am I correct ?
Wouldn't it be better to have a query that counted the views off the logging table ? I can't see any advantage to doing it the way its been designed except to save time.
Hi experts, I have a textbox and a upload file function in my asp.net page.User can either copy/paste their resume in text or upload their resume file and submit the application.The uploaded file will be saved into a BLOB column, but do you know if text in textbox can be saved into BLOB column? I received error message on the code:'save Applicant resume to a BLOB-image datatype column objComd.Parameters.Add(New SqlParameter("@AppResume", SqlDbType.NText)) error:Exception has been thrown by the target of an invocation.Operand type clash:ntext is incompatible with image
hi please help me,i have a table queried using this sql, select name,(select count(*) from myTable a where a.name = r.name ) as Total, (select count(*) from myTable b where b.name = r.name and dnum > '1') as Used, (select count(*) from myTable c where c.name = r.name and dnum < '1') as remainingfrom myTable r group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150% this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks
hi everybody, i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server? What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'
I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.
Essentially I have a number of different alternatives, and have found some potential solutions, including:
www.dbforums.com/arch/7/2003/9/914261
However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun
Additionally, I would like to remove Holiday's from the calculation for service level as well.
I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.
The DTD I have for the Holiday table is as follows:
CREATE Table Holidays (HolidayDate DateTime) GO Insert Into Holidays (HolidayDate) Values ('12-25-2004')
Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates
