Max database size on SQL-SERVER7

Hello

Does anyone one know the max size of a database that I can create on
MS-SQL server 7. Have had any problems using databases near the max
size (slow performance, etc)

Thanks
Helenahmatijaca@.yahoo.ca (Helena) wrote in message news:<c5a36d06.0310080727.2468ecd5@.posting.google.com>...
> Hello
> Does anyone one know the max size of a database that I can create on
> MS-SQL server 7. Have had any problems using databases near the max
> size (slow performance, etc)
>
> Thanks
> Helena

Hi Helena,

According to Books Online, it's 1,048,516 TB which is definitely a big DB.
Yes definitely for DBs os this size, the hardware needs would change.

Regards,
-Manoj|||"Helena" <hmatijaca@.yahoo.ca> wrote in message
news:c5a36d06.0310080727.2468ecd5@.posting.google.c om...
> Hello
> Does anyone one know the max size of a database that I can create on
> MS-SQL server 7. Have had any problems using databases near the max
> size (slow performance, etc)

Backups are generally one of the biggest problems I've seen with VLDBs.

You have to have a backup/restore strategy that works for your business
rules.

If it takes 24 hours to restore your database and business rules require you
to back it up in less than 6, you'll have to change your infrastructure.

Generally performance comes down a LOT to design. Proper hardware can make
a huge difference, but even with extremely fast hardware, if you have a
lousy design and end up doing a table scan of a multigig DB, your
performance will suck.

On the other hand, if you have mediocre hardware, but you can do index
lookups for every query (as opposed to a table scan) you'll do a lot better.

>
> Thanks
> Helenasql

Matrix with Percentages

Is there any way to create a matrix table like this? I can't figure out how
to do percentages.
Rank Number %
Professor 48 49
Associate Professor 25 26
Assistant Professor 24 25
Total 97 100
Thanks!
Danhi Dan
I have a table in my report and have the following as an expression.
=FormatPercent(Fields!SLAResponseCompliant.Value/Fields!ClosedTickets.Value)
not sure how it will go with a matrix table but if you use something
similar? or it may at least give you something to work with.
Jewel
"Dan" wrote:
> Is there any way to create a matrix table like this? I can't figure out how
> to do percentages.
> Rank Number %
> Professor 48 49
> Associate Professor 25 26
> Assistant Professor 24 25
> Total 97 100
> Thanks!
> Dan
>sql

Matrix Subtotals/Cells

I've seen in a fex posts on this forum that it's at this moment not yet possible to create a dynamic matrix where for each cell i can calculate his row-percentage, his column-percentage. His total percentage can be done.

A way i could still do this is to create another matrix that only holds the totals, but how can i reference a cell-value of a matrix from another field ? (If this can be done).

KG.

Harry.

Another approch i'm trying is make some hardcoded fields for the total of each row and column. In this case it is possible as i know that there will always be 7 rows (1week) and 5 performances ...

When referencing a fields as =Sum(Fields("DW_" + CStr(Fields!CompD_DayOfFilmWeek.Value)).Value, "SdwOlap") i'm getting it right.

But when i place tis formula in a cell of a matrix i'm getting in every column the grand total and not the total for that row, although, in the row below i entered the formula ="DW_" + CStr(Fields!CompD_DayOfFilmWeek.Value), and there i'm nicely seeing DW_1 to DW_7 depending on the right row.

Strange behaviour that i can't explain .....

|||

In the grouping scope parameter of the aggregate, you are specifying a value, "SdwOlap". What is this refering to? Is this the name of the dataset or matrix? If it is, then this explains why the totals are actually grand totals and not just the totals for the row. The aggregate is aggregating the data of this field for the entire dataset. Try setting this to the scope of the row grouping, or omit it altogether.

Ian

sql

matrix row & column subtotals

The "Adding Subtotals" section in MSDN has me totally confused, I'm not
seeing a Subtotal menu. I need to create a simple cross tab table but
I'm not having any luck.
_____________________________________________
| "TITLE" | Column Name | "TOTAL" |
_____________________________________________
| Row Name | data | row total |
_____________________________________________
| "TOTAL" | column total | matrix total |
_____________________________________________Right click the Column Name cell and choose subtotals, to get column totals.
Right click the Row Name cell and choose subtotals, to get row totals.
"Harolds" wrote:
> Right click the Column Name cell and choose subtotals.
> "tomk10" wrote:
> > The "Adding Subtotals" section in MSDN has me totally confused, I'm not
> > seeing a Subtotal menu. I need to create a simple cross tab table but
> > I'm not having any luck.
> >
> > _____________________________________________
> > | "TITLE" | Column Name | "TOTAL" |
> > _____________________________________________
> > | Row Name | data | row total |
> > _____________________________________________
> > | "TOTAL" | column total | matrix total |
> > _____________________________________________
> >
> >|||Right click the Column Name cell and choose subtotals.
"tomk10" wrote:
> The "Adding Subtotals" section in MSDN has me totally confused, I'm not
> seeing a Subtotal menu. I need to create a simple cross tab table but
> I'm not having any luck.
> _____________________________________________
> | "TITLE" | Column Name | "TOTAL" |
> _____________________________________________
> | Row Name | data | row total |
> _____________________________________________
> | "TOTAL" | column total | matrix total |
> _____________________________________________
>sql

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 or Table?

