Hello Forum Experts,
I am horribly new at SSRS, I am using a 2008 version currently.
The project summary is simply this, Let's say I have a factory with "n" tools that can produce 25 units per hour. I have a current unit count in queue and I now what my incoming rate is per hour. I want to plot what unit levels of inventory are for the future pending on a parameter of "n" tools changing.
I can get the "n" tools and current units in either a parameter or a variable. These values are determined from DataSets 1 and 2.
In DataSet3 I have the incoming unit per hour for the next 24 hours, or now() + 24 hours grouped by hour. 24 rows.
My calculations for the unit production will be in the sort of : (Summary Version)
Let's Say DataSet4 has 2 columns for simplicity, the Date and the Incoming Units.
I Create a new Column Called Inventory with the summarized calculation of-
IFF (Row = 1, (Current Units + Row 1 Incoming Units) - ("n" Tools * 25), (Previous Row of Inventory + Incoming Units) - ("n" Tools * 25))
Here is my problem, if a build a chart it seems I need to use a DataSet, I cannot use Aggregate calculations or variables in a new calculated field. I need the field to create a chart series.
I can kind of get close by performing the calculations in a new column in a Tablix, but I cannot (or do not know how) to use the new column in the Tablix as a series source of the chart.
I would bet money that I am missing something stupid.
Thanks in advance.
All objects within SSRS work on one dataset. You have 3 datasets currently (current inventory, incoming and tools). You have choices to get those 3 datasets into an SSRS report.
1. Create a single dataset combining the three datasets into a single item.
2. Create another dataset which holds a tally table that can be used to lookup the values from the other three datasets.
Option 2 is preferable if the three datasets cannot be combined easily into a single dataset, for instance, where one of the datasets is sourced from SSAS.Option 1 :
The dataset should hold a dataset such as -- Hour (0 to 24), IncomingUnits, CurrentUnits (for 0 hour only), nTools
Hour IncomingUnits CurrentUnits nTools
0 0 300 0
1 100 0 5
2 2000 0 10
3 1000 0 15
24 0 0 2
Create a line chart that uses an expression for the series as :
=RunningValue(Fields!CurrentUnits.Value + Fields!IncomingUnits.Value - (25*Fields!nTools.Value),Sum,Nothing)Option 2 :
The four datasets could be :
Tally_DataSet : Hour
CurrentInventory_DataSet2 : Hour , CurrentStock
nTools_DataSet1 : Hour, n
Incoming_DataSet3 : Hour, Incoming
Create a line chart based on the Tally_DataSet that uses an expression for the series as :
(25 * iif(Fields!Hour.Value=0,Lookup(Fields!Hour.Value,Fields!Hour.Value,Fields!n.Value,"nTools_DataSet1"),0))
Attached is a picture of the report that I think matches your requirement and a sample report (RDL) with hardcoded sample data.
Hope this helps