One database item per page Reporting Services

I'm creating Reporting Services reports in Visual Studio. I create a new
report, and see the Data, Layout, and Preview tabs.
In the data tab, I say: "select name from mytable". Four names are returned.
I'd like to display one name per page.
I posted this question a few days ago. The folks that responded (thanks)
suggested that in the Layout tab, I could place a table or list on the form,
then drag the "name" from DataSet1 onto the table or list. The problem with
this approach is that all 4 names are grouped on the first page. I'l like to
see one name per page.
In reality, mytable contains dozens of fields which will go on the report
form. I want Reporting Services to print out a one page report for Mr. Able,
Mr. Baker. Mr. Charles, and so on.
--
RandyI think the best way to handle this is with the List control.
In the layout tab drag the list from the toolbox.
You can then drag the name (and any other field) into the list control.
You can then right click on the List control and select properties.
In the General tab, click "Edit details group..."
In the top line of the expression list, choose the name field from the drop
down.
Check the "Page break at end" checkbox.
Each instance of the list will now show up on it's own page. You can drag
whatever fields you want into the list box.
Hope this helps.
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Hi,
Hey Its simple. Suppose "select name from mytable" is your query.
After dragging table and placing fields as per your wish. Create a group
with "name" field, so whenever name changes it goes to the next page (dont
forget to click on "page break at end".
Amarnath
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Got it! Many thanks...
--
Randy
"Amarnath" wrote:
> Hi,
> Hey Its simple. Suppose "select name from mytable" is your query.
> After dragging table and placing fields as per your wish. Create a group
> with "name" field, so whenever name changes it goes to the next page (dont
> forget to click on "page break at end".
> Amarnath
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy|||That's exactly what I needed. Many thanks...
--
Randy
"Limey" wrote:
> I think the best way to handle this is with the List control.
> In the layout tab drag the list from the toolbox.
> You can then drag the name (and any other field) into the list control.
> You can then right click on the List control and select properties.
> In the General tab, click "Edit details group..."
> In the top line of the expression list, choose the name field from the drop
> down.
> Check the "Page break at end" checkbox.
> Each instance of the list will now show up on it's own page. You can drag
> whatever fields you want into the list box.
> Hope this helps.
>
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy

One column missing from datasets even after refresh?

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

I have.

One thing you can do is check for a matching .rdl.data file and blow that away, see if next data pull rewrites the cached data.

Another thing you can probably do is just add it in manually. Rightclick on the *name* of the dataset (underneath the Report DataSets node in the Datasets window) and choose "Add..."

>L<

|||

Thanks for the help! The first method doesn't work. Even if I add a new report from scratch, the available fields are missing that column if I put the sp in the query builder from report build wizard.

Rightclick on the name of the dataset and add the missing column works, but after I deploy the report and go view the report from brower, I got the following message:

An error has occurred during report processing.

Query execution failed for data set 'RegionalLevelByWeek'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||Hmm. I think the first method doesn't work if you have a (usually generated) schema file associated

with the data -- I'm not looking at reports right now, so I could be wrong) -- you might have to "refresh" that file as well.

On the second one, do you have to re-deploy the datasource to the server or something? (remember it's "don't overwrite datasources during deploy" by default) Same is true with default values of params -- given your situation as you put it in this message, that might well be involved.

Now that I look at what you're saying here though... As far as your current thing with the two SP's, you could just put a dummy column into the first result set, couldn't you? The important thing to remember is that there is going to be *one* set of columns associated with that report, never two, so if your proc can return differently based on a param you pretty much have to make them match.

I'm sorry I can't do a worked example right now, it's always *something* find-able though...

>L<

sql

OmitDocumentMap

I have a report on reporting server that I need to email as excel file weekly.
As default it comes with document map as the first excel tab when exported
which I need to stop from happening.
I have found on the internet that I can use this
<DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
but I dont know where to put it, I tried putting it in different places in
the xml but no good it keeps coming back with errors such as
Deserialization failed: The element 'Report' in namespace
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
has invalid child element 'DeviceInfo'
So please can anyone tell me exactly where does the device info need to go?bump
"jl45" wrote:
> I have a report on reporting server that I need to email as excel file weekly.
> As default it comes with document map as the first excel tab when exported
> which I need to stop from happening.
> I have found on the internet that I can use this
> <DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
> but I dont know where to put it, I tried putting it in different places in
> the xml but no good it keeps coming back with errors such as
> Deserialization failed: The element 'Report' in namespace
> 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
> has invalid child element 'DeviceInfo'
> So please can anyone tell me exactly where does the device info need to go?
>

OmitDocumentMap

I have a report on reporting server that I need to email as excel file weekly.
As default it comes with document map as the first excel tab when exported
which I need to stop from happening.
I have found on the internet that I can use this
<DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
but I dont know where to put it, I tried putting it in different places in
the xml but no good it keeps coming back with errors such as
Deserialization failed: The element 'Report' in namespace
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
has invalid child element 'DeviceInfo'
So please can anyone tell me exactly where does the device info need to go?HTML Viewer Commands (rc:)
You use HTML Viewer commands to tell SSRS 2005 how to render the report. You
can use the commands to control how the viewer appears to the user, as well
as to control certain aspects of how the report appears in the viewer.
-- Shows or hides the report document map.
rc:DocMap=true
Reeves
"jl45" wrote:
> I have a report on reporting server that I need to email as excel file weekly.
> As default it comes with document map as the first excel tab when exported
> which I need to stop from happening.
> I have found on the internet that I can use this
> <DeviceInfo><OmitDocumentMap>true</OmitDocumentMap></DeviceInfo>
> but I dont know where to put it, I tried putting it in different places in
> the xml but no good it keeps coming back with errors such as
> Deserialization failed: The element 'Report' in namespace
> 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'
> has invalid child element 'DeviceInfo'
> So please can anyone tell me exactly where does the device info need to go?
>

OLE DB Source Error Output

In the Input and Output Properties tab under Advance Editor for OLE DB Source, I cannot remove columns. I copied this Source from a standard template and have made the normal changes to make it work. However I keep getting this error...

Error: 0xC020837B at Load Server Security, OLE DB Source [1]: The output column "DBName" (1632) on the error output has no corresponding output column on the non-error output.

Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

DBName of course is one of the columns that no longer exist, but I can't remove. Whenever I try to remove one of the columns, I get this error...

Error at Load Server Security [OLE DB Source[1]]: The column cannot be deleted. The component does not allow columns to be deleted from this input or output. Is there anything that I can do to remove the columns? Is there just a simple setting that I can change to make this work?
If you just double click on the OLE DB source, you can choose "Columns" on the left hand side and unselect the columns you do not want in the data flow.|||There is a problem with that though because I can't make any changes within the source. I always get the error...

Invalid object name 'tempdb.dbo.Server_Roles. This is because the table isn't created yet. It gets created when the connection manager connects to each server. It is a temporary table. Thus, it won't let me make any changes that I can't make in advance editor.

-Kyle
|||

Why don't you create the table somewhere else, get it configured properly, then change the table name afterward?

|||

That doesn't work. Currently I created a table in the temp directory and pointed the connection manager towards that DB and server. I still can't click on column and I can't change anything unless I hit cancel. My guess is that it may not be connecting to the server and DB correctly. I don't get any explanation for the error, its just one of the H errors.

-Kyle

|||I guess I don't understand what you are trying to do. Please explain further with more detail.

OLE DB Command

I am having trouble figuring out how to use the OLE DB Command (for updating a record). On the first tab (Connection Managers), I can select my database server under 'Connection Manager'. When I looked at the second tab (Component Properties) I thought, this does not seem to have anything related to choosing a data TABLE to use. The third and fourth tabs do not seem to work because they require an output and none is there, and I can't add one (?)

When I selected the 3rd tab, I see the following at the bottom of the window :

Error at ReadAL3Files [OLE DB Command [6638]]: An OLE DB error has occured. Error code: 0x80040E0C. An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object"

So I look at the properties for the components, to see if there is one where 'text' is not set. Then I think, maybe I have to enter the update statement directly (under SQL Command). There didn't seem to be a way to build this kind of statement, so I guessed at the syntax

update [dbo].[BriteMeter]
set sampledatetime = [Column_7],
GradeID = [Column_8]
where SampleID = SampleID;

and switching to tab 3 and 4 showed a different error :
Statement(s) could not be prepared
Invalid column name 'Column 8'
Invalid column name 'Column 7'

I've tried entering those column names as described here : http://technet.microsoft.com/en-us/library/ms141138.aspx. Tried putting them in quotes, square brackets, I always get that error.

Am I approaching this right or am I missing something?

Dear Friend,

Create the update SQL statment inside database as a stored procedure with input and output parameters.

And in the OLE DB Command text property write: EXEC StoredProcedure ?,?

This example has 2 input parameters.

In my blog check the label SSIS and there is an example.

Helped?

regards!

|||Seriously? There is no simpler way to do an update than this? Well... ok.

|||

Dear friend,

In my opinion, is the better way to do that. The most advantages with this way is that:

1. You avoid SQL injection

2. The SP is already compiled in your database.

3. in the case you need to make some changes, you only need to change the SP in spite of change the stored procedure...

Helped? :-(

I hope so... if not tell me!

Kind regards!