Showing posts with label sort. Show all posts
Showing posts with label sort. Show all posts

Sunday, March 25, 2012

Adding a line to a chart

Hi,
Does anyone know how to manually add a line to a chart in whatever location
I want without using any sort of dataset? Or is this even possible?
ThanksIt depends.
(1) If you do NOT have any dynamic series groupings in the chart, it is
easy. Just add another item in the "Values" section of the chart properties
dialog / data tab. For the value you can use a constant expression like
=10000 to get a line at y-axis value 10000.
(2) If you have a series grouping (e.g. group by year), then you would have
one "Value" (i.e. static series) for the actual value to be shown and you
would add a second "Value" for the line. Since you would want this line to
only show up once even though there are several series groups at runtime,
you have use an expression to show a constant value only for one particular
grouping value and Nothing for all other grouping values of the series
grouping.
You will find an example at the bottom of this posting based on Northwind
data. The example will need some careful study to see how to make the
constant line appear only once in the chart. Note: the additional legend
entries cannot be removed. The only alternative here is to not draw any
legend on the chart and use an expression to show certain datapoint labels
inside the chart e.g. only on the first datapoint of every series.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
All code samples are provided "AS IS" without warranty of any kind, either
express or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose.
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:30C566FB-2146-484B-8AD2-6D48D4E0BF9D@.microsoft.com...
> Hi,
> Does anyone know how to manually add a line to a chart in whatever
location
> I want without using any sort of dataset? Or is this even possible?
> Thanks
===========================================
<?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>
<Chart Name="Sales">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>BottomCenter</Position>
<Layout>Table</Layout>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=iif(1997 = Year(Fields!OrderDate.Value), 130000,
Nothing)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Sales_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Month(Fields!OrderDate.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Sales_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=""</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>="Sales " & Year(Fields!OrderDate.Value)</Label>
</StaticMember>
<StaticMember>
<Label>=iif(1997 = Year(Fields!OrderDate.Value), "Target", "
")</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY Orders.OrderDate</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>

Saturday, February 25, 2012

Add fields vs. Join new table

Is there any sort of rule of thumb for this situation?
I have a fairly large table in my database (currently 21 fields with
over 800,000 records and growing) that is selected from, inserted to and
updated fairly often. I am adding some new features and cant decide if
it would be more efficient to add 2 fields to this table (of which 97%
of records would be null in these 2 fields) or create a new table that
would need 4 fields but only need records for the 3% and join to 1st
table for queries and reports.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!>> Is there any sort of rule of thumb for this situation?
Yes, determine whether these potential columns are really attributes of the
entity type, which the table represents. Based on your narrative, since you
have a mostly NULLs, it is reasonable to consider them as inapplicable
attributes. So they must be moved to another table. This would give you the
logical solution.
Btw, in some cases certain designers trivialize logical consequences and pay
more attention to space and performance related considerations. The myopic
justification is such cases is generally focused on the reduction in the
number of joins as an aid in performance improvements.
Anith|||I think you should have a separate table for those fields and join the 1st
table for reports and queries.
"Debbie" wrote:

> Is there any sort of rule of thumb for this situation?
> I have a fairly large table in my database (currently 21 fields with
> over 800,000 records and growing) that is selected from, inserted to and
> updated fairly often. I am adding some new features and cant decide if
> it would be more efficient to add 2 fields to this table (of which 97%
> of records would be null in these 2 fields) or create a new table that
> would need 4 fields but only need records for the 3% and join to 1st
> table for queries and reports.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

Sunday, February 19, 2012

Add blank lines after end of data on group

I have a report that looks sort of like:
Route: 123
Name: Jim
Name: Sue
Name: Mary
Route: 456
Name: Monica
Name: Steve
which page breaks on "Route". What i need is to have blank lines to
fill to the end of the page like:
Route: 123
Name: Jim
Name: Sue
Name: Mary
Name:
Name:
Name:
Name:
Name:
Name:
Thank you in advance!
Cheers,
SteveOn Dec 4, 4:18 pm, sfp <stevenprev...@.gmail.com> wrote:
> I have a report that looks sort of like:
> Route: 123
> Name: Jim
> Name: Sue
> Name: Mary
> Route: 456
> Name: Monica
> Name: Steve
> which page breaks on "Route". What i need is to have blank lines to
> fill to the end of the page like:
> Route: 123
> Name: Jim
> Name: Sue
> Name: Mary
> Name:
> Name:
> Name:
> Name:
> Name:
> Name:
> Thank you in advance!
> Cheers,
> Steve
Unfortunately, this type of behavior is not supported; however, you
could try using a while loop and determining the number of records per
page in T-SQL (the stored procedure/query that is sourcing the report)
and then return (x - known records per Route) as null/blank rows,
where x is the number of records that will fit on a page when printed/
etc based on your desired export option. Another much less desirable
option would be to modify the report after the fact using a
custom .NET application that utilizes StreamReader, StreamWriter, etc.
With this option, you can control extra text down to the character;
however, this a good deal of work and most likely is not the best
option for you situation. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi
Heres another tip to do what you want , I assume you are using a
table. Place another column in your table and use =rownumber(nothing).
When you render the report, you'll see the number of rows which are
created prior to the page break. Lets say 10 rows are created. Now
write a stored proc or a query , something like this
Declare @.counter int
Declare @.Temp table (rowno int)
Set @.counter=0
While @.Counter<10
Begin
Insert into @.Temp
Select @.Counter
Set @.counter = @.Counter + 1
End
Select * from @.Temp
Now create another table object under the one you have created, with
one column , no header or footer and the above data source. Hardcode
the value "Name:" in the single column . That should do it. Please be
aware that I have not tested the code I have written.
Cheers
Shai
On Dec 5, 11:11 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Dec 4, 4:18 pm, sfp <stevenprev...@.gmail.com> wrote:
>
>
> > I have a report that looks sort of like:
> > Route: 123
> > Name: Jim
> > Name: Sue
> > Name: Mary
> > Route: 456
> > Name: Monica
> > Name: Steve
> > which page breaks on "Route". What i need is to have blank lines to
> > fill to the end of the page like:
> > Route: 123
> > Name: Jim
> > Name: Sue
> > Name: Mary
> > Name:
> > Name:
> > Name:
> > Name:
> > Name:
> > Name:
> > Thank you in advance!
> > Cheers,
> > Steve
> Unfortunately, this type of behavior is not supported; however, you
> could try using a while loop and determining the number of records per
> page in T-SQL (the stored procedure/query that is sourcing the report)
> and then return (x - known records per Route) as null/blank rows,
> where x is the number of records that will fit on a page when printed/
> etc based on your desired export option. Another much less desirable
> option would be to modify the report after the fact using a
> custom .NET application that utilizes StreamReader, StreamWriter, etc.
> With this option, you can control extra text down to the character;
> however, this a good deal of work and most likely is not the best
> option for you situation. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -