Data Source For Report Model Project

When I tried to generate ad-hoc reports with Microsoft reporting services
2005 (april CTP), it generally ask for the tables and views. Is there a way
that I can just put in a stored procedure and use the results as a data
feeder for the report model? Thanks.I don't think this is supported out of the box
You can however create a named query as part of the data source view
"Geek" wrote:
> When I tried to generate ad-hoc reports with Microsoft reporting services
> 2005 (april CTP), it generally ask for the tables and views. Is there a way
> that I can just put in a stored procedure and use the results as a data
> feeder for the report model? Thanks.sql

Data script task - how to generate multiple rows from one row ? (string splitter)

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

ThibautJust take your source and throw it into a derived column transformation where you'll perform your string split to create new fields for each "token." Then you'll go into an unpivot transformation where you'll take the new columns and turn them into rows.

Why use a script task when you can use the optimized data flow tools as-is?|||

You need to create an asynchrnous transform. So add the transform, select and also setup columns as required. Ensure the SynchronousInputID of the output is set to 0, making it async.

Then read rows and add them to the output as required. Sum dummy code -

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

While Row.NextRow() ' This happens once for each input row

' Do something here, and as required, add rows to the output, use a loop or whatever

For i As Int = i < 10

With OutputBuffer

.AddRow() ' Adding a new output row and setting values. Can do this as many times as we like, 0 or more times in the context of this input row loop interation

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

|||Thanks a lot Darren ! That's really perfect (and thanks for the detailed sample, I really appreciate).

regards,

Thibaut|||Just be careful because some have tested the script task and it performs slower than the other, native dataflow tasks.|||Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?|||

DarrenSQLIS wrote:

Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?

Yep, no problem... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=857796&SiteID=1|||Hi Phil

thanks for your input as well. Like Darren, I agree that a good rule of thumb is that a pipeline component will work generally faster than a corresponding script component.

A script component is generally one-shot code, which generally doesn't get the same level of testing and optimization. In the article you point to, the script task benchmark shows an average CPU usage of 5% (which is often a sign of synchronization issues, contention etc). Here (like suggested by a commenter), a modification of the implementation (like handling sets of rows instead of one row at a time) would most likely boost the performance a lot.

So I wouldn't draw conclusions based on a single script example, given that each implementation is likely to vary a lot in terms of performance and memory consumption - just like any kind of code!

But anyway - it seems that I have two solutions for my problem now. I don't hesitate to use script task when they prove useful, but I first try to stick to the pipeline components.

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

I've finished the job using a script task (I will report back the details later).

I'm curious about how it would have been possible to implement this using a derived column transformation (at first sight I couldn't find out).

Phil could you give a bit more details ? I could not find any real string splitting functions in SSIS - were you thinking of using a FIND function recursively (or any other feature I've missed ?)

cheers

Thibaut

Data Header on every page

I have a report grouped by accounts. Can I generate something like this:-
Joe Blogs Inc... [Header]
--
Client: Microsoft Group Page 1/5
Total Page 1/25
Account No:- 123456 Address :- 123 Infinate Loop
Account Name : MSFT Location :- Competition Headquarters
Name | Date | Item | Code | Desc | Qty | Price
MSFT 6/7/05 123 3456 Netscape 1 $2
________________________________________________ [5 pages later]
Client :- GOOGLE Group Page 1/20
Total Page 6/25
Account No:- 876543 Address :- 678 Google Valley
Account Name : GOOG Location :- Googlville
Name | Date | Item | Code | Desc | Qty | Price
GOOG 6/7/05 123 3456 Netscape 1 $2
--
Page 6 - 25 [Footer]
Above shows 2 groups, each group has a small synopsis before the detail data
begins. I have been racking my brains trying to get RS to do just this. I
intend to have 1 dataset which contains all this data(joined in the sql),
however I have no problems having 2 datasets, 1 for Detail and 2 for Synopsis
if it is so requried - I just need directions on how to join the 2.
Hope ya'll can help...It would probably be best to use only one dataset. It looks like you can do
all of this in a single table with groups and merged columns. If not, then
a list with an embedded table.
Paul Turley
"d pak" <dipakb@.exchnage.ml.com> wrote in message
news:414D274C-BB96-42DD-9BD0-B5DE142EC79A@.microsoft.com...
>I have a report grouped by accounts. Can I generate something like this:-
> Joe Blogs Inc... [Header]
> --
> Client: Microsoft Group Page 1/5
> Total Page 1/25
> Account No:- 123456 Address :- 123 Infinate Loop
> Account Name : MSFT Location :- Competition Headquarters
> Name | Date | Item | Code | Desc | Qty | Price
> MSFT 6/7/05 123 3456 Netscape 1 $2
> ________________________________________________ [5 pages later]
> Client :- GOOGLE Group Page 1/20
> Total Page 6/25
> Account No:- 876543 Address :- 678 Google Valley
> Account Name : GOOG Location :- Googlville
> Name | Date | Item | Code | Desc | Qty | Price
> GOOG 6/7/05 123 3456 Netscape 1 $2
> --
> Page 6 - 25 [Footer]
> Above shows 2 groups, each group has a small synopsis before the detail
> data
> begins. I have been racking my brains trying to get RS to do just this. I
> intend to have 1 dataset which contains all this data(joined in the sql),
> however I have no problems having 2 datasets, 1 for Detail and 2 for
> Synopsis
> if it is so requried - I just need directions on how to join the 2.
> Hope ya'll can help...