I am trying to create a report that should have fixed columns. I
started out creating a matrix, but was having loads of trouble keeping
the columns I wanted. Here is what it should look like:
Total <0 days 1 day 2 days 3 days 4 days 5-10
days 11-15 days 16-30 days
------
Type1 | x x x x
x x x x x
Type2 | x x x x
x x x x x
Type3 | x x x x
x x x x x
------
Total | xx
Basically, I need to group the columns into the date groupings shown
above (<0 days, 1 day, etc) and have counts for each Type (Type1,
Type2) along with a grand total.
Would it be better to use a table for this or a matrix? Also, how
should I get the data grouped correctly in the most efficient manner
so the counts are sorted according to the Type and date gorups they
fall in (stored proc., views, etc.)? Any clues or tips would be
helpful.
Thanks in advance for your help.In general, data processing (grouping, aggregating, etc.) is done more
effectively in SQL as opposed to SSRS. My goal is usually to do all
the processing in SQL then simply drag and drop fields from a dataset
into a report's data region. I almost always try to use stored procs
to generate datasets. It seems to me that creating and using a view
adds unecessary steps.
In your case, I would use a stored proc to aggregate the counts
according to the date ranges and group things by Type. That should
give you a result set roughly identical to your report output. It
should be simple to then move the data into a table in your report.
If you are stuck with a preset dataset, I think a matrix would work
better for your situation since you can group on both your X and Y
axis.
HTH, good luck
toolman
Just Another Reporter wrote:
> I am trying to create a report that should have fixed columns. I
> started out creating a matrix, but was having loads of trouble keeping
> the columns I wanted. Here is what it should look like:
> Total <0 days 1 day 2 days 3 days 4 days 5-10
> days 11-15 days 16-30 days
> ------
> Type1 | x x x x
> x x x x x
> Type2 | x x x x
> x x x x x
> Type3 | x x x x
> x x x x x
> ------
> Total | xx
> Basically, I need to group the columns into the date groupings shown
> above (<0 days, 1 day, etc) and have counts for each Type (Type1,
> Type2) along with a grand total.
> Would it be better to use a table for this or a matrix? Also, how
> should I get the data grouped correctly in the most efficient manner
> so the counts are sorted according to the Type and date gorups they
> fall in (stored proc., views, etc.)? Any clues or tips would be
> helpful.
> Thanks in advance for your help.|||In general, data processing (grouping, aggregating, etc.) is done more
effectively in SQL as opposed to SSRS. My goal is usually to do all
the processing in SQL then simply drag and drop fields from a dataset
into a report's data region. I almost always try to use stored procs
to generate datasets. It seems to me that creating and using a view
adds unecessary steps.
In your case, I would use a stored proc to aggregate the counts
according to the date ranges and group things by Type. That should
give you a result set roughly identical to your report output. It
should be simple to then move the data into a table in your report.
If you are stuck with a preset dataset, I think a matrix would work
better for your situation since you can group on both your X and Y
axis.
HTH, good luck
toolman
Just Another Reporter wrote:
> I am trying to create a report that should have fixed columns. I
> started out creating a matrix, but was having loads of trouble keeping
> the columns I wanted. Here is what it should look like:
> Total <0 days 1 day 2 days 3 days 4 days 5-10
> days 11-15 days 16-30 days
> ------
> Type1 | x x x x
> x x x x x
> Type2 | x x x x
> x x x x x
> Type3 | x x x x
> x x x x x
> ------
> Total | xx
> Basically, I need to group the columns into the date groupings shown
> above (<0 days, 1 day, etc) and have counts for each Type (Type1,
> Type2) along with a grand total.
> Would it be better to use a table for this or a matrix? Also, how
> should I get the data grouped correctly in the most efficient manner
> so the counts are sorted according to the Type and date gorups they
> fall in (stored proc., views, etc.)? Any clues or tips would be
> helpful.
> Thanks in advance for your help.|||In general, data processing (grouping, aggregating, etc.) is done more
effectively in SQL as opposed to SSRS. My goal is usually to do all
the processing in SQL then simply drag and drop fields from a dataset
into a report's data region. I almost always try to use stored procs
to generate datasets. It seems to me that creating and using a view
adds unecessary steps.
In your case, I would use a stored proc to aggregate the counts
according to the date ranges and group things by Type. That should
give you a result set roughly identical to your report output. It
should be simple to then move the data into a table in your report.
If you are stuck with a preset dataset, I think a matrix would work
better for your situation since you can group on both your X and Y
axis.
HTH, good luck
toolman
Just Another Reporter wrote:
> I am trying to create a report that should have fixed columns. I
> started out creating a matrix, but was having loads of trouble keeping
> the columns I wanted. Here is what it should look like:
> Total <0 days 1 day 2 days 3 days 4 days 5-10
> days 11-15 days 16-30 days
> ------
> Type1 | x x x x
> x x x x x
> Type2 | x x x x
> x x x x x
> Type3 | x x x x
> x x x x x
> ------
> Total | xx
> Basically, I need to group the columns into the date groupings shown
> above (<0 days, 1 day, etc) and have counts for each Type (Type1,
> Type2) along with a grand total.
> Would it be better to use a table for this or a matrix? Also, how
> should I get the data grouped correctly in the most efficient manner
> so the counts are sorted according to the Type and date gorups they
> fall in (stored proc., views, etc.)? Any clues or tips would be
> helpful.
> Thanks in advance for your help.|||If the date ranges that form the column headers are at all dynamic, I'd use a
matrix report. Create a table in your db that contains the possible column
header values, and other cols that define what each header means. E.g.:
Range Min Days Max Days
<0 null 0
1 day 0 1
2 days 1 2
...
16-30 days 16 30
Create a query that joins whatever col contains the date (or number of days)
you are grouping on to the min and max days col (make it a cartesian product
if you want to include all possible ranges defined above). Then use the
'range' field from the query as the "across" grouping.

Matrix Like Table

Hi Friends,

I am working on a sport site, where i have to create a Calender Likething where i have to show which sport is avaible in which month,we can have any numbers of sports as the club grow it can add 100s ofdifferent sport to itself, so i was not able to understand how to makea database for this kind of thing, can anyone please help

Games - Jan Feb Mar Apr May Jun Jul …..Dec
Cricket Yes Yes Yes No No No No ....
Hockey No Yes Yes No No Yes Yes ....
Horse R Yes No No No Yes Yes Yes ....

Thanks in Advance
Regards
Viking

Hi
you can try this structure...

Tbl_Sport
Sports_ID(Unique Key , Auto Incremented)
MonthsChar(10)
SportsVarChar(30)
AvailbilityBinary

Matrix custom subtotals ? How is it possible ?

