Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Reporting Services Development
 how can i split a table into half

Author  Topic 

karenros
Starting Member

37 Posts
Posted - 2007-07-31 : 10:11:31
Hi i want to split a table into half

i have tried giving this expression =Not(Rownumber(Nothing) /2 <=5)

but it doesnt help if the resultset is too big. Any Help will be appreciated.

Regards,
Karen

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-31 : 11:35:01
Can you provide some sample data and expected "split".

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-07-31 : 12:35:51
quote:
Originally posted by dinakar

Can you provide some sample data and expected "split".

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


#65279;Dodge & Cox Stock _______ % Loomis Sayles:Bd;Rtl _______ %
Alger:SmCp & MdCp;A _______ % Met West:Low Dur Bond;M _______ %
AllianBer Intl Value;R _______ % Oakmark Global Select;I _______ %
American Funds CIB;R-5 _______ % PIMCO:Real Rtn;A _______ %
American Funds CWGI;R-5 _______ % Royce Fd:Value Pls;Inst _______ %
Bridgeway:Sm Cp Val;N _______ % Third Avenue:Value Fund _______ %
Columbia:Mars 21st;A _______ % Thornburg Value;A _______ %
Davis NY Venture;A _______ % Vanguard Sm-Cp Idx;Inv _______ %
Fairholme:Fairholme _______ % Vanguard Tgt R 05;Inv _______ %
Mutual Shares;A _______ % Vanguard Tgt R 15;Inv _______ %
Vanguard Tgt R 25;Inv _______ %
Vanguard Tgt R 35;Inv _______ %
Vanguard Tgt R 45;Inv _______ %
Vanguard T Stk Idx;Adm _______ %
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-07-31 : 12:37:04
quote:
Originally posted by karenros

quote:
Originally posted by dinakar

Can you provide some sample data and expected "split".

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


#65279;Dodge & Cox Stock _______ % Loomis Sayles:Bd;Rtl _______ %
Alger:SmCp & MdCp;A _______ % Met West:Low Dur Bond;M _______ %
AllianBer Intl Value;R _______ % Oakmark Global Select;I _______ %
American Funds CIB;R-5 _______ % PIMCO:Real Rtn;A _______ %
American Funds CWGI;R-5 _______ % Royce Fd:Value Pls;Inst _______ %
Bridgeway:Sm Cp Val;N _______ % Third Avenue:Value Fund _______ %
Columbia:Mars 21st;A _______ % Thornburg Value;A _______ %
Davis NY Venture;A _______ % Vanguard Sm-Cp Idx;Inv _______ %
Fairholme:Fairholme _______ % Vanguard Tgt R 05;Inv _______ %
Mutual Shares;A _______ % Vanguard Tgt R 15;Inv _______ %
Vanguard Tgt R 25;Inv _______ %
Vanguard Tgt R 35;Inv _______ %
Vanguard Tgt R 45;Inv _______ %
Vanguard T Stk Idx;Adm _______ %

Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-07-31 : 12:43:06
Dinakar,

Thanks for ur answer. right now this report is been populated using a stored procedure and in my report i have 2 tables

Fields!investmentName.Value Fields!InvestmentName.Value

and i have grouped each table with =Not(RowNumber(Nothing)/2 <= 5)

so for eg. if my dataset or result set has 10 rows

i want it to be displayed as
1 6
2 7
3 8
4 9
5 10.


Right now if i have 24 rows in the resultset but it displays it like this

1 11
2 12
3 13
4 14
5 15
6 16
7 17
8 18
9 19
10 20
21
22
23
24

Any help will be appreicated.
Regards,
Karen

Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-31 : 14:33:55
I think you can create 2 tables with one column each, do a little calculation to find out where to split (if 25 rows, 13, if 24 rows then 12 etc). Get the result of the final SQL query into a table variable with an identity column, Populate each table on the report with a select from the table variable one with rows < split value, the other with values > split value.

for xample, lets say your final SQL returns 25 rows.
Declare @res (rowid int identitY, ...<other columns>)
insert into @t
SELECT <columns <-- your final SQL query

Declare @split
select @split = case when max(rowid)%2 = 1 then (max(rowid)/2) + 1 else max(rowid)/2 end from @res

Use each of the folloing SELECTs to populate the 2 tables on your report.

SELECT * from @res where rowid <= @split
SELECT * from @res where rowid > @split



Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-07-31 : 15:35:48
quote:
Originally posted by dinakar

