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 RepliesI 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
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
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
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 have a table of Affiliates, each Affiliate can have a ParentId, which refers to another Affiliate. How can I retrieve all children of a given Affiliate, along with their depth level? I have this query which selects the total number of descendants of a given Affiliate, but I need to select all the children + their depth level, up to a certain level.
WITH Descendants AS(
SELECT RootId = Id, Id
FROM Affiliates
UNION ALL
SELECT RootId, Af.Id
FROM Descendants
[Code] .........
What I want to achieve is a statement that has 3 parameters: RefererId, StartLevel, and EndLevel.
I need a statement that selects all the specified Affiliate.ReferrerId's descendants including their level distance from the referrer, filtered by the StartLevel and EndLevel (WHERE CurrentLevel >= StartLevel AND CurrentLevel <= EndLevel ).
hello friends!
Can any body guide me regarding Tree/hierarchy Multi-Referential table structure.
What i have is only for known tree structure i.e upto 3 or 4 level but if i waanna to find nth level hoe shud i design my table structure.
T.I.A
hi,
How to get 2 level tree node in the sql server 2005. We have table with data.On load we populate the tree in Asp.net.
Regards,
Vinayak Panchal
Hi:
Is there a resource or an object in Reporting Services that I could make a tree view in a report file .rdl and see the result on it?
If not, is there a software of MS that a could make a component for it, and connect this component in the report file .rdl ?
In the Microsoft SQL Server Management Studio, could anyone tell me how to enable the 'tree view' for the Summary window? It currently behaves like a file folder where I can move up and down 'folders' but can't see the tree of where I'm at.
View 1 Replies View RelatedWe need to present hierarchical data on a web page, the same way thetree view shows files in Windows Explorer. Here's the catch: thattree view needs to be bound to a SQL Server database. How can this bedone?
View 3 Replies View RelatedMy Table Structure
Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....
MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
Can you help me please?
ID ParentiD IsAutoCalculate Level
1 0 1 0
2 1 0 1
3 1 0 1
4 1 0 1
5 2 0 2
6 2 0 2
7 3 0 2
8 4 0 2
9 0 1 0
10 9 0 1
11 0 1 0
12 11 1 1
13 12 0 2
The above table shows a parent child relationship with the hierarchy shown in column level. for each parent (IDs 1, 9, 11), I want the first child level where the column IsAutoCalculate = 0
so for parent Id 1, the rows to be returned is of level 1 as that is the first child row of this parent with IsAutoCalculate = 0. The rows with level 2 should not be returned
For parent id 3, the rows to be returned will be with level 2 as this is the first child row of this parent with IsAutoCalculate = 0
Hai Iam new to SSRS 2005, please help me regarding below Drill Through Report Problem. Assum my problem with below example.Iam tried Hard iam not able to find the solution, Any body please help me.my real time problem is same as below functionality
If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state, in the preview of the report iam applying drilldown to America column like + America, i need when clicking the America column i want to display Florida state under the column of Country like tree view structure
i need output like this and the same time i want to display other columns those also contain tree view structure assume other one is department column, with contains computers and sales, individualy computers contains HP, Sales contains Bikes
Country Department
+ America + Computers
---Florida -- HP
+ SothAfrica + Sales
---Capetown __Bikes
How to implement above output using drill through functionality, i tried with subreports and used all grouping formats, is possible for display output like above explain the procedure .
Thanks In advance
Jacks
Hi.
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
Thanks
Novi
I added the row group to show up as the tree view in the left side of the report. But I need it to display the records in the report detail for just that group value for the records when I clicked the group value from the tree view list.
Currently, it‘s not doing that. It takes me to that page where the group value is listed but it lists all other records too (which I don't want).
Hi,
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
Thanks
This is what I want to do:1. Delete all tables in database with table names that ends with anumber.2. Leave all other tables in tact.3. Table names are unknown.4. Numbers attached to table names are unknown.5. Unknown number of tables in database.For example:(Tables in database)AccountAccount1Account2BinderBinder1Binder2Binder3.......I want to delete all the tables in the database with the exceptionof Account and Binder.I know that there are no wildcards in the "Drop Table tablename"syntax. Does anyone have any suggestions on how to write this sqlstatement?Note: I am executing this statement in MS Access with the"DoCmd.RunSQL sql_statement" command.Thanks for any help!
View 2 Replies View RelatedHi All,
I have report which correctly outputs the PDF Export as well as when it displays it in the Reporting Services it is correct.
When it is deployed at the report server(IE) the view seems to differ a bit. As the report is a combination of Table adjacent to a matrix the IE view makes the Matrix move in the next line. Any thoughts on how can i control the display properties at the IE level only as the other views seems to be working fine.
Regards,
Vikram
Please find the table data below,
ISSUE_ID QTR_YEAR QTR_NUMBER TAX ISSUE_QTR_STATUS
10001 2014110APPROVED
10001 2014215Draft
10001 2014320APPROVED
10001 2014410APPROVED
We need two reports one is QTD and another one YTD..FOR QTD report is working fine.We are doing summing of YTD report as Q1+Q2+Q3+Q4
ISSUE_QTR_STATUS as APPROVEd
We are going to select the YTD Q1 reports we need the output as 10 Summing of Q1
We are going to select the YTD Q2 reports we need the output as 10 Summing of Q1+Q2
We are going to select the YTD Q3 reports we need the output as 30 Summing OF Q1+Q2+Q3
We are going to select the YTD Q4 reports we need the output as 40 Summing of Q1+Q2+Q3+Q4
ISSUE_QTR_STATUS as DRAFT
We are going to select the YTD Q1 reports we need the output as 0 Summing of Q1
We are going to select the YTD Q2 reports we need the output as 15 Summing of Q1+Q2
We are going to select the YTD Q3 reports we need the output as 15 Summing OF Q1+Q2+Q3
We are going to select the YTD Q4 reports we need the output as 15 Summing of Q1+Q2+Q3+Q4
We need a output view level.We are not using procedure in the report.
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/
[code]....
I can set the compatibility level by using :sp_dbcmptlevel
But before doing this - How can i view my current DB's compatibility level?
Any help appreciated
Hi there, I'm new here and quite green when it comes to SQL in general - I havent used it in any depth for some years and, in the absence of our DBA I've been asked to produce a report... I'm just using query analyser to extract some data as I don't have access to Crystal or anything similar...
I have a table laid out as follows - the PK is REC_ID. It basically stores all the contract start dates for each client we deal with.
RECID------Company-----Product-----------Date
445as------ABCLtd------ICT-Hardware------2007-01-14 00:00:00.000
253s1------ABCLtd------SOFT-Mainstream---2007-01-18 00:00:00.000
567o8------ABCLtd------SOFT-Maintenance--2007-01-18 00:00:00.000
809b9------Gen4Ltd-----ICT-Hardware------2007-01-14 00:00:00.000
098xc------Gen4Ltd-----SOFT-Maintenance--2007-01-18 00:00:00.000
551df------SteetsPLC---ICT-Hardware------2007-01-14 00:00:00.000
919sd------SteetsPLC---SOFT-Upgrade------2007-01-18 00:00:00.000
010qr------SteetsPLC---SOFT-Maintenance--2007-01-18 00:00:00.000
124vv------SteetsPLC---PERS-Allocations--2007-01-18 00:00:00.000
I can easily extract, for example, how many companies have taken up individual services, or many times individual services have been employed... what I'm having difficulty with is trying to extract, for example, all those companies who have had say taken up ICT AND SOFT services, whether explicit or using a LIKE statement.
For instance, if I wanted to find all those companies that had taken up both 'ICT-Hardware' AND 'Soft-Maintenance' and literally just get back the results...
Company
ABCLtd
SteetsPLC
Or a numeric result(in this case 2) would be ok...
How would I do that?
Sorry, this may seem to be very obvious to you but I'm at a loss...
Hope you can help.
Hello,
My 3D bar charts don't look as good as they could because the bars are too thick when displayed in 3D (depth-wise). If I have many bars, the bars are very thin on the front, and then go back far too much. Has anyone played with the settings to see how to optimize this? Even with 5% rotations, it is still too thick for my taste and doesn't look like the previews in the designer.
Mike
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 Replies View RelatedI'm having a brain freeze.
I seem to recall that there is a system stored procedure that lists table objects and the "depth" of their dependencies. Something like:
table name 3
table 2 name 3
table 3 name 2
table 4 name 1
table 5 name 0
The results show the name of the table object and the numerical depth of dependencies (ie, if the table had no foreign keys, the depth would be 0).
Does this ring a bell with anyone? I've googled, but my google-shui is weak today.
Regards,
hmscott
I am using Stored Procedures and C# to create my very first web app (VS2005 Professional, C#, SQLServer 2005). I am wanting to display the users just inserted details via the form view in read only mode. I have read through several partial examples, but I am still unsure as to how to call the id of the just inserted record and use that ID to display the details in the Form View as many examples do not use stored procedures and make use of VB code (I am using C#) and the SqlCommand. I am pretty sure that I do not have to use SqlCommand as I am using an object data souce with a DAL – but I could be wrong, as I am a noob. My stored procedure reads as follows: ALTER PROCEDURE dbo.usp_ResumeNameDetailsInsertFV @FirstName varchar(50), @MiddleName varchar(50), @LastName varchar(50), @UserID uniqueidentifier AS INSERT INTO [ResumeNameDetail] ([FirstName], [MiddleName], [LastName], [UserID]) VALUES (@FirstName, @MiddleName, @LastName, @UserID) RETURN SCOPE_IDENTITY() The stored procedure is called through the object data source and inserts the record as it should. I have manually tested this through the execute stored procedure in VS Server tab. Below is my Object Data Source: <asp:ObjectDataSource ID="ObjectDataSourceResumeNameDetailsFV" runat="server" DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="{0}" SelectMethod="GetResumeNameDetailsFV" TypeName="ResumeTableAdapters.ResumeNameDetailsTableAdapter" UpdateMethod="Update"> <DeleteParameters> <asp:Parameter Name="NameDetailID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="MiddleName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="NameDetailID" Type="Int32" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="GridViewResumeNameDetails" PropertyName="SelectedValue" Name="NameDetailID" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="MiddleName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:ControlParameter ControlID="HiddenFieldGUID" Name="UserID" PropertyName="Value" Type="String" /> </InsertParameters></asp:ObjectDataSource> Below is the partial of my Form View: <asp:FormView ID="FormViewResumeNameDetails" runat="server" Caption="<br />" CaptionAlign="Top" CssClass="formView" DataKeyNames="NameDetailID" DataSourceID="ObjectDataSourceResumeNameDetailsFV" EnableViewState="False" HeaderText="Name Details" HorizontalAlign="Center" OnItemDeleted="FormViewResumeNameDetails_ItemDeleted" OnItemInserted="FormViewResumeNameDetails_ItemInserted" OnItemUpdated="FormViewResumeNameDetails_ItemUpdated" RowStyle-HorizontalAlign="Center" SkinID="FormViewStandard" Width="100%"> Below is my insertion C# code: protected void FormViewResumeNameDetails_ItemInserted(object sender, FormViewInsertedEventArgs e){ GridViewResumeNameDetails.DataBind(); FormViewResumeNameDetails.DataBind(); GridViewResumeNameDetails.Visible = false; FormViewResumeNameDetails.Visible = true; } Any help in writing th C# code to call the stored procedure to call the scope_identity would be appreciated, as this has me stumped, even though I am sure it is quite a simple fix.
View 4 Replies View RelatedHello:
I've created a simple greenbar matrix using the tips suggested by Chris Hays. In Visual Studio the row totals display with the correct alternating background color, but when viewing the report via the web, the report does not render any background color for the totals?
I've attached a rough example (I trimmed out a couple of colums, so don't try to add up the column values displayed with the total value). As you can see, the values for the Total column do not get shaded, even though in VS2005 it shows the Total column shading the rows correctly.
200707
200708
200709
Total
AM 12
-
-
-
-
AM 1
-
-
-
-
AM 2
-
-
-
-
AM 3
-
-
-
-
AM 4
-
-
-
-
AM 5
-
-
-
-
AM 6
-
-
-
-
AM 7
-
-
1
2
AM 8
4
5
2
11
AM 9
1
2
1
6
AM 10
4
5
3
12
AM 11
2
3
2
8
PM 12
10
19
4
103
PM 1
3
3
1
8
PM 2
4
7
2
13
PM 3
-
12
1
16
PM 4
1
5
-
8
PM 5
-
-
-
-
PM 6
-
-
-
-
PM 7
-
-
-
-
PM 8
-
2
-
2
PM 9
-
-
-
-
PM 10
-
-
-
-
PM 11
-
-
-
-
I know how to explicitly set the various properties of the totals value field (i.e, click on the little green triangle to set the properties of totals), but I can't figure out how to make it dynamic. Trying to reference ReportItems!ColorTextbox.value generates scope error.s
Thanks in advance for help on this one.
--Pete