Hi ALL,
I have a problem and i becoming crazy !
I create a report like this using a Matrix in Reporting Services
Sales
Area 1 250
Customer 1 100
Customer 2 150
Area 2 60
Customer 3 40
Customer 4 20
I need to add a total row witch is the sum of AREA 1 + AREA 2 lke this
IMPORTANT : rows in matrix becomes directly from my query... they are
not grouping.
Sales
Area 1 250
Customer 1 100
Customer 2 150
Area 2 60
Customer 3 40
Customer 4 20
TOTAL 310
How can I express the contition... If I add a total to the matrix my
total is 620 (wrong !...)
Thank you !
Gas1970On Jun 13, 11:16 am, alberto.gasta...@.libero.it wrote:
> Hi ALL,
> I have a problem and i becoming crazy !
> I create a report like this using a Matrix in Reporting Services
> Sales
> Area 1 250
> Customer 1 100
> Customer 2 150
> Area 2 60
> Customer 3 40
> Customer 4 20
> I need to add a total row witch is the sum of AREA 1 + AREA 2 lke this
> IMPORTANT : rows in matrix becomes directly from my query... they are
> not grouping.
> Sales
> Area 1 250
> Customer 1 100
> Customer 2 150
> Area 2 60
> Customer 3 40
> Customer 4 20
> TOTAL 310
> How can I express the contition... If I add a total to the matrix my
> total is 620 (wrong !...)
> Thank you !
> Gas1970
Traditionally, in some cases, I obtain my sums/totals in the stored
procedure/query that is sourcing my matrix report. This should be a
good option in your case as well. Also, depending on the complexity of
the matrix report, you may need to use a while loop/cursor to
determine the sum/totals. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Matrix Cross-tab Report Not Counting Records

I am new to Reporting Services and cross-tab reporting in general. I
need to create a basic report that has dates across the top and reps
down the left. The data I would like to see reflected is the number of
entries created in the table by rep by day with cross sums. What I am
getting is a report that has a column for every entry with the date
repeated over and over and no totals, only 1 in each column.
Visually I need to see:
Day 1 Day 2 Day 3 Total
__________________________________
Rep1 4 10 1 15
Rep2 2 4 3 9
Total 6 14 4 24
What I am getting is a report where Day 1 would be repeated 6 times
with an entry of 1.
Can someone point me in the right direction?I would like to add that my table uses a datetime field that has a
unique value for each record. I do need a specific timestamp.
Probably part of my problem, but how do I resolve it?
zanecolvin@.gmail.com wrote:
> I am new to Reporting Services and cross-tab reporting in general. I
> need to create a basic report that has dates across the top and reps
> down the left. The data I would like to see reflected is the number of
> entries created in the table by rep by day with cross sums. What I am
> getting is a report that has a column for every entry with the date
> repeated over and over and no totals, only 1 in each column.
> Visually I need to see:
> Day 1 Day 2 Day 3 Total
> __________________________________
> Rep1 4 10 1 15
> Rep2 2 4 3 9
> Total 6 14 4 24
> What I am getting is a report where Day 1 would be repeated 6 times
> with an entry of 1.
> Can someone point me in the right direction?|||Solved my own problem. Created a view converting data type from and
back to datetime to get 12:00 time on all entries. Used that for the
report.
zanecolvin@.gmail.com wrote:
> I would like to add that my table uses a datetime field that has a
> unique value for each record. I do need a specific timestamp.
> Probably part of my problem, but how do I resolve it?
>

Matrix columns - Conditional Formatting - Column Number

Hi,
I am trying to create a Matrix with each dynamically created column having a
differnet background colour. Any ideas on how to achieve this ?
If I could access the column number some how I could just apply conditional
formatting based on the column number, but I cant seem to find out how to get
at the column number.How are you creating the columns dynamically?
Do you know what is your maximum # or columns?|||1. I am not - I Just drag the field to the first column heading in the
matrix and reporting services does the dynamic bit.
2. No, it will be different on each implementation, but generally less than
10 - so I am happy to put a sensible limit on it and have the colours wrap
around after a set number of columns if the limit is exceeded - or base the
colour on some formula.
As an example :
Centre1 Centre2 ..... Centre N
Males x x ..... x
Females x x ..... x
...
...
more stats
I want the column for centre1 in blue, centre2 in yellow... and so on.
Each implementaion will have different centre names and centre ids, so I
dont want to tie the formatting into a particluar value in the data. One
solution would be to add a "rank" field to my data set and then use this
field in the conditional formatting, but I dont really want to add this
complexity to my data set and feel there must be an easier way than this.
"sorcerdon@.gmail.com" wrote:
> How are you creating the columns dynamically?
> Do you know what is your maximum # or columns?
>|||Best way to achieve it is to find some pattern in ur existing cloumns
but i m pretty sure you may have already done it. So cant you create a
custom column from your query having nothing but simply an integer
containing column no? It should give you the column no u want in the
matrix|||Yes this is one way to achieve it. This is what I was suggesting when I said
I could create a "rank" column.
I just think there should be a better way of doing this than having to amend
the data set. Adding a "column no" column to the dataset may not always be
straightforward and may add unnecessary complexity to the query.
I guess what I am asking is if there is a way of retrieving the column no
from the matrix and whether changing the dataset is the only solution.
"Techotsav" wrote:
> Best way to achieve it is to find some pattern in ur existing cloumns
> but i m pretty sure you may have already done it. So cant you create a
> custom column from your query having nothing but simply an integer
> containing column no? It should give you the column no u want in the
> matrix
>|||Hi Techotsav,
I am afraid it is not possible to do this easily like retrieve the column
no from the matrix and whether changing the dataset is the only solution.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix Calculation - New to RS2000

