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?
Let me first describe my report: I have the following table
Header Group 1 row--There is a textbox that in this row that when toggled makes visible the two below group 1 rows and the detail row. Group 1 row Group 1 row Detail row Group 2 row-- This row has a textbox that can hide the below group 2 row. Group 2 row-- This row contains a subreport.
Currently, I have a parameter that allows the user to conditionally collapse or show the tables' information for printing purposes so the user doesn't have to go through and click on so many plus signs. My problem is I would like to use a parameter to conditionally hide both of the Group 2 rows so that the user cannot view or click on them. The information in Group 2 is extra and not always needed, so I would like to provide the user with a way to hide this information when they go to view the report. However, I imagined this would be easy enough. I went to group2's visibility tab through the table interface and set the expression to "=IIF(Parameter!ShowIndexes.Value, FALSE, TRUE)". However, when I view the report the report is hiding the detail information as well as the Group2 rows.
We have a strange problem. We have a grouping that returns summed values in textboxes (in the group header) and the last column includes a subreport. When we run the report, the textboxes in the group returns the sums, however the subreport displays "No Rows Returned" as that is the parameter value for NoRows. However, we know that there should be some rows in the subreport as when we preview that report using the same parameters, it displays properly.
Just for fun, we created a new group header (using the same grouping level) that included nothing except for that subreport and when we preview the report, it properly shows the subreport values.
What could be wrong here? And thanks for the information.
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.
I am trying to create a report with a sub report in Sql Server 2012 using Report Builder Version 3. I can run the subreport without any problems. I read where using a shared connection can cause this error so both the main report and the subreport use a connection that is embedded in my report.
For testing, I created the subreport without a parameter and added it to the main report. When I ran it that way, the report worked and sub report displayed the data. So I know it can read from the database.It seems to only give me this error when I am trying to tie the two reports together using a parameter.
How do I hide a subreport if the parent report row containing the subreport is a certain value? I am attempting to create a statement with ageing buckets. In the main body of the report I have to display payments and invoices. If the invoice row is blank, I need to hide the row containing the subreport.
I have a report which contains a subreport placed inside a list. when the subreport returns no data. it appears as a blank space in the main report. So i want to suppress the list when the subreport returns no data. Can somebody help me with this?
Contribution 1 is Netsales-zzzz-yyyy. in my cube i use customrollupcolum. so that in pivot table this condition is no problem. but in the RS is problem.
so, in RS i use 2 report. one report as master define group1 (NetSales, Contibution 1). and the other report as detail of master report. so i use subreport in the table in master report. but the parameter in the sub report cannot handle value parameter from table (netsales,contibution 1).
I have constructed my main report using the table item from the toolbox. In maintable I have two groupings.
In the maintable I have grouping2 set for a page break after each grouping.
I have set the subreport parameter to the field value of the maintable grouping2.
I want to display the contents of the subreport after grouping1 of the maintable on the same page.
I have achieved a result by placing the subreport in maintable_group2 footer. However when I save it as an excel sheet it says "Subreports within table/matrix cells are ignored".
I really don't like how the report displays like this in the report server however I will need to export this report to excel.
I hope hope you can help me because I am sort of helpless here. I searched the web for this issue ("reporting services subreport overflow to new page no table header in main report"), however could not seem to be lucky enogh to find a result. The search string pretty much sums it up: A table cell of my report contains a sub-report that displays multiple lines of data itself. If the data output of the sub-report is long enough to overflow to the next page, the table header of the main report table is not displayed on the new page.
Some background info: The data table of this report has three data rows per record, the first of which contains a cell containing an employee's name and some more merged cells containing the sub-report in question. The header row of the main report data table has the attribute "RepeatOnNewPage" set to true and the data table itself has the attribute "RepeatHeaderOnNewPage" also set to true. If it matters, the SQL Server version is the one needed to run MS CRM 3.0 - 2000 it was, IIRC. My software design environment ist Visual Studio 2003 with the Reporting Services addon.
So, if anyone of you knows, what do I have to do to display the table header even on the pages a sub-report overflows to?
I very much look forward to your answers or helpful links.
My problem is about the rendering behaivor of large subreports (more than 1 page) In that cases the subreport begins in a second page (a new page), it skips the end of the first page and starts on the top of the next page, leaving the first page with blank space. Another efect of this behaivor is that the parent table's header row will not print to the next pages even it the RepeatOnNewPage is set to true.
I saw many posts related to this (I list them at the end) and only one answer from the Microsoft team (in the post of the user Luca B. - sub-reports don't start in the first page http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=18227&SiteID=1). In resume, the answer is that there are an implicit keep toghether property and that this behaivor will be reviwed in the fucture
I am using SQL Server 2005 with SP2 and the "behaivor" continues.
Someone of the Microsoft team can tell us if this behaivor will be changed and when?
I find the folowing posts reletade to the problem:
Post title - Author- URL
sub-reports don't start in the first page - Luca B. - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=18227&SiteID=1
Can't get a Subreport to start on the correct page or table header to repeat on multiple pages - unit107 - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=423599&SiteID=1
Subreport not printing or exporting properly - EJJ -http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=803828&SiteID=1
Sub Reports Issue - Leo12 - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033532&SiteID=1
Sub report ocassionally forcing page break when printing - Tom Majarov - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244333&SiteID=1
Headers on new pages using Lists and Subreports - Charleh - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=93675&SiteID=1
Subreports and page breaks - dsavill - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=355655&SiteID=1
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.
i have a report that contains a sub-report that is embeded in one of the textboxes of the table. what i am trying to do is have the main table toggle visilibity based on the value of the sub-report...in my case the toggle is very simple - if the sub-report returns any value at all, then i need the row in the parent table to be 'visible'
I am staring to lose my mind on all this "work-around" BS in trying to create a dynamic header.
I have a report with contains a table, whose first two lines are a header line in which I want to repeat on each page. Then I have about 20 detail rows of information related to the header. The 21st detail row is a merged cell that contains a subreport relating to the header item.
When printing to PDF, the detail rows consume about half the page and the subreport is anywere from half a page to several pages long. When the subreport is more than half a page, it skips the end of the first page and starts on the top of the next page, leaving the bottom half of the first page blank. No matter what I set the page breaking to, it continues this behavior. In addition, when the subreport data spans more than one page, my parent table's header row will not print to the next page even it the RepeatOnNewPage is set to true.
I am coding this way because I have found no good way of creating a dynamic header that will correctly and consistantly display data that has detail that spans more than one page.
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 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.
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..
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId FROM Position INNER JOIN [Month] m INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId) ON m.Id = Position.MonthId GROUP BY TeamMemberId
Returns TeamMemberId MonthId 1 14 6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName FROM Position INNER JOIN [Month] m INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2) ON m.Id = Position.MonthId GROUP BY TeamMemberId, PositionName
Returns TeamMemberId MonthId PositionName 1 2 ***. d. Geschäftsführung 6 20 ***. d. Geschäftsführung 1 14 CEO 6 16 CEO
The red rows are the ones I wanna get. How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns? Any help is much appreciated.
