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>
Showing posts with label manually. Show all posts
Showing posts with label manually. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Adding "All" value to parameter field
Hi,
I just want to know if is it possible to add a "ALL" value to the drop down
list of a parameter field?
I can't seem to manually add the "ALL" value after I specify that the
parameter's values are taken from a query.
For example, I have a parameter, "Country" and its values are taken from a
query. Now I have the country values populated. How do I add the ALL value
from here?
Thanks,Use a union in the original query to add a dummy 'All' record to your
primary result set. I've used that on many similar occasions.
Julian Bowker
Marble Steps Systems
"et_ck" <etck@.discussions.microsoft.com> wrote in message
news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> Hi,
> I just want to know if is it possible to add a "ALL" value to the drop
> down
> list of a parameter field?
> I can't seem to manually add the "ALL" value after I specify that the
> parameter's values are taken from a query.
> For example, I have a parameter, "Country" and its values are taken from a
> query. Now I have the country values populated. How do I add the ALL value
> from here?
> Thanks,|||Can we take this question one step farther? Adding a UNION will make the
word "ALL" appear in the parameter drop down list, but what technique do you
use in the report dataset to implement "ALL" in the WHERE clause?
Is there a better technique than coding:
WHERE (country = @.country OR @.country = 'ALL')
I find that I loose the ability to render the query in the GUI if I use
complex AND/OR combinations in parenthesis.
Thanks in advance.
Randy Howie
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>|||That technique is much better than just about any other that I have seen.
Saying that you might prefer to use like and have the value be the wild card
for the database you are going against (for instance % if SQL Server). I.e.
Where (country like @.country)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Randy Howie" <Randy Howie@.discussions.microsoft.com> wrote in message
news:E849778B-9215-42B2-AC0D-702E0101B6C2@.microsoft.com...
> Can we take this question one step farther? Adding a UNION will make the
> word "ALL" appear in the parameter drop down list, but what technique do
> you
> use in the report dataset to implement "ALL" in the WHERE clause?
> Is there a better technique than coding:
> WHERE (country = @.country OR @.country = 'ALL')
> I find that I loose the ability to render the query in the GUI if I use
> complex AND/OR combinations in parenthesis.
> Thanks in advance.
> Randy Howie
> "Julian Bowker" wrote:
>> Use a union in the original query to add a dummy 'All' record to your
>> primary result set. I've used that on many similar occasions.
>> Julian Bowker
>> Marble Steps Systems
>> "et_ck" <etck@.discussions.microsoft.com> wrote in message
>> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
>> > Hi,
>> >
>> > I just want to know if is it possible to add a "ALL" value to the drop
>> > down
>> > list of a parameter field?
>> >
>> > I can't seem to manually add the "ALL" value after I specify that the
>> > parameter's values are taken from a query.
>> >
>> > For example, I have a parameter, "Country" and its values are taken
>> > from a
>> > query. Now I have the country values populated. How do I add the ALL
>> > value
>> > from here?
>> >
>> > Thanks,
>>|||Hi Julian,
Thanks for the help.
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>sql
I just want to know if is it possible to add a "ALL" value to the drop down
list of a parameter field?
I can't seem to manually add the "ALL" value after I specify that the
parameter's values are taken from a query.
For example, I have a parameter, "Country" and its values are taken from a
query. Now I have the country values populated. How do I add the ALL value
from here?
Thanks,Use a union in the original query to add a dummy 'All' record to your
primary result set. I've used that on many similar occasions.
Julian Bowker
Marble Steps Systems
"et_ck" <etck@.discussions.microsoft.com> wrote in message
news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> Hi,
> I just want to know if is it possible to add a "ALL" value to the drop
> down
> list of a parameter field?
> I can't seem to manually add the "ALL" value after I specify that the
> parameter's values are taken from a query.
> For example, I have a parameter, "Country" and its values are taken from a
> query. Now I have the country values populated. How do I add the ALL value
> from here?
> Thanks,|||Can we take this question one step farther? Adding a UNION will make the
word "ALL" appear in the parameter drop down list, but what technique do you
use in the report dataset to implement "ALL" in the WHERE clause?
Is there a better technique than coding:
WHERE (country = @.country OR @.country = 'ALL')
I find that I loose the ability to render the query in the GUI if I use
complex AND/OR combinations in parenthesis.
Thanks in advance.
Randy Howie
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>|||That technique is much better than just about any other that I have seen.
Saying that you might prefer to use like and have the value be the wild card
for the database you are going against (for instance % if SQL Server). I.e.
Where (country like @.country)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Randy Howie" <Randy Howie@.discussions.microsoft.com> wrote in message
news:E849778B-9215-42B2-AC0D-702E0101B6C2@.microsoft.com...
> Can we take this question one step farther? Adding a UNION will make the
> word "ALL" appear in the parameter drop down list, but what technique do
> you
> use in the report dataset to implement "ALL" in the WHERE clause?
> Is there a better technique than coding:
> WHERE (country = @.country OR @.country = 'ALL')
> I find that I loose the ability to render the query in the GUI if I use
> complex AND/OR combinations in parenthesis.
> Thanks in advance.
> Randy Howie
> "Julian Bowker" wrote:
>> Use a union in the original query to add a dummy 'All' record to your
>> primary result set. I've used that on many similar occasions.
>> Julian Bowker
>> Marble Steps Systems
>> "et_ck" <etck@.discussions.microsoft.com> wrote in message
>> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
>> > Hi,
>> >
>> > I just want to know if is it possible to add a "ALL" value to the drop
>> > down
>> > list of a parameter field?
>> >
>> > I can't seem to manually add the "ALL" value after I specify that the
>> > parameter's values are taken from a query.
>> >
>> > For example, I have a parameter, "Country" and its values are taken
>> > from a
>> > query. Now I have the country values populated. How do I add the ALL
>> > value
>> > from here?
>> >
>> > Thanks,
>>|||Hi Julian,
Thanks for the help.
"Julian Bowker" wrote:
> Use a union in the original query to add a dummy 'All' record to your
> primary result set. I've used that on many similar occasions.
> Julian Bowker
> Marble Steps Systems
> "et_ck" <etck@.discussions.microsoft.com> wrote in message
> news:0971547C-4373-4AB3-9F57-F4BCFAE44756@.microsoft.com...
> > Hi,
> >
> > I just want to know if is it possible to add a "ALL" value to the drop
> > down
> > list of a parameter field?
> >
> > I can't seem to manually add the "ALL" value after I specify that the
> > parameter's values are taken from a query.
> >
> > For example, I have a parameter, "Country" and its values are taken from a
> > query. Now I have the country values populated. How do I add the ALL value
> > from here?
> >
> > Thanks,
>
>sql
Subscribe to:
Posts (Atom)