Hello!
Iâ'm hoping somebody out there can help me! I am a new user of SQL Reporting
Services 2000 and have been asked to create a report showing studentsâ'
attendance. After a lot of reading and research I created a matrix report
that looks like this;
=Fields!attendance_type.value Total
=Fields!student_id.value
=Fields!week_no.value =Fields!register_id.Value =count(Fields!attendance_type.value)
It gives me the following results;
PR AA AB Total
0003 7 ACCA2 4 0 0 4
BUSA2 4 0 0 4
LANA2 2 2 0 4
8 ACCA2 2 2 0 4
BUSA2 2 2 0 4
LANA2 2 0 0 2
9 ACCA2 4 0 0 4
BUSA2 2 0 2 4
LANA2 3 0 0 3
10 ACCA2 4 0 0 4
BUSA2 4 0 0 4
LANA2 3 0 0 3
I would now like to add another column next to Total (subtotal of
=Fields!attendance_type.value) to calculate the % Attendance by using
PR/Total*100.
So in the end, I want something like this;
PR AA AB Total % Attendance
0003 7 ACCA2 4 0 0 4 100
BUSA2 4 0 0 4 100
LANA2 2 2 0 4 50
8 ACCA2 2 2 0 4 50
BUSA2 2 2 0 4 50
LANA2 2 0 0 2 100
Your help would be much appreciatedâ?¦
Wilma KhaliqYou can do a second query and append it to this one with "union", like
select blah blah from blah where blah
union
select blah2 blah2 from blah2 where blah2
except in the bottom select you can do your total, making a new type called
'TOTAL'
--
"Everyone knows something you don't know"
"Whee4ever" wrote:
> Hello!
> Iâ'm hoping somebody out there can help me! I am a new user of SQL Reporting
> Services 2000 and have been asked to create a report showing studentsâ'
> attendance. After a lot of reading and research I created a matrix report
> that looks like this;
> =Fields!attendance_type.value Total
> =Fields!student_id.value
> =Fields!week_no.value =Fields!register_id.Value =count(Fields!attendance_type.value)
> It gives me the following results;
> PR AA AB Total
> 0003 7 ACCA2 4 0 0 4
> BUSA2 4 0 0 4
> LANA2 2 2 0 4
> 8 ACCA2 2 2 0 4
> BUSA2 2 2 0 4
> LANA2 2 0 0 2
> 9 ACCA2 4 0 0 4
> BUSA2 2 0 2 4
> LANA2 3 0 0 3
> 10 ACCA2 4 0 0 4
> BUSA2 4 0 0 4
> LANA2 3 0 0 3
> I would now like to add another column next to Total (subtotal of
> =Fields!attendance_type.value) to calculate the % Attendance by using
> PR/Total*100.
> So in the end, I want something like this;
> PR AA AB Total % Attendance
> 0003 7 ACCA2 4 0 0 4 100
> BUSA2 4 0 0 4 100
> LANA2 2 2 0 4 50
> 8 ACCA2 2 2 0 4 50
> BUSA2 2 2 0 4 50
> LANA2 2 0 0 2 100
> Your help would be much appreciatedâ?¦
> Wilma Khaliq

Matrix and Jump to Report

