Max Degree of Parallelism - clearification

Tracy you stated, "SQL itself will still use all of the processors, but one
query alone will use a maximum of 2 at one time." When the query runs I
understand it's limited to only utilizing 2 CPUs simultaneously, how do
threads play a role in this? Is it one thread per CPU? Or can multiple
threads run on a CPU?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45782C05.8090107@.realsqlguy.com...
> Jaski wrote:
> Setting it to 2 will restrict any given query to using 2 processors
> simultaneously. SQL itself will still use all of the processors, but one
> query alone will use a maximum of 2 at one time.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
On Thu, 07 Dec 2006 20:25:18 GMT, "Jaski" <Jaski@.nospam.com> wrote:

>Tracy you stated, "SQL itself will still use all of the processors, but one
>query alone will use a maximum of 2 at one time." When the query runs I
>understand it's limited to only utilizing 2 CPUs simultaneously, how do
>threads play a role in this? Is it one thread per CPU? Or can multiple
>threads run on a CPU?
Multiple threads can run on a CPU, that's for sure.
It is very confusing, but I think the MAXDOP SQL parameter may control
that, but not the number of physical processors. Or, not.
J.
|||On Fri, 8 Dec 2006 09:34:35 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>A query executing in parallel will not use several threads on the same processor. That would
>decrease performance. SQL Server has one scheduler per processor (core, or logical if HT), and it
>will make sure that the query will use threads over different schedulers (i.e., processors).
I was commonly seeing seven and more SPIDs on a two-processor,
single-core, non-hyper'd system, SQL2K Enterprise.
So, SPID is not quite the same as thread, is it?
I was asking about this back in April and May:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/d961ed7576f6a6f9/8336914737a3e20a?lnk=st&q=group%3Amicrosoft.public .sqlserver.*+author%3Ajxstern&rnum=16&hl=en#833691 4737a3e20a[/url]
J.
|||On Fri, 8 Dec 2006 21:20:36 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Hmm, I was pretty certain that SQL Server would not use several threads per SPID and processor
>(i.e., one spid would get maximum one thread per processor(=scheduler)). Did you see more rows then
>the number of processors (from sp_who or similar) with the same spid number?
Very definitely.
Certainly surprised me!!

>If so, then you can
>ignore my prior post (as I apparently then have misunderstood things).
OK.

>No. SQL Server has a thread pool. Also, SQL Server has a pool of SPIDs. Whenever a SPID need to do
>something, it uses one or more threads from the thread pool. I might be slightly off here with the
>details, so you might want to check out Slava Oks' blog for details.
http://blogs.msdn.com/slavao/
?
Looks good, thanks.
J.

Matrix Report: Need some suggestions please

I have to create a report that will list the room bookings for a certain
hotel in a certain time frame.
Sound easy enough right...
This is what the report should look like, first lets accept that I pass in
parameters for the following:
1. Start Date
2. End Date
3. The hotel I want the the bookings for
Then the report should look like the following:
Static Column
Twin Room 1
Twin Room 2
Single 1
1 Jan 2004
Party Name
Guest Names
Agency
Jones x2
R. Jones
T. Jones
AAA Travel
2 Jan 2004
Party Name
Guest Names
Agency
Twin Room 1
Twin Room 2
Twin Room 3
Single 1
3 Jan 2004
Party Name
Guest Names
Agency
Gates x1
B. Gates
MS
As you can see from above the amount of rooms changes on certain dates
depending on seasons etc. so the columns should be able to change
dynamicaly.
The rooms don't have a unique number - The hotel has listing of room types,
and how many rooms there are ie.
Hotel1,Twin,2
Hotel1,Single,1
Do i need to use some form of cubed data?
Have tables/matrix within tables/matrix?
Use lists, with tables in them?
How would I want my data returned to achieve the desired result?
Some suggestions, ideas would be appreciated.
Thanks in advance.Ok so Outlook Express don't like tables that much here is the table layout
Static Column Twin Room 1 Twin
Room 2 Single 1
1 Jan 2004 Party Name Jones x2
Guest Names R. Jones
T. Jones
Agency AAA Travel
2 Jan 2004 <empty row>
Twin Room 1
Twin Room 2 Twin Room 3 Single 1
3 Jan 2004 MS Party x2
Oracle x1
B. Gates
L. Jones
S. Balmer
MS Travel
Oracle Travel
Hope this is clearer now.
"Reg" <reg@.dsl.za.org> wrote in message
news:uaYE%23ugpEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I have to create a report that will list the room bookings for a certain
> hotel in a certain time frame.
> Sound easy enough right...
> This is what the report should look like, first lets accept that I pass in
> parameters for the following:
> 1. Start Date
> 2. End Date
> 3. The hotel I want the the bookings for
> Then the report should look like the following:
>
> Static Column
> Twin Room 1
> Twin Room 2
> Single 1
> 1 Jan 2004
> Party Name
> Guest Names
>
> Agency
>
>
> Jones x2
> R. Jones
> T. Jones
> AAA Travel
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> 2 Jan 2004
> Party Name
> Guest Names
>
> Agency
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Twin Room 1
> Twin Room 2
> Twin Room 3
> Single 1
> 3 Jan 2004
> Party Name
> Guest Names
>
> Agency
>
>
> Gates x1
> B. Gates
>
> MS
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> As you can see from above the amount of rooms changes on certain dates
> depending on seasons etc. so the columns should be able to change
> dynamicaly.
> The rooms don't have a unique number - The hotel has listing of room
types,
> and how many rooms there are ie.
> Hotel1,Twin,2
> Hotel1,Single,1
> Do i need to use some form of cubed data?
> Have tables/matrix within tables/matrix?
> Use lists, with tables in them?
> How would I want my data returned to achieve the desired result?
> Some suggestions, ideas would be appreciated.
> Thanks in advance.
>

