I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I have named this text box €™SubRepM1€™ The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year. For the first month the value is =sum(Fields! Month_1.Value)*-1, and I have named this text box 'MainRepM1' The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values but I now need to total these values together for each month in order to produce a grand total.
I have tried using the following to add the totals for Month 1 together, =subreport1.Report.SubRepM1 + MainRepM1 but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
in my report i have amount field. i want to display its total (sum) on each page..... i have tried runningvalue in tablefooter but it gives final total.......means grand total... i have also tried sum function in tablefooter & set repeateoneachpage = true but it gives same result.............. and i want pagewise totals...... anybody can help me...................
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.
For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,
For 1st group
'Page 1 of 3€™ when user click next page it should be 'Page 2 of 3' etc
I know how to reset the page numbers with each group, but how do you reset the total page number within each group.
EX. Code for page of total pages
="Page " & Globals.PageNumber & " of " & Globals.TotalPages
EX. Code to reset within a group Custom Code: Shared offset as Integer Shared currentgroup as object
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object If not (group = currentgroup) offset = pagenumber - 1 currentgroup= group end if return pagenumber - offset end function
I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.
I want to avoid a Loop (i.e. cursor or a CTE).
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4)) Insert Into @Stage Select convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai Union all select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0 Union all select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
UPDATEs1 SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)
I am using SSRS (Sql server reporting services) for one of my report related to EIS-MIS. i want group wise page numbering and also display the no. of pages for that particular group.
e.g suppose group 1 has 5 pages then it should display page no. 1 /5, 2/5,3/5,4/5, 5/5. now suppose group change then it shold display 1/4 like this
I have already implemented groupwise page numbering using then custom code and i have call that function from the header portion of the report. now the question is how can i display the total no. of pages in particular group?
For that i have add one count column in query itself but as we know we can't use field value in header and footer portion and also we can't use global variables in data portion.
="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()
When I run the report on the report manager, (through the site), the execution time, number of pages everything show properly. But when I print the report, the execution time and total pages is printing huge numbers, which are not in any way related the actual page numbers.
For example: When I run the report it shows the footer like below: Printed by xxxxxxxxx on 3/28/2008 1:59:01 PM Execution Time: 6 Seconds Page 1 of 213 When I print Printed by xxxxxxxxx on 3/28/2008 1:59:01 PM Execution Time: 47 Seconds Page 1 of 483
Does anyone know what is the solution to this problem is? I really appreciate your help,
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters. I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:
SELECT t.gl7accountsid, a.accountnumber, a.description, a.category, t.POSTDATE, t.poststatus, t.TRANSACTIONTYPE, t.AMOUNT, case when t.transactiontype=2 then amount * (-1) else amount end as transamount, t.ENCUMBRANCESTATUS, t.gl7fiscalperiodsid
FROM UrsinusCollege.dbo.gl7accounts a
join ursinuscollege.dbo.gl7transactions t on a.gl7accountsid=t.gl7accountsid
where (t.gl7fiscalperiodsid >= 97 And t.gl7fiscalperiodsid<=@FiscalPeriod_identifier) And poststatus in (2,3) and left(a.accountnumber,5) between '2-110' and '2-999' And right(a.accountnumber,4) > 7149 And not(right(a.accountnumber,4)) in ('7171','7897')
order by a.accountnumber
Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.
Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.
I need to be able to reset the page numbering of a report for each top-level group in a table, and I need to be able to show the total number of pages for each group. For example, if I'm printing invoices and each invoice is a table group, I need to be able to show "Page 1 of 5", Page 2 of 5" and then show "Page 1 of 3", "Page 2 of 3" for the next invoice. I've read the so-called workarounds. They don't work well. They really don't, not when you have more than one user running a report at the same time and not if you display the report and print at the same time. So, please don't refer me to http://blogs.msdn.com/ChrisHays/. What I'm really asking is whether Microsoft is currently working on a resolution or not, or if perhaps this has already been resolved in a new Service Pack or HotFix that I may have missed. People have been asking for this since Reporting Services came out. I'm just wondering what Microsoft is doing about it.
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
I would like to know the total length of data type in a table. I ran the following query. Will this give me the correct information? I also ran sp_columns <table name> and it too give the length. But There is a difference in the numbers. Am I doing something wrong and which is the correct the query or sp_column.
select sum(length) from syscolumns where id in (select id from sysobjects where name = 'XYZABC')
Hello, I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.
I have about 8000 records close to 2000 records for the last 4 years and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.
Here are my field names: [year] ***4 choices 2007, 2006, 2005, 2004*** [name] [rush_no] ***integer*** [rush_net] ***integer*** [YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***
I also need to create the same table that will "total/sum" the same records.
I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.
My report looks something like this :
ID Name Oct Nov Dec Jan Feb March Total 001 alex 87.6% 104.1% 65.1% 50.2% 85.6% 002 Linda 87.4% 109.1% 68.1% 35.2% 90.8% 003 Jon 87.6% 104.1% 004 alex 87.6% 104.1% 65.1% 50.2% 85.6% 005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
For the 002 ID, though he has worked for Nov and Dec the total % is blank.
The formula that I used for all of these entries is :
and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.
It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db. ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is: select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user) I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net. Many thanks
I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.
Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.
Please help me in making such a query.
I am trying and I think that some Date & Time functions will help but still not able to achieve it.
Hi, My application needs to retrieve data from a table which has more than 15 lakh records. The records keep increasing in thousands every 15 days. Is there anyway i can reduce the time to retrieve? basically i have a select statement with a few conditions and a clause for the id's of these records.
I am trying to write a stored procedure that will select information from a SQL table based on a specific time. For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm. Any thoughts?
I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.
I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.
I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.
This is my Access Query; SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight, Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings! Booking_Cost+Room_Facilities!FacilityCost AS TotalCost, [TotalCost]*17.5/100+[TotalCost] AS [Total+VAT] FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;
and this is what I have managed to salvage in MSSQL format: SELECT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Rooms.CostPerNight, Room_Facilities.FacilityCost FROM Rooms INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID) INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)