January 5, 2011 at 2:37 am
I have following two master and detail tables
VTRCheckList (Master Table)
----------------------------
CREATE TABLE [dbo].[VTRCheckList](
[CLid] [int] NOT NULL,
[CLName] [varchar](90) NULL,
CONSTRAINT [PK_VTRCheckList] PRIMARY KEY CLUSTERED
(
[CLid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
VTRCheckList Detail Table
--------------------------
CREATE TABLE [dbo].[VTRCheckListDetails](
[rec_id] [bigint] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL,
[branchid] [int] NULL,
[hub_id] [int] NULL,
[vtrRespDate] [date] NULL,
[CLid] [int] NULL,
[VtrValue] [varchar](5) NULL,
[trans_status] [int] NULL,
[last_updated] [datetime] NULL,
[update_user] [int] NULL,
CONSTRAINT [PK_VTRCheckListDetails] PRIMARY KEY CLUSTERED
(
[rec_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
BRANCH NAME TABLE
----------------------
CREATE TABLE [dbo].[VTRBranches](
[BranchiD] [int] NOT NULL,
[BranchName] [varchar](90) NULL,
CONSTRAINT [PK_VTRCheckList] PRIMARY KEY CLUSTERED
(
[BranchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
now how to sum all the branch data and show (30) branches in columns like the following of the given month
CLNAme................Branch1....Branch2.....Branch3.....Branch4.....Branch5....Branch6........
checklist 1...............2000.......4343.........4454..........4545...........4545.......45454
January 5, 2011 at 3:55 am
Josh,
based on your other thread you already have a DynamicCrossTab on almost the same data.
Just change how the temp tables are populated and adjust the column names in your dynamic sql statement.
Give it a try and see how far you can modify it until you get stuck. Then post back what you have. Additionally to the sample data you provided "over there" we'd need sample data for the [VTRBranches] table including the expected result based on those sample data.
January 5, 2011 at 4:33 am
Lutz,
I got stuck in a really nasty situation with the dynamic query in my previous thread. If I run the procedure it sure populates the column and display them but for asp.net reporting the dataset needs physical select columns from the procedure but it is coming up with only two columns (CLName and Total) ...hope I am making sense.
January 5, 2011 at 4:50 am
joshtheflame (1/5/2011)
Lutz,I got stuck in a really nasty situation with the dynamic query in my previous thread. If I run the procedure it sure populates the column and display them but for asp.net reporting the dataset needs physical select columns from the procedure but it is coming up with only two columns (CLName and Total) ...hope I am making sense.
Please keep the discussion in the related thread in order to avoid confusion. 😉
Question answered in the related thread.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply