Firehose mode: row

Showing posts with label row. Show all posts

Friday, March 30, 2012

grouping rows by customer

my source flat file has many rows per customer,
but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups).
I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database?
As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Thanks

Vlad

won't A Flat file source and aggregation transform suffice your needs?

Rafael Salas

|||

I tried this, but I do not think it would help :-(

it is more complicated calculation, than just grouping.

I would rather do it in the script load into a Collection, loop, calculate, replace, substitute etc. and then save to database.

|||

Well you know your data...good luck with that!

Rafael Salas

|||

The Aggregate transformation does more than Group By. You don't want to do this in script. You can do SUM, AVG, MIN, MAX with the Aggregrate Transformation. If you need to then do something else combine the Derived Column Transformation with it.

http://msdn2.microsoft.com/en-US/library/ms138031.aspx

Grouping Row Border

Hi,
I'm trying to achieve the following in a table. Have border after/before the
grouping changes to the next value.
Nov 1 v1 v2 v3 v4
v1 v2 v3 v4
_____________________
Nov 3 v1 v2 v3 v4
_____________________
Is there an easy way to do this?Yes. Add either a top or bottom border to group footer row in the table. The
report at end of this posting demonstrates how to accomplish this task.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:O%23n$LYymEHA.3684@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to achieve the following in a table. Have border after/before
the
> grouping changes to the next value.
> Nov 1 v1 v2 v3 v4
> v1 v2 v3 v4
> _____________________
> Nov 3 v1 v2 v3 v4
> _____________________
> Is there an easy way to do this?
>
BorderBetweenGroups.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.125in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.125in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>76d8d0cd-7eef-42fe-aa1e-81920ad6bfed</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<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>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>2fd0260c-7640-49fb-9128-9a9647b7cdcf</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Wednesday, March 28, 2012

Grouping problem

I need to provide a summary row at the bottom of each group that sums accounts of a specific type and subtracts it from the group total.

Acct Balance Prior Balance

A 10 5

B 4 8

C 7 6

D 9 12

Total 30 31

exp accts 13 20

Final 17 11

Is there an easy way to do this in reporting services?

So you want to perform a conditional sum based on another field (account type) in the dataset?

You need add some additional rows to the group footer and put an Iif statement inside the Sum that returns the value to aggregate for a match and 0 otherwise e.g.

Sum(Iif(Fields!account_type.Value = "exclude", Fields!Balance.value, 0))

Then in the last row subtract one row from the other.

The following is RDL code for an example:

<?xml version="1.0" encoding="utf-8"?>

<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>

</ConnectionProperties>

<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>

</DataSource>

</DataSources>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>balance_1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>prior_balance_1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!prior_balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>exc accts</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Final</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<DataSetName>DataSet1</DataSetName>

<rd:DefaultName>account</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>=Fields!account.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>balance</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>prior_balance</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!prior_balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>account</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>balance</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>prior balance</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance

union

select 'B', 2, 4, 8

union

select 'C', 1, 7, 6

union

select 'D', 2, 9, 12</CommandText>

<DataSourceName>master</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

<DataField>account</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>balance</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>prior_balance</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

Grouping on multiple Datasets

is it possible to group 2 or more datasets?
i.e i have a row that returns count of sales, ATV etc from one view
and paidout sales from another view this has to come from 2 datasets
due to them having different selection criteria
i am grouping by region on dataset1 but need to group by region on
dataset2 aslo?
Any help much appreciatednot possible today.
An option is to create a subreport.
The first report group the objects from the first dataset, the subreport
display the data from the second dataset filtered by a paremeter which is
the region.
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1181571015.624028.95320@.c77g2000hse.googlegroups.com...
> is it possible to group 2 or more datasets?
> i.e i have a row that returns count of sales, ATV etc from one view
> and paidout sales from another view this has to come from 2 datasets
> due to them having different selection criteria
> i am grouping by region on dataset1 but need to group by region on
> dataset2 aslo?
> Any help much appreciated
>|||many thanks|||It depends on what you are doing.
Are the datasets just select commands?
If so a simple union query is all that is required.
It you have a table per dataset, then you group each one accordingly.
More information is required but there are always many
'workarounds'...
Regards,
Tom Bizannes
Reporting Services Designer
Sydney Australia

Grouping Issue

i am grouping one field(CustomerID) in Crystal Report 10 even though value(111) of that field(CustomerID) is repeating in entire row. for example

Output is coming this way

customer ID Product name
111 Key board
111 Mouse
111 Mother Board

Output is suppose to come this way

customer ID Product name
111 Key board
Mouse
Mother Board

Can anyone help me to figure out why the value of field is repeating in row even though i have put that field in Group Section???

Thank in AdvacneIs your output
Preview View
<GH>111
<D>111 Key board
<D>111 Mouse
<D>111 Mother Board

Design View
Group Header: Group #1 name
Details: <Customer_id><Product_name>

<GH> = Group Header <D> = Details

If so you left the <customer_ID> field in the Details section

Let me know if Im off.

Thanks

Monday, March 26, 2012

grouping data...

greetings,
i want to show a 2nd lvl report, but i dont want it to be group.. e.g. when
my user clicks on the row or on the + sign , it expends and execute another
query. is that possible?Use drill-through. Click on the row or textbox you want the user to click on,
then go to the Properties window and find a property called Action. Click on
the ellipsis button to the right of it, and choose Jump to report. Take it
from there!
Charles Kangai, MCT, MCDBA
"Asha" wrote:
> greetings,
> i want to show a 2nd lvl report, but i dont want it to be group.. e.g. when
> my user clicks on the row or on the + sign , it expends and execute another
> query. is that possible?

Wednesday, March 21, 2012

Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Monday, March 19, 2012

Group Number

I have a table that groups on one field.
I would like the group row to show an incrementing number for each group, i.e. if there are twenty records in the dataset that are grouped into 4 groups on the report, I would like the group headers to show Group 1, Group 2, Group 3 and Group 4 respectively.
I have tried fiddling around with rownumber, but that just gives me the number of rows in the group or in the entire dataset. I can't figure a way of doing it in the SP (without using Cursors) as the grouping fields are not always the same. I have also tried fiddling around with previous but that also led nowhere.
Is there no GroupNumber property or something similar I can get at?Group on Fields!Group.Value (or something similar) and use the following
expression: =RunningValue(Fields!Group.Value, CountDistinct, Nothing)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Another frustrated developer"
<Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
news:C8E2AC24-C6FD-4C60-B190-480B1A395A3B@.microsoft.com...
> I have a table that groups on one field.
> I would like the group row to show an incrementing number for each group,
i.e. if there are twenty records in the dataset that are grouped into 4
groups on the report, I would like the group headers to show Group 1, Group
2, Group 3 and Group 4 respectively.
> I have tried fiddling around with rownumber, but that just gives me the
number of rows in the group or in the entire dataset. I can't figure a way
of doing it in the SP (without using Cursors) as the grouping fields are not
always the same. I have also tried fiddling around with previous but that
also led nowhere.
> Is there no GroupNumber property or something similar I can get at?|||Small clarification:
The first parameter should be the same field used in the group expression:
Fields!<FieldName>.Value
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:emNXUUpaEHA.2388@.TK2MSFTNGP11.phx.gbl...
> Group on Fields!Group.Value (or something similar) and use the following
> expression: =RunningValue(Fields!Group.Value, CountDistinct, Nothing)
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Another frustrated developer"
> <Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
> news:C8E2AC24-C6FD-4C60-B190-480B1A395A3B@.microsoft.com...
> > I have a table that groups on one field.
> >
> > I would like the group row to show an incrementing number for each
group,
> i.e. if there are twenty records in the dataset that are grouped into 4
> groups on the report, I would like the group headers to show Group 1,
Group
> 2, Group 3 and Group 4 respectively.
> >
> > I have tried fiddling around with rownumber, but that just gives me the
> number of rows in the group or in the entire dataset. I can't figure a
way
> of doing it in the SP (without using Cursors) as the grouping fields are
not
> always the same. I have also tried fiddling around with previous but that
> also led nowhere.
> >
> > Is there no GroupNumber property or something similar I can get at?
>|||Great Job guys, works a dream.
Thanks very much.
Rich.
"Bruce Johnson [MSFT]" wrote:
> Small clarification:
> The first parameter should be the same field used in the group expression:
> Fields!<FieldName>.Value
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:emNXUUpaEHA.2388@.TK2MSFTNGP11.phx.gbl...
> > Group on Fields!Group.Value (or something similar) and use the following
> > expression: =RunningValue(Fields!Group.Value, CountDistinct, Nothing)
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Another frustrated developer"
> > <Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
> > news:C8E2AC24-C6FD-4C60-B190-480B1A395A3B@.microsoft.com...
> > > I have a table that groups on one field.
> > >
> > > I would like the group row to show an incrementing number for each
> group,
> > i.e. if there are twenty records in the dataset that are grouped into 4
> > groups on the report, I would like the group headers to show Group 1,
> Group
> > 2, Group 3 and Group 4 respectively.
> > >
> > > I have tried fiddling around with rownumber, but that just gives me the
> > number of rows in the group or in the entire dataset. I can't figure a
> way
> > of doing it in the SP (without using Cursors) as the grouping fields are
> not
> > always the same. I have also tried fiddling around with previous but that
> > also led nowhere.
> > >
> > > Is there no GroupNumber property or something similar I can get at?
> >
> >
>
>

group into 1 row

I have a query that I need to group the results for a particular client into
1 row. The problem is that I'm creating some columns on the fly using Case
statements, and the columns contain a Y or N as their data. Because of
this, I can't get the results in 1 row. I want to know if there is a way to
do this.
I've come up with a sample query that uses the Northwind db that will show
you what I'm trying to do. I'd like the results of the query below in 1
row. I know it doesn't make sense if you look at the result for the query
below, but it's just a representative example for what I'm doing.
select CategoryName,
Drink1 = Case ProductName when 'Chai' then 'Y' end,
Drink2 = Case ProductName when 'Chang' then 'Y' end
from [Products by Category]
where ProductName in ('chai','chang')
Thanks, Andresomething like this?
select CategoryName,
Drink1 = max(Case ProductName when 'Chai' then 'Y' end),
Drink2 = max(Case ProductName when 'Chang' then 'Y' end)
from [Products by Category]
where ProductName in ('chai','chang')
group by categoryName
I used max because the other value is only null. Use suitably for your issue
.
Hope this helps.|||Yes, exactly like that! Thanks.

Group ID and ID Column

Hi,

I want to get the value from a row that contains the minimum value of a field AND for which I group by a 3rd field. For example:

The table tbl1 has:

X Quantity Location

1 20 slot 1

1 34 slot 1

3 17 slot 1

42 12 slot 5

5 65 slot 5

If I just want the quantities and location I can do:

Select Location, Min(Quantity) from tbl1 group by Location

However if I want X (or any other field in the table) I cannot do:

Select X, Location, Min(Quantity) from tbl1 group by Location

because X is not in the group by clause.

And putting X in the group by clause causes incorrect results.

Does anyone know the correct select statement?

jerry

It might help if you provided sample output from the query you are trying to write.|||

In the case of a group by, any non grouped columns in the select must be contained in an aggregate function.

Think about your example, If you group by Location which value of x should you get for location = 'slot 1' 1 or 3.

so, use MIN(X) or MAX(X) in your select and it should work

|||

Can you explain what you are trying to accomplish?

;with cte

(

select X, Quantity, Location, row_number() over(partition by Location order by Quantity) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

If you use sql server 2000,

Code Snippet

Create Table #qty (

[X] Varchar(100) ,

[Quantity] Varchar(100) ,

[Location] Varchar(100)

);

Insert Into #qty Values('1','20','slot1');

Insert Into #qty Values('1','34','slot1');

Insert Into #qty Values('3','17','slot1');

Insert Into #qty Values('42','12','slot5');

Insert Into #qty Values('5','65','slot5');

Select * From #qty [Main]

Join (

Select

[Location]

,Min([Quantity])[Quantity]

From

#qty

Group By

[Location]

) as [Data] On [Data].[Quantity] = [Main].[Quantity]

And [Data].[Location] = [Main].[Location]

Group Footers?

I am new to reporting services. On a detail table of my report, I would like
to print
a large comment field after each row. The comment should span the entire
width of the table. (Sort of like the outlook preview row capability) Is
this possible?
Thanks,
Deniseright click to the left of you detail row and 'insert row below'.
Select all cells in this row, right click and 'merge'. You can make this any
size you need and will show up between your report detail. You can format as
needed.
--
U. Tokklas
"Denise" wrote:
> I am new to reporting services. On a detail table of my report, I would like
> to print
> a large comment field after each row. The comment should span the entire
> width of the table. (Sort of like the outlook preview row capability) Is
> this possible?
> Thanks,
> Denise

Group Footer on Next Page

Anyone know if there is a way to keep at least one detail row with the group
footer at page break?
It doesn't make for a very nice report when some pages begin with a group
footer and no details to go with it.
Thanks to anyone that can help with this.I am having the same problem. MS documentation says that "a table group
footer and the last previous detail row before the footer are kept together".
This is not happening for me. I get the group footer on a page by itself
when the last table detail line ends a group.
Any ideas?
"Alison" wrote:
> Anyone know if there is a way to keep at least one detail row with the group
> footer at page break?
> It doesn't make for a very nice report when some pages begin with a group
> footer and no details to go with it.
> Thanks to anyone that can help with this.
>
>|||Sorry forgot to mention: For me, the table group footers page breaks look
great when previewing in rs, but after saving to PDF, then the group footer
is not "kept together" with the last detail row.
Help?
"LesW" wrote:
> I am having the same problem. MS documentation says that "a table group
> footer and the last previous detail row before the footer are kept together".
> This is not happening for me. I get the group footer on a page by itself
> when the last table detail line ends a group.
> Any ideas?
> "Alison" wrote:
> > Anyone know if there is a way to keep at least one detail row with the group
> > footer at page break?
> > It doesn't make for a very nice report when some pages begin with a group
> > footer and no details to go with it.
> >
> > Thanks to anyone that can help with this.
> >
> >
> >

Friday, March 9, 2012

Group By Row Count And Force Page Break

Hello All!

Is there a "clean" way to group rows from a table by the row count, and then force a page break each time the count hits a maximum number (say 25 rows)?

As an alternative, can you set the maximum count of rows in a table, and then force a page break if it reaches that count? (this may actually be a better solution for my needs).

Thanks!

Create a group, set the expression for grouping to "=Fix((RowNumber("table1")-1) / 25)", and set the page break at end option on. To vary the number of rows per page, change 25 to your desired value.|||This works good, except that your table footer will always be on another page. Any ideas on how to prevent this using the solution above?|||In order to get table header and footer on all pages (and no hanging footer on last page), put the table in a list and do the grouping on the list.

Group By Row Count And Force Page Break

Hello All!

Is there a "clean" way to group rows from a table by the row count, and then force a page break each time the count hits a maximum number (say 25 rows)?

As an alternative, can you set the maximum count of rows in a table, and then force a page break if it reaches that count? (this may actually be a better solution for my needs).

Thanks!

Group By Row

I am looking for a way to group on a row. I am not sure that this can
be done, but here is what I have.
My dataset looks like the following:
JobNumber Invoice Code Property PropertyValue Cost
071201 61556 Area
101 10.00
071201 61556 Cost Center
1101020 10.00
071201 61556 Equipment #
23R05 10.00
I want my report to look like this:
071201
61556
Area
Cost Center
Equipment #
Cost
Is there a way?Maybe you can use the case function but I'm not sure.
SELECT JobNumber, Invoice Code,
CASE Property
WHEN 'Area'
THEN 'Area' AS Area
WHEN 'Cost Center'
THEN 'Cost Center' AS CC
WHEN 'Equipment #'
THEN 'Equipment #' AS EQUIP
ELSE
etc...
END
So you gonna have different ALIAS FLD from the same physical fld.
I repeat that I didn't test it.
If not maybe using crosstable may solve ur problem.
Let me know if it works
Julien
"TCogan" <tcogan@.starcon.org> wrote in message
news:1174668240.221027.226060@.b75g2000hsg.googlegroups.com...
>I am looking for a way to group on a row. I am not sure that this can
> be done, but here is what I have.
> My dataset looks like the following:
> JobNumber Invoice Code Property PropertyValue Cost
> 071201 61556 Area
> 101 10.00
> 071201 61556 Cost Center
> 1101020 10.00
> 071201 61556 Equipment #
> 23R05 10.00
>
> I want my report to look like this:
> 071201
> 61556
> Area
> Cost Center
> Equipment #
> Cost
> Is there a way?
>|||Grouping by row can be done with toggling and visible which uses a + sign to
expand and contract , the same method can be used to display all at once.
Amarnath, MCTS
"TCogan" wrote:
> I am looking for a way to group on a row. I am not sure that this can
> be done, but here is what I have.
> My dataset looks like the following:
> JobNumber Invoice Code Property PropertyValue Cost
> 071201 61556 Area
> 101 10.00
> 071201 61556 Cost Center
> 1101020 10.00
> 071201 61556 Equipment #
> 23R05 10.00
>
> I want my report to look like this:
> 071201
> 61556
> Area
> Cost Center
> Equipment #
> Cost
> Is there a way?
>

Sunday, February 26, 2012

GROUP BY Bit in Integer

I have a table where each row contain a unique individual and it's scrap code. The scrap code is an integer where each of the 32 bits represent a unique scrap cause and each individual may have more than one scrap cause.

As an example: the indidual may be both too heavy and too high

Lets say these two scrap causes are represented by bit 0 and bit 1.

That again converts to the integer value 1 and 2. If both bits are true the value of the integer for that row/individual is 3. I use a support table that contain each bit (representetd as an integer value) and a description.

ScrapCode(ScrapCode, Description)

with upto 32 rows.

With this it is easy to decode the scrap code for a selected individual into separate causes, but that is for one individual.
Now I would like to create a report that count codes based on the unique scrap codes in the scrap code table. In other words I would like to group by each bit in the integer.

Table(Id, date, ScrapCode)

Select Count(*) from Table

WHERE ScrapCode <> 0

Group By "Bit"

Any suggestions?

can u supply an example of the expected outcome.|||

One way to do it is to take advantage of the POWER function, the bitwise '&' operator and a table of numbers. I mocked up the data with this:

declare @.aTable table
( rid integer,
scrapCode integer
)

insert into @.aTable
select iter,
-1000000000 + 2147483646*dbo.rand()
from small_iterator (nolock)

The definition of the SMALL_ITERATOR and DBO.RAND() objects can be found here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

I computed the results with this:

Select iter-1 as [Cause],
Count(*) as[Cause Count]
from @.aTable
inner join small_iterator (nolock)
on iter <= 32
WHERE ScrapCode <> 0
and ( iter < 32 and
ScrapCode & Power (2, iter-1) > 0 or
iter = 32 and
ScrapCode < 0
)
group by iter-1
order by iter-1

/*
Cause Cause Count
-- --
0 16333
1 16494
...
30 16470
31 15336
*/

|||

Found it myself after some trial and error:

Code Snippet

SELECT v.ScrapCode, COUNT(p.Snr) AS NoOfScrap

FROM [table] p, [ScrapCodes] v

WHERE p.date > '2007-04-20'

AND p.Scrapcode <> 0

AND p.ScrapCode & v.ScrapCode <> 0

GROUP BY v.ScrapCode

Output:

1 15

2 250

4 5

8 98

512 23

....

Friday, February 24, 2012

group by

I have table
date,type,price
date and price is changing
i need to group it, to have
max(date), type and price (from the row with max date)
it's no problem to take
max(date) and type bat without the third column, i need all columns
HelpUSE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([date] datetime, type char(10), price money
, PRIMARY KEY (type, [date]))
GO

INSERT INTO myTable99([date],type,price)
SELECT '1/1/2001','bat' ,10.00 UNION ALL
SELECT '1/1/2002','bat' ,20.00 UNION ALL
SELECT '1/1/2003','bat' ,30.00 UNION ALL
SELECT '1/1/2004','bat' ,40.00 UNION ALL
SELECT '1/1/2001','ball',40.00 UNION ALL
SELECT '1/1/2002','ball',30.00 UNION ALL
SELECT '1/1/2003','ball',20.00 UNION ALL
SELECT '1/1/2004','ball',10.00
GO

SELECT l.*
FROM myTable99 AS l
JOIN ( SELECT type, MAX([date]) AS MAX_date
FROM myTable99
GROUP BY type) AS r
ON l.type = r.type
AND l.[date] = r.MAX_date

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Long thanks

Sunday, February 19, 2012

gridview

iam taking first value of label and comparing with rest of values if it is same then i want to change the color of that row.
but what is happening is entire gridview backcolor is changing to red.

i want to change only those rows which matches.

void GridView1_DataBound(object sender, EventArgs e)
{
foreach (GridViewRow gv in GridView1.Rows)
{

Label lbl = (Label)gv.Cells[2].Controls[0].FindControl("Label1");
string t = lbl.Text;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Label lbl4 = (Label)GridView1.Rows[i].Cells[2].Controls[0].FindControl("Label1");
if (t == lbl4.Text)
{
GridView1.Rows[i].Cells[2].BackColor = System.Drawing.Color.Red;

//gv.BackColor = System.Drawing.Color.Red;

}

}

}

}Please, move this to appropriate forum.

