Hi,
This is my dataset for a report. the reason i am creating this table is because i want to split the result set of the store procedure rpt_Selectinvestments, so that i can display the results of the table thats InvestmentName evenly.
The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.
Create table #TmpResults
( rowid int IDENTITY,
PlanId int,
PlanName varchar(200),
InvestmentName varchar(500),
InvestmentType char(1),
IsPortfolioFundOnly bit,
InvestmentId int)
Declare @.PlanId int
set @.PlanId = 682
Insert Into #TmpResults
Exec ICCStatements..rpt_SelectInvestments @.PlanId
I am also creating a Internal parameter called Split which is an integer which has the following expression
select split = case when max(rowid)%2 = 1 then max(rowid)/2) + 1 else max(rowid)/2 end from #TmpResults.
but when i try to run my report i am getting an error saying that "Split doesnt have the expected parameter type.
Some one please please help me
So what can i do in order to by pass it.
Regards,
Karen
Karenros wrote:
The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.
Create table #TmpResults
( rowid int IDENTITY,
PlanId int,
PlanName varchar(200),
InvestmentName varchar(500),
InvestmentType char(1),
IsPortfolioFundOnly bit,
InvestmentId int)
Declare @.PlanId int
set @.PlanId = 682
Insert Into #TmpResults
Exec ICCStatements..rpt_SelectInvestments @.PlanId
The first time you run this it is creating a table called TmpResults. The second time you run this, it tries to create a table called TmpResults, but it looks in your database and finds that there is already one there, thus the error.
|||
so what should i do.. All i am trying to do is to split resultset into half so that i can display them in 2 tables.
Can u please help me out.
|||how can i take the results of the sproc and insert it into a table? Is it possible to do it...
|||Have you tried using functions instead?
You can create a function that returns a table and I would think you could insert that table into another table. I typically just select from it though instead of inserting it.
|||
can u please give me an example of that. or do u mean write a custom code to do it?
Regards
Karen
|||
Code Snippet
USE [database]
GO
/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 08/03/2007 10:29:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[func2]
(
@.StartDate varchar(100),
@.EndDate varchar(100)
)
RETURNS TABLE
AS
RETURN (SELECT SUM(TOTAL) AS TOTAL FROM MYTABLE WHERE StartDate = @.StartDate AND EndDate = @.EndDate)
Then you could put this in a stored procedure:
Code Snippet
select SUM(TOTAL) AS TOTAL from dbo.func2('07/01/2006', '07/31/2006')
As you can see, a table is returned from func2 and you can select from it.
In your case, you would want to try to use that table that is returned and insert the first half into one table and the second half into another table.
|||
Greg,
Thanks for ur answer. This is what i have done right now, I have created a functions which is a follows
ALTER Function [dbo].[Func2]
(
@.PlanId int
)
RETURNS Table
AS
Return (Select Count(*) as RowId from PlanFund Where PlanId = @.PlanId)
and this is the sproc that i am using to poplulate my report it is as follows
ALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanId AS integer)
AS
-- History
-- 08/17/2004 svanpatter/JSWCO initial version created
-- 08/30/2004 svanpatter/JSWCO add
-- Select available funds
SELECT
[ClientPlan].PlanId,
[ClientPlan].PlanName,
-- Fund.[FundName] AS InvestmentName,
CASE
WHEN
PlanFund.PlanFundDisplayName IS NULL
THEN
Fund.ShortName
ELSE PlanFund.PlanFundDisplayName
END InvestmentName,
'F' AS InvestmentType,
--EmpIncrementPct =
--CASE
-- WHEN EmpIncrementPct IS NULL THEN '0'
-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther
-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))
--END,
--PlanFund.PlanId As InvestmentID
PlanFund.IsPortfolioFundOnly,
PlanFund.FundDisplayOrder As InvestmentID
FROM
[ClientPlan]
--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId
INNER JOIN PlanFund ON [ClientPlan].PlanId = PlanFund.PlanId And IsPortfolioFundOnly = "0"
INNER JOIN Fund ON PlanFund.FundId = Fund.FundId
--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperID
WHERE
[ClientPlan].PlanId = @.PlanId
UNION
-- Select Portfolios
SELECT
[ClientPlan].PlanId,
[ClientPlan].PlanName,
PlanPortfolio.PortfolioName AS InvestmentName,
'P' AS InvestmentType,
--EmpIncrementPct =
-- CASE
-- WHEN EmpIncrementPct IS NULL THEN '0'
-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther
-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))
-- END,
NULL,
PlanPortfolio.PortfolioId As InvestmentID
FROM [ClientPlan]
INNER JOIN PlanPortfolio ON [ClientPlan].PlanId = PlanPortfolio.PlanId
--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId
WHERE
[ClientPlan].PlanId = @.PlanId
ORDER BY
InvestmentType, InvestmentID
Select RowId from dbo.Func2(@.PlanId)
As u can see at the end of the sproc i am calling the function...
and when i run this sproc i get 2 tables one which returns the each record and the other one which returns the count for the other table. like suppose if i have 24 records... Select RowId returns 24.
but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?
any help will be appreciated
|||
but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?
Are you using rpt_SelectInvestments as the sproc in your report?
If so, then you need to incorporate "select RowId from dbo.Func2(@.PlanId)" into your sproc. Right now you have it as two separate select statements.
|||ok i dont think the function i created will work... so is there a way that i can put the results of the sproc in a parameter or a variable in the report ?
For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?
Regards
Karen
|||
For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?
For the first time when you call your procedure from report with all valid parameter value. It will create list of all parameter for your report. which are useed to call the procedure next time. And you can modify parameter from menu Report - > Report parameter...
If you have parameter in stored proc with output type. It will create that also as report parameter.. and you can use them on report whereever you want whenever you want.
|||
Hi its Me,
Thanks for your answer..
So in my sproc if i do
Create proc [dbo].[procname]
@.PlanId as integer,
@.Count int output
AS
Select
<whatever> i want
fromm
tablename
Union
Select statment
where PlanId = @.PlanId
and then at the end i am setting
SEt @.Count = @.@.RowCount
Return @.count.
When i run the sproc it asks me a value for @.Count...
What should i do..
Regards
Karen
|||select blank or null for output parameter. or just pass any value.. that doesnt make any difference to your proc. as you are not using that parameter in your proc..
|||thanks for ur answer... But how can i get value of the output parameter in the report?
Regards,
Karen
|||In expression just write :
=Parameters!Count.Value
And you will get the value of the parameter..
No comments:
Post a Comment