Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding Chart From Tablix as Data Source Expand / Collapse
Author
Message
Posted Sunday, October 27, 2013 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 6:23 PM
Points: 4, Visits: 8
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.

Post #1508696
Posted Monday, October 28, 2013 3:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,146, Visits: 1,367
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


  Post Attachments 
Factory_Inventory.zip (5 views, 3.24 KB)
Inventory_Change.jpg (12 views, 87.82 KB)
Post #1508782
Posted Monday, October 28, 2013 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 6:23 PM
Points: 4, Visits: 8
Mark,

Thank you very much for the reply and the great suggestions, I am putting them to work right now.
Post #1508946
Posted Monday, October 28, 2013 2:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 6:23 PM
Points: 4, Visits: 8
I used Option #1 and it worked great, thank you very much for your help.
Post #1509118
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse