October 27, 2013 at 9:19 am
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.
October 28, 2013 at 3:29 am
mike.tv (10/27/2013)
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 :
=RunningValue(
iif(Fields!Hour.Value=0,Lookup(Fields!Hour.Value,Fields!Hour.Value,Fields!CurrentStock.Value,"CurrentInventory_DataSet2"),0) +
iif(Fields!Hour.Value=0,Lookup(Fields!Hour.Value,Fields!Hour.Value,Fields!Incoming.Value,"Incoming_DataSet3"),0) -
(25 * iif(Fields!Hour.Value=0,Lookup(Fields!Hour.Value,Fields!Hour.Value,Fields!n.Value,"nTools_DataSet1"),0))
,Sum,Nothing)
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
Fitz
October 28, 2013 at 9:05 am
Mark,
Thank you very much for the reply and the great suggestions, I am putting them to work right now.
October 28, 2013 at 2:47 pm
I used Option #1 and it worked great, thank you very much for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply