Showing posts with label displays. Show all posts
Showing posts with label displays. Show all posts

Monday, March 26, 2012

dynamic pivot?

I need to write a report that displays the time each operation was started
across the page like this:
Ord_Num OC020 OC030
-- --- --
---
520208 2004-10-01 08:22:00.000
2004-10-01 08:25:00.000
520273 2004-10-01 08:13:00.000
2004-10-01 08:16:00.000
520292 2004-10-01 08:04:00.000
2004-10-01 08:06:00.000
520324 2004-10-01 08:30:00.000
2004-10-01 08:33:00.000
520388 2004-10-01 08:27:00.000
2004-10-01 08:31:00.000
520472 2004-10-01 08:26:00.000
2004-10-01 08:30:00.000
The problem is that I don't know at design time what the operations are all
going to be. Here is some SQL to set up the problem:
Use Pubs
go
Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
PlannedStartDT datetime)
go
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520324', 'PR120', 'Oct 1 2004 8:52AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:28AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:32AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:38AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC020', 'Oct 1 2004 8:27AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'OC030', 'Oct 1 2004 8:31AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR010', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR020', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520388', 'PR120', 'Oct 1 2004 8:37AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC020', 'Oct 1 2004 8:26AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR010', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR020', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR120', 'Oct 1 2004 8:33AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520472', 'PR130', 'Oct 1 2004 8:53AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC020', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'OC030', 'Oct 1 2004 8:30AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520529', 'PR130', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC020', 'Oct 1 2004 8:09AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR010', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR020', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520658', 'PR120', 'Oct 1 2004 8:41AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC020', 'Oct 1 2004 8:12AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'OC030', 'Oct 1 2004 8:15AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR010', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR020', 'Oct 1 2004 8:22AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520840', 'PR120', 'Oct 1 2004 8:49AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC020', 'Oct 1 2004 8:01AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'OC030', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520860', 'PR120', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC020', 'Oct 1 2004 8:02AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'OC030', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR010', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('520900', 'PR020', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:18AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC020', 'Oct 1 2004 8:06AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'OC030', 'Oct 1 2004 8:07AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521056', 'PR120', 'Oct 1 2004 8:55AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC020', 'Oct 1 2004 8:08AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'OC030', 'Oct 1 2004 8:10AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521103', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC020', 'Oct 1 2004 8:21AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'OC030', 'Oct 1 2004 8:25AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR010', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR020', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR120', 'Oct 1 2004 8:34AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR130', 'Oct 1 2004 8:39AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR140', 'Oct 1 2004 8:50AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521144', 'PR150', 'Oct 1 2004 8:51AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC020', 'Oct 1 2004 8:03AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'OC030', 'Oct 1 2004 8:04AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR010', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR020', 'Oct 1 2004 8:16AM' )
Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
('521163', 'PR120', 'Oct 1 2004 8:16AM' )
go
I get nice results with the query below, but again, at design time I don't
know how many operations there will be or what they are.
Select Ord_Num,
OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
From ProcessControl
Group By Ord_Num
Is there any solution to this that does not require dynamic SQL?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyHi
You may want to look at:
http://www.windowsitpro.com/SQLServ...5608/15608.html
but it also uses dynamic SQL.
John
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>|||You might want to check out www.rac4sql.net for a solution to your problem.
Also, check out
http://www.windowsitpro.com/Windows...54/pg/2/2.html.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OTnp$MEHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I need to write a report that displays the time each operation was started
> across the page like this:
> Ord_Num OC020 OC030
> -- --- --
--
> ---
> 520208 2004-10-01 08:22:00.000
> 2004-10-01 08:25:00.000
> 520273 2004-10-01 08:13:00.000
> 2004-10-01 08:16:00.000
> 520292 2004-10-01 08:04:00.000
> 2004-10-01 08:06:00.000
> 520324 2004-10-01 08:30:00.000
> 2004-10-01 08:33:00.000
> 520388 2004-10-01 08:27:00.000
> 2004-10-01 08:31:00.000
> 520472 2004-10-01 08:26:00.000
> 2004-10-01 08:30:00.000
> The problem is that I don't know at design time what the operations are
> all
> going to be. Here is some SQL to set up the problem:
> Use Pubs
> go
> Create Table ProcessControl (Ord_Num nvarchar(12), OpCode nvarchar(12),
> PlannedStartDT datetime)
> go
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520324', 'PR120', 'Oct 1 2004 8:52AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:28AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:32AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:38AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC020', 'Oct 1 2004 8:27AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'OC030', 'Oct 1 2004 8:31AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR010', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR020', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520388', 'PR120', 'Oct 1 2004 8:37AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC020', 'Oct 1 2004 8:26AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR010', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR020', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR120', 'Oct 1 2004 8:33AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520472', 'PR130', 'Oct 1 2004 8:53AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC020', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'OC030', 'Oct 1 2004 8:30AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520529', 'PR130', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC020', 'Oct 1 2004 8:09AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR010', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR020', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520658', 'PR120', 'Oct 1 2004 8:41AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC020', 'Oct 1 2004 8:12AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'OC030', 'Oct 1 2004 8:15AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR010', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR020', 'Oct 1 2004 8:22AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520840', 'PR120', 'Oct 1 2004 8:49AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC020', 'Oct 1 2004 8:01AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'OC030', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520860', 'PR120', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC020', 'Oct 1 2004 8:02AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'OC030', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR010', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('520900', 'PR020', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:18AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC020', 'Oct 1 2004 8:06AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'OC030', 'Oct 1 2004 8:07AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521056', 'PR120', 'Oct 1 2004 8:55AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC020', 'Oct 1 2004 8:08AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'OC030', 'Oct 1 2004 8:10AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521103', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC020', 'Oct 1 2004 8:21AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'OC030', 'Oct 1 2004 8:25AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR010', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR020', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR120', 'Oct 1 2004 8:34AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR130', 'Oct 1 2004 8:39AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR140', 'Oct 1 2004 8:50AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521144', 'PR150', 'Oct 1 2004 8:51AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC020', 'Oct 1 2004 8:03AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'OC030', 'Oct 1 2004 8:04AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR010', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR020', 'Oct 1 2004 8:16AM' )
> Insert Into ProcessControl (Ord_Num, OpCode, PlannedStartDT) Values
> ('521163', 'PR120', 'Oct 1 2004 8:16AM' )
> go
> I get nice results with the query below, but again, at design time I don't
> know how many operations there will be or what they are.
>
> Select Ord_Num,
> OC020 = min(case when Opcode = 'oc020' then PlannedStartDT else NULL end),
> OC030 = min(case when Opcode = 'oc030' then PlannedStartDT else NULL end),
> PR010 = min(case when Opcode = 'PR010' then PlannedStartDT else NULL end),
> PR020 = min(case when Opcode = 'PR020' then PlannedStartDT else NULL end),
> PR120 = min(case when Opcode = 'PR120' then PlannedStartDT else NULL end),
> PR130 = min(case when Opcode = 'PR130' then PlannedStartDT else NULL end),
> PR140 = min(case when Opcode = 'PR140' then PlannedStartDT else NULL end),
> PR150 = min(case when Opcode = 'PR150' then PlannedStartDT else NULL end)
> From ProcessControl
> Group By Ord_Num
> Is there any solution to this that does not require dynamic SQL?
> Thanks.
>
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
Posted by mod at 1:04 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Thursday, March 22, 2012

Dynamic Parameters

Hi All,

I am stuck with a report, could you please help.

I have a dynamic stored procedure, which depending on Begindate and Enddate displays columns deptname and amount (depending on the month the amount field is going to sum and display the results in the appropriate date fields), that is sum(amount) in Jan05,Feb05,Mar05......depending on the Begindate and Enddate, it's displays the values.

EX:-

Deptname Jan 05 Feb 05 Mar 05

Housing 5 5 5

Shipping 45 56 85

Handling 10 14 18

How do i incorporate this into the Report designer, because when i use this Stored procedure and create a report, i can see the values coming in the Dataset, but i don't see the Amount values coming into the Preview Page.I only see deptname coming into the Preview page.

i am building a string in the Stored procedure.(Just Letting you know)

what can i do to make the amount values be displayed under appropriate month.

Thanks,

vnswathi.

What is the output of stored procedure? It should return date, category, Quantitiy/Number (appears in dataset)

If you return those three values from stored procedure then you can use Table grouping feature in the Report desinger to build the report.

Thanks
Murthy

|||

I am getting values from the Stored procedure and also able to see the results in the Dataset on the report designer DATA page, but when it comes to laying out the Report on the report designer LAYOUT page and see the PREVIEW, that is where my problem comes.

I am able to display the Department name column but not the Amount column which i have grouped by Year and month.

Any help is appreciated.

Thanks,

vnswathi.

|||

If you get into Dataset then you will surely have those values in Layout/Preview pages too.

Can you verify textbox properties in the layout screen and see are you able to get dataset values there or not? Also check visibility attribute of those text fileds too?

As a simple test, you can verify this by placing a textbox and set those dataset values and see the preview.

Thanks
Murthy

|||I have a other feeling that you may doing something wrong in the Grouping section. Are you building drill-down report? If yes you have to check toggle items?|||

Ok, i was doing it right, but the only thing which i was doing wrong was using a tabluar layout.

I have used Matrix layout and it worked.

Thanks,

vnswathi.

|||Great...any idea what is going wrong with the tabular report?
Posted by mod at 11:52 PM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Dynamic Parameters

Hi All,

I am stuck with a report, could you please help.

I have a dynamic stored procedure, which depending on Begindate and Enddate displays columns deptname and amount (depending on the month the amount field is going to sum and display the results in the appropriate date columns), that is sum(amount) in Jan05,Feb05,Mar05........depending on the Begindate and Enddate, it's displays the values.

EX:-

Deptname Jan 05 Feb 05 Mar 05

Housing 5 5 5

Shipping 45 56 85

Handling 10 14 18

How do i incorporate this into the Report designer, because when i use this Stored procedure and create a report, i can see the values coming in the Dataset, but i don't see the Amount values coming into the Preview Page.I only see deptname coming into the Preview page.

I am building a string in the Stored procedure.(Just Letting you know)

what can i do to make the amount values be displayed under appropriate month.

Thanks,

vnswathi.

hi ,

i guess i came across one such report and what i did was instead of using strings i used the BeginDate and Enddate Parameters and feed them into a function which would rip off the months and the sum up the data corresponding to the month.

month(@.BeginDate) would get u the month and then you can use Case statement to actually sum up the amount for that particular month.

i guess it more of a tedious job, but should work.

|||

yes, it works for months, but how about years.

because we know that there would be 12 Months, but you don't know the Date range.

Instead i have used Matrix layout and it worked.

Thanks,

vnswathi.

Posted by mod at 11:51 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Sunday, February 19, 2012

dynamic columns in matrix

hi all,

i m using ssrs 2005. i want to generate a report which displays data according to the dataset returned.Now this dataset can return any number of columns.

does matrix use helps out in this case..

if anyone can really explain me on it or can even point to certain articles in this regard,that wudd be wonderful

thanks a ton...

hi all
m quite new to reporting services so may b it sounds easy for u champs but still ur replies wudd b appreciated..

thanks a ton ...

|||Hi,hilander:
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link yourdataset to a report at runtime by using ReportViewer.LocalReport methods or at disign level too.

Hopefully I help you.

|||

Hi,hilander:
We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

Posted by mod at 3:52 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

Wednesday, February 15, 2012

Dyanamic date changing to current date when printing PDF

I have a dynamic date in my reporting services report that displays
the previous month and year we are reporting on. Here is the
expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
Year(Now())
When I create a pdf from this it displays correctly, but when I print
it, it always puts the current month and year in this field. Does
anyone know why and how to prevent it from doing this when I print the
pdf?
Thanks for any help you can give.
-CarlyJust to clarify, you create a pdf then print the pdf from within Adobe
Acrobat and while the pdf file you see on the screen has September
2007, for instance, the printed pdf will have October 2007?
Is that correct? You're printing the pdf, not printing the report
directly from Report Manager?
- C
On Oct 5, 10:22 am, caroline.fe...@.sdncommunications.com wrote:
> I have a dynamic date in my reporting services report that displays
> the previous month and year we are reporting on. Here is the
> expression: =MonthName(Month(Now().AddMonths(-1))) & " " &
> Year(Now())
> When I create a pdf from this it displays correctly, but when I print
> it, it always puts the current month and year in this field. Does
> anyone know why and how to prevent it from doing this when I print the
> pdf?
> Thanks for any help you can give.
> -Carly
Posted by mod at 8:38 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)