I have a report with some groups which can all be expanded/collapsed by clicking on a textbox with an action attached to it. The example I used can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=600583&SiteId=1
But I want to hide the row containing this textbox (or the textbox itself) on the actual printed report. Is there any report item that can tell me if the report is in preview phase, or can it be solved in any other way?
I posted last year about getting and Expand All/Collapse All link working in reports, and it does work perfectly for on-demand reporting. I was able to get these working using the boolean report parameter and then the Jump to Report option going back to itself changing that parameter.
The issue that I have just discovered is that when a report with these Expand All/Collapse All links are in a report snapshot, clicking those links will cause it to re-render the report from the datasource at the time the link is clicked. This is an issue for one of the datasources we are using because the data is always changing, so when it goes back to re-render the counts and data returned are completely different then they were when the snapshot was first created. As time goes on the data will no longer even be in the datasource since it can only maintain 4 months of data.
Is there another way to get all detail groups to expand/collapse at once that does not require the report to be completely re-rendered from the datasource?
I'm relatively new to this, so bear with me here. (SQL Server 2005 Express, Datatypes are all varchar, int or money, nothing crazy...)
I currently have a table (not designed by me...) which looks like this:
ProjectID Months Expenses
3214 JAN 45.67
3214 MAR 56.78
1234 JAN 78.99
4567 MAY 43.56
And so on.... And I need this:
Project ID Jan Feb Mar Apr May Jun etc....
3214 45.67 56.78
1234 78.99
4567 43.56
I had attempted to do it using this code (really sloppy... I know. Beginner's attempt...)
DECLARE @Months varchar(4)
DECLARE @Counter int
DECLARE @Rowcount int
EXEC @RowCount=dbo.ReturnRowCount
SET @Counter = 0
WHILE @Counter <= @RowCount
BEGIN
SET @Counter=@Counter+1
SET @Months=(SELECT Months FROM TestData WHERE RowNum=@Counter)
SELECT @Months
WHILE @Months='JAN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jan)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='FEB'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Feb)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Mar)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='APR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Apr)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAY'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, May)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jun)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUL'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jul)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='AUG'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Aug)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='SEP'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Sep)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='OCT'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Oct)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='NOV'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Nov)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='DEC'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Dec)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
SET @Months=''
END
SELECT RowNum, ProjectID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec FROM SusansOutputTable
I also tried using IF statement instead of the imbedded WHILE statement and had the same result. What it's returning is 98,765 results of the same thing, with the exception of the last 25 rows, which return what I expected (which still needs to be collapsed...) I know there HAS to be an easier way to do this, I'm afraid it might be a bit beyond me though, any help?
Ps. ReturnRowCount function just returns the row count of the base table, assuming a rowid column with an IDENTITY int variable, which I can safely assume in this case. Also, the counter seems to work fine, but something is wrong in my internal portion of the while loop I think...
I am having a difficult time trying to generate a result set that collapses multiple rows into one. However, I am also getting some of the columns that I am trying to SUM, to double their value.
I have created a report where I have Z-Index all set correctly, but I cannot see the desired effect of toggling as can be seen in the AdventureWorks sample of SalesOrder report that gets shipped with SSRS. What am I missing?
I have a report that is grouping phone calls by "Category" (Outgoing, Incoming, Voicemail, etc). When the report is rendered, each category (along with totals) is shown and the details (each actual call) are hidden. Using toggling, I allow the user to burst open any category to view all of the call details.
I join the query for this report so that even if a category has 0 calls for the day, it is still shown. When expand a category with no calls I get a blank record (as I should, because there are no call details).
The question I have is ... Can I have the toggle be conditional? Can I have the ability to toggle thr group only when there are call details? Can I have the + to the left of the category name not be shown?
Thanks in advance for any help! Merry Christmas!!!
On one of my reports, the +/- for expand/collapse of groups is reversed -- i.e. when the group is expanded it displays "+" and when the group is collapsed it displays "-". The display of the document map is correct.
I have a db-query that returns quite a few rows which I must show in a table. At initial load the report only shows the row group heading for the detailed data, and when clicked the detailed data for a group expands.
My problem is that reporting services assumes that when collapsed it should summarize everything in each column and display it.
Here is an illustration of the problem.
First the detailed view of my data (Notice the 5th and 6th rows which are simply the values in the 1st through 4th rows divided into groups - and the "Total" row shows the actual total for Row 1 Group Header: [-] "Row 1 Group Header" "Detail data 1" "10" "Detail data 2" "10" "Detail data 3" "4" "Detail data 4" "6" "Row 1 group 1" "20" "Row 1 group 2" "10" "Total" "30" The detailed view is just as I want it - no problems there.
However, when the detailed view is collapsed, reporting services calculates the sum for the column - which is 90 - not 30 as is the actual total: [+] "Row 1 Group Header" "90"
Is there any way I can redefine the formula/insert filters used for calculating the "collapsed-total" for each column when the row is collapsed? Or maybe simply prevent it from showing a "collapsed-total" altogether? Or can you think of another way to structure the data so the "collapsed-totals" gives me the actual value (30)?
I thought about splitting the values into a column for each data-group, so that I'd have a column for the "Detail data n" values, a 2nd column for the "Row 1 group n" values and a 3rd column for the "Total" values - this would yield correct "collapsed-total" values. But is really a plan B - I really do not want to do that unless it is the only way out of my dilemma.
In my report i have more than 10 sub reports each should be displayed when we click on some label but my requirement is to expand all the sub reports at a time or collapse the sub reports.URL.....problem here is it is a genius solution working beautifully in my local machine but in the client server if i click on the Expand/Collapse radio button it is asking for the parameters again.
I have a problem with collapsing of groups. It seems that the reports saves the expand/collapse state of groups between renderings ... bug or feature?
In my report I have a dataset which contains some numerical data over time.
The report is designed to show data from a selected month as well as January to selected month. A parameter is used to select the month.
The presentation looks like the following: Company total Department Employee Employee ... Department Emplyee ... ... The employees have their initial state set to hidden, with their respective department as toggler.
This works perfectly ... within Visual Studio. When I deploy this report to my web server strange things happens.
Fx 1) If I expand one department to see the data for the employees for may and then decide to look at data from another month. 2) I change the month parameter and click 'View report' to render the report again. 3) The report renders as it should. All departments are collapsed. 4) If I then expand some other department than before, both the newly selected department and the old one are expanded ... a behavior that is not that practical
It seems that the report saves the expand/collapse state for the groups between renderings. So when I click expand for the second time it expands the one I just clicked and the department which was expanded before rendering.
Is there a way to expand/collapse all items in the documentmap ?
I know there is an option DocumentMapCollapsed but this is to collapse/expand the documentmap panel. I would like to control the collapse state of the items into the document map ...
has anyone out there found a way to get the best of both recursive hierarchy and drill down in the same report, ie without needing to know how many levels there are in your hierarchy, still being able to report them like a tree view with collapse and expand capability at each level?
How do I programmatically check a row group's Visibility or Expand/Collapse flag in a matrix table? For example, I have a matrix table contains the following groups:
Row groups: Facility --> Category Type --> Category Column groups --> year, quarter, month
I want to be able to programmatically update the table content if Category rows are not visible (Category Type row group is collapsed).
Hi! I dont know if i will explain this correctly, but my problem is with reporting service. I'm supposed to Sum value in one textbox and than that sum use it in sum in other textbox.
Something like this: Sum(Fields!Abc.Value/(Fields!dfg.Value+Sum(Fields!abc.Value)),"matrix1_RowGroup1")*100 I get error msg 'The Value expression for the textbox 'textbox49' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.'
Something like this wont work either Sum(Fields!abc.Value/ReportItems("textbox56").Value)*100 Error The Value expression for the textbox 'textbox55' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
So, pls help if you know how to reference textbox in other in body of report. Thx.
I have several textbox on my report and I would like to remove line if blank.
texbox1 textbox2 <--- use Expression in order to display it. textbox3
------------------------------------------------------------------------------ Current Display :
sergio myers
<-------- insert a blank line donna summer
_____________________________________________ The Desire Result that I would like to have : sergio myers donna summer _____________________________________________ I would like to remove the middle line if blank so it can be display like this : sergio myers donna summer
Does anybody knows how to accomplish this in Reporting Services. Foxpro uses "Remove line if blank" and 'Print only when expression is true'
Hey I got a SQL DB with three tables, where two of the collums of the main table gets its data from two other tables, ok? Now, on one page, called "search" the user should be able to search in the DB, and on another page the user should be able to register some keywords, my question is how can the user register several keywords in the same textbox, using perhaps a ; as a separator between the words? I hope you understand what I mean..
Hello How do u update the datafield of a row using buttonClick in vb? I have a details view whereby it will display the username and timeleft once log in. currently the datafield, timeleft on one user in the database is null. I'm using sqldatasource. Once log in, it will direct to this page where the user enters a code on the textbox and click the enter button. This will cause an update on the user's timeleft. But the code must not be entered to the database. For eg. If code = 1111 then 'update timeleft = 60 The timeleft is in minutes. 60 will be stored as double in the user's row in the database. How do i do this? Please help asap. Thanks! :)
HelloI have a problem when I want to update my textbox, I have a textbox called Textbox1, when I Insert data from it to database it is working well, even when I Update it WITHOUT SELECT field from database it is working well again,but when I Select [pagename] from database then want to Update it it is not working, nothing happend and in debuging the textbox doesnt have any value. what shoud i do? the value become null? Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Label1.Text = Request("Id").ToString() Dim conname As String = ConfigurationManager.ConnectionStrings("IPbrandConnectionString").ConnectionString() Dim con As New SqlConnection(conname) Dim sql As String = "SELECT [Id], [pagename] FROM [content] WHERE ([Id] = @Id)" Dim cmd As New SqlCommand(sql, con) cmd.Parameters.Add(New SqlParameter("@Id", SqlDbType.Int, 32)) cmd.Parameters("@Id").Value = Label1.Text.ToString() con.Open() Dim reader As SqlDataReader = cmd.ExecuteReader() Do While reader.Read() TextBox1.Text = reader("pagename") Loop reader.Close() con.Close() End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conname1 As String = ConfigurationManager.ConnectionStrings("IPbrandConnectionString").ConnectionString() Dim con1 As New SqlConnection(conname1) Dim sql1 As String = "UPDATE [content] SET [pagename] = @pagename WHERE [Id] = @Id " Dim cmd1 As New SqlCommand(sql1, con1) cmd1.Parameters.Add(New SqlParameter("@Id", SqlDbType.Int, 32)) cmd1.Parameters("@Id").Value = Label1.Text.ToString() cmd1.Parameters.Add(New SqlParameter("@pagename", SqlDbType.VarChar, 50)) cmd1.Parameters("@pagename").Value = TextBox1.Text.ToString() cmd1.UpdatedRowSource = UpdateRowSource.OutputParameters Try con1.Open() cmd1.ExecuteNonQuery() Catch err As SqlException Throw New ApplicationException("Data error.") Finally con1.Close() End Try Server.Transfer("main.aspx")
I am working with a Detailsview. I am displaying information from a number of different tables, and so when I configure the datasource I have to specify a custom sql statement. I can get it to display the right columns, but I cannot seem to be able to use a where statement. I am wanting to display the info in the Detailsview based on what is entered into a textbox, but I get an error when I try to add in the where statement. Can someone tell me what I am missing here? Thanks
Hey guys, I am trying to create a report which features a lot of textboxes side by side, but I have the problem that even though they are the same size when the textboxes are placed side by side or above or below another textbox and then the preview tab is selected they often look different for example they may not be aligned correctly or the borders look thicker in different textboxes.
I'm trying to assign multiple values to a textbox and I'm receiving an error. The error says, "The value expression for the textbox AcctName contains an error." The first value is account number and the second value is account name. An example follows:
1234 - SPC Travel Agency
My expression for the textbox contains the following:
I have a report with parameters but I show the parameters in a combo box (drop down list) with the valid values only, so, I need a single text box that autocomplete what the user types, could you help me with this please?
Hi, A report that looks perfect in the report designer has a minor problem when rendered. The textboxs widen if data is in them. If they are empty they are the proper length.
Do anyone know how I can replace or Substitute the current value of a form textbox with another value.For example, the string "YES" appears on the textbox but hit the SAVE button I would like "TRUE" to be entered in the SQL Server database table. Thanks.