Matrix Report, groups and subtotal error

Please, i need a very urgent help, for this question... tkssss :-)

I need to create a report with dynamic columns. I try use Matrix, but in this time, i dont konw how to calcutate the subtotal e groups in the correct way.

The data for generate the report ( http://xs116.xs.to/xs116/07233/DataReport.JPG )
This is the actual report ( 2 rows groups and 1 columns group ) ok, but how group dates now ? ( http://xs116.xs.to/xs116/07233/ReportInvalid.JPG
The correct report i need( if is possible?) (http://xs116.xs.to/xs116/07233/ReportOK.JPG)
I need group by Filial (idFilial) Dates (Dta) and inside dates the Dispositivos(NomeDispositivo or IdDispositivo) of the date.... and finally total all by group Filial (idFilial)...

Code of my actual report....

Code Snippet

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="INTEGRAReport">
<ConnectionProperties>
<ConnectString>server=111.11.1.111\GPS;Integrated Security=False;User Id=integraapp;Password=asasasas;initial catalog=INTEGRA_DESENV;Persist Security Info=True;Connection Timeout=360</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>637ed8b0-ea9b-4da1-b6b8-6710cf62db84</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1.25in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>1.25in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1.25in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeFilial.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_GroupFilial">
<GroupExpressions>
<GroupExpression>=Fields!IdFilial.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.25in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Dta.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!IdProduto.Value</GroupExpression>
<GroupExpression>=Fields!Dta.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.25in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeDipositivo.Value</Value>
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="NomeProduto">
<rd:DefaultName>NomeProduto</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeProduto.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_NomeProduto">
<GroupExpressions>
<GroupExpression>=Fields!NomeProduto.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.375in</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.375in</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Qtd</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Saldo</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>ConsolidadoEntregasDataSet_RelatorioConsolidadoEntregas</DataSetName>
<Width>6.25in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Filial
/
Dispositivo</Value>
</Textbox>
</ReportItems>
</Corner>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Height>1.125in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.375in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qtd">
<rd:DefaultName>Qtd</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Qtd.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Saldo.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.875in</Height>
</Body>
<PageHeader>
<Height>0.25in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>872304d3-bc47-4430-8d0b-ea8c9d927f42</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="ConsolidadoEntregasDataSet_RelatorioConsolidadoEntregas">
<rd:DataSetInfo>
<rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>
<rd:DataSetName>ConsolidadoEntregasDataSet</rd:DataSetName>
<rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
<rd:TableAdapterName>RelatorioConsolidadoEntregasTableAdapter</rd:TableAdapterName>
<rd:TableName>RelatorioConsolidadoEntregas</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>dbo.RelatorioConsolidadoEntregas</CommandText>
<DataSourceName>INTEGRAReport</DataSourceName>
</Query>
<Fields>
<Field Name="IdFilial">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdFilial</DataField>
</Field>
<Field Name="IdProduto">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdProduto</DataField>
</Field>
<Field Name="NomeProduto">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeProduto</DataField>
</Field>
<Field Name="NomeFilial">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeFilial</DataField>
</Field>
<Field Name="NomeDipositivo">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeDipositivo</DataField>
</Field>
<Field Name="Dta">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Dta</DataField>
</Field>
<Field Name="Qtd">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Qtd</DataField>
</Field>
<Field Name="Saldo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Saldo</DataField>
</Field>
<Field Name="IdDispositivo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdDispositivo</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>11.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

try right clicking the date row group and click subtotal then right click first row group and and click subtotal. By the way, i see 3 row groups and 2 column groups. If this does not work could you show a screenshot of the layout and the result of this fix? Thanks.|||you need to move the date grouping down one

matrix, properties (dialog), groups, row groups, select the date one, click the down button one time|||this may be of use also:
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

Matrix Report - Add Pct of Total Column

How could I add a percentage of total column to a matrix that looks
like this:
Employment Status Owned Rented Total
Full-Time 10 10 20
Part Time 20 20 40
Totals 30 30 60
I want it to look like:
Employment Status Owned Rented Total Pct of Tot
Full-Time 10 10 20 33.3
Part Time 20 20 40 66.7
Totals 30 30 60 100.0
Any help would be appreciated.
Regards,
Tom OlthoffWrite your expression, format the textox using P or P2
<OTSolutions@.shaw.ca> wrote in message
news:1123180864.018293.197390@.g49g2000cwa.googlegroups.com...
> How could I add a percentage of total column to a matrix that looks
> like this:
> Employment Status Owned Rented Total
> Full-Time 10 10 20
> Part Time 20 20 40
> Totals 30 30 60
> I want it to look like:
> Employment Status Owned Rented Total Pct of Tot
> Full-Time 10 10 20 33.3
> Part Time 20 20 40 66.7
> Totals 30 30 60 100.0
> Any help would be appreciated.
> Regards,
> Tom Olthoff
>|||Ok, once I have the expression how do I add the column to the end of
the matrix? There is no Add Column when I right click the last column
on the matrix. Just Add Column Group, which does not do what I need it
to do.
Regards,
Tom

Matrix filter - rendering error - BUG?

I have a matrix-rowgroup with a filter. The filter is a simple
"DataSet.SomeValue = True" and it works just fine most of the time.
However, depending on my query-data there will be situations where filter
will filter out all data, thus leaving zero rows. This 'should' result in an
empty matrix.
But instead i get an error when viewing my report.
RenderingException: "Operation is not valid due to the current state of the
object."
Is this intentional?
Can anyone suggest a work-around?You probably have an expression somewhere that tries to access an item from
the matrix. As the matrix item is not there, you are getting the error. You
should recode your expression with an IIF(Fields!MatrixElement is
nothing,...,...).
I also have a matrix which I am filtering. Sometimes I get no data. When I
have no rows returned, there is just a blank report, no errors like you are
getting. Of course I could use the NoRows property to display an alternative
text when no rows are returned.
HTH
Charles Kangai, MCT, MCDBA
"Kristian Vinther" wrote:
> I have a matrix-rowgroup with a filter. The filter is a simple
> "DataSet.SomeValue = True" and it works just fine most of the time.
> However, depending on my query-data there will be situations where filter
> will filter out all data, thus leaving zero rows. This 'should' result in an
> empty matrix.
> But instead i get an error when viewing my report.
> RenderingException: "Operation is not valid due to the current state of the
> object."
> Is this intentional?
> Can anyone suggest a work-around?

Master-detail subreports and performance.

If I create a master detail-report using subreports, will a separate query
run for each subreport at the time it loads? Lets say I have a report with
500 master rows, will it have to run 500 queries for the subreports before
the first page loads?
I know there is no on-demand subreport load, and from what I read you can't
do a heterogeneos joins between 2 record sets in the same report - so that
the detail recordset would filter under each master row based on an id.
So what is the best way to produce a large master-detail report with
reasonable performance?
Thanks.You are correct, the subreport is executed for each master record. Other
than making sure your database is optimized (correct indexes for example)
there isn't much you can do to speed it up the report running. However, if
the report is only needed for a particular time (for instance, this report
is run daily) you could use snapshots. Read up on that in books online.
The other possibility is to consider drill through. In that case you show
the master tables with a link that when the user wants additional data they
click on the link and use jump to report to pull up the additional data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David W" <spivey@.nospam.post.com> wrote in message
news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
> If I create a master detail-report using subreports, will a separate query
> run for each subreport at the time it loads? Lets say I have a report
> with 500 master rows, will it have to run 500 queries for the subreports
> before the first page loads?
> I know there is no on-demand subreport load, and from what I read you
> can't do a heterogeneos joins between 2 record sets in the same report -
> so that the detail recordset would filter under each master row based on
> an id.
> So what is the best way to produce a large master-detail report with
> reasonable performance?
> Thanks.
>|||Thanks Bruce for confirming my suspicions. I'm definitely not doing a
report that generates over 500 queries to load a page regardless of database
optimizations or snapshot usage.
Well, I ended up doing a SQL join between my master recordset query and my
detail recordset query to produce one mammoth recordset containing all
fields. Then I grouped on the master columns, and displayed the detail
records as expandable sections. Its not efficient by any means but it works
reasonably well, and its only a single query, and it gives me the
master-detail effect.
Any idea when we are going to see the next version of Reporting Services.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23AwSqovlHHA.4552@.TK2MSFTNGP04.phx.gbl...
> You are correct, the subreport is executed for each master record. Other
> than making sure your database is optimized (correct indexes for example)
> there isn't much you can do to speed it up the report running. However, if
> the report is only needed for a particular time (for instance, this report
> is run daily) you could use snapshots. Read up on that in books online.
> The other possibility is to consider drill through. In that case you show
> the master tables with a link that when the user wants additional data
> they click on the link and use jump to report to pull up the additional
> data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "David W" <spivey@.nospam.post.com> wrote in message
> news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
>> If I create a master detail-report using subreports, will a separate
>> query run for each subreport at the time it loads? Lets say I have a
>> report with 500 master rows, will it have to run 500 queries for the
>> subreports before the first page loads?
>> I know there is no on-demand subreport load, and from what I read you
>> can't do a heterogeneos joins between 2 record sets in the same report -
>> so that the detail recordset would filter under each master row based on
>> an id.
>> So what is the best way to produce a large master-detail report with
>> reasonable performance?
>> Thanks.
>|||What you are explaining here is drill down. It is a nice user interface but
not one I do too often because of the amount of data returned.
I would consider looking into using drill through in the future. Users are
very comfortable with it and you get really good performance.
The next release of RS (non-service pack) is with Katmai (next version of
SQL Server). RS is part of SQL Server and releases with both service packs
and major releases for SQL Server. Early on this was not true but now it is
totally in sync with the rest of SQL Server.
http://www.microsoft.com/presspass/press/2007/may07/05-09KatmaiPR.mspx
Right now they are just saying 2008.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David W" <spivey@.nospam.post.com> wrote in message
news:u4QXU2vlHHA.3872@.TK2MSFTNGP04.phx.gbl...
> Thanks Bruce for confirming my suspicions. I'm definitely not doing a
> report that generates over 500 queries to load a page regardless of
> database optimizations or snapshot usage.
> Well, I ended up doing a SQL join between my master recordset query and my
> detail recordset query to produce one mammoth recordset containing all
> fields. Then I grouped on the master columns, and displayed the detail
> records as expandable sections. Its not efficient by any means but it
> works reasonably well, and its only a single query, and it gives me the
> master-detail effect.
> Any idea when we are going to see the next version of Reporting Services.
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23AwSqovlHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> You are correct, the subreport is executed for each master record. Other
>> than making sure your database is optimized (correct indexes for example)
>> there isn't much you can do to speed it up the report running. However,
>> if the report is only needed for a particular time (for instance, this
>> report is run daily) you could use snapshots. Read up on that in books
>> online.
>> The other possibility is to consider drill through. In that case you show
>> the master tables with a link that when the user wants additional data
>> they click on the link and use jump to report to pull up the additional
>> data.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "David W" <spivey@.nospam.post.com> wrote in message
>> news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
>> If I create a master detail-report using subreports, will a separate
>> query run for each subreport at the time it loads? Lets say I have a
>> report with 500 master rows, will it have to run 500 queries for the
>> subreports before the first page loads?
>> I know there is no on-demand subreport load, and from what I read you
>> can't do a heterogeneos joins between 2 record sets in the same report -
>> so that the detail recordset would filter under each master row based on
>> an id.
>> So what is the best way to produce a large master-detail report with
>> reasonable performance?
>> Thanks.
>>
>