Automatically setup table from dataset

I need to add tables to an existing "report" from datasets that have 50+
columns. Each table is a different dataset. Is there anyway to replicte the
behiour of the wizard on an existing report? Adding columns one at a time is
a pain, it'd be easier for me to add them all and then remove the few I don't
need.
And yes I know that db tables that wide and reports with this much info are
somewhat useless but I'm forced to swim in the pond I'm in.Can't you just copy and paste a previously created table? It will maintain
all the properties of the table.
>I need to add tables to an existing "report" from datasets that have 50+
> columns. Each table is a different dataset. Is there anyway to replicte
> the
> behiour of the wizard on an existing report? Adding columns one at a time
> is
> a pain, it'd be easier for me to add them all and then remove the few I
> don't
> need.
> And yes I know that db tables that wide and reports with this much info
> are
> somewhat useless but I'm forced to swim in the pond I'm in.

Automatic synchronization of lookup columns

Data Flow:

Step 1: I use a OleDb Source Task to read data from a table (say table A) with 5 columns.

Step 2: Then I use a lookup task to add a sixth column (resulting from lookup on table B).

Step 3: Then I save the 6 columns to table C using a OleDb Destination Task.

Now, I go to underlying database and modify the tables like:

- remove 1 column from table A (not the column used for lookup later).
- remove 1 column from table B (not the columns used for lookup and not the columns retrieved after lookup)
- remove 1 column from table C (same as the one removed from table A).

If I get back to SSIS designer, it is nice enough to show warnings and ask whether I want to fix the metadata mismatch automatically. But, it does this for OleDb Source and Destination tasks only and NOT for lookup task. If I open the lookup task and hit the columns tab, it still shows old coulmn set for table B. It will be nice if these can be fixed automatically too. Or is it too much to ask for?

thanks,
Nitesh

Yes it'd be nice :)
You will notice similar behaviors also in other trasformations in the data flow (like Union All eg.). If you modify the structure of your data sources, you do need to check almost all of the sources, tasks and destinations involved.
Some months ago i asked in the beta place for a "Synchronize All" botton in the Data Flow editor form. But perhaps there will be better solutions then mine.
|||

Can you open a DCR for this?

thanks

Auto Resize a report width?

Hi,

I have a very big report with 60 columns and some columns are invisible.

The problem is now if I have 30 columns that are invisible (hidden=TRUE), the report width still stays at 60 columns width, and the Horizontal scroll bar become very long and it just scroll to blanks.

I would like to know how to resize the report after it is generated?

Thanks.

Jon

Anyone here can help pls?|||Is it possible you can do it as a matrix, this will at least allow dynamic size based on columns.

?|||

Hello Jon,

the first thing you will have to do is to set the width of the columns to 0 - invisble does not shrink the column automatically but will bring up a blank space with the standard width of the column instead.

the second thing should be to rethink about your report design - I personnaly would consider that 30 columns is way to much information for the user...

third thing:

Why do you want to 30 columns marked as visible?

just delete them instead...

Or is the visibility triggered via a report parameter?

Than you should think about using a matrix instead of a table and also should the database do the work and the decision which element should be placed in the database. reducing the data set will lead to a improved performance.

cheers

Markus

|||

If this report is to be run interactively through the reportviewer what you could do is run the report as a local report. You then could load the report layout into memory and modify the report width dynamically (xpathnavigator class would be one way) and then supply the modified RDL as a memory stream to the report viewer.

|||

thanks for all the reply.

i will try all the solution you have suggested.

Auto Resize a report width?

Hi,

I have a very big report with 60 columns and some columns are invisible.

The problem is now if I have 30 columns that are invisible (hidden=TRUE), the report width still stays at 60 columns width, and the Horizontal scroll bar become very long and it just scroll to blanks.

I would like to know how to resize the report after it is generated?

Thanks.

Jon

Anyone here can help pls?|||Is it possible you can do it as a matrix, this will at least allow dynamic size based on columns.

?|||

Hello Jon,

the first thing you will have to do is to set the width of the columns to 0 - invisble does not shrink the column automatically but will bring up a blank space with the standard width of the column instead.

the second thing should be to rethink about your report design - I personnaly would consider that 30 columns is way to much information for the user...

third thing:

Why do you want to 30 columns marked as visible?

just delete them instead...

Or is the visibility triggered via a report parameter?

Than you should think about using a matrix instead of a table and also should the database do the work and the decision which element should be placed in the database. reducing the data set will lead to a improved performance.

cheers

Markus

|||

If this report is to be run interactively through the reportviewer what you could do is run the report as a local report. You then could load the report layout into memory and modify the report width dynamically (xpathnavigator class would be one way) and then supply the modified RDL as a memory stream to the report viewer.

