Multiple Data sources for a dataset

Hey everyone,

I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?

thanks,

Keith

Not within the report I'm afraid.

However when building your own application, if you use the report viewer control then you can use .Net datasets to populate data sources. You could therefore populate the dataset manually in C-sharp or VB from the various data sources and pass that to the report viewer control.

The other option is to write your data delivery extension using the reporting services extensible object model.

|||What are the datasources? If they are two different database systems (SQL Server, Oracle, etc), you can consider using linked servers to combine the data before SSRS ever receives it. Just be aware of the performance implications of doing so.

HTH...

Joe

Multiple Columns from WHERE?

Hey all,
I was curious if this was possible...
I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement

so here is an idea of what I'm talking about

SELECT COUNT(*) as HighStock FROM products WHERE qty > 100

now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...

is there a way to get all that in one result set? Something like

SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20

so then my result would be
HighStock LowStock
50 10

anyone have any clues on that? thanks! :)We're going to take what Bugs Bunny used to call "a left turn at Albuquerque" on you to get to the same place:SELECT Sum(CASE WHEN qty > 100 THEN 1 END) AS HighStock
, Sum(CASE WHEN qty < 20 THEN 1 END) AS LowStock
FROM products-PatP|||Pat Phelan ur logic is gr8!! hats off 2 u|||hey Pat!
thanks a million, it actually worked :)
appreciate your help on that one!|||No problem! Always glad to do what I can to confuse things. ;)

-PatP|||PAt. Ur logic is gr8..Hats off to u|||Oh, that wascally Pat! Ain't he a stinker?|||Oh, that wascally Pat! Ain't he a stinker?He he he he he!

-PatP|||Hey Pat...
is it possible to add a DISTINCT to that sum statement somewhere? like give me the Sum(CASE WHEN qty > 100 THEN 1 END WHERE DISTINCT(myfield))
?
I'm sure that syntax is way off but I hope you get my meaning. I Appreciate your help on this one :)|||Ummm... Nope, clear as mud.

-PatP|||Pat, do you have any possible solutions as a workaround? It seems that I have to split up my query into a temp table get the distinct values into it, then query against the temp table. I was hoping to avoid having to make 4 seperate queries. thanks again|||I don't have any clue what you are asking, so I can't answer. I strongly suspect that there is a way to solve your problem, once I understand what you want.

An example would help a lot, showing a dozen or less rows of data, and the result set that you expect to be returned based on that data.

-PatP

Multiple col ina single col

I getting trouble to combine two col into a sinle col how can I do that??You will have to use text concatenation.
(Field1 & Field2).|||Hi,

Select field1 + field2 from tabname

Thanks & Regards

(M. Nedu)
mneduu@.gmail.com

multiple charts

