Adding totals to matrix report columns

Hi,

I'm creating a martix report that must have overall averages at the end of each row and column. I've added a list with a textbox to cater for the row totals but this doesn't work for the columns (unless there's some way of displaying the list horizontally...?) Adding a new item within the matrix doesn't work as it won't accept aggregate functions. There must be a way of doing this quite easily but I can't figure it out.

Any ideas or suggestions would be much appreciated.

Thanks,

Aidan

Turns out the solution is very simple - all you have to do is right click on the group header cell and select 'subtotal' from the pop-up menu. You can change the properties of the subtotal cell by clicking on the green triangle in the corner of the cell when viewing the properties window. It made more sense when I realised that the pivot cell must have an aggregate value - I was aggregating in my stored procedure so it took a while to figure it out...

Hope this helps someone at some point.

Aidan

|||

Thanks for pointing out the "SubTotal" setting for the column totals. However my question is regarding the Row Totals.

My report format is roughly like so

Category1 Category2 Category3 ... CTotal

Day1 10 20 30 60
Day2 11 22 33 66
Day3

...

RTotal 21 42 63 X

Basically I'm querying for rows by Day and have a GROUP BY for Category for certain type of records and am displaying the COUNT() on each day (e.g. 10, 20, 30)

So theCTotal is easily accomplished using the "SubTotal" setting. The problem is I cannot get RTotal to work. I at a point where I'm thinking of doing theRTotal in my query itself using a temp table for the original data, then doing a SUM of all Category COUNTS vertically and then doing a UNION to give me the bottom row. Needless to say, this would so lame of me I will not able to show my face to anyone.

So if you can set me straight on how you managed to do the Row Totals using a List Region, I would sure appreciate it. For the life of me, I cannot get it to work.

Best.

|||

You should be able to use the same subtotal function by right-clicking the the cell with your (Day1, Day2, Day3) Date Value (usually the second cell from the top in the leftmost column).

Adding to 2 different tables

Hi Everyone,

I have a page with a textbox and a dropdown list.
The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list.

I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list.

Here is the codebehind file:

Imports System.Data.SqlClientImports System.Web.ConfigurationPartialClass cms_Management_Company_NewCompanyInherits System.Web.UI.PageDim companyNameAs String Dim companyActiveAs Boolean Dim companyArchivedAs Boolean Dim companyDelegatesAs Integer Dim userForeNameAs String Dim userSurnameAs String Dim userEmailAs String Dim userUsernameAs String Dim userPasswordAs String Dim userActiveAs Boolean Dim userTypeIDAs Integer Dim companyIDAs Integer Dim iAs Integer Dim NoLoopsAs Integer Protected Sub btnSave_Click(ByVal senderAs Object,ByVal eAs System.Web.UI.ImageClickEventArgs)Handles btnSave.ClickDim conStringAs String = WebConfigurationManager.ConnectionStrings("General").ConnectionStringDim conAs New SqlConnection(conString)Dim cmdAs New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@.CompanyName, @.CompanyActive, @.CompanyArchived, @.CompanyDelegates)", con) cmd.Parameters.AddWithValue("@.CompanyName", companyName) cmd.Parameters.Item("@.CompanyName").Value = txtCompanyName.Text cmd.Parameters.AddWithValue("@.CompanyDelegates", companyDelegates) cmd.Parameters.Item("@.CompanyDelegates").Value = lstDel.SelectedValue cmd.Parameters.AddWithValue("@.CompanyActive", companyActive) cmd.Parameters.Item("@.CompanyActive").Value =True cmd.Parameters.AddWithValue("@.CompanyArchived", companyArchived) cmd.Parameters.Item("@.CompanyArchived").Value =False Using con con.Open() cmd.ExecuteNonQuery() con.Close()End UsingDim con2As New SqlConnection(conString)Dim cmd2As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@.UserForeName, @.UserSurname, @.UserEmail, @.UserUsername, @.UserPassword, @.UserActive, @.UserTypeID, @.CompanyID)", con2) cmd2.Parameters.AddWithValue("@.UserForeName", userForeName) cmd2.Parameters.Item("@.UserForeName").Value ="First Name - Delegate 1" cmd2.Parameters.AddWithValue("@.UserSurname", userSurname) cmd2.Parameters.Item("@.UserSurname").Value ="Surname - Delegate 1" cmd2.Parameters.AddWithValue("@.UserEmail", userEmail) cmd2.Parameters.Item("@.UserEmail").Value ="Email Address - Delegate 1" cmd2.Parameters.AddWithValue("@.UserUsername", userUsername) cmd2.Parameters.Item("@.UserUsername").Value ="Username - Delegate 1" cmd2.Parameters.AddWithValue("@.UserPassword", userPassword) cmd2.Parameters.Item("@.UserPassword").Value ="Password - Delegate 1" cmd2.Parameters.AddWithValue("@.UserActive", userActive) cmd2.Parameters.Item("@.UserActive").Value =True cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 2 cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 1 Using con2 con2.Open()For i = 1To NoLoops cmd2.ExecuteNonQuery()Next i con2.Close()End Using Response.Redirect("~/cms/Management/Company/Company.aspx")End SubEnd Class

The other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)

I hope this makes sense.

Thank you very much guys.

Scott.

Hi,

To get the recently added record's ID use

SELECT SCOPE_IDENTITY()

and catch the returned value using

cmd.ExecuteScalar()

HTH
Regards

|||

Hi,

Thanks for the reply, where in the code would I put these elements? I am very new to .NET.

thanks again,

Scott.

Adding multiple textboxes to PageHeader

I am creating matrix reports programmatically and have come up against a problem where I can only add a single textbox into the pageheader. Writing the report using the report designer software I can add multiple textboxes to the pageheader, however trying to add many of them using the ReportItem object I keep getting a syste.object[] cannot be used in this context. error.

for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };
}

This is the line of code that I am hitting the problem. This works fine, however only inserts the last textbox in the Items Array.

If I change the code to

for (int i = 0; i < Items.Count; i++)

{

reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };

}

It executes, but fails when trying to render it into XML.

Has anyone managed to get multiple textboxes programatically into the pageheader?In the first approach you are overwriting the Items array with each iteration of the for loop. And in the second approach you are setting each entry in the Items array to a new object array, so you end up with an array of arrays. Instead what you should do is just add the object to the array. Try using the following code.

// create a new array for the text boxes in the header
reportItems.Items = new object[Items.Count];

// iterate though all the Items creating a textbox, which is added to the report items object array
for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle);
}

Ian

adding data entered in to web textbox to rdlc report

I have a report services report that I created in local mode that takes data from a dataset and creates a report off of it. I need to add a field into the report that shows some of the data that the user enters in to the textboxes on the web page. Can someone explain to me how to do this or point me to a resource that will show me. Thanks!!

Pass the textbox data as parameters to your report. This entails simply loading the textbox(es) data into a parameter array & passing it to your report. Cheers!