Hi, I am new to SSRS. Is there a way to show part of a report based on User. Say we have a report consisting data for all department. When a department director view it, the report only display data for his/her department. Can this be achieved in SSRS? Thank you in advance for your help.
View 3 Replies
Hello. I am trying to write a report that pulls information in from Active Directory. I have a view created that gets a listing of users and a view that creates a listing of user groups, but I can't seem to figure out how to get all user groups that are associated with the users. This is what I have.
SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, 'SELECT cn, groupMembershipSAM FROM ''LDAP://wmdomain.local'' WHERE objectcategory=''group''') Rowset_1
SELECT *
FROM OPENQUERY(ADSI,
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn
FROM ''LDAP://wmdomain.local''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Rowset_1
Let me know if you have any suggestions!
BJ
Hi,
I have some sensitive and non-sensitive info/fields in one of my reports. Is there a way to hide/show the info based on the logged in user? or do I have to create separte reports for each type of info like one for sensitive and another one for non-sensitive info.
Thanks inadvace for your help!
Hi All,
I have requirement where first I need to show only one report
parameter. Based on user selection I need to prompt or show the user
another report parameter.
Say suppose I have 3 parameters. User selects first value in first
parameter I should not show the other 2 parameters. If user selects
second value in first parameter I should show second parameter and
hide third parameter. There is no relationship between these 2
parameters except user selection. Similarly if user third value in
first parameter then I should show third parameter and hide second
parameter.
Is this possible? I can not see any Visible property for report
parameters.
If yes, how to achieve this functionality?
Appreciate your help.
Regards,
Raghu
Hi ,
I am working on Sql server Reporting Services(Sql Server 2005),
i have designed a Report and deployed that report on Report Server, on this report i need to show the user selected values
in page header by using Parameters which i have already created in the Report.
There are 35 to 40 fields in the Front End(Asp.net2.0)
on passing all these values from front end Report is prompting for parameters which is not desired for my case even though i have made ShowParameterPrompts to false.
Thanks in advance
Srinivas Govada
I have created reports using SSRS 2005 and deployed in ReportServer.
I calling these reports form my web application.Users have different roles based on their login into web application.I need check these user roles and display the reports based on their roles. There could be 10 reports in total, but for this user i should display only 4 out of them. Its pretty urgent. Can somebhelp on this?
I am trying to develop a data model that will filter data based on the user logged in. SUSER_SNAME()
I have a list of User Id's in a look up table, a where clause in a view that filters the data based on who is logged in. This works fine and the data even seems to be filtered when I explore the data in the Data Source View. How ever when I publish the data model to the reporting services site and use report builder the user logged in is no longer recognized. All I am really trying to do is set up report builder so that users can generate reports and only have access to thier data via a customer number. I just can't allow users to see other users data and I don't want to set up a different datasource view for each user. Does any one have any experience with this?
Why terms are not being corrected on a partial match? I thought this was the point of term based relation rules.
I am testing in an email address (nvarchar) field?
E.g. trying to correct @@ to @ and .con to .com (just to test)
And everything passes.
If I test with an entire value, the field is corrected.
E.g. 605688878@@qq.com corrects to 605688878@qq.com if I enter those exact values as term based relations.
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
hi
I have 10 pages in my report and i want to export only 3 page .
any body can help me on this
Thanks
Pratik Mehta
Hi all,
I was wondering whether there is a way to show the user an error when the user did not select a specific parameter.
In my case, I have two optional parameters. The user has to select either one to view the report. If the user does not select either one, I would like to show some sort of an error page indicating so. However, all i am getting is a complete blank with no report in sight. I as a developer know this is an error as a result of missing parameters, but i was wondering whether I could produce a page or direct it to a page so that the user doesn't go all horrified at an empty page ?
Thanks
Bernard
On my Home page I have a gridview that has Four columns: CustomerID, Product, Quantity, then a link (which appears on each row..called View). When a user clicks View on a particular row it will then send that particular CustomerID to the next page (CustomerProfile.aspx).
On the CustomerProfile.aspx page I have a textbox where the Customer Name should appear. How do I go about displaying the customer name in the textbox based on the CustomerID passed to the customerprofile.aspx page? Do I need to call a stored procedure? Do i need to call the Customer Table in my code?
(i'm using C#) Thanks!! Ur help is very much appreciated!
Side Note: i am passing the CustomerID by a SESSION variable called customerID ~~~>this is in my pageLoad method labelCustomerID.Text = Session["customerID"].ToString();
but i'm wanting to have the Customer Name in a textbox based on the above ^^^ session variable. My textbox is called txtCustomerName.Text
*** Customer Table***CustomerIDCustomerNameCustomerAddress
^^^wanting to call the customer name and put it in the customer name text box based on the particular customerID
I'd like to hide/show a table based on selected parameters. In the current setup I have a matrix and two tables underneath that need to be displayed from time to time. First of I use a multi-value parameter called "Lieferart". Depending on the parameter I have set the visibility of the two tables with the following expression:
=iif(Parameters!lieferart.Value(0) like "Nagel%", False, True)
This expression doesn't work though, any ideas??? I am also unsure about what I have to do If multiple values from the parameter list are selected as Parameters!lieferart.value(0) doesn't necessarily need to have a label like "Nagel" included...
I do appreciat your help, I want to run a store procedure which will show 6 months . I do not know how to write the procedure, here in the notion in my mind, I want to be able to pass an input parameter (month) to the procedure which will then run a query to show 6 months prior the input parameter month, how can I do that, thanks for your help
Ali
I simply need to list the most recent value for each employee here... the value at each person's maximum date
Sample Detail Data:
EmpID Date Value
1 1/1/14 27
1 2/12/15 333
2 5/5/15 255
3 5/4/15 110
3 1/1/13 67
3 3/2/14 80
[Code] ....
What is the most efficient way to display the most recent value for each employee ID via MS SQL.?
How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.
View 7 Replies View RelatedHi all,
I'm very new to report builder and I don't even know if this is possible. Some of my users have asked if they can build an ad-hoc report to show say top 50 annual salaries. This has to be done soley with whatever report builder has for building ad-hoc reports as that's all the users have access to.
Any help would be most appreciated. Thanks.
Is it possible to show the user name (such as 'phuser') who is a member of the sysadmin group (NOT my idea!) I notice if you go to current connections is SQL EM the name shows, but if I login as that user if I try, user, user_name, etc inside of QA it shows DBO
View 4 Replies View Related(SQL Server 2008) I want to display a total for each user that is returned from my query result. I was trying the union all route as that is what I am most comfortable with, however, the result set returns the total row at the bottom instead of under each user. This is the desired result set...Here is my query, what do I need to alter to have the output display like so?
Code:
Create Table #Test
(
id varchar(50),
name varchar(500),
out1 int,
out2 int,
total4day int,
[code]....
I created the following Default
CREATE DEFAULT HelloWorld AS 'HelloWorld'
Now, tell me how can I get the information for this default? I can find it in sys.objects and it says its a DEFAULT CONSTRAINT, but sys.default_constraints does not show anything. Anyone?
Thanks
I've created a linked server with a pretty basic Excel spreadsheet, and used this command to create a linked server to it:
sp_addlinkedserver ''XL_SPS_1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls', null, 'Excel 8.0'
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel
as
select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
Thanks,
--Stan
How do I get data on my linked report based on my grouped subtotal and grand total from the main report. The subtotal and grand total are calculated columns.
I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.
The following is code for my summary report. The results are shown below.
SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts,
CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST'
WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST'
ELSE 'OTHER'
END AS Actn_COAST
FROM tbl1
where ACTNDATE between @STARTDT and @EndDT
Code for my detail report contains the following SQL
SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY
I have linked my report based on the NumbOfAccts column. I am able to get data if I click any of the NumbOfAccts values related to the state I want. However when I am not sure how to make the subtotal and grand total work. I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast. If I click on the Grand Total, I only want data related to those 2 coasts.
Hello.I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:http://forums.asp.net/p/1159666/1913519.aspx#1913519http://forums.asp.net/p/1161930/1924264.aspxhttp://forums.asp.net/p/1116601/1732359.aspx#1732359http://forums.asp.net/t/1104718.aspxhttp://forums.asp.net/p/1096290/1655706.aspx#1655706http://forums.asp.net/p/1110162/1707952.aspx#1707952 Basically, I need a DropDownList to display only projects for which the logged in user is assigned as leader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code? * Here is the definition of the SqlDataSource: <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>" SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @Leader)" OnSelecting="SqlDataSource5_Selecting"> <SelectParameters> <asp:Parameter Name="Leader" Type="Object" /> </SelectParameters> </asp:SqlDataSource> * Here is the definition of the SqlDataSource5_Selecting method: protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters("@Leader").Value = loggedInUserId; } where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey; Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId; in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
View 11 Replies View RelatedI am in process of building a website where the user can upload files and then those files are loaded in to a sql server database. I am using some sprocs to scrub the data and then insert them into the production database.. And in my sprpc before and after updating or inserting a record or scrubing... i am returning the count by raising an error. or returning the rownumber where the error occured.. is there any way i can get the raise error part or whatever error i get while scrubing the data and relay it back to the user in a user freindly way or in a text file... or the best thing is can i open smalll window where i can show them what processing is goin on and alert them if there are any errors...
Any help will be appreciated.
Regards
Karen
In our new app, a user is given the option of uploading one or files to the server and saving the filename and path to the database.
We would like to display all files associated with a particular user.
Obviously, the code below is only displaying one file.
How to display all files associated with a particular employee?
select ROW_NUMBER()OVER (ORDER BY empID) as empnum , fName, filePath from Employee where EmpID='KF14170';
I might add the format of the file is empID plus the filename:
Example: KF14170Evidence1, KF14170Evidence2.
I tried creating Toggle Option over a Text box to Show the Image on Click but Since the Toggle refreshes the entire report user faces performance issue. Is there any other option to avoid the Toggle Refreshing the report or to show an image on Click of an Text Box?
View 3 Replies View RelatedHi,
We have a report published to two report servers ( same configuration). this report displays data in two side by side tables. on server 1, the left side table does not get displayed where as both the tables show fine on server 2.
What could be the reason for this
Any help greatly appreciated.
Thanks,
Phani
Hello,
Since a week we have a strange problem.
We are using SQL Server 2005 with SP2 on a Windows Server 2003 updated last time in November. We have 2 servers like this, one is a virtual machine and the second one, a physical one.
Since a week, sometimes, the reports in the web report viewer just don't show. They keep loading for hours without showing anything. Beside, everything's fine, the server has plenty of free resources (CPU, RAM...), Visual Studio works normally, SSMS too. We can even go in http://localhost/reports and http://localhost/reportserver and see the folders, files etc... but when trying to load a report (what ever it is, even a small one) nothing comes, not even the message "Report is loading".
It happens not every day, sometimes two or three times a day at different hours.
In the Windows event viewer, nothing.
In the SSRS logs, nothing (as far as I can understand these logs).
It looks as if a job or a process was running and was blocking the reports or a service which would stop automatically.
To have it work again, a reboot of the server works or within 2 or 3 hours, it comes back to normal state.
If someone has an idea of what can be the problem or some tips, please feel free because we are running out of ideas.
Thanks in advance
How to display sql compute sum command their result in amount and qty in report?
I am try this query is successful show the total amount and qty in studio management server and show in screen. But run this query on report is without to show last column of qty and amount, why ?
select
xsodetail.shopcode,xsodetail.memono,xsodetail.txdate,
xsodetail.sku,xsoheader.depositamt,xsopayment.paymentcode,xsopayment.paymentamt,
xsodetail.itemamt,xsodetail.salesqty
from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsopayment on xsoheader.shopcode + xsoheader.memono = xsopayment.shopcode + xsopayment.memono
where (xsodetail.sku = 'L000254' or xsodetail.sku='L000256') and xsoheader.voidflag='N' and xsodetail.txdate = CONVERT(varchar(100), GETDATE(), 112)
compute sum(xsopayment.paymentamt) , sum(xsodetail.salesqty)
Hi,
I've designed a report and assigned it a datasource that binds with a SP with a parameter. In the XML code of the dataset I can see the input parameter has been recognised by the wizard. Then, I built a report with that Dataset as the source. Everything is fine till this time. Now through my aspx code I pass on the value of the parameter to the report. But the report doesn't show any values at all. The aspx code is like
Code Snippet
Dim reportParameter As New ReportParameter("@UserID", Convert.ToInt32(TextBox1.Text), True)
Dim test() As ReportParameter = {reportParameter}
ReportViewer1.LocalReport.SetParameters(test)
Any ideas what I could be doing wrong?
VS 2005 & SSRS 2005.
TIA.
I have two seperate reports that have parameter clientkey in common. I want to show on one report these 2..first the one, then the 2nd below it. I've tried jsut using 2 tables but they overlap. I'm now trying a subreport, but I'm not sure how to do this or even if it is the right method.
View 1 Replies View RelatedHi all,
I was wondering whether there was a way to implement a "Hide all/show all" option in the report. are there any examples that I could refer to ?
Thanks !
Bernard
Hello,
I'm currently investigating whether SQL Reporting Services might be the right solution to some reporting requirements I have been given (web reports).
There is a requirement that some of the columns are hidden to begin with and can be shown by clicking on the header of one of the other columns. E.g. There is a list of clients and a "Total Revenue" column, clicking on the "Total Revenue" column header would show an additional 5 or so columns which show a breakdown of different figures that make up the total revenue.
So my question is this: Is it possible to show/hide columns by clicking on the headers of other columns in the report?
Thanks,
Lachlan