Dear all,
I have 5 SQL Servers and totally around 100 databases (1 database for 1
client).
Whenever I finish a Report development on my development server, I will need
to repeat the same task on these 100 databases 100 times ?
Although I can copy the Report Definition File from the development server
to these 100 databases' folders, I still need to do the following tasks :
(1)Create 100 new Visual Studio projects
(2)Enter "Start Item" and "Target Server URL" 100 times
(3)Create 100 new Shared Data Sources
(4)Build and Deploy Visual Studio projects 100 times.
Are there any better or more efficient ways to deploy a new Report in a
multiple servers or multiple databases environment ?
Please help me.
Thanks a lot."cpchan" wrote:
> Dear all,
>
> I have 5 SQL Servers and totally around 100 databases (1 database for 1
> client).
>
> Whenever I finish a Report development on my development server, I will need
> to repeat the same task on these 100 databases 100 times ?
> Although I can copy the Report Definition File from the development server
> to these 100 databases' folders, I still need to do the following tasks :
>
> (1)Create 100 new Visual Studio projects
> (2)Enter "Start Item" and "Target Server URL" 100 times
> (3)Create 100 new Shared Data Sources
> (4)Build and Deploy Visual Studio projects 100 times.
>
> Are there any better or more efficient ways to deploy a new Report in a
> multiple servers or multiple databases environment ?
>
> Please help me.
> Thanks a lot.
>
Hi
I am not a reporting services expert, you may find more help in the
reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
You can probably do what you require using multiple configurations for a
single project that deploys onto the different servers:
http://msdn2.microsoft.com/en-us/library/aa179464.aspx
http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
You could probably use the command prompt utilities and write a
parameterised script that will deploy to a server
John|||Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:679E4454-427D-4734-B5BB-7817C3C11BCB@.microsoft.com...
> "cpchan" wrote:
> > Dear all,
> >
> >
> > I have 5 SQL Servers and totally around 100 databases (1 database for 1
> > client).
> >
> >
> >
> > Whenever I finish a Report development on my development server, I will
need
> > to repeat the same task on these 100 databases 100 times ?
> > Although I can copy the Report Definition File from the development
server
> > to these 100 databases' folders, I still need to do the following tasks
:
> >
> >
> > (1)Create 100 new Visual Studio projects
> > (2)Enter "Start Item" and "Target Server URL" 100 times
> > (3)Create 100 new Shared Data Sources
> > (4)Build and Deploy Visual Studio projects 100 times.
> >
> >
> >
> > Are there any better or more efficient ways to deploy a new Report in a
> > multiple servers or multiple databases environment ?
> >
> >
> >
> > Please help me.
> > Thanks a lot.
> >
> Hi
> I am not a reporting services expert, you may find more help in the
> reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
> You can probably do what you require using multiple configurations for a
> single project that deploys onto the different servers:
> http://msdn2.microsoft.com/en-us/library/aa179464.aspx
> http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
> You could probably use the command prompt utilities and write a
> parameterised script that will deploy to a server
> John
Showing posts with label report. Show all posts
Showing posts with label report. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
How to show two dataset with equal & non equal of multiple selection.
Dear Friends,
In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Please advice. Thanks.
warm regards
Rakin
Singapore.
It sounds like you are using RS 2005, since you have multi value report parameters.
On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:
Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value
The IN-filter can automatically deal with multi-value parameters (which represent an array of values).
-- Robert
|||Thanks Robert.
You are right. I am using RS 2005.
Now, I am able to get the value for the first option. ( with IN )
But I could n't find any NOT IN option in the filter for the second set of results ?
Could you please advice for this ?
Regards
Rakin
Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:
E.g. select * from products where ReorderLevel not IN (@.Level)
I also attached a small sample report at the bottom based on the Northwind database.
-- Robert
<?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>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.
The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.
Have any of you encountered anything like this?
Niall
How to show two dataset with equal & non equal of multiple selection.
Dear Friends,
In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Please advice. Thanks.
warm regards
Rakin
Singapore.
It sounds like you are using RS 2005, since you have multi value report parameters.
On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:
Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value
The IN-filter can automatically deal with multi-value parameters (which represent an array of values).
-- Robert
|||Thanks Robert.
You are right. I am using RS 2005.
Now, I am able to get the value for the first option. ( with IN )
But I could n't find any NOT IN option in the filter for the second set of results ?
Could you please advice for this ?
Regards
Rakin
Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:
E.g. select * from products where ReorderLevel not IN (@.Level)
I also attached a small sample report at the bottom based on the Northwind database.
-- Robert
<?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>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.
The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.
Have any of you encountered anything like this?
Niall
sqlHow to show the current datasource in a report?
I'm using Reporting Services 2000 on SQL Server 2000. I want to show the currently used datasource to avoid troubles when switching between test data and productive data. Is it possible to show the name and/or the description of the used datasource?
Thanks for your answer,
AndreasYou could try selecting @.@.servername, db_name()
along with your query.|||
Unfortunalty servername and database name ist not helpful for the user and works only on MS Sql Server. Isn't there a possibility to get the abstract name and description of the current configured datasource?
Thanks for your answer,
Andreas
How to show table header, footer even when the dataset is empty?
hi,
Just got a question about the report. When my report does not get any data from database, like dataset is empty, it will show nothing. This is reasonable. But what our customers want is, they still need to show the table header, footer, but no data.
Thanks.
How to show table header, footer even when the dataset is empty?
hi,
Just got a question about the report. When my report does not get any
data from database, like dataset is empty, it will show nothing. This
is reasonable. But what our customers want is, they still need to show
the table header, footer, but no data.
ThanksHi Nick,
What is in the table headers and footers...dataset field values only
or possibly the header or footer has text? If you do have text in the
header or footer and they don't appear, check the NoRows property for
the table...any text or string value expression you set will cause the
table to not appear in favor of the expression you set. Text in
headers or footers does display, by default, even if a table has no
records...so you can also check for condittional logic that is hiding
the header or footer.
Maybe you can explain what your table headers and footers have in them?
If it's all dataset field vlaues-what shoud appear?
MattA
Reporting Services Newsletter at www.reportarchitex.com
Just got a question about the report. When my report does not get any
data from database, like dataset is empty, it will show nothing. This
is reasonable. But what our customers want is, they still need to show
the table header, footer, but no data.
ThanksHi Nick,
What is in the table headers and footers...dataset field values only
or possibly the header or footer has text? If you do have text in the
header or footer and they don't appear, check the NoRows property for
the table...any text or string value expression you set will cause the
table to not appear in favor of the expression you set. Text in
headers or footers does display, by default, even if a table has no
records...so you can also check for condittional logic that is hiding
the header or footer.
Maybe you can explain what your table headers and footers have in them?
If it's all dataset field vlaues-what shoud appear?
MattA
Reporting Services Newsletter at www.reportarchitex.com
how to show summary numbers inside data
Hello, I am very new to Rpt Svcs, and am stuck on a report I have to build.
I have a simple report that shows agent code, state, and zipcode for hte
clients of a given agent. I need to modify this report to show a summary of
how many clients are in each zipcode in each state for each agent.
How do I design my report to allow a first glance to be the agent(s), then
drill down to a state-->count of clients/state rpt, then drill down to number
of clients per zip in that state, then drill down to show the full data for
that zip?
is this a code issue or can rpt svc group my results and display the data
the way I want it?
thank you in advance!!No and yes.
Sort the data by agent, zipcode and client.
Add group for agent and for zipcode.
In the footer of each group, add a field to capture count. For the
expression:
=Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
the result set.
After that, get some beer and sandwiches and do what I do, chase your golf
balls around in the woods all day.
-Tim
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> build.
> I have a simple report that shows agent code, state, and zipcode for hte
> clients of a given agent. I need to modify this report to show a summary
> of
> how many clients are in each zipcode in each state for each agent.
> How do I design my report to allow a first glance to be the agent(s), then
> drill down to a state-->count of clients/state rpt, then drill down to
> number
> of clients per zip in that state, then drill down to show the full data
> for
> that zip?
> is this a code issue or can rpt svc group my results and display the data
> the way I want it?
> thank you in advance!!|||Hey pretty simple, can be done using toggle visibility.. If you want some
samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
after seeing that I think you can build your reports.
Amarnath
"Tim Dot NoSpam" wrote:
> No and yes.
> Sort the data by agent, zipcode and client.
> Add group for agent and for zipcode.
> In the footer of each group, add a field to capture count. For the
> expression:
> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
> the result set.
> After that, get some beer and sandwiches and do what I do, chase your golf
> balls around in the woods all day.
> -Tim
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> > build.
> > I have a simple report that shows agent code, state, and zipcode for hte
> > clients of a given agent. I need to modify this report to show a summary
> > of
> > how many clients are in each zipcode in each state for each agent.
> > How do I design my report to allow a first glance to be the agent(s), then
> > drill down to a state-->count of clients/state rpt, then drill down to
> > number
> > of clients per zip in that state, then drill down to show the full data
> > for
> > that zip?
> > is this a code issue or can rpt svc group my results and display the data
> > the way I want it?
> > thank you in advance!!
>
>|||Rule Number 1. Never respond to peoples' requests for assistance after
beers. Actually, I still think my advice was sound...
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:FCE0E20C-2D62-4277-BBBC-FD1437165F8C@.microsoft.com...
> Hey pretty simple, can be done using toggle visibility.. If you want some
> samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
> after seeing that I think you can build your reports.
> Amarnath
>
> "Tim Dot NoSpam" wrote:
>> No and yes.
>> Sort the data by agent, zipcode and client.
>> Add group for agent and for zipcode.
>> In the footer of each group, add a field to capture count. For the
>> expression:
>> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client
>> in
>> the result set.
>> After that, get some beer and sandwiches and do what I do, chase your
>> golf
>> balls around in the woods all day.
>> -Tim
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
>> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
>> > build.
>> > I have a simple report that shows agent code, state, and zipcode for
>> > hte
>> > clients of a given agent. I need to modify this report to show a
>> > summary
>> > of
>> > how many clients are in each zipcode in each state for each agent.
>> > How do I design my report to allow a first glance to be the agent(s),
>> > then
>> > drill down to a state-->count of clients/state rpt, then drill down to
>> > number
>> > of clients per zip in that state, then drill down to show the full data
>> > for
>> > that zip?
>> > is this a code issue or can rpt svc group my results and display the
>> > data
>> > the way I want it?
>> > thank you in advance!!
>>
I have a simple report that shows agent code, state, and zipcode for hte
clients of a given agent. I need to modify this report to show a summary of
how many clients are in each zipcode in each state for each agent.
How do I design my report to allow a first glance to be the agent(s), then
drill down to a state-->count of clients/state rpt, then drill down to number
of clients per zip in that state, then drill down to show the full data for
that zip?
is this a code issue or can rpt svc group my results and display the data
the way I want it?
thank you in advance!!No and yes.
Sort the data by agent, zipcode and client.
Add group for agent and for zipcode.
In the footer of each group, add a field to capture count. For the
expression:
=Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
the result set.
After that, get some beer and sandwiches and do what I do, chase your golf
balls around in the woods all day.
-Tim
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> build.
> I have a simple report that shows agent code, state, and zipcode for hte
> clients of a given agent. I need to modify this report to show a summary
> of
> how many clients are in each zipcode in each state for each agent.
> How do I design my report to allow a first glance to be the agent(s), then
> drill down to a state-->count of clients/state rpt, then drill down to
> number
> of clients per zip in that state, then drill down to show the full data
> for
> that zip?
> is this a code issue or can rpt svc group my results and display the data
> the way I want it?
> thank you in advance!!|||Hey pretty simple, can be done using toggle visibility.. If you want some
samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
after seeing that I think you can build your reports.
Amarnath
"Tim Dot NoSpam" wrote:
> No and yes.
> Sort the data by agent, zipcode and client.
> Add group for agent and for zipcode.
> In the footer of each group, add a field to capture count. For the
> expression:
> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
> the result set.
> After that, get some beer and sandwiches and do what I do, chase your golf
> balls around in the woods all day.
> -Tim
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> > build.
> > I have a simple report that shows agent code, state, and zipcode for hte
> > clients of a given agent. I need to modify this report to show a summary
> > of
> > how many clients are in each zipcode in each state for each agent.
> > How do I design my report to allow a first glance to be the agent(s), then
> > drill down to a state-->count of clients/state rpt, then drill down to
> > number
> > of clients per zip in that state, then drill down to show the full data
> > for
> > that zip?
> > is this a code issue or can rpt svc group my results and display the data
> > the way I want it?
> > thank you in advance!!
>
>|||Rule Number 1. Never respond to peoples' requests for assistance after
beers. Actually, I still think my advice was sound...
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:FCE0E20C-2D62-4277-BBBC-FD1437165F8C@.microsoft.com...
> Hey pretty simple, can be done using toggle visibility.. If you want some
> samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
> after seeing that I think you can build your reports.
> Amarnath
>
> "Tim Dot NoSpam" wrote:
>> No and yes.
>> Sort the data by agent, zipcode and client.
>> Add group for agent and for zipcode.
>> In the footer of each group, add a field to capture count. For the
>> expression:
>> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client
>> in
>> the result set.
>> After that, get some beer and sandwiches and do what I do, chase your
>> golf
>> balls around in the woods all day.
>> -Tim
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
>> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
>> > build.
>> > I have a simple report that shows agent code, state, and zipcode for
>> > hte
>> > clients of a given agent. I need to modify this report to show a
>> > summary
>> > of
>> > how many clients are in each zipcode in each state for each agent.
>> > How do I design my report to allow a first glance to be the agent(s),
>> > then
>> > drill down to a state-->count of clients/state rpt, then drill down to
>> > number
>> > of clients per zip in that state, then drill down to show the full data
>> > for
>> > that zip?
>> > is this a code issue or can rpt svc group my results and display the
>> > data
>> > the way I want it?
>> > thank you in advance!!
>>
How to show SUBREPORT when no data is returned.
I have a subreport within the details section of my main report. This is enclosed by a single group. When I preview the subreport alone, it diplays the subreport correctly with no data (meaning it still displays the header and footer without a detail section). When the subreport is run within the main report and no subreport data is returned the subreport does not show.
Based on other threads I understand that the default functionality is to display headers and footers when no data is returned. This appears accurate except on a subreport.
Would the "no rows"-property help you? there you can define a message that is shown when the subreport has no rows..|||I fixed this by returning a row of nulls in my return dataset when no records where found. Works.
How to show SUBREPORT when no data is returned.
I have a subreport within the details section of my main report. This is enclosed by a single group. When I preview the subreport alone, it diplays the subreport correctly with no data (meaning it still displays the header and footer without a detail section). When the subreport is run within the main report and no subreport data is returned the subreport does not show.
Based on other threads I understand that the default functionality is to display headers and footers when no data is returned. This appears accurate except on a subreport.
Would the "no rows"-property help you? there you can define a message that is shown when the subreport has no rows..|||I fixed this by returning a row of nulls in my return dataset when no records where found. Works.
How to show rows of dataset as columns in report
Hi,
I have a dataset whose rows need to shown as columns of a table in report.
Is it possible to do so?
Please help
regards,
SachinCan you use a matrix instead of a table?
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> Hi,
> I have a dataset whose rows need to shown as columns of a table in report.
> Is it possible to do so?
> Please help
> regards,
> Sachin|||Hi,
I am quite new to reporting services.
Wll matrix solve my problem?
Please help.
regards,
Sachin.
"Steve MunLeeuw" wrote:
> Can you use a matrix instead of a table?
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> > Hi,
> >
> > I have a dataset whose rows need to shown as columns of a table in report.
> > Is it possible to do so?
> >
> > Please help
> > regards,
> > Sachin
>
>|||I think that it will. The Adventure Works Sample Reports that ships with
SSRS 2005 has a Company Sales report that demonstrates the use of the
matrix. It has Order Year and Order Qtr as columns across the top.
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:334F642D-F816-46FD-BBF4-7F8D1E0B8276@.microsoft.com...
> Hi,
> I am quite new to reporting services.
> Wll matrix solve my problem?
> Please help.
> regards,
> Sachin.
>
> "Steve MunLeeuw" wrote:
>> Can you use a matrix instead of a table?
>> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
>> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
>> > Hi,
>> >
>> > I have a dataset whose rows need to shown as columns of a table in
>> > report.
>> > Is it possible to do so?
>> >
>> > Please help
>> > regards,
>> > Sachin
>>sql
I have a dataset whose rows need to shown as columns of a table in report.
Is it possible to do so?
Please help
regards,
SachinCan you use a matrix instead of a table?
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> Hi,
> I have a dataset whose rows need to shown as columns of a table in report.
> Is it possible to do so?
> Please help
> regards,
> Sachin|||Hi,
I am quite new to reporting services.
Wll matrix solve my problem?
Please help.
regards,
Sachin.
"Steve MunLeeuw" wrote:
> Can you use a matrix instead of a table?
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
> > Hi,
> >
> > I have a dataset whose rows need to shown as columns of a table in report.
> > Is it possible to do so?
> >
> > Please help
> > regards,
> > Sachin
>
>|||I think that it will. The Adventure Works Sample Reports that ships with
SSRS 2005 has a Company Sales report that demonstrates the use of the
matrix. It has Order Year and Order Qtr as columns across the top.
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:334F642D-F816-46FD-BBF4-7F8D1E0B8276@.microsoft.com...
> Hi,
> I am quite new to reporting services.
> Wll matrix solve my problem?
> Please help.
> regards,
> Sachin.
>
> "Steve MunLeeuw" wrote:
>> Can you use a matrix instead of a table?
>> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
>> news:5E1EAF65-33C1-4D19-B230-D4826ACA3BA7@.microsoft.com...
>> > Hi,
>> >
>> > I have a dataset whose rows need to shown as columns of a table in
>> > report.
>> > Is it possible to do so?
>> >
>> > Please help
>> > regards,
>> > Sachin
>>sql
How to show progress bar in report column
Hi,
I have to show particular item progress status in a report column in graphical format.
For example, if item finish state is 60% then, column should display status in progress bar or similar to that.
Is this possible?
Any help would be appreciated.
Thanks
Hi,
you either make a graph showing 60 in the scale of 100 or use any thrid party tool from dundas to show gauges (or a different object)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
sqlhow to show one record per one page
I am using reporting services to build a report to show customer's balance statements. I need each page only show one customer and related transactions and balance etc.
Now it shows several customers' balance in one page, how can i change it to show only one record per one page, and also one customer per one page when print it out.
how can i achieve that?
cheers
place all your controls in a list controlin the properties for the list control
group by customer
check the "page brake before" option
How to show new Columns when user clicks on a cell in a report ?
Hi,
I have a requirement in my project which specifies that when the user clicks
on the drill down(or a cell), the report should show new columns which
basically describes the details of the drilldown action. Is it possible to
show new columns on drill down ? Currenlty Iam using Jump to Report
navigation property to show the details in a new report. Basically I want
new columns(which describe the cell clicked, for eg if the cell clicked is
total of all payments, I want to show details of the payment when user
clicks on it ) when user clicks on the cell. If I use drill down then all
detail columns are blank befor drill down action. Can anyone help me in
doing this ?
--
Thanks,
RKOn Oct 16, 3:59 pm, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> I have a requirement in my project which specifies that when the user clicks
> on the drill down(or a cell), the report should show new columns which
> basically describes the details of the drilldown action. Is it possible to
> show new columns on drill down ? Currenlty Iam using Jump to Report
> navigation property to show the details in a new report. Basically I want
> new columns(which describe the cell clicked, for eg if the cell clicked is
> total of all payments, I want to show details of the payment when user
> clicks on it ) when user clicks on the cell. If I use drill down then all
> detail columns are blank befor drill down action. Can anyone help me in
> doing this ?
> --
> Thanks,
> RK
Hi!
whene you use navigation and jum to report ,you can see parameters in
front of this,you must difine parameter in the second report and in
first report in property -navigation-parameter ,you choose your
parameter and fill your parameter with one field of dataset .
Regards.
I have a requirement in my project which specifies that when the user clicks
on the drill down(or a cell), the report should show new columns which
basically describes the details of the drilldown action. Is it possible to
show new columns on drill down ? Currenlty Iam using Jump to Report
navigation property to show the details in a new report. Basically I want
new columns(which describe the cell clicked, for eg if the cell clicked is
total of all payments, I want to show details of the payment when user
clicks on it ) when user clicks on the cell. If I use drill down then all
detail columns are blank befor drill down action. Can anyone help me in
doing this ?
--
Thanks,
RKOn Oct 16, 3:59 pm, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> I have a requirement in my project which specifies that when the user clicks
> on the drill down(or a cell), the report should show new columns which
> basically describes the details of the drilldown action. Is it possible to
> show new columns on drill down ? Currenlty Iam using Jump to Report
> navigation property to show the details in a new report. Basically I want
> new columns(which describe the cell clicked, for eg if the cell clicked is
> total of all payments, I want to show details of the payment when user
> clicks on it ) when user clicks on the cell. If I use drill down then all
> detail columns are blank befor drill down action. Can anyone help me in
> doing this ?
> --
> Thanks,
> RK
Hi!
whene you use navigation and jum to report ,you can see parameters in
front of this,you must difine parameter in the second report and in
first report in property -navigation-parameter ,you choose your
parameter and fill your parameter with one field of dataset .
Regards.
How to show multiple main reports with their subreports one set by
Hi,
I have made a SQL Reporting Services report with the subreport. I need
my report to show multiple records with their subreport at the same time.
At now, I can generate multiple main reports altogether and then they are
followed by the subreport with multiple subreport details. It's just like
the following:
1st Page: 1st record in the Main report
2nd Page: 2nd record in the Main report
3rd Page: 1st Subreport detail (use Table object)
2nd Subreport detail (use Table object)
Inside the table of the 3rd page, it shows the 1st
Subreport details and then 2nd Subreport details.
What I actually need is I want my report to show one record in the main
report and then followed by the corresponding subreport details, then next
page for the next record in the main report and then followed by the
corresponding subreport details.
1st Page: 1st record in the Main report
2nd Page: 1st Subreport detail
3rd Page: 2nd record in the Main report
4th Page: 2nd Subreport detail
Is there anyone who can help me to solve this problem ?
Thanks in advance.
RogerHave you tried putting your subreport inside a table cell? The table could
contain your main rows and your subreports inside the same table as
repeating elements.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:17AC53EB-D966-476E-8B86-30B0864DE28B@.microsoft.com...
> Hi,
> I have made a SQL Reporting Services report with the subreport. I
> need
> my report to show multiple records with their subreport at the same time.
> At now, I can generate multiple main reports altogether and then they are
> followed by the subreport with multiple subreport details. It's just
> like
> the following:
> 1st Page: 1st record in the Main report
> 2nd Page: 2nd record in the Main report
> 3rd Page: 1st Subreport detail (use Table object)
> 2nd Subreport detail (use Table object)
> Inside the table of the 3rd page, it shows the 1st
> Subreport details and then 2nd Subreport details.
> What I actually need is I want my report to show one record in the
> main
> report and then followed by the corresponding subreport details, then next
> page for the next record in the main report and then followed by the
> corresponding subreport details.
> 1st Page: 1st record in the Main report
> 2nd Page: 1st Subreport detail
> 3rd Page: 2nd record in the Main report
> 4th Page: 2nd Subreport detail
> Is there anyone who can help me to solve this problem ?
> Thanks in advance.
> Roger
>
I have made a SQL Reporting Services report with the subreport. I need
my report to show multiple records with their subreport at the same time.
At now, I can generate multiple main reports altogether and then they are
followed by the subreport with multiple subreport details. It's just like
the following:
1st Page: 1st record in the Main report
2nd Page: 2nd record in the Main report
3rd Page: 1st Subreport detail (use Table object)
2nd Subreport detail (use Table object)
Inside the table of the 3rd page, it shows the 1st
Subreport details and then 2nd Subreport details.
What I actually need is I want my report to show one record in the main
report and then followed by the corresponding subreport details, then next
page for the next record in the main report and then followed by the
corresponding subreport details.
1st Page: 1st record in the Main report
2nd Page: 1st Subreport detail
3rd Page: 2nd record in the Main report
4th Page: 2nd Subreport detail
Is there anyone who can help me to solve this problem ?
Thanks in advance.
RogerHave you tried putting your subreport inside a table cell? The table could
contain your main rows and your subreports inside the same table as
repeating elements.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:17AC53EB-D966-476E-8B86-30B0864DE28B@.microsoft.com...
> Hi,
> I have made a SQL Reporting Services report with the subreport. I
> need
> my report to show multiple records with their subreport at the same time.
> At now, I can generate multiple main reports altogether and then they are
> followed by the subreport with multiple subreport details. It's just
> like
> the following:
> 1st Page: 1st record in the Main report
> 2nd Page: 2nd record in the Main report
> 3rd Page: 1st Subreport detail (use Table object)
> 2nd Subreport detail (use Table object)
> Inside the table of the 3rd page, it shows the 1st
> Subreport details and then 2nd Subreport details.
> What I actually need is I want my report to show one record in the
> main
> report and then followed by the corresponding subreport details, then next
> page for the next record in the main report and then followed by the
> corresponding subreport details.
> 1st Page: 1st record in the Main report
> 2nd Page: 1st Subreport detail
> 3rd Page: 2nd record in the Main report
> 4th Page: 2nd Subreport detail
> Is there anyone who can help me to solve this problem ?
> Thanks in advance.
> Roger
>
How to show line numbers in the first column of the report
Hi,
I want to show line numbers on report page in the first column of my
report. I am working on microsoft reporting services 2000.
How i can do this.
Regards
DineshOn Mar 9, 11:59 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi,
> I want to show line numbers on report page in the first column of my
> report. I am working on microsoft reporting services 2000.
> How i can do this.
> Regards
> Dinesh
This should work: =RowNumber("DataSourceName") as an expression in the
Value property of the first column.
Regards,
Enrique Martinez
Sr. SQL Server Developer
I want to show line numbers on report page in the first column of my
report. I am working on microsoft reporting services 2000.
How i can do this.
Regards
DineshOn Mar 9, 11:59 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi,
> I want to show line numbers on report page in the first column of my
> report. I am working on microsoft reporting services 2000.
> How i can do this.
> Regards
> Dinesh
This should work: =RowNumber("DataSourceName") as an expression in the
Value property of the first column.
Regards,
Enrique Martinez
Sr. SQL Server Developer
How to Show Last Admit Date
I have a report that shows patient information, such as patient number, name, and admit date. It also has a date parameter. I enter in the date and it shows all the patients admitted that day. I would like to show the last date the patient was admitted before the date I ran the report for.
For example, if I run the report for 12/20/06, I want to see the patients that were admitted that day and their info like name, and patient number. I would also like to see the last date they were admitted.
Could you just do a subquery to get the max date for each patient from before the date parameter? Something like this:
select ...
(select max(admit_date) from patient_admittances pa where p.patient_id = pa.patient_id and admit_date < @.RunDate)
from patients p
inner join ...
Jarret
How to show group header/footer again after hiding them?
I have started to work on a report with table data region, and there
was need to sort the records withing a group, so I created a new
group, assigned the appropriate sort order, and then cleared "Include
group header" and "Include group footer" checkboxes. Because of this
the group did not show up in the layout window. But now I want to make
some changes to the sort, and I could not find a way to get to the
group definition again. Please help. Thanks.Found the solution. From Table Properties, go to Groups tab, and then
you can manage the groups there.
was need to sort the records withing a group, so I created a new
group, assigned the appropriate sort order, and then cleared "Include
group header" and "Include group footer" checkboxes. Because of this
the group did not show up in the layout window. But now I want to make
some changes to the sort, and I could not find a way to get to the
group definition again. Please help. Thanks.Found the solution. From Table Properties, go to Groups tab, and then
you can manage the groups there.
How to show empty rows in crystal report when data reaches to EOF
I have designed a report in Crystal Report .Net with tabular Layout. I want to have table layout for empty rows in last page. But Report Detail Section is repeated only for record count, no more. How can I show blank rows in report?At the Report footer have a table structure with empty rows
Otherwise post some sample data and the format you want
Otherwise post some sample data and the format you want
How to show duplicates in reports
I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.
The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:
This is what we currently get:
| Catagory | Class | Detail |
| Type 1 | A | Item 1 |
| B | Item 2 | |
| Item 3 | ||
| Type 2 | A | Item 4 |
| B | Item 5 |
This is what we want:
| Catagory | Class | Detail |
| Type 1 | A | Item 1 |
| Type 1 | B | Item 2 |
| Type 1 | B | Item 3 |
| Type 2 | A | Item 4 |
| Type 2 | B | Item 5 |
I'm sure its something simple, but we just cant seem to find the solution,
Thanks
Darin
Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.
|||Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.
I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.
|||Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.
My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.
Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?
|||Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.
Here are the defintion of the Textbox properties:
=========================================
Textbox
The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:
Attributes/Elements
Name
Card
Type
Description
Value
1
Expression
(Variant)
An expression, the value of which is displayed in the text-box.
This can be a constant expression for constant labels.
CanGrow
0-1
Boolean
Indicates the Textbox size can increase to accommodate the contents
CanShrink
0-1
Boolean
Indicates the Textbox size can decrease to match the contents
HideDuplicates
0-1
String
Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.
Ignored in matrix subtotals.
ToggleImage
0-1
Element
Indicates the initial state of a toggling image should one be displayed as a part of the textbox.
UserSort
0-1
Element
Indicates an end-user sort control should be displayed as a part of this textbox in the UI.
DataElementStyle
0-1
Enum
Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.
Regards
How to show duplicates in reports
I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.
The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:
This is what we currently get:
| Catagory | Class | Detail |
| Type 1 | A | Item 1 |
| B | Item 2 | |
| Item 3 | ||
| Type 2 | A | Item 4 |
| B | Item 5 |
This is what we want:
| Catagory | Class | Detail |
| Type 1 | A | Item 1 |
| Type 1 | B | Item 2 |
| Type 1 | B | Item 3 |
| Type 2 | A | Item 4 |
| Type 2 | B | Item 5 |
I'm sure its something simple, but we just cant seem to find the solution,
Thanks
Darin
Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.
|||Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.
I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.
|||Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.
My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.
Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?
|||Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.
Here are the defintion of the Textbox properties:
=========================================
Textbox
The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:
Attributes/Elements
Name
Card
Type
Description
Value
1
Expression
(Variant)
An expression, the value of which is displayed in the text-box.
This can be a constant expression for constant labels.
CanGrow
0-1
Boolean
Indicates the Textbox size can increase to accommodate the contents
CanShrink
0-1
Boolean
Indicates the Textbox size can decrease to match the contents
HideDuplicates
0-1
String
Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.
Ignored in matrix subtotals.
ToggleImage
0-1
Element
Indicates the initial state of a toggling image should one be displayed as a part of the textbox.
UserSort
0-1
Element
Indicates an end-user sort control should be displayed as a part of this textbox in the UI.
DataElementStyle
0-1
Enum
Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.
Subscribe to:
Posts (Atom)