How do i do a sum(IIF in a group footer. I just want to display one year total.
=SUM(IIF(Fields!Column_Text.Value="2007", Fields!Period_1.Value, 1))
This works in the details section, but gives me an error in the footer section. What should i do? PLease help, this is driving me crazy!
How would I get the rownumber() for a Group Footer in report designer not in my query?
I put the syntax rownumber(nothing), but it is giving me the row numbers for the detail section.
Any help would be appreciated.
I have a tabular report with grouping on the following fields:
Grouped rows: MDC, DrgDesc, ChronicOther
Detail row: Cases
The desired output follows:
MDC1
DrgDesc1
Chronic 50
Other 25
Total cases for DrgDesc1= 75
DrgDesc2
Chronic 20
Other 33
Total cases for DrgDesc2 = 53
etc....
I have everything working up to the Total cases for each DrgDesc. I tried adding a group footer to the Cases row, however this sub-totals for Chronic and Other rather than summing them together. Example is below.
MDC1
DrgDesc1
Chronic 50
50
Other 25
25
DrgDesc2
Chronic 20
20
Other 33
33
etc....
How can I achieve the desired result?
Is there any way to know you're at the last row in a group footer? I'd like to just hide all other rows except that one...
Thanks,
Steve
Hi,
I want to display a concatinated value of strings (DB field) having a "/" in between, for each group separately in the group footer.I tried using custome code.I was calling that code in detail.In detail it was showing correctly i.e running concatenated value for each record.But when i used the same global variable in group footer it's taking previous group's concatenated value and first groop footer shows a blank string.I guess its because groop footer thing is getting executed before the detail part.so can you please give me any solution for this.
Thanks in advance
Suchi
I have a report that is being called via stored proc, and i want to group by contract. when report gets generated i get multiple contracts info. but will be grouped/sorted by contract.
please how can i have a group header and also a group footer to show a summary of each contract information with some calculated fields in it.
i may get 100 records related to 10 contracts , 10 rows for each contract.
as soon as the first contract info is shown on the report it has to show a summary related to the first contract in the group footer, and then continue populating the second contract info and so on.
Please i am totally new to reporting and help would be appreciated. thank you all.
I am new to SSRS 2005 and so far the only way I can figure out how to edit groups in a table is to right click on the group header or footer and select Edit Group. But how would you edit the group if you chose to not have a header or footer? And how would you delete the group for that matter? I'm looking for something like the Crystal Reports Group Expert.
View 4 Replies View RelatedI am using RS 2005 with SP2. I am trying to print a table Group footer on a brand spanking new page. I have checked the "Page Break at Start" of the Group, However in the Report view the footer starts printing directly below the end of the group, and not on a new page. I have also tried to add a rectangle to the group footer and selected Page Breaks before rectangle but the group footer still doesnt print on a page of its own. I would appreciate any suggestions.
I've read a bunch of threads and can't seem to find an exact problem as what I'm having.
I believe my problem is simple enough to understand, but might not be that simple to solve. I'm hoping otherwise.
My report shows columns:
CustomerNumber
CustomerName
SummedInvoiceAmounts (summed from SQL query)
Region
The problem is that we have the same CustomerNumber with multiple CustomerNames:
CustomerNumber CustomerName Sum Region
ABC advancedballoon 1000 East
ABC AdvancedBall. 1000 East
As you can see they are the same company but in our Accounting system they show up mulitple times (about 20 companies like this). This is from clerks adding in multiple ways or misspelling, etc. Can't fix, but have to work around.
Anyhow, I have my detail row that shows two lines for the companies like this and one line for the companies that are correct. I have this report I have added two groups "Region", "CustomerNumber".
If I put all the detail Fields down in the CustomerNumber footer and hide the detail row the report looks fine to the eye. When adding a Sum down in the Region footer field though; it pulls in the added amount and makes the total wrong.
I tried running value, but it's basically the same as the sum for what I need and it doesn't work. Is there a simple way to do this using the reporting tool? In Crystal this was very easy, but I haven't figured this out yet. Any help would be greatly appreciated. Thanks.
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.
I tried entering this:
=SUM(Fields!Period_1.Value,"table2_Period") / Max(Fields!Goal_1.Value)
but i get an error:
[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.
Build complete -- 1 errors, 0 warnings
what am i doing wrong, please help!
I have a report with a table. Inside the table I have a subreport in a group footer and I want to force a page break before the footer.
I've tried putting the subreport inside a rectangle and set the rectangle to have pages breaks but that doesn't work.
any ideas?
Thanks!
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, ...
I ran into this a few weeks ago and found that several others had as well, but no one had yet found an answer. I found the answer (at least one of the answers) this weekend and wanted to share it. I had recently added a subreport to a group footer in a table and started getting an error. Depending on the data, the error wasn't always present. I didn't have a problem when I moved the subreport to any other area in the report.
Here is the error I was getting:
An error occurred during local report processing
An internal error occurred on the report server. See the error log for more details.
It was a local report (rdlc), so naturally, there was no error log <g>. I added an event handler for ReportError and collected this information:
7/23/2006 5:18:22 PM
OS: Microsoft Windows NT 5.1.2600 Service Pack 2
*** EXCEPTION ***
Message: Exception has been thrown by the target of an invocation.
Source: mscorlib
Stack Trace: at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.UserControl.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at SX.MainProgram.Main() in C:Documents and SettingsMYeagerMy DocumentsVisual Studio 2005ProjectsSXSXMainProgram.cs:line 22
*** INNER EXCEPTION ***
Message: Reporting Service Exception
Source: SX
Stack Trace: at SX.ReportSalesForm.reportViewer1_ReportError(Object sender, ReportErrorEventArgs e) in C:Documents and SettingsMYeagerMy DocumentsVisual Studio 2005ProjectsSXSXReportFormsReportSalesForm.cs:line 84
at Microsoft.Reporting.WinForms.ReportViewer.OnError(Exception e)
at Microsoft.Reporting.WinForms.ReportViewer.UpdateUIState(Exception e)
at Microsoft.Reporting.WinForms.ReportViewer.OnRenderingCompleteUI(ProcessThreadResult result, PostRenderArgs postRenderArgs)
*** INNER EXCEPTION ***
Message: An error occurred during local report processing.
Source: Microsoft.ReportViewer.WinForms
Stack Trace: at Microsoft.Reporting.WinForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, CreateAndRegisterStream createStreamCallback, Warning[]& warnings)
at Microsoft.Reporting.WinForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, String& mimeType, String& encoding, String& fileNameExtension, String[]& streams, Warning[]& warnings)
at Microsoft.Reporting.WinForms.ProcessingThread.ProcessThreadMain(Object arg)
*** INNER EXCEPTION ***
Message: An internal error occurred on the report server. See the error log for more details.
Source: Microsoft.ReportViewer.Common
Stack Trace: at Microsoft.ReportingServices.Diagnostics.Utilities.RSTraceInternal.Assert(Boolean condition, String componentName)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteVariant(Object variant, Boolean convertDBNull)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteScopeTableValues(Object value)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteScopeTableValues(Object value)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteScopeLookupTable(ScopeLookupTable scopeTable)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteSubReport(SubReport subReport)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItem(ReportItem reportItem)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItemList(ReportItemList reportItems)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItemCollection(ReportItemCollection reportItems)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteRectangle(Rectangle rectangle)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItem(ReportItem reportItem)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItemList(ReportItemList reportItems)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItemCollection(ReportItemCollection reportItems)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteTableRow(TableRow tableRow)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteTableRowList(TableRowList rows)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteTableGroup(TableGroup tableGroup)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteTable(Table table)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteDataRegion(DataRegion dataRegion)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItem(ReportItem reportItem)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItemList(ReportItemList reportItems)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportItemCollection(ReportItemCollection reportItems)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReport(Report report)
at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.WriteReportSnapshot(ReportSnapshot reportSnapshot)
at Microsoft.ReportingServices.ReportProcessing.ChunkManager.SnapshotChunkManager.SaveReportSnapshot(ReportSnapshot reportSnapshot)
at Microsoft.ReportingServices.ReportProcessing.ProcessingResult.Save()
at Microsoft.Reporting.LocalService.Render(PreviewItemContext itemContext, Boolean allowInternalRenderers, ParameterInfoCollection reportParameters, IEnumerable dataSources, DatasourceCredentialsCollection credentials, CreateAndRegisterStream createStreamCallback, ReportRuntimeSetup runtimeSetup, ProcessingMessageList& warnings)
at Microsoft.Reporting.WinForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, CreateAndRegisterStream createStreamCallback, Warning[]& warnings)
This wasn't exactly enlightening, an exception in an ASSERT in the reporting services dll, but since I knew that results varied with different data, I tracked it down that way.
THE SOLUTION:
The problem occurs when data for group expression is null. If there is no subreport in the group footer, this isn't an issue, but if there is, you get the error. For instance, if your data comes from a SQL statement with a LEFT OUTER JOIN (as mine did), and the group expression uses a column that contains nulls as a result of the LEFT OUTER, this exception will be thrown. This one was VERY DIFFICULT to track down, but it's easily reproducable. It has nothing to do with what (if anything is) on the subreport, what the data the subreport uses looks like, whether there are parameters or not. It's purely having nulls in the data used by the group expression for the main report. I found that if I changed my SELECT from mytable.mycolumn to ISNULL(mytable.mycolumn, 0) the error went away.
Mike Yeager
Hi,
I accidentally unclicked the check-boxes for group-header and group footer - I meant to leave the group header in. Now, I can't get to the group to edit it! Is there any way I can get back to it?
I have a report (bills of lading for shipments) that uses a table to organize the data coming in and groups by customer. Each customer grouping has a header, body and footer in the table. A print run might generate 100 bills of lading, each between 1 and 4 pages long.
I have marked the group to force a new page when each new group occurs.
As it is the report runs beautifully EXCEPT that the footer prints wherever it happens to end up -- midway down the final page of the bill and looks quite goofy.
What I really need (and my client is really surprised that SSRS won't me) is to print the group footer at the bottom of the final page of each bill of lading.
Bills of lading may be several pages per customer, so until the group footer is hit, I don't want any footer info at all. As I said, it all works fine except for the final page of each bill, where the group footer occurs whereever it has ended up.
Jim
12/4/07
This post didn't get much of a response. So perhaps I should amend it -- If what I am trying to do is impossible, what would be the preferred way?
Hi,
Im currently writing a report in SSRS which requires to take data in the fields, manipulate and perform functions on it then display it within its own textbox. For example the field Cost is manipulated so that a column will show the cost depreciated after 5 years.
Cost Final Cost
500 250
1000 634
700 500
Footer: Totals 1384
My problem is that within each group (as the data is grouped by its type) I need to have a sum of the Final Cost data. As this is NOT a field I cannot use =Sum(Fields!FinalCost.Value, "group_1") but rather I need to use =Sum(ReportItems!FinalCost.Value, "group_1"). I know that SSRS does not allow this, but after trying to find an answer going extensively through google and many forums, I am not able to find a solution on how to sum up the Final Cost column.
If anyone has any ideas on how to resolve this issue I will be greatly thankful,
Cheers,
Ben
Hello,
I am having a problem trying to do some conditional formatting on the text color.
This textbox is in the Group Footer so i essentially want change the color to red if the SUM of one field in the group is less than the SUM of another field in the same group.
I am doing this:
=Iif(Sum(Fields!YTDChargeHours)< Sum(Fields!YTDForecast), "Red", "Black")
but i am getting this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜YTDChargeHours€™ contains an error: Operation is not valid due to the current state of the object.
Thanks in advance!
How can I calculate a subtotal for a Report Item? I have a textbox(lets call it "PlusMinus") in the detail section of my table, which is a calculated textbox of two others (lets call them "Budget" and "Spent"). So, PlusMinus = (Budget - Spent). What I would like to do is get a subtotal for PlusMinus. I have tried several ways, using Sum() or RunningValue, even tried to write code, but I can't seem to get it right. Any ideas??
Thanks in advance!
I have a group in my report that contains an empty footer row to get a better visibility. Because the row has a frame it should be hidden if its the last row on the page. How can I manage that?
View 4 Replies View RelatedHi!
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.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
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.
Thanks!
Karen
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.Â
View 4 Replies View RelatedI'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
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.
View 1 Replies View RelatedI have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
Example:
Now:
Code Snippet
Column Column
Section1 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Section2 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Should be:
Code Snippet
Column Column
Section1 -
Section1 Mnemonic
Section1 Mnemonic
Section1 Mnemonic
Section2 -
Section2 Mnemonic
Section2 Mnemonic
Section2 Mnemonic
I have a code like this:
SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
Hi,I was hoping someone may be able to help me with a tricky T-SQLproblem.I need to come up with a SELECT statement that does basically thefollowing:Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from[StageDb].[dbo].tblPersonInfo group by RCRD_REFNOHowever, I need to select ONLY the TOP 1 of each group (i.e. only 1record for each unique RCRD_REFNO). The problem is of course that if Iadd 'top 1' after select, it only brings back 1 record full stop,rather than 1 for each group!Now, I have previously come up with a similar query that DOES do thissuccessfully, but it relies on a criteria (such as a unique identifier)-unfortunately, the nature of the table I'm using for this currentjob means that it actually doesn't have a primary key, as it'ssimply a staging area for raw data, and can even have completelyidentical records in it. I think the only way I'm going to be able todo it is to literally use the 'TOP' command somehow, but am notsure how to adapt the above to implement it...I'd be very gratefulfor any advice.Many thanks
View 6 Replies View Related