|||

thanks for all the reply.

i will try all the solution you have suggested.

Auto Resize a report width?

Hi,

I have a very big report with 60 columns and some columns are invisible.

The problem is now if I have 30 columns that are invisible (hidden=TRUE), the report width still stays at 60 columns width, and the Horizontal scroll bar become very long and it just scroll to blanks.

I would like to know how to resize the report after it is generated?

Thanks.

Jon

Anyone here can help pls?|||Is it possible you can do it as a matrix, this will at least allow dynamic size based on columns.

?|||

Hello Jon,

the first thing you will have to do is to set the width of the columns to 0 - invisble does not shrink the column automatically but will bring up a blank space with the standard width of the column instead.

the second thing should be to rethink about your report design - I personnaly would consider that 30 columns is way to much information for the user...

third thing:

Why do you want to 30 columns marked as visible?

just delete them instead...

Or is the visibility triggered via a report parameter?

Than you should think about using a matrix instead of a table and also should the database do the work and the decision which element should be placed in the database. reducing the data set will lead to a improved performance.

cheers

Markus

|||

If this report is to be run interactively through the reportviewer what you could do is run the report as a local report. You then could load the report layout into memory and modify the report width dynamically (xpathnavigator class would be one way) and then supply the modified RDL as a memory stream to the report viewer.

|||

thanks for all the reply.

i will try all the solution you have suggested.

Auto populating the fields box.

After I hook up a datasource with a stored procedure, I
run the query and expect the fields box to populate with
the retun query columns. This does not happen. Are there
certain conditions that the query has to meet for SQL RS
to do this, for example, does the query have to return a
permanent table?
Thanks,Yes.
For your query, in order to update the fields list, you will need to click
on the "Refresh Fields" button in the Data Set toolbar in Data View.
The report designer has two methods available to update the fields list.
1. Automatic (fast, low-cost): The Schema is queried by calling
IDbCommand.ExecuteReader(SchemaOnly), which returns schema information
without executing the query (parameter values are not needed). This method
is automatically invoked when you switch from Data to Layout View. It is
fast because the query doesn't have to be executed. However, there are
queries whose schemas can only be discovered by running the query. The
queries which don't return a schema include stored procedures that either
use dynamic SQL or use temporary tables. Most SQL command text and stored
procedure calls with a fixed schema work fine here.
2. Manual (slow, higher-cost): The schema is queried by calling
IDbCommand.ExecuteReader(SingleResult), which executes the query returning
the 1st row and also schema information. This method is invoked when you
click on the "Refresh Fields" button in the Data View (query designer). For
stored procedures, this method requires asking the user for sample parameter
values and then running the query with those values.
--
Jerry Povse
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"stevec" <anonymous@.discussions.microsoft.com> wrote in message
news:27dae01c4635a$b1d41810$a301280a@.phx.gbl...
> After I hook up a datasource with a stored procedure, I
> run the query and expect the fields box to populate with
> the retun query columns. This does not happen. Are there
> certain conditions that the query has to meet for SQL RS
> to do this, for example, does the query have to return a
> permanent table?
> Thanks,

auto number

I have two columns A and B, I want to auto-number B with respect to A
How can it be done?
For eg. I will have data like column A column B
A 01
A 02
A 03 ...
then A1 01
A1 02
if the next value for column A = A1, then value for column B should be 03
and if the next value for columnA = A then the next value for column B shoul
d
be 04
Thank you,
-Meselect id, colid,
(select count(*) from syscolumns as B
where A.id = B.id
and A.colid >= B.colid)
from syscolumns as A
order by 1, 2
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 13:53:02 -0700, Me <Me@.discussions.microsoft.com>
wrote:

>I have two columns A and B, I want to auto-number B with respect to A
>How can it be done?
>For eg. I will have data like column A column B
> A 01
> A 02
> A 03 ...
> then A1 01
> A1 02
>if the next value for column A = A1, then value for column B should be 03
>and if the next value for columnA = A then the next value for column B shou
ld
>be 04
>Thank you,
>-Me|||Some examples here.
http://www.aspfaq.com/2427
"Me" <Me@.discussions.microsoft.com> wrote in message
news:4D0AF582-BAD2-49B1-B421-4D22ACEEFE71@.microsoft.com...
>I have two columns A and B, I want to auto-number B with respect to A
> How can it be done?
> For eg. I will have data like column A column B
> A 01
> A 02
> A 03 ...
> then A1 01
> A1 02
> if the next value for column A = A1, then value for column B should be 03
> and if the next value for columnA = A then the next value for column B
> should
> be 04
> Thank you,
> -Me
>