Grid pane in the table in enterprise manager

HI
When you right click open table to return all rows, click on the "Show/Hide Grid pane" icon . When you click on any row the first column called "Column" a dropdown shows all the filed names of that particular table.
This feature used to work fine untill recently wherein when I click on the column, I don't get to see all the filed names but now it just shows TableName.* i.e. all columns.
This is the case when I try to create a view in enterprise manager also. When you add a table to create a view, I cannot see all the filed names . I'm not sure what to do , to get the field names to appear? Is it some kind of setting in enterprise manager.
All the info would be appreciated.
Ta
NStop using Enterprise Manager (EM) and start using Query Analyzer (QA)

My own opinion

MOO

EM tends be quirky...|||I do use the Query analyser more than the EM, but it's just a functionality would reappear in the EM. Ta

Friday, March 30, 2012

grouping rows by customer

Grouping Row Border

Wednesday, March 28, 2012

Grouping problem

Grouping on multiple Datasets

Grouping Issue

Monday, March 26, 2012

grouping data...

Wednesday, March 21, 2012

Group Total..

Monday, March 19, 2012

Group Number

group into 1 row

Group ID and ID Column

Group Footers?

Group Footer on Next Page

Friday, March 9, 2012

Group By Row Count And Force Page Break

Group By Row Count And Force Page Break

Group By Row

Sunday, February 26, 2012

GROUP BY Bit in Integer

Friday, February 24, 2012

group by

Sunday, February 19, 2012

gridview

Grid pane in the table in enterprise manager

Firehose mode

Blog Archive

About Me