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.
Has anyone else had this problem?
thx
Helen
Hi,
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).
Thanks.
SouBee
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?
Hi All,
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!!!
-Matt
I have a relatively simple problem.
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.
View 9 Replies View RelatedHi'
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.
What to do?
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 ...
thanks in advane
Hello all,
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?
Kind regards,
Dirk Holland
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?
View 3 Replies View RelatedI have a report with 3 groups, and a toggle on the first cell in the group header, and group totals on that line also. So it renders as:
- Group 1 G1Total G1Total G1Total
- Group 2 G2Total G2Total G2Total
- Group 3 G3Total G3Total G3Total
Detail DAmt DAmt DAmt
I would like to save some space and render more like
- Group 1 G1Total G1Total G1Total
- Group 2 G2Total G2Total G2Total
- Group 3 G3Total G3Total G3Total
Detail DAmt DAmt DAmt
I haven't been able to find a way to have the first group cells overlap each other. Is there a way to do that and am I missing something obvious?
Hi,
I have 3 textboxes going down.
box1
box2
box3
Is there a way to hide the box2 (with some condition) and shift up the box3 when displaying the result?
like this:
box1
box3
I know how to use the hidden property but it leave a blank space between box1 and box3.
FYI. I'm trying to do this in the PAGE HEADER section.
thank you for your help.
jib
Table created
declare @t table
(
Qtr VARCHAR(1000),visittype VARCHAR(1000),
CMSContractID VARCHAR(1000,memid VARCHAR(1000),
CarrierMemID VARCHAR(1000),HeadOfHouse VARCHAR(1000),
dobdatetime ,FullName VARCHAR(1000),minstartdt datetime,maxenddt datetime
[Code] ....
Expected output:
QtrvisittypeCMSContractIDmemidCarrierMemIDHeadOfHousedobFullNameminstartdtmaxenddt
06/01/2014 - 08/31/2014EDH8677CA140812115 1000000039546468-Apr-54TOM5-Jul-146-Jul-14
06/01/2014 - 08/31/2014EDH8677CA140812115 1000000039546468-Apr-54TOM9-Jul-149-Jul-14
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 working with SQL reporting services, I am getting the data dispalyed...
the only problem is that I am not able to collapse, ie (+) button next to the row header and the cloum header.
Can any one let me know, how to get that....
Thnks in adv.
Hello -
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 two tables as:
Table 1
EmpID (int), AcctID (int), ActivityDate(datetime), Process (nvarchar 20), Cost (money)
Table 2
EmpID (int), AcctID (int), ActivityDate(datetime), Process (nvarchar 20), Duration (smallint)
Some sample data:
Table 1
501, 7998, 04-2-2008, Process1, $100
501, 7998, 04-2-2008, Process2, $75
Table 2
501, 7998, 04-2-2008, Process2, 3
Result i need is:
501, 7998, 04-02-2008, $175, 3
What I am getting is:
501, 7998, 04-02-2008, $175, 6
Any suggestions or help will be appreciated.
Thank you
- will
Hi Guys,
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 table t1
id maxday
1 4
2 6
3 3
4 10
5 10000
I want to expand it to t2
id d maxday
1 0 4
1 1 4
1 2 4
1 3 4
1 4 4
2 0 6
2 1 6
2 2 6
2 3 6
2 4 6
2 5 6
2 6 6
3 0 3
3 1 3
3 2 3
3 3 3
4 0 10
------
How can I do that? Thanks
Hi,
I need to expand tempdb database. The users keep getting this error
when they try to do a sort:
SQL Server message(1510): state 2
Sort failed. Out of space or locks in database 'tempdb'
Also in the error log I am getting these errors:
Error 1105, Severity: 17, State: 1
Can't allocate space for object '-641' in database 'tempdb'because
the 'system' segment is full. If you ran out of space in Syslogs, dump the
transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to
increase the size of the segment.
What I am wondering is if expanding tempdb will fix the problem, or is
there another fix that I should be looking at.
Any direction you could give me on this would be appreciated. I have very
little experience with SQL 6.5
Thanks in advance
Phil
In SQL 2005, when a file is expanded because of the auto expand option turned on, is this recorded anywhere (each time it expands)?
View 2 Replies View RelatedI've created a report with drill downs that are hidden initially. While viewing the report in preview, I'll expand the drill down to see the data.
If I go and print the report, the report is printed as collapsed (which is the initial setting) rather than expanded (which i expanded in the preview).
Is there a way to print in the "what-you-see-is-what-you-get" kinda way expanding and collapsing drill downs?
Thanks in advance!
I would like to have a report that globally sets the visibility (or expanding and unexpanding) of a group. Right now, I have the + and - signs for a group, but have to select each on individually in the report.
Thanks,
Linda
Hi!
I am trying to deploy a package to SQL. However, when I open Integration services I can not expand MSDB folder. Error is; SQL Server specified in SSIS service configuration is not present or available.... Login time expired .....(MsDTSSrvr)
I have found post here that says MsDtsSrvr.ini.xml file needs to be changed from
<Name>MSDB</Name>
<ServerName>.</ServerName>
to
<Name>MSDB</Name>
<ServerName>MyServerName</ServerName>
I tried MyServerName - does not work. I also tried value from connecting to Database Engine ( MyServerNameSQL2K5LOCAL) - does not help either.
What am I doing wrong?
Also, I need some documentation on deploying packgaes using File System.
I have saved Copy of the Package as File System location.
When I doubleclick the .dtsx file, Connection Manager holds my local SQL info. Goal is to deploy this to Development and other environments. Can this be handled some other way so I do not have to modify this file on every environment?
Also, this Execute Utility Package says at the top to use this utility to run SSIS on local computer.
So far, help on MSoft sites did not really help me a lot.
this is what I am using:
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: IDE Standard
SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00
I was wondering if anyone can help me out with this. Is there any way that you can have a document map in SQL Reporting Services expand on load, or if there is a way to programmatically set the Document Map nodes to be expanded when a report is generated.
Thanks in advance for your help
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I get the following error when I try to expand the sql server log in the management studio. My login is in the securityadmin server role.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
We have a person who CAN connect to a named instance in SQL Server Management Studio. There is nothing in the log for a failed login for him. However, when he tries to expand the Tables folder under the only user database in the instance, he gets the error: Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum). Additional Information: An exception occured while executing a Transact-SQL statement or batch. Select permission denied on object 'extended_properties',database 'mssqlsystemresource', schema 'sys'(Microsoft SQL Server, Error:229).
He is trying to expand the Tables folder under a database named ADSALLDB. He can see the folder as well as the other folders (i.e. Views, Synonyms, Programmability,etc.), but can't expand any of them. He can expand the folders under the system databases.
Other uses set up just like him can connect. He is set up with read/write access to the database ADSALLDB. He can expand the system databases.
This is a named instance in a 6 node cluster. SQL Server 2005 SP1 Build Level 2221.
Thank you for any help.
Has anyone had an issue with SQL Server not being able to expandagainst a RAID 5 file system? My current configuration is that theserver is started and stopped using the local system account. I haveonly one database (besides the master, model,etc)on the server. Whathas happend to me several times is that the primary database inquestion try's to expand the main datafile for the database (.mdf). Isetup the database to not expand automatically initially so that I canbe sure that we have enough file system space. Becuase of problems withthe application I decided to automatically expand. The other day thedevelopers came to me indicating that the databse was full and neededto be expanded. Knowing that the database was in automatic expandingmore I was surprise to hear this. I went into EM and attempted toexpand first the log and it would not indicating that it there was anissue in attempting to do so. I have never heard of a database notbeing able to expand. I ran DBCC's, etc and it came up clean. I triedto back the database up to disk and it would not backup. I finally hadto rename the datbase and rebuild it using DTS and scripts. I thoughtI had fixed it only to find out today that it (again) won't expand. Irenamed the datbase and then tried taking an older backup file andrestore it and it would not restore. This problem seems to be relatedto the file system but how I do not know.So, I am ready to run rebuild master but I have sone this before onlyto have this come back on me. I am at a complete loss. In the past Ihave had to rebuild the entire server and database from scratch. Theonly problem is that this has been done 3 times now with no completesolution or explaination. If any of you have seen this type ofbehavior and know whats going on please, please let me know what youthink the case and solution is!
View 8 Replies View Related
Hi,
I have created a menu using code behind i.e from c#.
is there a property of menuitem, with which i can expand i particular menutiem.
following is my code:-
*****************************************************
Menu objmnu = new Menu();
MenuItem objmnu1 = new MenuItem();
objmnu1.header = "Root"
objmnu.Items.Add(objmnu1);
MenuItem objm1 = new MenuItem();
objm1.header = "Child1"
objmnu1.Items.Add(objm1);
MenuItem objm2 = new MenuItem();
objm2.header = "Child2"
objmnu1.Items.Add(objm2);
MenuItem objm21 = new MenuItem();
objm21.header = "Node1"
objm2.Items.Add(objm21);
MenuItem objm22 = new MenuItem();
objm22.header = "Node2"
objm2.Items.Add(objm22);
*****************************************************
what i want is on click of a button i want to expand "Child2" node.
i need help.
bye
Mohan Nautiyal
I have seen a few posts in several forums about SQL Reporting Services 2005 not supporting the expand all / toggle all / or show all feature that many of us require for our report clients. A simple method I use is to create a report parameter names ShowAll. In the properties I have the following: Name is ShowAll / Data type is String / Prompt is Show All? and the Allowo blank value box is checked. In available values I chose Non-queried and in Label and Value I placed Yes - Collapsed and No - Expanded then in Default values I put Collapsed
Then on the field's initial visibility property, I changed the default from collapsed to expression and added the following:
=iif(Parameters!ShowAll.Value = "Collapsed", True, False)
subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.
View 7 Replies View Related
Yet another newbie question here in my quest to learn reporting services.
Is there a property I can set to automatically expand all drill-downs when a user goes to print a report? As it is now all the drill-downs remain collapsed when the user goes to print, which is problematic on large reports where there may be many records between groupings (users may expand one group containing 1000's of records, and then decide to simply print everything -- but they'll only get the expanded data -- I'd like to expand everything automatically and have it all print.)
Thanks,
I need to expand my page width and height. The report designer and report viewer allows me to do that. The expanded page does not export to the PDF Format. I am stuck width 6 1/4 x 8/12 for the PDF format. I am using the windows report viewer.
View 2 Replies View Related