Can I combine two charts together?
For example, I want to have a line chart and a bar chart on the same graph.
Is it possible?
ThanksSQL RS2K doesn't currently support dual axis, but a few creative folks have
come up with a workaround to the problem.
Ivolves creating a report composed of two seperate graphs; 1st graph is a
bar chart; 2nd graph is a line chart. Place one directly above the other.
Then Set BackgroundColor property of each graph so it is transparent and
placed them both on top of a colored rectangle. This gives the appearance
of a single graph having dual axis.
BTW - dual Y axis graphs are planned for the next release (SQL 2005 RS).
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:04026512-0870-46F3-A42A-77CB86580986@.microsoft.com...
> Can I combine two charts together?
> For example, I want to have a line chart and a bar chart on the same
> graph.
> Is it possible?
> Thanks|||Thanks, will try it.
"John H. Miller" wrote:
> SQL RS2K doesn't currently support dual axis, but a few creative folks have
> come up with a workaround to the problem.
> Ivolves creating a report composed of two seperate graphs; 1st graph is a
> bar chart; 2nd graph is a line chart. Place one directly above the other.
> Then Set BackgroundColor property of each graph so it is transparent and
> placed them both on top of a colored rectangle. This gives the appearance
> of a single graph having dual axis.
> BTW - dual Y axis graphs are planned for the next release (SQL 2005 RS).
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:04026512-0870-46F3-A42A-77CB86580986@.microsoft.com...
> > Can I combine two charts together?
> > For example, I want to have a line chart and a bar chart on the same
> > graph.
> > Is it possible?
> >
> > Thanks
>
>|||The combination of bar and line charts is not supported; however, combining
column and line charts is supported.
Example RDL file is copy&pasted to the end of this message (in case your
newsreader cannot read
attachments). You might also search in BOL for "hybrid line-column chart" -
it is the "plot data as line" option in the chart value dialog.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
----
<?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="newChart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>20</Inclination>
<Shading>Real</Shading>
<WallThickness>25</WallThickness>
<DrawingStyle>Cylinder</DrawingStyle>
<Clustered>true</Clustered>
</ThreeDProperties>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Firebrick</Color>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsInStock.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!UnitsOnOrder.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
</DataLabel>
<Marker>
<Type>Square</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Caption>Products</Caption>
<Style />
</Title>
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderWidth>
<Default>1.5pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<CrossAt>0</CrossAt>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<Type>Column</Type>
<Top>0.25in</Top>
<Title>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Title>
<Width>5.625in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup2">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!CategoryName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ProductName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ProductName.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>5.25in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Stock</Label>
</StaticMember>
<StaticMember>
<Label>Order</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>OldLace</BackgroundColor>
</Style>
</PlotArea>
<Left>0.25in</Left>
<ValueAxis>
<Axis>
<Title>
<Caption>Amount</Caption>
<Style />
</Title>
<Style>
<BorderColor>
<Default>IndianRed</Default>
</BorderColor>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>Blue</Default>
</BorderColor>
<BorderStyle>
<Default>Dashed</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderColor>
<Default>CornflowerBlue</Default>
</BorderColor>
<BorderStyle>
<Default>Dotted</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MinorTickMarks>Cross</MinorTickMarks>
<Min>0</Min>
<MajorInterval>20</MajorInterval>
<MinorInterval>5</MinorInterval>
<Interlaced>true</Interlaced>
<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>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>8.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SupplierID">
<DataField>SupplierID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryID">
<DataField>CategoryID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="QuantityPerUnit">
<DataField>QuantityPerUnit</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="UnitsInStock">
<DataField>UnitsInStock</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="UnitsOnOrder">
<DataField>UnitsOnOrder</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ReorderLevel">
<DataField>ReorderLevel</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="Discontinued">
<DataField>Discontinued</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="NullUnits">
<DataField>NullUnits</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 *, NULL AS NullUnits
FROM [Alphabetical list of products]
WHERE (UnitsOnOrder > 0)</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||John,
I've already played a lot with multiple charts, using transparency. My
problem is, that the specified chart size always includes the label values,
the title, the legend, etc.
This means, that depending on the max. length of these items, the plot areas
are of different size, so that in most cases, I'm not able to have the plot
areas exactly on the same place. Do you have any solution for that?
Thanks
"John H. Miller" wrote:
> SQL RS2K doesn't currently support dual axis, but a few creative folks have
> come up with a workaround to the problem.
> Ivolves creating a report composed of two seperate graphs; 1st graph is a
> bar chart; 2nd graph is a line chart. Place one directly above the other.
> Then Set BackgroundColor property of each graph so it is transparent and
> placed them both on top of a colored rectangle. This gives the appearance
> of a single graph having dual axis.
> BTW - dual Y axis graphs are planned for the next release (SQL 2005 RS).
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:04026512-0870-46F3-A42A-77CB86580986@.microsoft.com...
> > Can I combine two charts together?
> > For example, I want to have a line chart and a bar chart on the same
> > graph.
> > Is it possible?
> >
> > Thanks
>
>