Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts

Monday, March 19, 2012

Collapsible reports

Hi,

I have a Dataset with repetitive data and i want to group all data based on a particular field and have it disaplayed as collapsible rows on a report.

BTW i am generating reports dynamically in my APP.Any ideas on how to proceed?

If I understand correctly, you want to group your data and have the details toggled by the group. To do that, you just need to add a table group on the field, set the visibility properties on the detail row of the table - 1. it's toggled by a textbox in the group header 2. the initial visibility to be hidden if you want to start as collapsed.|||

Hi,

Thanks 4 ur post wang...

You understood my question exactly...

Do u want me to add one more table to the Report?

could you please be more elaborate as to what needs to be done?

|||

No, you don't need another table. Here is a sample report to show how to do this:

<?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="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>30d8ee62-a72d-48fa-ad77-66fdebc3f620</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>
<Table Name="table1">
<DataSetName>DataSet1</DataSetName>
<Top>0.625in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>5</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!CategoryID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>2</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 />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<rd:DefaultName>ProductName</rd:DefaultName>
<ZIndex>1</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!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<rd:DefaultName>UnitPrice</rd:DefaultName>
<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!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>textbox6</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>8</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>Category ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Unit Price</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>3.25in</Height>
</Body>
<rd:ReportID>87afdd90-7b33-49bc-8880-0df212c6637a</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT TOP 10 CategoryID, ProductName, UnitPrice
FROM Products</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.50001in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<PageFooter>
<Height>0.375in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>
<TopMargin>1in</TopMargin>
</Report>

Posted by Laree at 6:27 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Sunday, March 11, 2012

code to return a dataset

Our database is normalized to the point that I need to build a dataset to
render a report from. The code to build the dataset has logic that I would
like to keep in a custom assembly. What is the best way to call that code
and have it pass the dataset back for use by the report?
Thanks,
ShawnYou'd make life alot easier for yourself if you build the code using a
stored procedure... Any reason not to? You can encrypt it if that is your
concern.
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> Our database is normalized to the point that I need to build a dataset to
> render a report from. The code to build the dataset has logic that I
> would
> like to keep in a custom assembly. What is the best way to call that code
> and have it pass the dataset back for use by the report?
> Thanks,
> Shawn|||Thanks, but the users that will be implementing the reports won't have the
ability to make stored procedures. Is there another way?
"Mary Bray [MVP]" wrote:
> You'd make life alot easier for yourself if you build the code using a
> stored procedure... Any reason not to? You can encrypt it if that is your
> concern.
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
> news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> > Our database is normalized to the point that I need to build a dataset to
> > render a report from. The code to build the dataset has logic that I
> > would
> > like to keep in a custom assembly. What is the best way to call that code
> > and have it pass the dataset back for use by the report?
> >
> > Thanks,
> > Shawn
>
>
Posted by Laree at 8:30 PM 0 comments
Labels: , , , , , , , , , , , , , ,

Wednesday, March 7, 2012

Code a function to return a dataset in which there are two tables and relationship

I used a function to create dataset as below:

PublicFunction GetSQLDataSet(ByVal SQLAsString)As DataSet

.....

MyConnection =

New SqlConnection(MyConnectionString)

MyCommand =

New SqlCommand(SQL, MyConnection)

MyDataSet =

New DataSet

MySQLDataAdapter =

New SqlDataAdapter(MyCommand)

MySQLDataAdapter.Fill(MyDataSet)

.....

End function

It works fine.

How to code a function to return a dataset in which there are two tables and relationship?

You can use a SqlCommand which returns 2 tables--that can be a stored procedure which returns 2 tables, or a batch contains 2 SELECT commands seperated by ';' (e.g. "select * from t1; select * from t2). Then when you use the SqlDataAdapter to fill the DataSet, the DataSet will contain 2 tables. Then you can add relationships between the 2 tables as you like, you can refer to:

http://msdn2.microsoft.com/en-us/library/ay82azad.aspx

Posted by Laree at 1:57 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)