Hello,
i create a matrix with this layout
A Students B Students C Students (...) Total
Teacher A 2 6 4 12
Teacher B 4 3 5 12
Teacher C 1 3 8 12
(...)
each count has a link to another report who shows the students width the
specific grade. How i do to make subtotal jump to another report ? Or how i
disable the hiperlink on subtotal ?
regards
--
[... JFR .. ]Assuming the column grouping is called "StudentsGroup" and the row grouping
is called "TeacherGroup", you can use an expression in the textbox action of
the cell:
=iif( InScope("TeacherGroup"), iif(InScope("StudentsGroup"), "... your
existing hyperlink ...", "... hyperlink for the student subtotal cell ..."),
"... hyperlink for teacher subtotal cell ...")
If you don't want a hyperlink to be shown, use the keyword Nothing instead
of the hyperlink string. The MSDN documentation on the functions above can
be found here:
* IIF: http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
* InScope:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
"JFR" <JFR@.discussions.microsoft.com> wrote in message
news:DD290BA9-ECB5-442F-B30F-CF69865D20F3@.microsoft.com...
> Hello,
> i create a matrix with this layout
> A Students B Students C Students (...) Total
> Teacher A 2 6 4 12
> Teacher B 4 3 5 12
> Teacher C 1 3 8 12
> (...)
> each count has a link to another report who shows the students width the
> specific grade. How i do to make subtotal jump to another report ? Or how
i
> disable the hiperlink on subtotal ?
> regards
> --
> [... JFR .. ]|||Thanks, it works just fine.
"Robert Bruckner [MSFT]" wrote:
> Assuming the column grouping is called "StudentsGroup" and the row grouping
> is called "TeacherGroup", you can use an expression in the textbox action of
> the cell:
> =iif( InScope("TeacherGroup"), iif(InScope("StudentsGroup"), "... your
> existing hyperlink ...", "... hyperlink for the student subtotal cell ..."),
> "... hyperlink for teacher subtotal cell ...")
> If you don't want a hyperlink to be shown, use the keyword Nothing instead
> of the hyperlink string. The MSDN documentation on the functions above can
> be found here:
> * IIF: http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
> * InScope:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JFR" <JFR@.discussions.microsoft.com> wrote in message
> news:DD290BA9-ECB5-442F-B30F-CF69865D20F3@.microsoft.com...
> > Hello,
> >
> > i create a matrix with this layout
> >
> > A Students B Students C Students (...) Total
> > Teacher A 2 6 4 12
> > Teacher B 4 3 5 12
> > Teacher C 1 3 8 12
> > (...)
> >
> > each count has a link to another report who shows the students width the
> > specific grade. How i do to make subtotal jump to another report ? Or how
> i
> > disable the hiperlink on subtotal ?
> >
> > regards
> >
> > --
> > [... JFR .. ]
>
>

Matrix and date colum header

Hi all,
I am trying to create a "simple" matrix.
I have:
--
| DateRange |
---
| Department | sum(value) |
---
I am giving the matrix an sql statement that returns all the values
and dates for a particular department on a YEARLY output.
So what I'm trying to do is have it so I get something like:
2006-2007 2005-2006
Department A 123 112
Department B 455 333
Department C 334 444
Department D 123 333
Department E 444 232
ie, have it sum the values on a year interval. I could do this easily
in Crystal, but am unsure as to how to do it in Reporting Services.
At present Im getting a column header for each date value resturned in
my SQL - which is not what I want!
eg:
01/01/2007 01/03/2007 04/03/2006
A 123 333
B 222
C 1222
How do I group the column header by year'
Any info would be much appreciated!!!!You can have year(date) but it gives you the year, so what you can do is use
a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
and use it in the matrix.
Amarnath
"David Conte" wrote:
> Hi all,
> I am trying to create a "simple" matrix.
> I have:
> --
> | DateRange |
> ---
> | Department | sum(value) |
> ---
> I am giving the matrix an sql statement that returns all the values
> and dates for a particular department on a YEARLY output.
> So what I'm trying to do is have it so I get something like:
>
> 2006-2007 2005-2006
> Department A 123 112
> Department B 455 333
> Department C 334 444
> Department D 123 333
> Department E 444 232
> ie, have it sum the values on a year interval. I could do this easily
> in Crystal, but am unsure as to how to do it in Reporting Services.
> At present Im getting a column header for each date value resturned in
> my SQL - which is not what I want!
> eg:
> 01/01/2007 01/03/2007 04/03/2006
> A 123 333
> B 222
> C 1222
> How do I group the column header by year'
> Any info would be much appreciated!!!!
>|||On Feb 5, 8:21 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> You can have year(date) but it gives you the year, so what you can do is use
> a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
> and use it in the matrix.
> Amarnath
>
Hi Amarnath,
Thanks for the reply. However I'm not sure how to tackle this by
using a case statement?
ie, not sure how to set it up to get it to seperate 2006-2007 etc?
Here is my sql so far:
SELECT MedicareItemKey, TestDepartments.Code as Department,
TransactionAmount, DateServiced
FROM InvoiceTransactions
INNER JOIN TestDepartments ON InvoiceTransactions.DepartmentKey =TestDepartments.[Key]
WHERE MedicareItemKey IS NOT NULL
AND ((InvoiceTransactions.[DateServiced] >= '01-
Jan-'+convert(char,year(getdate())-0))
AND (InvoiceTransactions.[DateServiced] < '01-
Jan-'+convert(char,year(getdate())+1)))
ORDER BY TestDepartments.Code, MedicareItemKey, DateServiced
As you can see above, the DateServiced column is the one I am having
issues with in my matrix.
Thanks!|||David,
I have create a small table with data
" create table INV (dept varchar(10),Amt numeric(10,2), dDate datetime)"
and tried this sql, according to your conditions you can modify.
SELECT Dept, SUM(Amt) As Amt,
(case when year(Inv.dDate) between 2004 and 2005 then '2004-2005'
when year(Inv.dDate) between 2005 and 2006 then '2005-2006'
when year(Inv.dDate) between 2006 and 2007 then '2006-2007'
end) as Dyear
FROM Inv
Group By Dept, ddate
order by Dept
Just open a report and place the matrix and drag and drop in the respective
column, you will get your desired results.
Let me know for any problems.
Amarnath
"David Conte" wrote:
> On Feb 5, 8:21 pm, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > You can have year(date) but it gives you the year, so what you can do is use
> > a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
> > and use it in the matrix.
> >
> > Amarnath
> >
> Hi Amarnath,
> Thanks for the reply. However I'm not sure how to tackle this by
> using a case statement?
> ie, not sure how to set it up to get it to seperate 2006-2007 etc?
> Here is my sql so far:
> SELECT MedicareItemKey, TestDepartments.Code as Department,
> TransactionAmount, DateServiced
> FROM InvoiceTransactions
> INNER JOIN TestDepartments ON InvoiceTransactions.DepartmentKey => TestDepartments.[Key]
> WHERE MedicareItemKey IS NOT NULL
> AND ((InvoiceTransactions.[DateServiced] >= '01-
> Jan-'+convert(char,year(getdate())-0))
> AND (InvoiceTransactions.[DateServiced] < '01-
> Jan-'+convert(char,year(getdate())+1)))
> ORDER BY TestDepartments.Code, MedicareItemKey, DateServiced
> As you can see above, the DateServiced column is the one I am having
> issues with in my matrix.
> Thanks!
>|||On Feb 6, 5:14 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> David,
> I have create a small table with data
> " create table INV (dept varchar(10),Amt numeric(10,2), dDate datetime)"
> and tried this sql, according to your conditions you can modify.
> SELECT Dept, SUM(Amt) As Amt,
> (case when year(Inv.dDate) between 2004 and 2005 then '2004-2005'
> when year(Inv.dDate) between 2005 and 2006 then '2005-2006'
> when year(Inv.dDate) between 2006 and 2007 then '2006-2007'
> end) as Dyear
> FROM Inv
> Group By Dept, ddate
> order by Dept
> Just open a report and place the matrix and drag and drop in the respective
> column, you will get your desired results.
> Let me know for any problems.
> Amarnath
> "David Conte" wrote:
> > On Feb 5, 8:21 pm, Amarnath <Amarn...@.discussions.microsoft.com>
> > wrote:
> > > You can have year(date) but it gives you the year, so what you can do is use
> > > a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
> > > and use it in the matrix.
> > > Amarnath
> > Hi Amarnath,
> > Thanks for the reply. However I'm not sure how to tackle this by
> > using a case statement?
> > ie, not sure how to set it up to get it to seperate 2006-2007 etc?
> > Here is my sql so far:
> > SELECT MedicareItemKey, TestDepartments.Code as Department,
> > TransactionAmount, DateServiced
> > FROM InvoiceTransactions
> > INNER JOIN TestDepartments ON InvoiceTransactions.DepartmentKey => > TestDepartments.[Key]
> > WHERE MedicareItemKey IS NOT NULL
> > AND ((InvoiceTransactions.[DateServiced] >= '01-
> > Jan-'+convert(char,year(getdate())-0))
> > AND (InvoiceTransactions.[DateServiced] < '01-
> > Jan-'+convert(char,year(getdate())+1)))
> > ORDER BY TestDepartments.Code, MedicareItemKey, DateServiced
> > As you can see above, the DateServiced column is the one I am having
> > issues with in my matrix.
> > Thanks!
Thanks Amarnath,
Appreciate the reply.
I think that clears things up for me, I will give it a go!
Cheers!

Matrix - Total in non column group

Is it possible to create a "total" for a non "column group" ?
Usually you add a "SubTotal" to get values in column groups summarized. Is
it possible to do something similar to a "row group column"?
Like:
DataTable
"Q1", Id1, "NonGroup", 1, "Group1", 5
"Q1", Id1, "NonGroup", 1, "Group2", 6
"Q1", Id2, "NonGroup", 1, "Group1", 7
"Q1", Id2, "NonGroup", 2, "Group2", 8
"Q1", Id2, "NonGroup", 2, "Group3", 9
Matrix
"Group1" "Group2" "Group3"
"Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9You can try to right click on your row group and choose Subtotal on this
one.
Kaisa M. Lindahl Lervik
"Maran" <Maran@.discussions.microsoft.com> wrote in message
news:7AA486D8-CB21-43D3-84E5-2AC99AED3801@.microsoft.com...
> Is it possible to create a "total" for a non "column group" ?
> Usually you add a "SubTotal" to get values in column groups summarized. Is
> it possible to do something similar to a "row group column"?
> Like:
> DataTable
> "Q1", Id1, "NonGroup", 1, "Group1", 5
> "Q1", Id1, "NonGroup", 1, "Group2", 6
> "Q1", Id2, "NonGroup", 1, "Group1", 7
> "Q1", Id2, "NonGroup", 2, "Group2", 8
> "Q1", Id2, "NonGroup", 2, "Group3", 9
> Matrix
> "Group1" "Group2" "Group3"
> "Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9
>|||Thank you for your response Kaisa.
I think I need to clearify my wished result, though.
If I do like suggested I get the total of all rows, 7. I like the result to
be 3, the total of the unique rows.
It might be a combination of grouping and "total".
Any suggestion welcome.
Martin Bring (Sogeti AB)
***********************
"Kaisa M. Lindahl Lervik" wrote:
> You can try to right click on your row group and choose Subtotal on this
> one.
> Kaisa M. Lindahl Lervik
> "Maran" <Maran@.discussions.microsoft.com> wrote in message
> news:7AA486D8-CB21-43D3-84E5-2AC99AED3801@.microsoft.com...
> > Is it possible to create a "total" for a non "column group" ?
> >
> > Usually you add a "SubTotal" to get values in column groups summarized. Is
> > it possible to do something similar to a "row group column"?
> >
> > Like:
> > DataTable
> > "Q1", Id1, "NonGroup", 1, "Group1", 5
> > "Q1", Id1, "NonGroup", 1, "Group2", 6
> > "Q1", Id2, "NonGroup", 1, "Group1", 7
> > "Q1", Id2, "NonGroup", 2, "Group2", 8
> > "Q1", Id2, "NonGroup", 2, "Group3", 9
> >
> > Matrix
> > "Group1" "Group2" "Group3"
> > "Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9
> >
>
>|||Just a question:
The third row in your list, is that supposed to be Id1 or Id2?
Have you looked at the InScope function? This can be used to set the scope
of the sum function, so that you can choose what group to sum in your
subtotal.
You might also want to look at the RunningValue function, with SUM and the
correct group as scope.
Kaisa M. Lindahl Lervik
"Maran" <Maran@.discussions.microsoft.com> wrote in message
news:082DE84D-0126-4892-8306-4676D390DE9A@.microsoft.com...
> Thank you for your response Kaisa.
> I think I need to clearify my wished result, though.
> If I do like suggested I get the total of all rows, 7. I like the result
> to
> be 3, the total of the unique rows.
> It might be a combination of grouping and "total".
> Any suggestion welcome.
> Martin Bring (Sogeti AB)
> ***********************
> "Kaisa M. Lindahl Lervik" wrote:
>> You can try to right click on your row group and choose Subtotal on this
>> one.
>> Kaisa M. Lindahl Lervik
>> "Maran" <Maran@.discussions.microsoft.com> wrote in message
>> news:7AA486D8-CB21-43D3-84E5-2AC99AED3801@.microsoft.com...
>> > Is it possible to create a "total" for a non "column group" ?
>> >
>> > Usually you add a "SubTotal" to get values in column groups summarized.
>> > Is
>> > it possible to do something similar to a "row group column"?
>> >
>> > Like:
>> > DataTable
>> > "Q1", Id1, "NonGroup", 1, "Group1", 5
>> > "Q1", Id1, "NonGroup", 1, "Group2", 6
>> > "Q1", Id2, "NonGroup", 1, "Group1", 7
>> > "Q1", Id2, "NonGroup", 2, "Group2", 8
>> > "Q1", Id2, "NonGroup", 2, "Group3", 9
>> >
>> > Matrix
>> > "Group1" "Group2" "Group3"
>> > "Q1", 3 (1+2), 12 (5+7), 14 (6+8), 9
>> >
>>

Matrix - How to calculate % of row?

Hi,

I wish to create a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (ignore rounding issues):

Status

A

B

C

D

E

Total

01/01/2007

Number

9

32

3

13

0

57

% of Total

15%

56%

5%

24%

0.00%

100.00%

Can someone advise the best way to calculate the % cells in this example?

Thanks

Can you do it in the dataset query and then just set that row equal to the result of the query? That seems to work the best for me.|||

Hi,

Unfortunately in this instance I need to find a solution that can be implemented within the report.

Thanks for the reply

|||

Don't know if this is any use but whilst trying to find out how to add totals to my first matrix report (still looking...) I came across

http://blogs.technet.com/mat_stephen/archive/2005/05/26/405407.aspx

Steven

|||

The reply to the comment on Matt Stephen's blog certainly helps:

"For matrix reports though, percentages can be calculated on the total for the report, but not for group totals on the column total or row total, since the column or row group cannot be referenced within the dataset. "

Suggesting that it is not possible, so the data will have to be calculated at source.

Thanks Steven.

|||Or you could just use the workaround of calculating the total and percentages in the source query.|||

If ur using the concept of SSAS i.e. if ur datasource is an analysis services data base then i can help you out.

By using the calculate in SSAS

calculate a new column with some name as %of total and specify the expression as

(A/Total,Number) or (A/Total,Number.All) {I dont remember correctly)

Where in your case if A is the column name and

Total ur entire total

and the Number is the "row name"

What this thing does is, it extends the scope of the expression (A/Total) to the entire row so you get the correct value.

Hope this is ur requirement .

Thanks

|||

Thanks for the response Mr.rajz

I had to change the query at source as I was not using SSAS.

Matrix - Create two rows in a matrix

Hello.
I hope to explain myself well - I want to make a matrix with two rows.
Lats say my data is this:
I hava a list of months and in every month I have a number of pepole
and there age.
How can I show this in a matrix?
It need to be in a matrix since I need the columns to expand acording
to the month but I don't know how to create two diffrent rows in my
matrix.
The data should look like this:
10/06 11/06 12/06 01/07 02/07 03/7
04/07 ....
num 5 1 2 5 4
5 7 ....
age 16.1 25 18.5 14.8 25.5
20.5 18.5 ....
Thanks for any help.On Nov 26, 7:18 am, nicknack <roezo...@.gmail.com> wrote:
> Hello.
> I hope to explain myself well - I want to make a matrix with two rows.
> Lats say my data is this:
> I hava a list of months and in every month I have a number of pepole
> and there age.
> How can I show this in a matrix?
> It need to be in a matrix since I need the columns to expand acording
> to the month but I don't know how to create two diffrent rows in my
> matrix.
> The data should look like this:
> 10/06 11/06 12/06 01/07 02/07 03/7 04/07 ....
> num 5 1 2 5 4 5 7 ....
> age 16.1 25 18.5 14.8 25.5 20.5 18.5 ....
> Thanks for any help.
Right-click on the Details section of the matrix, and go to Add Row.
Drag your second DataSet field into this new area. It will
automatically create the "num" and "age" row titles as a new static
Group, and these can be renamed like any other field.
-- Scott|||Hi Scott,
Thanks for the help.
Thats exactly what I needed.
I was sure, for some reason that this option will add another group
and not another simple row.
Thanks again,
Roy.
On 26 =D7=A0=D7=95=D7=91=D7=9E=D7=91=D7=A8, 21:33, Orne <polysilly...@.yahoo.=com> wrote:
> On Nov 26, 7:18 am, nicknack <roezo...@.gmail.com> wrote:
>
> > Hello.
> > I hope to explain myself well - I want to make a matrix with two rows.
> > Lats say my data is this:
> > I hava a list of months and in every month I have a number of pepole
> > and there age.
> > How can I show this in a matrix?
> > It need to be in a matrix since I need the columns to expand acording
> > to the month but I don't know how to create two diffrent rows in my
> > matrix.
> > The data should look like this:
> > 10/06 11/06 12/06 01/07 02/07 03/7 04/07= ....
> > num 5 1 2 5 4 5 = 7 ....
> > age 16.1 25 18.5 14.8 25.5 20.5 =18.5 ....
> > Thanks for any help.
> Right-click on the Details section of the matrix, and go to Add Row.
> Drag your second DataSet field into this new area. It will
> automatically create the "num" and "age" row titles as a new static
> Group, and these can be renamed like any other field.
> -- Scott

Mathematical help and not really SQL

Create table stdevtest
(col1 int)
Insert stdevtest values(1)
Insert stdevtest values(2)
Insert stdevtest values(3)
Insert stdevtest values(6)
Insert stdevtest values(7)
Insert stdevtest values(8)
Insert stdevtest values(8000)
select avg(col1) from stdevtest
select stdev(col1) from stdevtest
As you can see, I tried avg and also standard deviation, but per the values
above and I aint no mathematician, how can I somehow show the business
folks, that there are some outliers and excluding those eg: value 8000, our
average is really around 4 and stdev is around 2.88
Are there other ways to do this ? In this example, atleast I can see all the
data, but when I was analyzing some data with a few 1000 rows, i did a min
and max and found min being 1 and max being like 500000 and hence the avg is
around 200000 or so which may not be what i want to tell the business
folks.. Sad part is I also dont know how to tell them otherwise :(
Appreciate any help you can provide..Hi
You may want skewness see http://en.wikipedia.org/wiki/Skewness
and http://www.users.drew.edu/skass/sql/SkewKurtosis.sql.txt
Also read up on Median
http://en.wikipedia.org/wiki/Median
John
"Hassan" wrote:
> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise :(
> Appreciate any help you can provide..
>
>|||Or you could try something like this:
-- Chop off top and bottom
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
) b
WHERE a.col1 > b.min_col1
AND a.col1 < b.max_col1
-- Chop off top and bottom with tolerance
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
WHERE col1 > 1
AND col1 < 7000
) b
WHERE a.col1 >= b.min_col1
AND a.col1 <= b.max_col1
Curtesty of Ken Henderson originally I think!?
Hope that helps.
wBob
"Hassan" wrote:
> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise :(
> Appreciate any help you can provide..
>
>|||On Mon, 3 Dec 2007 22:59:25 -0800, Hassan wrote:
>Create table stdevtest
>(col1 int)
>Insert stdevtest values(1)
>Insert stdevtest values(2)
>Insert stdevtest values(3)
>Insert stdevtest values(6)
>Insert stdevtest values(7)
>Insert stdevtest values(8)
>Insert stdevtest values(8000)
>select avg(col1) from stdevtest
>select stdev(col1) from stdevtest
>As you can see, I tried avg and also standard deviation, but per the values
>above and I aint no mathematician, how can I somehow show the business
>folks, that there are some outliers and excluding those eg: value 8000, our
>average is really around 4 and stdev is around 2.88
>Are there other ways to do this ? In this example, atleast I can see all the
>data, but when I was analyzing some data with a few 1000 rows, i did a min
>and max and found min being 1 and max being like 500000 and hence the avg is
>around 200000 or so which may not be what i want to tell the business
>folks.. Sad part is I also dont know how to tell them otherwise :(
>Appreciate any help you can provide..
>
Hi Hassan,
If you just want to exclude the highest and lowest values, you can use
SELECT (1.0 * SUM(col1) - MAX(col1) - MIN(col1)) / (COUNT(*) - 2)
FROM stdevtest;
If you want to exclude the 10% highest and 10% lowest values, then use
something like this:
WITH RankedData
AS (SELECT col1 * 1.0 AS col1,
ROW_NUMBER () OVER (ORDER BY col1) AS rn,
COUNT(*) OVER () AS cnt
FROM stdevtest)
SELECT AVG(col1), STDEV(col1)
FROM RankedData
WHERE rn BETWEEN CEILING(cnt * 0.1) + 1 AND FLOOR(cnt * 0.9);
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Mathematical help and not really SQL

Create table stdevtest
(col1 int)
Insert stdevtest values(1)
Insert stdevtest values(2)
Insert stdevtest values(3)
Insert stdevtest values(6)
Insert stdevtest values(7)
Insert stdevtest values(8)
Insert stdevtest values(8000)
select avg(col1) from stdevtest
select stdev(col1) from stdevtest
As you can see, I tried avg and also standard deviation, but per the values
above and I aint no mathematician, how can I somehow show the business
folks, that there are some outliers and excluding those eg: value 8000, our
average is really around 4 and stdev is around 2.88
Are there other ways to do this ? In this example, atleast I can see all the
data, but when I was analyzing some data with a few 1000 rows, i did a min
and max and found min being 1 and max being like 500000 and hence the avg is
around 200000 or so which may not be what i want to tell the business
folks.. Sad part is I also dont know how to tell them otherwise
Appreciate any help you can provide..
Hi
You may want skewness see http://en.wikipedia.org/wiki/Skewness
and http://www.users.drew.edu/skass/sql/SkewKurtosis.sql.txt
Also read up on Median
http://en.wikipedia.org/wiki/Median
John
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>
|||Or you could try something like this:
-- Chop off top and bottom
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
) b
WHERE a.col1 > b.min_col1
AND a.col1 < b.max_col1
-- Chop off top and bottom with tolerance
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
WHERE col1 > 1
AND col1 < 7000
) b
WHERE a.col1 >= b.min_col1
AND a.col1 <= b.max_col1
Curtesty of Ken Henderson originally I think!?
Hope that helps.
wBob
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the values
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, our
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all the
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>

Mathematical help and not really SQL

Create table stdevtest
(col1 int)
Insert stdevtest values(1)
Insert stdevtest values(2)
Insert stdevtest values(3)
Insert stdevtest values(6)
Insert stdevtest values(7)
Insert stdevtest values(8)
Insert stdevtest values(8000)
select avg(col1) from stdevtest
select stdev(col1) from stdevtest
As you can see, I tried avg and also standard deviation, but per the values
above and I aint no mathematician, how can I somehow show the business
folks, that there are some outliers and excluding those eg: value 8000, our
average is really around 4 and stdev is around 2.88
Are there other ways to do this ? In this example, atleast I can see all the
data, but when I was analyzing some data with a few 1000 rows, i did a min
and max and found min being 1 and max being like 500000 and hence the avg is
around 200000 or so which may not be what i want to tell the business
folks.. Sad part is I also dont know how to tell them otherwise
Appreciate any help you can provide..Hi
You may want skewness see http://en.wikipedia.org/wiki/Skewness
and http://www.users.drew.edu/skass/sql...urtosis.sql.txt
Also read up on Median
http://en.wikipedia.org/wiki/Median
John
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the value
s
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, ou
r
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all t
he
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg
is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>|||Or you could try something like this:
-- Chop off top and bottom
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
) b
WHERE a.col1 > b.min_col1
AND a.col1 < b.max_col1
-- Chop off top and bottom with tolerance
SELECT a.*
FROM stdevtest a CROSS JOIN
(
SELECT MIN(col1) min_col1, MAX(col1) max_col1
FROM stdevtest
WHERE col1 > 1
AND col1 < 7000
) b
WHERE a.col1 >= b.min_col1
AND a.col1 <= b.max_col1
Curtesty of Ken Henderson originally I think!?
Hope that helps.
wBob
"Hassan" wrote:

> Create table stdevtest
> (col1 int)
> Insert stdevtest values(1)
> Insert stdevtest values(2)
> Insert stdevtest values(3)
> Insert stdevtest values(6)
> Insert stdevtest values(7)
> Insert stdevtest values(8)
> Insert stdevtest values(8000)
> select avg(col1) from stdevtest
> select stdev(col1) from stdevtest
> As you can see, I tried avg and also standard deviation, but per the value
s
> above and I aint no mathematician, how can I somehow show the business
> folks, that there are some outliers and excluding those eg: value 8000, ou
r
> average is really around 4 and stdev is around 2.88
> Are there other ways to do this ? In this example, atleast I can see all t
he
> data, but when I was analyzing some data with a few 1000 rows, i did a min
> and max and found min being 1 and max being like 500000 and hence the avg
is
> around 200000 or so which may not be what i want to tell the business
> folks.. Sad part is I also dont know how to tell them otherwise
> Appreciate any help you can provide..
>
>|||On Mon, 3 Dec 2007 22:59:25 -0800, Hassan wrote:

>Create table stdevtest
>(col1 int)
>Insert stdevtest values(1)
>Insert stdevtest values(2)
>Insert stdevtest values(3)
>Insert stdevtest values(6)
>Insert stdevtest values(7)
>Insert stdevtest values(8)
>Insert stdevtest values(8000)
>select avg(col1) from stdevtest
>select stdev(col1) from stdevtest
>As you can see, I tried avg and also standard deviation, but per the values
>above and I aint no mathematician, how can I somehow show the business
>folks, that there are some outliers and excluding those eg: value 8000, our
>average is really around 4 and stdev is around 2.88
>Are there other ways to do this ? In this example, atleast I can see all th
e
>data, but when I was analyzing some data with a few 1000 rows, i did a min
>and max and found min being 1 and max being like 500000 and hence the avg i
s
>around 200000 or so which may not be what i want to tell the business
>folks.. Sad part is I also dont know how to tell them otherwise
>Appreciate any help you can provide..
>
Hi Hassan,
If you just want to exclude the highest and lowest values, you can use
SELECT (1.0 * SUM(col1) - MAX(col1) - MIN(col1)) / (COUNT(*) - 2)
FROM stdevtest;
If you want to exclude the 10% highest and 10% lowest values, then use
something like this:
WITH RankedData
AS (SELECT col1 * 1.0 AS col1,
ROW_NUMBER () OVER (ORDER BY col1) AS rn,
COUNT(*) OVER () AS cnt
FROM stdevtest)
SELECT AVG(col1), STDEV(col1)
FROM RankedData
WHERE rn BETWEEN CEILING(cnt * 0.1) + 1 AND FLOOR(cnt * 0.9);
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis