How to assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.sql

How to assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.

How to Architect Full-Text Search

John Kane recently provided this response to a question of using a pattern
match in FTS.
John Kane 3/16/2005 6:27 PM PST
From Discussion Group: sqlserver.programming
Subject: Re: CONTAINS to behave as LIKE %abc%
Date: 3/16/2005 6:27 PM PST
From: John Kane
Farhan & Dave,
Farhan, the leading "*" (asterisk) wildcard in the search condition is
ignored and this is by design for SQL Server 7.0, 2000 and 2005.
SQL FTS only supports a trailing "*" (asterisk) wildcard, i.e.. a wildcard
word-based suffix search, for example, a search for "book*" will find book,
books, booking & booked. A leading "*" (asterisk) wildcard is not supported
because unlike T-SQL LIKE, SQL FTS is a language-specific linguistic search
method, while LIKE is a grep or pattern search method. Would you want to
search on "*og" and find God and Log in the same results?
Regards,
John-- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/
This is the behavior I want in my application that currently employs a FTS
Catalog to index text I recognize from an OCR process.
I am finding that customers want to search for substrings that are contained
in the words that are recognized.
The recognized text is stored in a TEXT datatype column in a SQL table and
is indexed by FTS.
My question is, how to architect a solution that provides a quick response
to the search parameters but allows finding all pattern matches when a
wildcard is added to the leading and trailing end of a word?
For example, find all documents that contain the string "emb". It should
return december, november, september.
Therefore, I want to use code such as "LIKE %emb%" but it must work on a
table with over one million rows of text to be searched.
I don't believe you can index a text datatype column.
Because I am processing documents of unknown length, varchar columns are not
large enough and I will exceed their storage limit.
If I can't use FTS because it doesn't support a wildcard prefix, what do you
suggest?
Thanks
You can index text columns, but that isn't what you were asking. Like
you mentioned, you cannot do "LIKE" type searching with full text
searching. The only alternative is do write a function that manually
goes through your fields and counts up the instances of match. This
would be incredibly slow with a large amount of fields though. Another
option would be to create your "own" index.
Below is a function i found somewhere that will return a count of the
number of times a string appears in a field. Below that is an example
sql of how to call it.
/************************************************** ****************
*
*Description:Counts the Instances OF a String within a String
*AS well You can pass Patterns or wild cards TO be searched AND
counted.
*
*Author: Brad Skidmore
*Date: 4/6/2004
*
************************************************** ****************/
CREATE FUNCTION dbo.CountTextFrequency
(
@.TextString text,
@.SubString varchar(8000)
)
RETURNS INT
AS
BEGIN
DECLARE @.Count int --Count the instances OF @.SubString
DECLARE @.Pos int --Pos inside CURRENT Chunk OF @.TextString
DECLARE @.txtLenint --Len OF the Chunk
DECLARE @.txtPosint --start pos OF the CURRENT chunk
DECLARE @.MyTextString varchar(8000) --text data OF CURRENT chunk
SET @.txtLen = 8000--Set the MAX Len a varchar can hold (Chunk the
BLOB text)
SET @.txtPos = 1--Start at 1 pos OF the Blob text
SET @.Count =0--Set the Count OF Substring
--Get the first Chunck
SET @.MyTextString = SUBSTRING(@.TextString, @.txtPos, @.txtLen)
--While the latest chunk has SOME data COUNT the instances OF
@.SubString
WHILE DATALENGTH(@.MyTextString) > 0
BEGIN
SET @.Pos = PATINDEX('%' + @.SubString + '%', @.MyTextString)
WHILE @.Pos > 0
BEGIN
SET @.Count = @.Count + 1
IF DATALENGTH(@.SubString) > 1
BEGIN
SET @.MyTextString = STUFF(@.MyTextString, 1, @.Pos +
DATALENGTH(@.SubString)-1 ,'')
END
ELSE
BEGIN
SET @.MyTextString = STUFF(@.MyTextString, 1, @.Pos ,'')
END
SET @.Pos = PATINDEX('%' + @.SubString + '%', @.MyTextString)
END
--Get Subsequent Chuncks
SET @.txtPos = @.txtPos + @.txtLen
SET @.MyTextString = SUBSTRING(@.TextString, @.txtPos, @.txtLen)
END
--Return the COUNT OF @.SubString found in ALL the chunks
RETURN(@.Count)
END
Here is the query to use this function...it will return a ranking type
result for you.
Select KEY, SUM(dbo.CountTextFrequency(SearchField,'test') +
dbo.CountTextFrequency(SearchField,'string')) as Score into FROM
database.dbo.tableiwanttosearch SearchIndex where
(dbo.CountTextFrequency(SearchField,'test') > 0 ) and
(dbo.CountTextFrequency(SearchField,'string') > 0 ) group by KEY
This is all rough..I tried it on my site, but found it took too long to
return results...I need it to be very fast.
Let me know if this helped.
|||Unfortunately SQL FTS does not provide this functionality. You will need to
use a Like statement for this and this does not allow you to search binary
documents stored in columns of the image data type. Like can also be very
slow.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Binder" <rgondzur@.hotmail.com> wrote in message
news:OTgGRCuaFHA.2128@.TK2MSFTNGP14.phx.gbl...
> John Kane recently provided this response to a question of using a pattern
> match in FTS.
> John Kane 3/16/2005 6:27 PM PST
> From Discussion Group: sqlserver.programming
> Subject: Re: CONTAINS to behave as LIKE %abc%
> Date: 3/16/2005 6:27 PM PST
> From: John Kane
> Farhan & Dave,
> Farhan, the leading "*" (asterisk) wildcard in the search condition is
> ignored and this is by design for SQL Server 7.0, 2000 and 2005.
> SQL FTS only supports a trailing "*" (asterisk) wildcard, i.e.. a wildcard
> word-based suffix search, for example, a search for "book*" will find
book,
> books, booking & booked. A leading "*" (asterisk) wildcard is not
supported
> because unlike T-SQL LIKE, SQL FTS is a language-specific linguistic
search
> method, while LIKE is a grep or pattern search method. Would you want to
> search on "*og" and find God and Log in the same results?
> Regards,
> John-- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/
>
> This is the behavior I want in my application that currently employs a FTS
> Catalog to index text I recognize from an OCR process.
> I am finding that customers want to search for substrings that are
contained
> in the words that are recognized.
> The recognized text is stored in a TEXT datatype column in a SQL table and
> is indexed by FTS.
> My question is, how to architect a solution that provides a quick response
> to the search parameters but allows finding all pattern matches when a
> wildcard is added to the leading and trailing end of a word?
> For example, find all documents that contain the string "emb". It should
> return december, november, september.
> Therefore, I want to use code such as "LIKE %emb%" but it must work on a
> table with over one million rows of text to be searched.
> I don't believe you can index a text datatype column.
> Because I am processing documents of unknown length, varchar columns are
not
> large enough and I will exceed their storage limit.
> If I can't use FTS because it doesn't support a wildcard prefix, what do
you
> suggest?
>
> Thanks
>
>
sql

