Showing posts with label together. Show all posts
Showing posts with label together. Show all posts

Monday, March 19, 2012

Multiple Datasets but needs to be tied together

I am needing to take data from one data set that consists of a list of parts and their stock information. Then I am needing to show any transactions that the individual parts may have had during the time that user defined through the filters.

This requires me to have two data sets since I can't do a join between the stocking information and the transactions due to the fact that it is very possible that every part won't have a transaction for each month of each year. Doing a left join still filters down the data too much so thus the need for two data sets.

I have these parameters:

Vendor, Year, Month, Type of request

Vendor and Type of Request is handled through StockInfoDataset. I then need to update the table showing 0 or transaction amount from TransactionDataset filtered by the ItemNumber and WarehouseCode that is in StockInfoDataset. Since it seems List and Table only allow one dataset in them, I am at a loss as to how I would go about doing this.

Explaining this is quite difficult in a forum post, but hopefully someone would be able to shine some light on possibly avenues to follow here....thank you in advance.

Josh

I'm not sure I understand 100% what you're facing, but here's how I would try to tackle this...

Each parameter can have a separate dataset, for lookup purposes.
Then the main dataset can be based on the selected report parameters.
Finally, I would use either a view or a stored procedure to return the needed dataset.
Using the view/sp provides a lot of flexibility that direct table queries do not, including the ability to join, summarize, and otherwise process values from multiple tables.

I hope that helps.

|||

Your solution may very well work, but I have never done it that way so I am a bit at a loss.....

Unfortunately, unless there is something I am missing, I can't use a view or stored procedure to derive this information....here is an example of what I am wanting to do

Vendor = ABC, Year = 2006, Month = 07, Type = Qty On Hand > 0

Table A

[Column AA] [Column AB] [Column AC]

Item1 Whs1 QtyOnHand1

Item2 Whs1 QtyOnHand2

Item3 Whs2 QtyOnHand3

Item4 Whs1 QtyOnHand0

Table B

[Columnb BA] [ColumnBB] [Column BC] [ColumnBD]

Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 07/01/06 QtySold2

Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 06/01/06 QtySold4

Item4 Whs1 07/01/06 QtySold5

So when I do

SELECT * FROM [TableA] LEFT OUTER JOIN [TableB] ON [TableA].[Column AA] = [TableB].[Column BA] AND [TableA].[Column AB] = [TableB].[Column BB] WHERE [TableA].[QtyOnHand] > 0

I am going to get a result set of

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 QtySold1

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 QtySold4

This is essentially what my View Does that I am selecting from....

Now in my report I have them select year and month......to filter what transactions are going to be summed.

My problem is that I still need to show the qtyOnhand and such even though there my be no transactions for the time period they select....so using the parameters above. This is what I get then after that I'll show what I want...

This is what I get:

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

This is what I want:

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 0

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 0

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 0

I hope this clarifies things a bit....

Thank you,

Josh

|||

It seems like it's possibly to query what you need and get it in 1 dataset, then maybe doing some filtering or special exclusions, but first,

can you please give your relevant tables and their column names? I know you were trying to be discrete, but COLUMNAA, COLUMNAB is hard to put into motion. After that, I'm sure someone can get you a query that will do the job (full outer join might be necessary).

Multiple Datasets but needs to be tied together

I am needing to take data from one data set that consists of a list of parts and their stock information. Then I am needing to show any transactions that the individual parts may have had during the time that user defined through the filters.

This requires me to have two data sets since I can't do a join between the stocking information and the transactions due to the fact that it is very possible that every part won't have a transaction for each month of each year. Doing a left join still filters down the data too much so thus the need for two data sets.

I have these parameters:

Vendor, Year, Month, Type of request

Vendor and Type of Request is handled through StockInfoDataset. I then need to update the table showing 0 or transaction amount from TransactionDataset filtered by the ItemNumber and WarehouseCode that is in StockInfoDataset. Since it seems List and Table only allow one dataset in them, I am at a loss as to how I would go about doing this.

Explaining this is quite difficult in a forum post, but hopefully someone would be able to shine some light on possibly avenues to follow here....thank you in advance.

Josh

I'm not sure I understand 100% what you're facing, but here's how I would try to tackle this...

Each parameter can have a separate dataset, for lookup purposes.
Then the main dataset can be based on the selected report parameters.
Finally, I would use either a view or a stored procedure to return the needed dataset.
Using the view/sp provides a lot of flexibility that direct table queries do not, including the ability to join, summarize, and otherwise process values from multiple tables.

I hope that helps.

|||

Your solution may very well work, but I have never done it that way so I am a bit at a loss.....

Unfortunately, unless there is something I am missing, I can't use a view or stored procedure to derive this information....here is an example of what I am wanting to do

Vendor = ABC, Year = 2006, Month = 07, Type = Qty On Hand > 0

Table A

[Column AA] [Column AB] [Column AC]

Item1 Whs1 QtyOnHand1

Item2 Whs1 QtyOnHand2

Item3 Whs2 QtyOnHand3

Item4 Whs1 QtyOnHand0

Table B

[Columnb BA] [ColumnBB] [Column BC] [ColumnBD]

Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 07/01/06 QtySold2

Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 06/01/06 QtySold4

Item4 Whs1 07/01/06 QtySold5

So when I do

SELECT * FROM [TableA] LEFT OUTER JOIN [TableB] ON [TableA].[Column AA] = [TableB].[Column BA] AND [TableA].[Column AB] = [TableB].[Column BB] WHERE [TableA].[QtyOnHand] > 0

I am going to get a result set of

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 QtySold1

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 QtySold1

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 QtySold3

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 QtySold4

This is essentially what my View Does that I am selecting from....

Now in my report I have them select year and month......to filter what transactions are going to be summed.

My problem is that I still need to show the qtyOnhand and such even though there my be no transactions for the time period they select....so using the parameters above. This is what I get then after that I'll show what I want...

This is what I get:

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

This is what I want:

Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 0

Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)

Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 0

Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 0

I hope this clarifies things a bit....

Thank you,

Josh

|||

It seems like it's possibly to query what you need and get it in 1 dataset, then maybe doing some filtering or special exclusions, but first,

can you please give your relevant tables and their column names? I know you were trying to be discrete, but COLUMNAA, COLUMNAB is hard to put into motion. After that, I'm sure someone can get you a query that will do the job (full outer join might be necessary).

Saturday, February 25, 2012

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
>
>