the field which has drilldown option is always showed minimum width after delopying to website
I attempted to set all properties of this fields as i can,such as "width","can grow" etc, but all do not work
any idea about this?
thanks
Hi,
I've been playing around with SSRS 2005 to see if it might be the solution for some reporting requirements I have been given, but I still haven't found a way to do exactly what I need. Basically I've been asked to provide a report which allows drilling down multiple levels of a client hierarchy to show some sales figures, sounds pretty simple right? The report will look like this (not showing sales figues here just client hierarchy)...
+ Client A
- Client B
- Client C
- Client D
+ Client E
- Client F
- Client G
- Client H
- Client I
- Client J
+ Client K
+ Client L
As you can see they want to be able to expand & collapse rows to drill down through their client hierarchy. The hierarchy will be 3 or 4 levels deep.
I have seen that RS2005 supports a simple way to drill-down via grouping, but I don't want to do it this way because it means that I will have to have a query which retrieves all of the data right away which would take several minutes.
What I'd really like to do is to lazily load the hierarchy so that the data is loaded as the tree is expanded so that it won't take too long to render the report initially. I.e. when they click on "Client B" I'll hot the DB and get the sales figures for all children of Client B, in the case above thats Clients C & D.
Can this be achieved using sub reports or report linking? If anyone knows of some tutorials or examples that might be able to help me with this I would really appreciate it.
Thanks.
Lachlan
Hello,
I am creating report which has group and I want to show the [+] in front of each grouped item and when user click this the group item will get displayed.
Please let me know any solution.
Hi,
I'm new to SQL Reporting Service. Now only I've started studying that. My question is,
I wanna list out my product sales for each month for each keywords. For example, I have 4 products, some 15 keywords (its an ecommerce site. Visitor coming from a particluar keyword and buying some products. Wanna list out sales for each keywords and product for Every month. To know the quarter sales wanna see the sales and product name for particular keyword). I'm having reports for each month sales. like,
Keywords
Product A
Product B
Product C
Product D
Keyword 1
125
24
275
12
Keyword 2
456
245
67
1223
For quarter sales, I want to see the sales and keywords list for each month and for each products in the same report. I don't want to create one another report for each and every product and month. If I want to see the sales and keywords for a particular product I want a report like,For Product 1
Keywords
Month1
Month2
Month3
Month4
Keyword 1
125
24
275
12
Keyword 2
456
245
67
1223
and For Month 1
Keywords
Product A
Product B
Product C
Product D
Keyword 1
125
24
275
12
Keyword 2
456
245
67
1223
Just by using drilldown I want this report. If any other method is possible, please tell me. It's very urgent. Please Help me. I'm very new to SQL Reporting Service. Started studying before 2 days. Thanks.............
When i am designing a report using BIDS how do i control the Drilldown on the Matrix Totals. At present when i created a drill down report it works fine for all non total Values but chooses a random Value when it drills on Totals (Usually the first but not always)
Eg if i have a Matix that looks like this
Name, Value1, Value2, Value3
John, 1,1,1 = 3
Mark 2,2,2 = 6
Fred 3,3,3 = 9
Total 6,6,6
If i want to Drill on either the totals at the bottom or the totals on the Right or the bottom, it will choose a random Value eg if i want ot see the totals for John the Column Parameter that it passes to my drill through report will be that for Value 1 which is incorrect.
Any help greatly appreciated.
Hi,
some of our Drill down has many values and we would like to display only top 10 ...
I know how to create CALCULATEDMEMBER for the cube ..but that becomes a measure and will NOT be in the drill down so,
how to create Top 10 for Raw Url
Drill Down
Groups
Top 10 Urls
( this has 10000 values€¦€¦.. can we just display top 10 here? Right now the report never comes back because of the count€¦ or if there is a better way let me know
thanks
Hello,
I am writing a report that has two levels of groupings like this and the visibity is controlled by Title and Staff Grouping (Partner, Employee) for drilling down.
WTD Hours
*Staff
*Manager
Joe 8
Max 8
Totals 16
*Senior Manager
Bob 7
Totals 7
Totals 23
*Partner
*Partner
Ed 20
Total 20
Totals 43
* = Toggle Item
Now, all this is fine except that when everything is collapsed, I want to show the totals on the group header and hide the group footer, like this:
WTD Hours
*Staff 23
*Partners 20
Totals 43
NOT like this:
WTD Hours
*Staff
Totals 23
*Partners
Totals 20
Totals 43
I have the functionality working with the toggle states and visibility, but whenever the group is collapsed, all the formatting is lost and it is just whitespace not matching the rest of the row.
Is there any way to control this by some expression in the value, etc?
Thanks in advance!
I'm basically learning SSRS (and SQL for that matter) so bear with me. I'm building some reports and discovered that apparently one has to have the Enterprise edition to have the drilldown capability. We really don't need the Enterprise edition for anything else, being a small company. Is there absolutely no way to do drilldown in the Standard edition, is there any way to add just that capability, and are there any sort of workarounds? Thanks.
I have a report that drills down into 2 different levels like this:- Main Report -> Report -> Detail Report.
Everything works fine for the first 2 reports but when I try to drill down to the 3rd report (Detail Report in my example above) I keep gettting this error:
The path of the item '(null)' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)
Has somebody got a solution or a workaround?
I have two servers running the same report server project with the same webapplication front end.
Both instances of SQL are on SP2.
When I drilldown in the report viewer on server A, and click next page it returns to the parent report. (which obviously it should go to the next page of the current report) Sometimes this odd behavior happens and sometimes it doesn't.
However on server B, everything works fine.
Note: the installation on server A went a littly crazy. Am I missing a service pack that would have fixed this?
When runing a report in Report Manager I can click on an item and SQL Server provides the logic to drill down into the underlying tables. I can find no button to go back. The only way I've found to go back is to click a different tab at the top of the screen (History or something) then click View again and you get the original report. In designer there's a back button. Is there not such a button when running a saved report????
Thanks.
Hello,
I have a drilldown matrix report similar to the one below...state - drilldowns to sales person which lists the sales for a particular product...in each state there are usually more than 10 names, however i would only like to see the TOP 10 name appear for the sale off that product
My SQL is as follows
SELECT Sales,Consultant, ProductNo, State, Sum(Value) As SumOfValue,
From tblSales
where productNo = 2000
ORDER BY SumOfValue Desc;
If I put in the select TOP 10 it returns only the top 10 for all the states however i need the top 10 off EACH state, is there a way to filter this..or better yet a sort button which will show the top 10 only.
Sales for product
NSW charlie brown 25
bob snow 20
william tell 10
....50 sales reps..etc
WA Charles manson 34
fiona apple 20
peter 1 ....more sales reps etc
Hi
I€™m having trouble again with a matrix report.
I wish to make a matrix report with 5 columns. The right part of course will be filled depending on how many moths the user has chosen. The left part will be field with country, city and then the person names and some other info.
What I want to do is to have the first 2 columns, country and city, with drilldown. That is pushing the plus sign to open the corresponding group. How do I do this?
Thanks in advanced
Kind Regards
Im having a silly question, but im not able to find a solution for this. Im having a simple tabular report in which i have 3 groups defined and a detailed row. It is a drill down report wherein we need click on each group to get the next group and so on. When i went thro the wizard i was able to get that, so i tried myself without the wizard by adding the table and groups manually. However i found for the details grouping i was not able to set the "Visibility can be toggled by another report Item" because it was disabled, but in the report which i generated thro. the wizard it was selected and the last group item was given in that. Im not able to find why the checkbox is disabled when i create the groups manually. Please Help.
View 6 Replies View RelatedHi,
I have a table with three group headers. I want the header row in the third section to be toggled by a textbox in the second header. This works as expected. However, there are times when there is no data in the third section. When this happens, the third section header row shows even though there is no data. I can also create an expression for the third section header's hidden property that looks for the existence of data, but then the toggle is deactivated. I need some way to say, "here's the toggle item. Show the header if there is data for it."
Any ideas are welcome.
Dear Friends,
I have 2 matrix in my report and I have the drilldown colunmns year, quarter, month and day. But when I click in year of matrix1 I want to expand the quarters for matrix 1 and 2... and not only for the matrix 1... How can I share the columns with other matrix?
I trued to use textboxes, but in textbox I only see one item... Q1, January... etc... in spite of have all the childs...
Understood?
regards!!!
I Want to Create Drill Down Report in SSRS . Here we Should Display Year Wise ,Month Wise, Day Wise For Particular party Salry . How To Do it?
View 2 Replies View RelatedHi Anyone,
I have a drilldown report includes three groups. I add the last group for pagination. But the details in each page doesn't accord to I specified and the detail record number is different in different page.
Another issue is the interactive sort always sort in the first page scope. I set the data region or grouping to the table, and evaluate expression scrope to Detail scope.
Anyone has experience on that?
Thanks a lot
I have a report that (for purposes of this example) displays States and the Cities in a state.
Now the State is the first group which I want to drill down into by expanding and then show the Cities as the detail line.
What I would expect to happen (and what does happen if I use the wizard instead of creating the report manually) is that I would click on a State and then the page would expand to make room for all the cities listed below.
(That is to say if I am showing only states, not expanded, then all 50 states fit on a single page. Then if I expand California all the states below that shift onto other pages to make room for the 400+ cities in California)
However, what happens is that when I preview the report instead of being 1 page long it is many pages in length with empty white space between the States (white space that would be occupied by the cities if I were to expand the state).
Can someone tell me what setting I need to adjust to get the report to dynamically resize when I expand/collapse groups?
Thank you.
does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report? The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand. But expand doesnt do anything after initial rendering.
The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.
I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
View 2 Replies View RelatedHi,
I will give someone a script that creates a database using :
create database mydatabase
my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737
go
use mosaikDB737
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileListInput](
[FileName] [char](50) NULL
) ON [PRIMARY]
END
use master
select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb')
select * from mosaikDB737.dbo.DBlistOutput
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems
FROM Table1 Inner join on table1.assettag = table2.assettag
where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed
thank you for any information or help.
Hi,
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
mySqlConnection.Close()
End If
End Try
End Sub
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
View 3 Replies View RelatedI have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
Hello,
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is
successful, my return value is coming back correctly (to whatever i set it)....but if there is an error
such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP.
It comes back blank. (The literature I've read says that processing should continue in the SP, so you
can perform error processing...is that right?)
I set the return var in my ASP as:
objCommand.Parameters.Append objCommand.CreateParameter("return",_
adInteger,adParamReturnValue,4)
and read it back as:
strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah
if @@error = 0
return(100)
else
return(200)
(Idon't ever get back "200")
Any ideas???
Thanks for your help.
Hi
I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody
My goal:
I have been written a GPS program that has an database.
The database will be replicated to an central web server.
That will say one web server and x numbers of laptops that has same GPS program. :)
All laptops uses internet to replicate.
Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:
Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.
Problaby I need some kind of an database on client and subscription to make the replication. :confused:
please help me, I'am almost finished with my project only replication part is over my head :(
if someone can point me to right direction in this issue. I would be greateful. :cool:
Thanks
KK
I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.
How can I use xp_sendmail using a dabase other than Master?? Please Help.
I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together.
So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?
select somedata, somedata, somedata, somedata
From kpi..temptablel l
left outer join @temps s on l.x = s.x
left outer join @tempf f on l.x = f.x
left outer join kpi..temptablee e on l.x = e.x
left outer join @tempn n on l.x = n.x
where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y
The Yak Village Idiot
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')
also is it wise to use Group by and having in sub-queries?
Hi
I have an slq Express mdf at path X and I copy it to path Y. When I open it up (from the Y path) using sql mgmt studio, it shows that it's from path X.
Why? How can I get sql mgmt studio to recognize it as a separate mdf, distinct from the one at path X?
TIA
rank beginner
I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.
View 5 Replies View Related