Is this possible?

hey guys,

I have a column called Error Count, which display the the error count values (Fields!error_count.value) from the dataset. And assume the report has some parameters.I want to change the values of this column by comparing the parameters value that the user specified with the values in the database. For instance, If sessions.timestamp == parameters!date.value then do something, where timestamp is a field in the sessions table and parameters!date.value is the the parameter value.

Please let me know if anybody came across this kind of situation and how you solve it.

Any idea is appreciated

Sincerely

Amde

Two approaches come to mind for accomplishing this.

The first way is to use a stored procedure, where you pass the report parameters to the stored procedure. In the stored procedure you have complete control over what values get put in the dataset.

The second approach would be to use a custom data processing extension. You could change the contents of the .Net data set returned by your sql query before returning it to reporting services. Or, another way would be to change the values on the fly when reporting services asks for a particular field from the data set.

I suggest using the first way, as it will require less code and infrastructure complexity.

Is this Code right

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

|||

Karenros wrote:

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

|||

Karenros wrote:

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..

Is this Code right

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

|||

Karenros wrote:

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

|||

Karenros wrote:

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..

sql

Is there any way to train a portion of a training data set from a selected dataset for data mini

Hi, all experts here,

I am wondering is there any way to select only a portion of a data set to train the mining model? In this case, I mean we dont need to split the dataset in advance, what I want to do is being able to select any random portion of a selected dataset to train a mining model. Any advices?

I am looking forward to hearing from you and thanks a lot in advance for your advices and help.

With best regards,

Yours sincerely,

With SQL Server 2005 you can replace DSV tables with named queries that are filtered by some condition you specify. One "simple" way to sample, for example is to add a random number column to your source data, and then filter on that random number in the DSV. To replace a table with a named query, simply right click the DSV table and select "Replace with named Query"

We are adding features to the next version of SQL Server Data Mining to assist with scenarios such as these.

|||

Hi, Jamie,

Thanks for your advices. It's been helpful.

With best regards,

Yours sincerely,

Is there any way to blank out certain columns in a single sele

The users doesn't want the data for these 4 columns to print out unless the
formID is one of the ones selected. I can use "" instead. The dataset is
sent to Crystal Report to print out so it's better using "". I think Crysta
l
prints out the word "NULL" if they're set to NULL.
Thanks.
"Raymond D'Anjou" wrote:

> Add a Case for each of these columns.
> Example:
> ...CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then NU
LL
> else b.form end as form,...
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:9BD3F043-072D-4598-B2BB-BA63529EA142@.microsoft.com...
>
>I don't know anything about Crystal report.
You can use '' for Text datatypes but the numerics and dates may not give
you what you want.
You may have to do a bit of CASTing for these types.
If the users can only choose 1 formID, just build your query differently in
C#.
if formid in ('2', '16', '11', '12', '1', '13', '10') then
select t.tid, t.tdate, t.trip_ticket,t.source,'' AS form, '' AS
formdate, '' AS printed,...
else
select t.tid,t.tdate,t.trip_ticket,t.source,b.form, b.formdate,
b.printed,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D27DCE8F-4B22-41C1-BDDF-A344467FCD63@.microsoft.com...
> The users doesn't want the data for these 4 columns to print out unless
> the
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think
> Crystal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||Hi,Thank you for the reply. I think the system got messed up here. The
"NULL" answer was address to Mesa's question.
My question for you is: Do I just append the "Case..." to the end of my
select
statement?
"Alpha" wrote:
> The users doesn't want the data for these 4 columns to print out unless th
e
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think Crys
tal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||No, the Case replaces the column name in your Select statement.
So, instead of:
select b.form,...
select CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then
NULL else b.form end as form,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:ED00B94C-E471-4508-9F1C-D8E2803E47EC@.microsoft.com...
> Hi,Thank you for the reply. I think the system got messed up here. The
> "NULL" answer was address to Mesa's question.
> My question for you is: Do I just append the "Case..." to the end of my
> select
> statement?
>
> "Alpha" wrote:
>