I want to make the total the same amount as the 2008 total per period. (everything in yellow is the group 1 (outer group and the red is the inner group. When i tried to get the value for the year 2008 and put it in the total field, i put Last(Fields!PeriodTotal.value), but it doesnt work, because sometimes it gives me the 2007 total, and sometimes it gives me the 2008 total. I dont know why. Any suggestions on how i can get the 2008 value everytime.
I have a sum , which is filtered within its table group, and then i have a fields in the footer. How would i aggregate these too together and use the result in another row within the footer.
[rsInvalidAggregateScope] The Value expression for the textbox €˜textbox129€™ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
electric: id_electric indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_electric money, status bit
Water: id_Water indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_water money, status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year' , Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes' From lodgings_Contract a , electric b , Water c Where a.id_room = b.id_room And a.id_room = c.id_room And b.status = 1 And c.status = 1 Group by Year(day_register)
Is there a way to generate an 'Others' field/item for a Field in the dataset and add it at the bottom of the group which is filtered for TOP N, for that field? For example, I have a table report which has a group on a field called Commodities, and I'm adding a TOP N filter to the group for getting the Top 30 commodities, and I need an Others item appended at the end of the group that gives me the total of all the commodities other than the top 30, and this group in turn has a subgroup that has top 10 Shippers for each commodity and also an Others item for the Shippers other than the top 10. My challenge is to create a row for the Others item of Commodities and calculate the Top N Shipper for this row which has the 'Others' Shipper item. I hope this is not too confusing to understand. Please feel free to ask me any questions to clarify.
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
How can I calculate the combined table size for the following: The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.
INSERT INTO [Table] SELECT '100','1','DD',1000000,'999999000', '2008-01-29','sa','F','2008-01-29','PARTIAL DATA','11502', '101','2008-01-29','ABC1111111','T','2008-01-29','S','11204'
EXECUTE sp_spaceused 'Table'
name | rows |reserved | data |index_size | unused [Table] | 1 |16KB | 8KB | 8KB | 0KB
i couldnot understand i) how the system calculate this 16 KB and 8 KB ? ii) if i changed the VARCHAR(255) to VARCHAR(50) ,will there be difference in size of the table ,if so then how ? iii) how can i see the change in size of table by reducing the size of datatype ?
I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".
In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)
How to calculate the values from a underlying table?
Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select fs_locn, fs_accno, amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1 end ) from accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014' groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
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, ...
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
I need to aggregate a table to three different levels but I need the results in a single table. Here is the sample data
IndicatorName DHBName PHOName Practice PracticeName Numerator Denominator
ABC SAM a PracticeA QW 22500 22.5
BNN SAM b PracticeB SSS 22500 22.5
dddd JONES c PracticeC FFFF 22500 45
ssss Alter d PracticeZ QW 22500 22.5
rrrr Sam a PracticeA FFFF 52500 60
ABC GINI b PracticeA ASDFF 45000 45
BNN Hoe c PracticeD Tahunanui Medical Centre 45000 15
Now I need to group this table first on the dhb level:
Query used SELECT IndicatorName, DHBName,sum( Num),sum( Den) FROM DHBLevel GROUP BY IndicatorName, DHBName
Then group on PHO Level
SELECT IndicatorName, DHBName, phoname,SUM(Num) AS Expr1, SUM(Den) AS Expr2 FROM DHBLevel GROUP BY IndicatorName, DHBName,phoname
Then on Practice Level
SELECT IndicatorName, DHBName, phoname,practicename,SUM(Num) AS Expr1, SUM(Den) AS Expr2 FROM DHBLevel GROUP BY IndicatorName, DHBName,phoname,practicename.
Now I need to see the aggregates in 1 single table only.
I have a table that has Finance transactions in it. I want to find the last time a transaction was put in and the balance that was recorded for that last entry for each account.
I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round. Any suggestions for "All products" ? Thanks Richard
I am creating a report based on a table in SQL Server 2005 Reporting Sevices. I need to display a subreport within the groupheader of a group I inserted to the table. When I try to preview the report I'll get the following error: "An error occured during local report processing. An internal error occurred on the report server."
When I place the subreport outside of the table the report runs. I tested the scenario in an emty table. When I place the report in the details row the subreport is displayed succesfully. If I insert a group to the table and place the subreport in the groupheader the same error occurs.
Is it impossible to use a subreport in a groupheader? If not, does anyone have an idea how to sove this issue?
Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario: Table A: _____________ BidID - Int identity AuctionID - int BiderName - varchar(50) bidAmount - money ______________________ Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly. BidID AuctionID BiderName BidAmount 1 4005 joeblow 100.00 2 4005 janedoe 101.00 3 4005 joeblow 107.00 4 4006 joeblow 100.00 5 4006 janedoe 105.00 6 4006 joeblow 106.00
I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be 3 4005 joeblow 107.00 6 4006 joeblow 106.00 Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right. Thanks in advance for your help.
Hi,I guess my brain stopped but I need help with this. create table #RETURN( uid int, cid int, serviceid int ) insert into #RETURN select 44,75,2 insert into #RETURN select 44,76,1 insert into #RETURN select 44,77,3 insert into #RETURN select 45,78,3I need to query this table that will get me a distinct UID with matching CID ordered by serviceid from lowest to highest.The solution should look likeuid cid44 7645 78Thanks
I have a table, Table1 with 3 columns as follows: colItemKey, colGrouping1, colGrouping2. colItemKey is the primary key. Say colGrouping1 has 4 different types: Grp1A, Grp1B, Grp1C and Grp1D and colGrouping2 has 5 as follows: Grp2A, Grp2B, Grp2C, Grp2D and Grp2E. How do I setup my select so that the result set is as follows:
I have a table called ACTIVATION_CONSUMPTION which is in PRIMARY file group, in order to move this table to new file group [FG_ACTV], I have done the following
1. ALTER DATABASE [MYDB] ADD FILEGROUP [FG_ACTV] (i have not attached a file to this file group)
2. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACTIVATION_CONSUMPTION]') AND name = N'ACTIVATION_CONSUMPTION_PK') ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] DROP CONSTRAINT [ACTIVATION_CONSUMPTION_PK] GO
I have a report with a table and it has 58 rows, in some of the rows i am displaying the data like this
Roth contribution (Heading)
data for the roth contribution.
So depending on the options checked sometimes the heading Roth contribution is at the end of the page and the data is the next page. so how can i group 2 table rows together.
I have tried clicking on the 2 rows and grouping it, but nothing seems to appear in the report..