How to add summary functions in group footers for report with no database field

Hi,

I use a crystal report that does not have any database fields, only formula fields. I already know how to add a group with the addgroup method, but need to know how are summary fields added?

I need to add a sub total for a money field in each group that has to show at bottom of each group but am unsure as to how this is done. Any help would be very much appreciated.

Again, I don't use any database field and so CR does not allow me to physically add a group section and paste any formulas. It seems this needs to be done by code.

I'm desperate!

Regards,

MikeHi,
U need database fiels in crystal report, it is very important, without having any data what r u gonna show in your report.

Field on which u want to summarize,

In your report detail section, right click the desired field and "Insert" > "Summary". Select "count" and place it in group footer

U will have various options as maximum , minimum, count , Mode etc, select according to your report requirements.

How to add subfolders to the SRS Server?

I have not been able to find info on how to do this.
I want to be able to group reports in subfolders on the report server.
i.e. Accounting in one, sales reports in another.
Does anyone know how to do this using rss scripts?The PublishSampleReports.rss script in C:\Program Files\Microsoft SQL
Server\90\Samples\Reporting Services\Script Samples should get you started.
To create the folders, you need to call the CreateFolder API. For easier
testing, test the APIs in a VB.NET console app and, when everything is
ready, migrate the as a script.
If you are fortunate to have SQL Server 2005, the Management Studio can
generate the starting script for you. For example, right-click on the Home
folder and choose New Folder. Name the new folder and choose the Script
button. The resulting script should look like:
Public Overridable Sub Main()
CreateFolder
End Sub
Private Sub CreateFolder()
Dim Folder As String = "Test"
Dim Parent As String = "/"
Dim Properties(-1) As Microsoft.SqlServer.ReportingServices2005.[Property]
RS.CreateFolder(Folder, Parent, Properties)
End Sub
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"patuww" <patuww@.yahoo.com> wrote in message
news:1132156524.312113.221860@.g49g2000cwa.googlegroups.com...
>I have not been able to find info on how to do this.
> I want to be able to group reports in subfolders on the report server.
> i.e. Accounting in one, sales reports in another.
> Does anyone know how to do this using rss scripts?
>

How to add calculated Facts in Measure Group

hi,

I am new to SSAS. i am working up on cube. I want to add calculated fact in the cube.

like... Suppose we have fact table in which we have fields like (ID, Status)

and status can have 3 values (1,2 ,3) . My requirement is Count of rows having Status 1 + Count of rows having status 2.

How to accomplish this task.

Additionally if dimension is required suppose i have Status(statusID) dimension also.

Please provide me urgent solution!

Thanks in advance.

Mandip

Hi Mandip,

before I start I beg for pardon, because I am working on a German version of SSAS.

You can put two new named calculations in Your datasource view.

First.

CASE WHEN Status = 1 THEN 1 ELSE NULL END

(suggested columnname Stat1)

Second:

CASE WHEN Status = 2 THEN 1 ELSE NULL END

(suggested columnsname Stat2)

Now You can use these two new columns as new measures (count or sum).

cheers

B.

|||

Thanks very much!

I know if the status entries are going to be static this would work.

but in my case at anytime in future we can have new status suppose (4)...

then can there any other method by which i need not to add new columns in fact table... and still i can be able to take calculated fact? Means i don't want to change design structure of cube.

Mandip

|||In the situation you describe, status should be implemented as a dimension. As a rule of thumb any time you want to report amounts "by" something, it is a good indicator that you should implement it as a dimension.

How to add an image dynamically using an URL

I need to generate a report which (1)using a dataset to get the path
and file names of a group of GIF images from a database table, and (2)
dynamically construct an URL for each images, and finally (3) using the
URLs to dynamically add the images into the report pages.
Since I have millions of images stored in a virtual directory, I cannot
add them all into my report project.
Thanks in advance if anybody have any solution and can share it with
me!
TommyYou will need SP1 of RS 2000 which supports retrieving "external" images
through http://. Check section 4.1.4 of the SP1 readme for details.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tommy" <qtao@.doe.k12.de.us> wrote in message
news:1114002588.814601.73710@.g14g2000cwa.googlegroups.com...
>I need to generate a report which (1)using a dataset to get the path
> and file names of a group of GIF images from a database table, and (2)
> dynamically construct an URL for each images, and finally (3) using the
> URLs to dynamically add the images into the report pages.
> Since I have millions of images stored in a virtual directory, I cannot
> add them all into my report project.
> Thanks in advance if anybody have any solution and can share it with
> me!
> Tommy
>

How to add a line/separator between groups?

Hi,

I have a simple report with multiple grouping. I like to add a separator between groups.

Some data (Group #1)

================= <-- separator line or color row or something

Some data (Group #2)

=================

Thanks.

You could use the footer of a group as the seperator between groups. If there is no footer for the group, you can set it in the properties of the group (rightclick the group and select properties) I think this is what you want!

|||

What I do is merge all the columns in the report for the Group Header & Footer textboxes. Then, for the Group Header textbox, I set the border style of the top of the textbox to Solid, and I do the same for the bottom of the Group Footer textbox.

hth

How to add a line/separator between groups?

Hi,

I have a simple report with multiple grouping. I like to add a separator between groups.

Some data (Group #1)

================= <-- separator line or color row or something

Some data (Group #2)

=================

Thanks.

You could use the footer of a group as the seperator between groups. If there is no footer for the group, you can set it in the properties of the group (rightclick the group and select properties) I think this is what you want!

|||

What I do is merge all the columns in the report for the Group Header & Footer textboxes. Then, for the Group Header textbox, I set the border style of the top of the textbox to Solid, and I do the same for the bottom of the Group Footer textbox.

hth

How to add a group header and footer for a report

I have a report that is being called via stored proc, and i want to group by contract. when report gets generated i get multiple contracts info. but will be grouped/sorted by contract.

please how can i have a group header and also a group footer to show a summary of each contract information with some calculated fields in it.

i may get 100 records related to 10 contracts , 10 rows for each contract.

as soon as the first contract info is shown on the report it has to show a summary related to the first contract in the group footer, and then continue populating the second contract info and so on.

Please i am totally new to reporting and help would be appreciated. thank you all.

Here's what you can do:

Click anywhere on your table while in Layout.
Right click on the box next to your details row (it has 3 horizontal bold bars on it), and select 'Insert Group'.
On the 'General' tab, make sure 'Include group header' and 'Include group footer' are both checked.
In the 'Group on:' section, drop down the box and select your contract field. (Or you can type: =Fields!contract.Value)
On the 'Sorting' tab, again, pick or type your contract field as above.
On the 'Visibility' tab, make sure that the 'Visible' radio button is selected.
Hit 'OK' and now you can add your fields to the group header and footer.

Hope this helps.

Jarret

|||

Did this work for you Reddymade?

Jarret

How to access to an another database?

Hello,

I 'm making a procedure that needs to use a table that is in an other database and that database is in an other SQL Server Group. Did you know if is it possible. How can I make this?

Thank you very much for your help and time.

Jonathan.

yes u can do this...add the other server as a linked server to ur server..using sp_addlinkedserver...

then refer the table in other db as

servername.dbname.dbo.tablename

eg. select * from server1.mydb.dbo.table1

|||Thank you very much, Nithin Khurana!!!!

Jonathan