665Ecko .
asked on
Uptime / Downtime Production Report SSRS 2012 Rookie looking for suggestions / support via Pivoting Table / Ranking Functions.
I am working on another report using `SSRS` via `Visual Studio 2010`. I am a rookie with SQL but things are starting to make sense as the weeks go on.
Purpose is to create a report that displays the `Batch up time / downtime` calculated from the `timestamps` taken from a SQL server database. The time stamp is represented as a date/time input, an example, `2018-02-09 14:43:29.193`.
There are 4 Event ID's 1-4 that must be met to trigger a time stamp in the database. Each batch has it's own 3 digit integer ID.
1-Batch Start, Not running production, but system is initiated. (Down)
2-Production Start, Running production (Up Time)
3-Production Stop, Machine is running, but not producing (Down)
4-Batch Stop, Production complete. (Down)
The difference between,
2&1 = Downtime
4&1 = Downtime
2&3 = Up time
If 4 is after 2 = Up time
If 4 is after 3 = Downtime
See the database image which is the working set I am dealing with.
I have done my best with this report with limited knowledge, and in the report I have created parameters to choose dates for the report, along with asking the user to pick the Batch ID along with Batch 1-4 Event's, however I am not sure how to tackle the calculated fields.
Below is the SQL code,
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [Productio_CrtNo]
,[BatchEvent_ID]
,[BatchEvent_TIME]
,[Operator_ID]
,[DieRolls_ID]
,[Batch_ID]
,[NoOfPockets]
,[WetCapsulesWeight]
FROM [SCM_DataCollecion].[dbo].
\ionTable]
Current Report image has also been uploaded for review. I hope it is clear, where I want to compare the BatchEvent_ID from a specific Batch_ID, sum up the up/down time time stamps which are then subtracted from the current time to give me my results. I plan on adding 2 additional parameters for UP / Down Time from which the user shall get a detailed result based on the Date / BatchEvent_ID / Batch_ID, however this is where I need an assist to save the day.
As this is my 4th week working with SSRS and SQL, if there is an easier way to tackle this I am open to hearing any suggestions.
I have been reading up on pivoting tables and or using a ranking function to make this work and I think this may be the way to go in calculated the `up / downtime`, however I need a bit of help on this part.
Thanks again
Purpose is to create a report that displays the `Batch up time / downtime` calculated from the `timestamps` taken from a SQL server database. The time stamp is represented as a date/time input, an example, `2018-02-09 14:43:29.193`.
There are 4 Event ID's 1-4 that must be met to trigger a time stamp in the database. Each batch has it's own 3 digit integer ID.
1-Batch Start, Not running production, but system is initiated. (Down)
2-Production Start, Running production (Up Time)
3-Production Stop, Machine is running, but not producing (Down)
4-Batch Stop, Production complete. (Down)
The difference between,
2&1 = Downtime
4&1 = Downtime
2&3 = Up time
If 4 is after 2 = Up time
If 4 is after 3 = Downtime
See the database image which is the working set I am dealing with.
I have done my best with this report with limited knowledge, and in the report I have created parameters to choose dates for the report, along with asking the user to pick the Batch ID along with Batch 1-4 Event's, however I am not sure how to tackle the calculated fields.
Below is the SQL code,
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [Productio_CrtNo]
,[BatchEvent_ID]
,[BatchEvent_TIME]
,[Operator_ID]
,[DieRolls_ID]
,[Batch_ID]
,[NoOfPockets]
,[WetCapsulesWeight]
FROM [SCM_DataCollecion].[dbo].
\ionTable]
Current Report image has also been uploaded for review. I hope it is clear, where I want to compare the BatchEvent_ID from a specific Batch_ID, sum up the up/down time time stamps which are then subtracted from the current time to give me my results. I plan on adding 2 additional parameters for UP / Down Time from which the user shall get a detailed result based on the Date / BatchEvent_ID / Batch_ID, however this is where I need an assist to save the day.
As this is my 4th week working with SSRS and SQL, if there is an easier way to tackle this I am open to hearing any suggestions.
I have been reading up on pivoting tables and or using a ranking function to make this work and I think this may be the way to go in calculated the `up / downtime`, however I need a bit of help on this part.
Thanks again
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Let me know,
thx
JohnE