I think you can create 2 tables with one column each, do a little calculation to find out where to split (if 25 rows, 13, if 24 rows then 12 etc). Get the result of the final SQL query into a table variable with an identity column, Populate each table on the report with a select from the table variable one with rows < split value, the other with values > split value.

for xample, lets say your final SQL returns 25 rows.
Declare @res (rowid int identitY, ...<other columns>)
insert into @t
SELECT <columns <-- your final SQL query

Declare @split
select @split = case when max(rowid)%2 = 1 then (max(rowid)/2) + 1 else max(rowid)/2 end from @res

Use each of the folloing SELECTs to populate the 2 tables on your report.

SELECT * from @res where rowid <= @split
SELECT * from @res where rowid > @split



Thanks a lot for your answer.
can i do this in my stored procedure?
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-31 : 16:23:06
You can leave your existing proc as-is and do all the modofications at the report layer. You cannot get the results of your proc into a table variable but you can do it into a temp table.

Create Table #TmpResults (rowid int identity, ...<other columns>
INSERT INTO #TmpResults EXEC dbo.YourProc..

and follow the rest from my post above.


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-08-01 : 01:36:09
quote:
Originally posted by dinakar

You can leave your existing proc as-is and do all the modofications at the report layer. You cannot get the results of your proc into a table variable but you can do it into a temp table.

Create Table #TmpResults (rowid int identity, ...<other columns>
INSERT INTO #TmpResults EXEC dbo.YourProc..

and follow the rest from my post above.


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


should i write it in the custom code... i am kinda new to sql server... so can pls give me the steps.
Regards
Karen
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-08-01 : 11:21:45
In report designer where you write SQL code (not custom code).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-08-01 : 11:28:40
quote:
Originally posted by dinakar

In report designer where you write SQL code (not custom code).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


What do u mean by <other columns> in

Create Table #TmpResults (rowid int identity, ...<other columns>
INSERT INTO #TmpResults EXEC dbo.YourProc..
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-08-01 : 11:30:07
The columns that your SQL query returns to the report. You create a table with exact same columns but with one additional identity type column that will be used to spill over data to the next table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-08-01 : 11:44:50
Thanks a lot for your reply, this may sound stupid but should i write above code as query string in the Dataset region or some where else?

Regards,
Karen
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-08-01 : 11:48:43
I worked on reports a long time back so I vaguely remember. I think its the dataset panel.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-08-01 : 12:10:32
Dinakar,

I tried giving this query in the dataset panel but i am getting an error

CREATE TABLE #TmpResults(rowid int IDENTITY,PlanId int, PlanName varchar(200), InvestmentName varchar(500), InvestmentType char(1), EmpIncrementPct int, IsPortfolioFundOnly bit, InvestmentId int )
INSERT INTO #TmpResults EXEC ICCStatements..rpt_SelectInvestments PlanId

and the error message is "Error Converting data type nvarchar to int
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-08-01 : 12:12:23
(1) >>>EXEC ICCStatements..rpt_SelectInvestments PlanId
Is ICCStatements the DB name?
(2) If you run the proc >>EXEC ICCStatements..rpt_SelectInvestments PlanId
do you get results without errors?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-08-01 : 12:16:38
quote:
Originally posted by dinakar

(1) >>>EXEC ICCStatements..rpt_SelectInvestments PlanId
Is ICCStatements the DB name?
(2) If you run the proc >>EXEC ICCStatements..rpt_SelectInvestments PlanId
do you get results without errors?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


Dinakar,
If i go to ssms and run the sproc rpt_SelectInvestments and give a PlanId which is an integer it exceutes without any errors.
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-08-01 : 12:17:21
And the answer for Point 1 in my post?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-08-01 : 12:19:36
quote:
Originally posted by dinakar

And the answer for Point 1 in my post?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


yeah ICCStatements is a dbname
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-08-01 : 12:22:50
>>>INSERT INTO #TmpResults EXEC ICCStatements..rpt_SelectInvestments PlanId

would be

INSERT INTO #TmpResults EXEC ICCStatements..rpt_SelectInvestments @PlanId

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

karenros
Starting Member

37 Posts
Posted - 2007-08-01 : 12:26:03
this is what i have given

CREATE TABLE #TmpResults(rowid int IDENTITY, PlanId int, PlanName varchar(200), InvestmentName varchar(500), InvestmentType char(1),
IsPortfolioFundOnly bit, InvestmentId int) Declare @PlanId int
INSERT INTO #TmpResults EXEC ICCStatements..rpt_SelectInvestments @PlanId

and when i run it says 0 rows affected by Last Query
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site