Repeating Record

How can I display same repeating record in a table?
Any help will be appreciated.any idea about this question?|||I guess you mean duplicates...

based on a key?

SELECT Col1, Col2
FROM myTable99
GROUP BY Col1, Col2
HAVING COUNT(*) > 1|||hi Brett,

Thank you ..it worked

Repeating attribute member in Excel 2007

I'm accessing SSAS 2005 cube with Excel 2007. In the pivot table, let's say that I pull premium by policy year, policy month.

The display in the pivot table would be like the following.

policy year policy month premium

2007 Jan 2,000,000

Feb 1,000,000

Mar 1,500,000

How can I repeat 2007 in the pivot table so that it would look like the following?

policy year policy month premium

2007 Jan 2,000,000

2007 Feb 1,000,000

2007 Mar 1,500,000

By repeating the attribute member in Excel 2007, my user can easily export the pivot table into a data set.

Mitch

One way to solve this is to make a SSRS2005 report with the SSAS2005 cube as a data source.

In Reporting Service you can control if a group member name should be repeated for each child or not.

Users can export this report to Excel.

I have not seen a solution in Excel 2007 pivot tables, more than allowing drill-through actions on cells, and let users get the leaf transactions behind that cell value.

HTH

Thomas Ivarsson

|||

Thomas,

Thanks for the feedback. For me, SSRS is not an option because my users want to create these data sets on the fly within Pivot table. I'll just let them know the feature is not there yet for now.

Mitch

Repeated Rows in Matrix

Hi,

I am facing a problem with Matrix control. My requirement is similar to the below problem.

The report should display Manager, Leaders under him, Members under leaders. The data is the working hours of members grouped by Month.

Manager Leader Member Jan Feb Mar Apr .....

M1 L1 E1 170 160 150 180

E2 159 161 130 185

E3 150 180 159 161

L2 E5 159 161 170 160

E6 159 161 130 185

E1 160 150 150 180

M2 L3 E5 150 180 159 161

L1 E1 160 150 150 180

I took Manager, Leader and Member Name columns as Row groups and Month of Working day as Column group. Working hours sum in Data Section. Every thing is working fine but some rows for same Manager are dividing into to parts and displaying as entries for seperate managers as below.

Manager Leader Member Jan Feb Mar Apr .....

M1 L1 E1 140 150 100 120

E2 119 149 50 120

E3 150 180 159 161

L2 E5 159 161 170 160

E6 159 161 130 185

E1 160 150 150 190

M1 L1 E1 30 10 50 60

E2 40 11 80 65

Is this any bug of Matrix control or is there any thing wrong in my design ?( I just dragged respective columns to row, column and data groups, nothing much). Are there any things i need to ensure ?

This could be caused by Manager fields not being exactly the same. There could be extra white space or a different case used for a character or two.

Try using the following as the grouping expression.

"=Fields!FieldName.Value.Trim().ToLower()"

Ian|||It is working. Thank you very much.

repeat data in matrix row group on every line

Hello everyone,
I have a matrix report that is grouped on 4 fields. I need the report
to display data for every line and not hide data for a group.
i.e
Currently:
Apex Merlot 750ml
375ml
1.5L
I need:
Apex Merlot 750ml
Apex Merlot 375ml
Apex Merlot 1.5L
thank you,
JustinSee the attached report for a sample of how to repeat your group data on
each line.
The structure below uses the terminology of your example
Row Group 1 Row Group 2 Row Group 3
Group by Wine Type Group by Quantity Group by Quantity
Display Wine Type Display Wine Type Display Quantity
You might be able to place Row Group 3 in a value cell.
You will need to strink Row Group 1's cell width and mostl like set its
background and text color to white.
You might consider using a table for this type of report since it becomes
rather trival to setup.
In this approach you would place the Wine Type and Quantity in a detail.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin" <stupy1@.hotmail.com> wrote in message
news:2e90a66b.0410050545.200d149@.posting.google.com...
> Hello everyone,
> I have a matrix report that is grouped on 4 fields. I need the report
> to display data for every line and not hide data for a group.
> i.e
> Currently:
> Apex Merlot 750ml
> 375ml
> 1.5L
> I need:
> Apex Merlot 750ml
> Apex Merlot 375ml
> Apex Merlot 1.5L
>
> thank you,
> Justin
RepeatMatrixGroupData.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>CustomerDataSet</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression />
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.875in</Width>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<rd:DefaultName>CompanyName</rd:DefaultName>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>0.875in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>5f06a1b6-3d14-4331-a0a7-3192c902022e</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.50001in</Width>
<DataSets>
<DataSet Name="CustomerDataSet">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT *
FROM Customers</CommandText>
</Query>
</DataSet>
<DataSet Name="CountryDataSet">
<Fields>
<Field Name="country">
<DataField>country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select country from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>8bc78064-37e7-4500-aba6-1ed9e3eb7429</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Thanks Bruce. My customers are going to have to wait on this one.
Hopefully, this feature will be included in a future release.
thanks again,
Justin

Repeat a field value(text box) on each page or display value in header

How can I accomplish putting a data driven title on each page of a
report? Header errors say you can't put a field value in a header or
footer, so I tried(from another post) putting a text box in the body of
the report, making it white and setting another text box inthe header = to the value of the text box in the body(=ReportItems!textBox1.Value).
This works until you have a LARGE text field object on a report, whose
contents span multiple pages. Then subsequent pages do not display this
field, I guess because the original text box is out "range" once the
hidden data field is no longer being displayed. I don't have a table.
It's a single record report. I have a bunch of List objects with
textboxes organized on the layout.
Any ideas on how to solve?I have the same problem in SRS 2000. Any ideas on how to solve this would be
greatly appreciated!
"pauldes@.kpdsys.com" wrote:
> How can I accomplish putting a data driven title on each page of a
> report? Header errors say you can't put a field value in a header or
> footer, so I tried(from another post) putting a text box in the body of
> the report, making it white and setting another text box inthe header => to the value of the text box in the body(=ReportItems!textBox1.Value).
> This works until you have a LARGE text field object on a report, whose
> contents span multiple pages. Then subsequent pages do not display this
> field, I guess because the original text box is out "range" once the
> hidden data field is no longer being displayed. I don't have a table.
> It's a single record report. I have a bunch of List objects with
> textboxes organized on the layout.
> Any ideas on how to solve?
>|||I wonder if you put the textbox and the table (or other fields you want shown
on your report) inside a list box and set the textbox to "repeat item with
data area on each page" (although this is supposed to work ... I have had
problems with the "repeat" property). Might be worth a try ...
"Patrik Ylén" wrote:
> I have the same problem in SRS 2000. Any ideas on how to solve this would be
> greatly appreciated!
> "pauldes@.kpdsys.com" wrote:
> > How can I accomplish putting a data driven title on each page of a
> > report? Header errors say you can't put a field value in a header or
> > footer, so I tried(from another post) putting a text box in the body of
> > the report, making it white and setting another text box inthe header => > to the value of the text box in the body(=ReportItems!textBox1.Value).
> >
> > This works until you have a LARGE text field object on a report, whose
> > contents span multiple pages. Then subsequent pages do not display this
> > field, I guess because the original text box is out "range" once the
> > hidden data field is no longer being displayed. I don't have a table.
> > It's a single record report. I have a bunch of List objects with
> > textboxes organized on the layout.
> >
> > Any ideas on how to solve?
> >
> >

Reordering SQL Tables

Hi,

I have inherited an inhouse employee self service application used to display various employee data.

I have one table that needs to have the data permanently reordered, as the front end web page reads the data from the table from the top to the bottom, but somehow this data has got mixed up and is not in the sequential order that it should be in.

I am new to SQL and haven't a clue how to change the web code, so was hoping I could run a script on that table to reorder the data in an ascending manner on one of the columns.

Is this possible? How can I do this?

Thanks

Chris

Hi Chris,

When you say "not in the sequential order" do you mean there is a column that has incrementing numeric values such as 1,2,3...100000 or that you expect results to be ordered by something like customer name etc?

You can only "order" a table via a clustered index, but this has no impact upon client query result formats (in fact it did pre 2000 if no order by was specified, but that's another story). You will need to specify an ORDER BY in the client, or in a view (via the TOP clause) or stored procedure. You cannot rely upon sql server to return ordered results unless you have explicity stated what and how you wish the ordering to ocurr.

Cheers,

Rob