Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding Chart From Tablix as Data Source


Adding Chart From Tablix as Data Source

Author
Message
mike.tv
mike.tv
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Mark Fitzgerald-331224
Mark Fitzgerald-331224
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3692 Visits: 1513
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
Attachments
Factory_Inventory.zip (12 views, 3.00 KB)
Inventory_Change.jpg (19 views, 87.00 KB)
mike.tv
mike.tv
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
Mark,

Thank you very much for the reply and the great suggestions, I am putting them to work right now.
mike.tv
mike.tv
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
I used Option #1 and it worked great, thank you very much for your help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search