Report Formatting: February 2012

Saturday, February 25, 2012

Report Inside a SubReport

I am running into a situation where i would like a report inside a
subreport.
Is it possible to accomplish this. ?
thxthx. had error in my sample first time, thought it might not be possible.
fixed it it. works!!! thx
"nkg" <xyz@.yahoo.com> wrote in message
news:e65TZcKmHHA.2552@.TK2MSFTNGP06.phx.gbl...
>I am running into a situation where i would like a report inside a
>subreport.
> Is it possible to accomplish this. ?
> thx
>

Report in SSRS using cube in SQL 2000

I want to create reports in SSRS that use cube in SQL 2000, I don't know which type of connection that i have to use,

I tested it with Microsoft SQL Analysis Services, means I connect to my cube with this type , everything is ok in developpement (create MDX query , make reports...), but in production It gives me this error message (cannot connect to the datasource),

so I don't know how to solve this problem,

Thanks in advance for your help...,

Regards

Posting the full error message would help.

Posting it in the Reporting Services forum would help even more.

-Jamie

Report in SQL Server 2005 - Suggestion needed

Hi,

I want to develop a monthly salary report for our employees. The salary is calculated on daily basis for individiaul employees. Thus the report is supposed to display Employee Name, ID etc + 31 columns (representing maximum days in a month). The report will display the employee basic information along with their salary calculation for each day. So its like 35 columns report. I already have used a cursor in my stored procedure with SQL Server 2005 to get this report (displayed in .NET Page) but the performance is more than worst and takes even 3-4 minutes to compile (which ofcourse is unacceptable). There are around 300 employees and they belong to different departments. When we select a given department, all the respective employees report has to be displayed. If i select All Departments, then the entire 300 employees report has to be displayed. I dont want to cache the data as it will have a big performance issue for the server.

I want some good suggestion on how to develop a monthly report in SQL Server 2005 which can display the data with optimized performance. Kindly help of any guru will be highly appreciated.

Regards...

Hello my friend,

I assume that your procedure for the calculations works but it is just slow. My advise would be to create a new table that stores this calculated information. After all, it will not change once calculated. What your employee earned last month each day will not change because it is in the past. Your calculated table, which you will populate from your stored procedure that does all of the current calculations would look something like this: -

EmployeeID DayDate Amount
101 2007/08/01 200
101 2007/08/02 250
101 2007/08/03 200
102 2007/08/01 180
...

Each record will be unique by the combination of the EmployeeID and DayDate.
At the end of each day, you run your stored procedure for all employees for the amounts that they earned just for that day, which will be inserted into the table above.

Then when you need to query this data, it will be much quicker and simpler query. This is the most effective way of speeding things up but if you have any concerns about this approach, please let me know.

Kind regards

Scotty

|||

Hi Scotty,

Thanks for the prompt reply and sorry for the late reply from my side. Well the problem is that i cannot create any table and thus have to use the actual values in the tables, calculate them and figure them out. I am using my cursor in the following manner:

Declare Temporary Table - Temp

Cursor -- Get All employees based on some condition

For each employee, Loop = 1 - 31 times (days in a month)

For each day (adding Loop to 1st Day of Month Selected), calculate salary and insert in Temp

//// In this temporary table, we have 31 rows inserted for each employee

End Loop

End Cursor

Pivot table (rotate table such that 31 rows for each employee are converted into a single row with 31 columns) to get the monthly report...

Kindly, could you give your expert opinion on how to improve this procedure...

report in SQL 2005 problem

Hi,
I am using the Business Intelligence Development studio to create a
simple report in SQL 2005 server. I have created a dataset and tried to
connect to a database and it failed to connect. An error is given out and
said that it cannot connect to the database because the server does not
allow remote connection and error 40. Can anyone tell me how to enable
"allow remote connection" in a database or how to solve this problem?
Thanks.Use the "SQL Server Surface Area Configuration" tool.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using the Business Intelligence Development studio to create a
> simple report in SQL 2005 server. I have created a dataset and tried to
> connect to a database and it failed to connect. An error is given out and
> said that it cannot connect to the database because the server does not
> allow remote connection and error 40. Can anyone tell me how to enable
> "allow remote connection" in a database or how to solve this problem?
> Thanks.
>|||Hi,
I tried to select "Local and remote connection" - "TCP/IP" and
"TCP/IP and named pipe" option and run again. But still got the same error.
Can anyone please help? Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
> Use the "SQL Server Surface Area Configuration" tool.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am using the Business Intelligence Development studio to create
>> a simple report in SQL 2005 server. I have created a dataset and tried to
>> connect to a database and it failed to connect. An error is given out and
>> said that it cannot connect to the database because the server does not
>> allow remote connection and error 40. Can anyone tell me how to enable
>> "allow remote connection" in a database or how to solve this problem?
>> Thanks.|||Sorry Folks. I got it. I just didn't point to the correct server. Cheers
"00KobeBrian" <a@.b.com> wrote in message
news:uhhxA8z6GHA.4644@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to select "Local and remote connection" - "TCP/IP" and
> "TCP/IP and named pipe" option and run again. But still got the same
> error. Can anyone please help? Thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ue7WsZV6GHA.3452@.TK2MSFTNGP05.phx.gbl...
>> Use the "SQL Server Surface Area Configuration" tool.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:%23shnTtR6GHA.4348@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am using the Business Intelligence Development studio to
>> create a simple report in SQL 2005 server. I have created a dataset and
>> tried to connect to a database and it failed to connect. An error is
>> given out and said that it cannot connect to the database because the
>> server does not allow remote connection and error 40. Can anyone tell me
>> how to enable "allow remote connection" in a database or how to solve
>> this problem? Thanks.
>

report in SQL 2005 problem

Report in Report Manager (server) does not look the same way that it looks in the Visual Studio

I am using SSRS 2005 and my server is a Microsoft Windows Server 2003 Enterprise Edition Service Pack 1.

Most of the differences between VS and Report Manager are formatting differences. Borders won't show up, scrolling does not work etc. Is there something i need to upgrade on the server.

Last thing but not the least. Everytime i change something in regards to the parameters i need to delete the report from the report manager and deploy in order for the change to appear on the Report Manager. Is there a fix for this?

Could somebody please help me with these?

1. differences in rendering between design-time and run-time -- the renderer used in Report Designer is quite different than the renderer used in Report Manager. The Designer renderer is GDI-based, whereas the Report Manager renderer is HTML-based. There are many differences between these renderers, including handling of overlapping items, borders, and pagination.

2. changes to parameters made at design-time require deletion and republishing -- this is an artifact of our "server wins" philosophy. Since you can change parameter characteristics in the Report Server after a report has been published 9and therefore which may conflict with the characteristics stored in the RDL), it is indeterminate which version of the parameters a user expects to be persisted -- do you expect customizations made after publishing to dominate, or does republishing dominate? We chose to go with the server's version of parameters. Note that if we chose the other way, you would lose server-based parameter customizations everytime you republished an RDL. Admittedly it it would be nice to give users control over this, like with an RDL-based design-time option to choose which parameter collection dominates, or a publish-time option so you could choose one or the other at publish time.

|||You mean there is no way i can reproduce my params changes in the report manager with out deleting the report itself. Everytime i do that it deletes all the subscriptions on my report. Isn't there any work around?|||

Go ahead and republish the RDL, but don't bother making changes to the parameter properties in Report Designer -- just make them directly in the Report Manager or Management Studio UI.

If you need to change the # or name of the parameters, that will have to be done in Report Designer.

Report in Report Manager (server) does not look the same way that it looks in the Visual Stu

I am using SSRS 2005 and my server is a Microsoft Windows Server 2003 Enterprise Edition Service Pack 1.

Most of the differences between VS and Report Manager are formatting differences. Borders won't show up, scrolling does not work etc. Is there something i need to upgrade on the server.

Could somebody please help me with these?

Go ahead and republish the RDL, but don't bother making changes to the parameter properties in Report Designer -- just make them directly in the Report Manager or Management Studio UI.

If you need to change the # or name of the parameters, that will have to be done in Report Designer.

Report in Multiple Columns

I have a report, let's say it has two table columns, Name and Phone. I need to format this report so that it will print Name and Phone top to bottom, and then start at the top of a second column instead of going to another page. Do I do this using body columns, and if so, can someone explain how. I have tried by changing the column property of the body to 2, but it just moved a table column over the the other body column. Am I heading down the wrong path? I am using VS 2003 to create the report.

Thanks

Setting the column property of the body is the right way to accomplish this. However, you need to check the page width value of the report. When Body.Column is 2, the report width is divided in half, if that is smaller than the body width/table width, your table'd get splitted.

Report in CRM ?

Hi All,
I created a report for Microsoft CRM 3.0... Do I have to "deploy" it
to the CRM server in order to use it in CRM or only to "build" the RDL
and add it in CRM ?
Any tips on this will be very helpfull
ThanksRDL is all XML, I dont really think "building" does much
"Frank" <flefe2000@.gmail.com> wrote in message
news:1170335048.873329.127820@.s48g2000cws.googlegroups.com...
> Hi All,
> I created a report for Microsoft CRM 3.0... Do I have to "deploy" it
> to the CRM server in order to use it in CRM or only to "build" the RDL
> and add it in CRM ?
> Any tips on this will be very helpfull
> Thanks
>

Report in Calendar/schedule layout

Is it possible to produce a report which is displayed as a calendar, similar to outlook?

im using rs2000 with no option of an upgrade to 2005

any help would be appreciated!

Unless you don′t use any pre-page which sends the parameters to the reportsserver for rendering the report with the specified paramters, there is no way. You can′t change the parameter collection in SQL Server 2000.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

What about for SQL 2005? I need to display a report that is grouped by date in calendar format with totals appearing in the date boxes. I'm guessing I can figure out a way to do this in a Matrix, but it will be very cumbersome and prone to error I'm guessing.

If anyone knows, it is appreciated.

|||Never mind. I didn't realize how relatively easy it would be to make a calendar report using the Matrix control in RS2005. I'm up and running.|||

I am trying to do something similar, but am having a problem.

Time is down the left hand side (the rows), and a room is each column. The problem is when rooms have overlapping times.

For example, room A is scheduled 1:00 - 2:00, and room B is 1:30 - 2:30. How do you avoid a line going through room A at 1:30?

Thanks,

Bob

|||You could try using a 3rd part component such as Dundas Calendar for Reporting Services.|||

I would recommend using the smallest increment of time as your separator. In the case above 30 minutes. In this way, you have the flexibility to have different rooms per half-hour, but you can also have the same room show at 1:00 and 1:30 to cover the 1 - 2 slot.

In this last case, to remove the line between 1:00-1:30 and 1:30-2:00 I am guessing you could use an expression on the BorderStyle for the Top. I haven't looked in detail at this, but I would think you could evaluate if the previous half-hour was occupied for this room and turn off the boundry. I don't know how easy this would be to accomplish, but it is worth looking at.

Hope that helps.

|||

Thanks for the replies.

The "smallest increment" methodology of csi_hugh is actually what I was trying. Using an expression for the borderstyles (top,bottom)does work to make the consecutive cells in the column look like one. The problem I have is that the increments are actually only 5 minutes, and the text for a classroom event can be rather long. The text has to either fit in one actual cell, or be split up programmatically amongst consecutive cells.

Thanks for the ideas, Bob

|||I think you can work around the text split issue with a rectangle in your table cell.|||

Does anyone having an example of one of these reports they have worked out?I tried doing this in a matrix but I am having a lot of trouble.The column grouping is room number and then I have rows for each time interval.I can access only one record per room which means I can only fill in one time interval.Any suggestions?

gisinnovations@.gmail.com

Report in Calendar/schedule layout

Is it possible to produce a report which is displayed as a calendar, similar to outlook?

im using rs2000 with no option of an upgrade to 2005

any help would be appreciated!

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

If anyone knows, it is appreciated.

|||Never mind. I didn't realize how relatively easy it would be to make a calendar report using the Matrix control in RS2005. I'm up and running.|||

I am trying to do something similar, but am having a problem.

Time is down the left hand side (the rows), and a room is each column. The problem is when rooms have overlapping times.

For example, room A is scheduled 1:00 - 2:00, and room B is 1:30 - 2:30. How do you avoid a line going through room A at 1:30?

Thanks,

Bob

|||You could try using a 3rd part component such as Dundas Calendar for Reporting Services.|||

Hope that helps.

|||

Thanks for the replies.

Thanks for the ideas, Bob

|||I think you can work around the text split issue with a rectangle in your table cell.|||

gisinnovations@.gmail.com

Report in Calendar/schedule layout

Is it possible to produce a report which is displayed as a calendar, similar to outlook?

im using rs2000 with no option of an upgrade to 2005

any help would be appreciated!

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

If anyone knows, it is appreciated.

|||Never mind. I didn't realize how relatively easy it would be to make a calendar report using the Matrix control in RS2005. I'm up and running.|||

I am trying to do something similar, but am having a problem.

Time is down the left hand side (the rows), and a room is each column. The problem is when rooms have overlapping times.

For example, room A is scheduled 1:00 - 2:00, and room B is 1:30 - 2:30. How do you avoid a line going through room A at 1:30?

Thanks,

Bob

|||You could try using a 3rd part component such as Dundas Calendar for Reporting Services.|||

Hope that helps.

|||

Thanks for the replies.

Thanks for the ideas, Bob

|||I think you can work around the text split issue with a rectangle in your table cell.|||

gisinnovations@.gmail.com

Report in Calendar/schedule layout

Is it possible to produce a report which is displayed as a calendar, similar to outlook?

im using rs2000 with no option of an upgrade to 2005

any help would be appreciated!

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

If anyone knows, it is appreciated.

|||Never mind. I didn't realize how relatively easy it would be to make a calendar report using the Matrix control in RS2005. I'm up and running.|||

I am trying to do something similar, but am having a problem.

Time is down the left hand side (the rows), and a room is each column. The problem is when rooms have overlapping times.

For example, room A is scheduled 1:00 - 2:00, and room B is 1:30 - 2:30. How do you avoid a line going through room A at 1:30?

Thanks,

Bob

|||You could try using a 3rd part component such as Dundas Calendar for Reporting Services.|||

Hope that helps.

|||

Thanks for the replies.

Thanks for the ideas, Bob

|||I think you can work around the text split issue with a rectangle in your table cell.|||

gisinnovations@.gmail.com

Report in an iFrame

I'm using SQL Reporting Services (not 2005).

I have a report which is loaded into an IFRAME (called 'reportFrame') which is loaded as part of a page within another IFRAME (called 'mainFrame').

I have set the rc:LinkTarget parameter to be "reportFrame", so that on drill-down it reloads in the correct place.

However, in IE 6, this doesn't work it just loads the report into mainFrame. In Firefox, although the rendered output of the report is garbage, the report does load into reportFrame when the '+' buttons are clicked.

Any ideas?I have spent some time trying to figure out what was wrong, and

basically it would appear that, if an iFrame has ANY style

information (through the style attribute or stylesheet) then IE will

NOT enforce the LinkTarget.

My solution is place the iFrame within a DIV tag if you need to apply any style information.

It is absolutely shocking that something like this breaks it, but that's the IE we know and love/hate.

Report Hyperlink Opening New Window

Hi, I am trying to get a link out of a report to open another web browser
with the HTML
rs:target=_blank as part of the string parameter that is passed in...but it
still opens the link in the same window.
?How does one open a link out of a report so that it will open a new window?
THANKS!!!There is no rs:target parameter. I suppose you mean rc:LinkTarget=_blank
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"G. Ray Giacalone" <rgiacalone@.virtual.com> wrote in message
news:O9F1lJRxFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi, I am trying to get a link out of a report to open another web browser
> with the HTML
> rs:target=_blank as part of the string parameter that is passed in...but
> it still opens the link in the same window.
> ?How does one open a link out of a report so that it will open a new
> window?
> THANKS!!!
>|||ok, so I tried this and it didn't work...or maybe I didn't understand it...
I'm in a text box in a table under
properties>>advanced>>navigation>>Jump to URL
and I enter
="http://kingofthegreens.com&rc:LinkTarget=_blank"
results in it still opening in the same window'
"Robert Bruckner [MSFT]" wrote:
> There is no rs:target parameter. I suppose you mean rc:LinkTarget=_blank
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "G. Ray Giacalone" <rgiacalone@.virtual.com> wrote in message
> news:O9F1lJRxFHA.3720@.TK2MSFTNGP14.phx.gbl...
> > Hi, I am trying to get a link out of a report to open another web browser
> > with the HTML
> > rs:target=_blank as part of the string parameter that is passed in...but
> > it still opens the link in the same window.
> >
> > ?How does one open a link out of a report so that it will open a new
> > window?
> >
> > THANKS!!!
> >
>
>|||I am having a similar issue, I can't find the syntax for adding
rc:LinkTarget. I am trying to add a url with one query string parameter and
open in a new window.
Thanks!
"Ben Sullins" wrote:
> ok, so I tried this and it didn't work...or maybe I didn't understand it...
> I'm in a text box in a table under
> properties>>advanced>>navigation>>Jump to URL
> and I enter
> ="http://kingofthegreens.com&rc:LinkTarget=_blank"
> results in it still opening in the same window'
>
> "Robert Bruckner [MSFT]" wrote:
> > There is no rs:target parameter. I suppose you mean rc:LinkTarget=_blank
> > See also:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "G. Ray Giacalone" <rgiacalone@.virtual.com> wrote in message
> > news:O9F1lJRxFHA.3720@.TK2MSFTNGP14.phx.gbl...
> > > Hi, I am trying to get a link out of a report to open another web browser
> > > with the HTML
> > > rs:target=_blank as part of the string parameter that is passed in...but
> > > it still opens the link in the same window.
> > >
> > > ?How does one open a link out of a report so that it will open a new
> > > window?
> > >
> > > THANKS!!!
> > >
> >
> >
> >|||Here is a response of mine about exporting to CSV that covers your
situation. Note that you need to have SP1 or SP2 installed to be able to use
this:
>>>>>>
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Very nice and very fast.
>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter Rogers" <Peter Rogers@.discussions.microsoft.com> wrote in message
news:F4C1AC98-3B0D-443D-B18A-0E7896435958@.microsoft.com...
>I am having a similar issue, I can't find the syntax for adding
> rc:LinkTarget. I am trying to add a url with one query string parameter
> and
> open in a new window.
> Thanks!
> "Ben Sullins" wrote:
>> ok, so I tried this and it didn't work...or maybe I didn't understand
>> it...
>> I'm in a text box in a table under
>> properties>>advanced>>navigation>>Jump to URL
>> and I enter
>> ="http://kingofthegreens.com&rc:LinkTarget=_blank"
>> results in it still opening in the same window'
>>
>> "Robert Bruckner [MSFT]" wrote:
>> > There is no rs:target parameter. I suppose you mean
>> > rc:LinkTarget=_blank
>> > See also:
>> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "G. Ray Giacalone" <rgiacalone@.virtual.com> wrote in message
>> > news:O9F1lJRxFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> > > Hi, I am trying to get a link out of a report to open another web
>> > > browser
>> > > with the HTML
>> > > rs:target=_blank as part of the string parameter that is passed
>> > > in...but
>> > > it still opens the link in the same window.
>> > >
>> > > ?How does one open a link out of a report so that it will open a new
>> > > window?
>> > >
>> > > THANKS!!!
>> > >
>> >
>> >
>> >

Report Hit Counts

This is gonna be simple - but not for me!!
I need to record the number of hits each report gets from users. As a bonus,
also the parameter values passed would be usefull also.
What would be the simplist and most efficient way of achieving the above
please.
Thanks and Happy New Year
TonyThe report server automatically logs all Report Executions. Look at the
Execution Log table. More info can be found here:
http://msdn2.microsoft.com/en-us/library/ms155836.aspx
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:43FF9683-CA87-414F-890A-5D1F310F8E5B@.microsoft.com...
> This is gonna be simple - but not for me!!
> I need to record the number of hits each report gets from users. As a
> bonus,
> also the parameter values passed would be usefull also.
> What would be the simplist and most efficient way of achieving the above
> please.
> Thanks and Happy New Year
> Tony|||More than what Daniel has said, there is a sample package with reports, and
SSIS package which parses the logs, moves the data into a table, and provides
about 6 reports against them... It is exactly what you need.
Look for it in the samples for SQL..
(The reports are really nice!>)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tony Scott" wrote:
> This is gonna be simple - but not for me!!
> I need to record the number of hits each report gets from users. As a bonus,
> also the parameter values passed would be usefull also.
> What would be the simplist and most efficient way of achieving the above
> please.
> Thanks and Happy New Year
> Tony

Report History in SQL Reporting Service

Hi,

I am using SQL Server 2005 Reporting service. I am working on a report. I have made several subscriptions for it. Report runs according to subscription. SQL Server Reporting service also offers functionality to save report histories. Now how can I configure it so that whenever report runs according to its subscription, a history should be automatically saved?

If in case it is not possible by some automatic process then is there any way to set multiple schedules for history of the single report like multiple subscriptions can be set for single report.

Waiting for your response. Thanks in advance.

Regards,
Sulman.

Under the History Properties of the report, you need to select the option to:
Store all report execution snapshots in history.

There are also other options to indicate how many snapshots you want to keep, as well as creating snapshots on a regular schedule.|||Thanx, J Quick

Yes there is an option in Hisotry properties "Store all report execution snapshots in history". But let me clear how it works.

On the Execution property page there are two option.
1. "Render this report with the most recent data"
This option means that whenever the report will be executed, it will get fresh data from database by making query on it.
2. "Render this report from an execution snapshot"
This option means that an execution snapshot of report will be saved either once when the Apply button is pressed or it will keep on updating depending upon schedule. But at one time there will be only one execution snapsho. And whenever the report will be executed, it will be rendered from that execution snapshot and not from the fresh data of database.

Now i come to History property mentioned above to "Store all report execution snapshots in history". It will work only when the execution property of report is set to "Render this report from an execution snapshot". And if execution snapshot is scheduled to be updated then each time the execution snapshot will update it will also be saved in history as history snapshot. So for this history property to work, we have to sacrifice the updated and fresh data. But in my case, i have to get fresh data every time and also i want to save the history of that executed report. Try to think on this line.

Anyhow i appreciate your effort. Thanx again.

Sulman.
|||

>> "It will work only when the execution property of report is set to "Render this report from an execution snapshot".

I don't think the statement above is correct, but I could be wrong since it has been some time since I tried it.

As I recall, the options to "Render this report with the most recent data" and "Store all report execution snapshots in history" are on different property pages and aren't mutually exclusive.

Report History

Hello,
Is it possible to setup Reporting Services so that when each report is run
the snapshot is saved for historical reporting purposes?
So far the only way I can create historical reports is by manually clicking
on the "New Subscription" button.
Ideally we need a solution that will allow us to save reports that are run,
back them up nightly, weekly or monthly, and have the potential to restore
them if necessary.
Anyone have ideas?
Much Appreciated!
Benjamin PierceReport Manager allows a number of History options. Please review the
Properties section for a given report.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:%23UvX%23ZEZEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Is it possible to setup Reporting Services so that when each report is run
> the snapshot is saved for historical reporting purposes?
> So far the only way I can create historical reports is by manually
clicking
> on the "New Subscription" button.
> Ideally we need a solution that will allow us to save reports that are
run,
> back them up nightly, weekly or monthly, and have the potential to restore
> them if necessary.
> Anyone have ideas?
> Much Appreciated!
> Benjamin Pierce
>

Report Hiding Duplicates, not wanted

I created a report which has duplicates on one field. I want the report to display the duplicates, but cannot figure out how. Anywhere I have found where there is a "Hide Duplicates" option I have it turned off (in matrix properties), but the report matrix still hides the duplicates. I don't understand why this would happen since the other fields on the same record are not duplicates. I tried grouping but that was no help.

The query in the dataset DOES return the duplicates as expected.

Anyone know how to fix this, or if it is a bug?

Thanks,

Chris

Hide Duplicates could be set to true in one of your textboxes. Right click on all of your matrix columns (which are textboxes) and go to Properties where you will Hide Duplicates checkbox. Or you can even select the textbox and click on F4 to see the Properties window.

Shyam

Report Help..!

Hi All,

I have a set of "VB reports" binded to a wizard, where you can select fields which will appear on the report. Also you can change Group By and Order By Fields.

Initiallly this was designed and used for "A4" size papers. But now I need dynamically adjust the report for selected paper size. Anyone can guide me on this...?

I am hoping to use papers larger than "A4" only....

- Help meHow about designing seperate files for different sizes and display them accordingly?|||Dim Report As New dsrVoucher

Report.SetUserPaperSize 1400, 2200
Report.PaperSize = crPaperUser

then so on...

but.. size u 'll given is in pixel..
not in inches..cms..
well.. as i also suggest.. the better way to do so is to design different report..with different page setup...
crystal report have some very unfortunate and teasing issues.. while printing .. bcoz of printer based reporting system..

but very some time teasing....and always comfortable at any type of reporting...
neways
best of luck

Report help please...

Hey guys,

Im kind of new to crystal, well enough to be dangerous anyway... but I have a report that I am trying to build. let me give you a little background on what the database is like so you have an idea of what Im trying to do. I am reporting against a SQL database that is our CRM system, which we track calls in. so you open a ticket, and then there are journal entries that are linked to that object in another table.

so what i want to do is display the open calls for a particular company, and the last two journal entries.

what i have so far, is everythign but the last two journal entries. i can get it to display all of them, but i only want the last two.. otherwise the report is way to big. i was thinking there must be a "TOP" type function or somethign kind of like in sql, where i could say only top 2 or something like that.. any help would be good... thanks!Have you tried this:

"SELECT TOP 2 * FROM YourTable ORDER BY OneField Desc"

I think this SQL will extract last 2 items.|||I have thought about doing that, but not really sure where to put it... i mean there are so many spots in crystal to put formulas... do i put it right on the field? on the section of the report? or where? right now im trying to use the suppress function with a formula, thinking it should work.. but its not... either it suppresses everythign or nothing... i cant get it to work if the formula condition is met, which the help files says that it should work like that...

im trying

{mytable.datecolumn} < Last7Days

figured ok if it is less then the last 7 days then suppress it... but that doesnt seam to work..|||I have thought about doing that, but not really sure where to put it...

I use Vb code to execute SQL. Have you tried SQL Expression Fields on CR?

Report help

Is it possible in reporting services to do the following:
I have a tabular report with months going across, and sales person going
down, the data shows the total sales value for each salesperson for each
month.. this I can do and is easy :-)
The tricky part that I have not worked out is this:
For an individual sales person: if a months sales value is less than the
previous month then I need the cell background to be red, if the value is
higher than the previous month then I need the cell background to be green.
Thanks,
Ricci.I am assuming you are using a matrix.
This is possible under fairly restrictive conditions:
* The query must return data in the following form:
Salesperson Month MonthlySales
Joe Jan 100
Joe Feb 101
Joe Mar 99
Mary Jan 200
Mary Feb 199
Mary Mar 201
* All aggregations must be done in the query and not in the Matrix
* Data must be sorted by salesperson and month in the query
Once these conditions are meet you can add the following expression to
background color property for the matrix cell that contains the monthly
sales:
=iif(Fields!Salesperson.Value = Previous(Fields!Salesperson.Value),
iif(Fields!MonthlySales.Value < Previous(Fields!MonthlySales.Value),
"Red", "Green"), "White")
Improvements to the Previous() method are currently under consideration for
inclusion in a future release.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ricci Hammond" <ricci@.riccisoft.nospam.co.uk> wrote in message
news:uC0HnjbXEHA.1048@.tk2msftngp13.phx.gbl...
> Is it possible in reporting services to do the following:
> I have a tabular report with months going across, and sales person going
> down, the data shows the total sales value for each salesperson for each
> month.. this I can do and is easy :-)
> The tricky part that I have not worked out is this:
> For an individual sales person: if a months sales value is less than the
> previous month then I need the cell background to be red, if the value is
> higher than the previous month then I need the cell background to be
green.
> Thanks,
> Ricci.
>

Report Heading with dates

I'm trying to create a heading for my report.

I want

sales figures between 09/11/2006 to 09/04/2007.

The date comes from parameters

What I have done is created 4 text boxes

textbox 1 = sales figures between

textbox 2 = =datepart("D", Parameters!param_datef.Value) & "/" & datepart("M", Parameters!param_datef.Value) & "/" & datepart("YYYY", Parameters!param_datef.Value)

textbox 3 = To

textbox 4 = =datepart("D", Parameters!param_datet.Value) & "/" & datepart("M", Parameters!param_datet.Value) & "/" & datepart("YYYY", Parameters!param_datet.Value)

Ok the problem is the text boxes when previewed are all over the place.

In crystal reports you can add a formula into a text box after text

Is this sort of thing possible in Reporting services

Thanks for all your help

You should stick this all in one textbox, not four:

= "Sales Figures between " & datepart("D", Parameters!param_datef.Value) & "/" & datepart("M", Parameters!param_datef.Value) & "/" & datepart("YYYY", Parameters!param_datef.Value) & " to" & =datepart("D", Parameters!param_datet.Value) & "/" & datepart("M", Parameters!param_datet.Value) & "/" & datepart("YYYY", Parameters!param_datet.Value)

It also is probably not necessary for you to "build up" the date - Try the Format() function: Format(Parameters!Param.Value, "FormatMaskGoesHere")

|||

Thanks Russell for you help

For the date how would you use format to convert the date format from yyyy-mm-dd to dd/mm/yyyy

Thanks

|||Format(date, "dd/MM/yyyy")

Report Headers vs Page Headers?

Is there such a thing as a Report Header, which only shows up at the
beginning of the report?
This is in contrast to a Page Header which shows up at the top of each page.
Thanks,
Daniel WilliamsYes, there is a report header. Type the text you want to be in the header in
the Body section before your data region. Similarly, you can type report
footer text below the data region.
Charles Kangai, MCT, MCDBA
"Daniel Williams" wrote:
>
> Is there such a thing as a Report Header, which only shows up at the
> beginning of the report?
> This is in contrast to a Page Header which shows up at the top of each page.
> Thanks,
> Daniel Williams
>
>

Report Headers vs Page Headers

Hi, this is probably a simple question but I just can't figure it out. I'm using SQL Server Reporting Services 2005 sp1.

I'm trying to design a report that displays a particular embedded image on the first page, and then a different one on every subsequent page. I.e full, fancy company logo on the first page, and a trimmed down version on every other. In Crystal, I would have simply used the Report Header and Page Header sections to achieve this - easy.

However, in SQL Reporting Services, there is only a Page Header section - therefore whatever I place in the Page Header shows on every page. Now, I know how to stop it displaying on the first page, but I don't know how to display the other image instead.

I read in a support forum that it is possible to do this by "placing report items above or below your data regions" but I can't get it to work.

Grateful for any assistance.

Matt

Hello Matt,

Here's what I did to accomplish this task.

In your 'Page Header' section, place your trimmed down image (you want to display on all pages but the first), and set the PrintOnFirstPage property to False. Then, in the 'Body' section, move everything down enough to place your fancy company image at the top. Now, your fancy logo will show on the first page and none after, and your trimmed image will display on all other pages.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks very much, that was helpful. I think I'm nearly there - one last Q though! Now that the Page Header is set not to print on the first page it leaves a white blank space where it will appear on the other pages. Therefore, the image placed in the 'Body' section appears about 1/3 of the way down the first page. Do you know any way to make it appear at the top - at the same level as the Page Header one?

Thanks again

Matt

|||

Here’s another way you could do this:

Remove all the images you created earlier
Change PrintOnFirstPage back to true for the Page Header
In the BackgroudImage.Value property of the Page Header, type this: =IIf(Globals!PageNumber = 1, "Link to image that should show on Page 1", "Link to image that should show on the rest of the pages")
Resize your Page Header section to fit the images.

Unless your fancy and trimmed images are the same height, there will be blank space below the shorter of the two.Since you can’t dynamically change the size of a section, I think that’s as good as you’re going to get.

Hope this helps.

Jarret

|||Thanks - I'll give that a try. SSRS is great, but there are some features which Crystal has had for years which it is sadly lacking

|||I'm having the same issue. I've found questions about this dating back to July of 2004, but no answers. Is it simply impossible to make a Page Header not print (and not take up space) on the first page?

Report Headers vs Page Headers

Hi, this is probably a simple question but I just can't figure it out. I'm using SQL Server Reporting Services 2005 sp1.

I read in a support forum that it is possible to do this by "placing report items above or below your data regions" but I can't get it to work.

Grateful for any assistance.

Matt

Hello Matt,

Here's what I did to accomplish this task.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks again

Matt

|||

Here’s another way you could do this:

Remove all the images you created earlier

Change PrintOnFirstPage back to true for the Page Header

In the BackgroudImage.Value property of the Page Header, type this: =IIf(Globals!PageNumber = 1, "Link to image that should show on Page 1", "Link to image that should show on the rest of the pages")

Resize your Page Header section to fit the images.

Hope this helps.

Jarret

|||Thanks - I'll give that a try. SSRS is great, but there are some features which Crystal has had for years which it is sadly lacking

Report Headers showing on blank page

I hope someone will have time to answer a really basic RS question.

I have a simple report, the query will only return a few rows (16 - 20), my header is repeating on a 2nd blank page. I'm sure it's because I grouped on a column from the record set. I can not find where you delete the grouping.

Anyone have any good suggestions on RS books?

Thanks

Hey Marc,

There are a couple of places to check:

When you click on the table, are there rows that have little numbers on them (1,2, etc)? If so, you should be able to right-click on these and select Delete Groups.

Another place could be the Properties of the table (Select the table, right click on the Properties option). Select the Grouping tab. Delete any of the groups in there (usually named something like "details_group")

Good luck!

Jessica

Report Headers in Report Services

Is there a way to "freeze" the report headers in Report Services? I'd like to have it work like Excel does when you freeze panes. My users complain that when they scroll down the report page, they lose the column headers and lose track of which column is which.

In your Layout section for table settings properties. Set the FixedHeader setting to True to keep the headers visible as you scroll down

|||

So-called fixed headers are available starting with Reporting Services 2005.

They are supported for matrix (see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167377&SiteID=1) and table.

Matrix: you can either have it on the row groups or column groups.

Table:
a) you can have a fixed table header (select it in the table properties dialog)
b) or you can have left-most or right-most fixed table columns (select an entire table column, and look for the "FixedHeader" property in the VS properties window of report designer).

-- Robert

|||

HI Robert,

I want to create a report with SQL Server Reporting Service 2000 where I need to have a fixed header and one fixed coloumn containg names of row items. I tried to find "Fixed Header" property in table region of toolbox but could not locate it anywhere.Can you please kindly tell me where should i look for it?

Thank you in advance.

Bharat Gadhia.

Report Headers in Report Services

In your Layout section for table settings properties. Set the FixedHeader setting to True to keep the headers visible as you scroll down

|||

So-called fixed headers are available starting with Reporting Services 2005.

They are supported for matrix (see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167377&SiteID=1) and table.

Matrix: you can either have it on the row groups or column groups.

-- Robert

|||

HI Robert,

Thank you in advance.

Bharat Gadhia.

|||In the Grouping and Sorting Properties pop-up window that you get to when you click on Edit Group, that is where you will find (under the General tab) the following check box:
"Group header should remain visible while scrolling."
Note that one of the entries said it is a property of the GROUP.|||HOWEVER, that is in Reporting Services 2005. Also, in a matrix I froze a header on a column group, and in the Preview it was of frozen (although it did not function very elegantly), but when it got over to EXCEL it was not frozen at all. So, does anyone know: can you freeze a column header or row header in a matrix in Reporting Services and have it still be frozen after you EXPORT the report to EXCEL?

Report Headers & Footers

I see the Page Header and Page footer, but what about Report Header and Report Footers? Doesn't look like ssrs has this ability, am I wrong?

I was able to accomplish what I want by following the suggestion in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=377566&SiteID=1

IMO, this is lame. Come on, even Access has a Report Header & Report Footer, what were they thinking? Seems like to get simple stuff done in ssrs you have to basically find hacks and workarounds. Not impressed.

|||

Hello,

Would the table header/footer work for you?

Jarret

|||

Yes, I ended having to use the table header/footer like it is described in the link in my previous post. I still think this is kind of a hack and am wondering why ReportHeader & ReportFooter were left out.

|||

The link in your previous post takes me to another post by you for Parameter Validation Failed. Oh well, glad you got it working.

Maybe I am missing something, but I can't think of any reason why they wouldn't be considered synonymous. Can you give me an example of something a report header can do that a table header can't (or vice versa), just for my knowledge?

Jarret

|||Sorry about that! I have fixed the link to point to the correct article, thanks for pointing that out.

Report Headers

I'm new to Reporting Services, but am familiar with other reporting tools. My question is, is there really no way to reference a field in a page header? If so, I suppose you could repeat some header information on subsequent pages from the detail section of the report using a text box, but how is that done because when I check the property box "Repeat report item with data region on every page" nothing happens. What is the best way to repeat header information on every page that needs to contain field values/information?

Thanks for any help you can offer.

Referencing a field from page header/footer is not supported currently. When you set a textbox to repeat on each page, you need to pick a data region for the textbox to repeat with. Suppose you have a table that'd span multiple pages, set the data region to the table. Or you can select the table header/footer row, and set it to repeat on each page.|||

You can use a field in the report header. Drop a field in the report body and make it invisible, if you don’t need it in the report body. Then drop a textbox on a header and type =ReportItems!TextboxName.Value (The textboxname is where your field is in the body)

Regards

|||

Thanks, GNDSP, but this solution seems to be unsuitable when the data you need repeated on each page comes from a different data set.
What do you do if the data being shown in the report body comes from a different data set than the header text?

I hope the next version of RS fixes this limitation of repeating data on multiple pages, as well as introducing a better way of integrating reports with your own code... the current "Code" tab in the report properties dialog box only supports Visual Basic and is hardly a suitable code editing window... how about "Code Behind" for reports, that also supports C#?

Thanks

Graham

Report Headers

Thanks for any help you can offer.

Regards

|||

Thanks

Graham

Report Headers

I'm using VS 2003 and SQL Server 2000 Reporting Services SP2.
I haven't found anything in Reporting Services that lets me create a report
header (a header that shows up only on the first page of a report) but am
wondering if there is some method of making the page header visible on the
first page and invisible on all others.
Thanks.
JonDon't use the header, but simply start the report with it
"Jon Limmer" wrote:
> I'm using VS 2003 and SQL Server 2000 Reporting Services SP2.
> I haven't found anything in Reporting Services that lets me create a report
> header (a header that shows up only on the first page of a report) but am
> wondering if there is some method of making the page header visible on the
> first page and invisible on all others.
> Thanks.
> Jon
>
>|||You're kidding! That easy!! Cool!
and thanks...
Jon
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:51F10004-77A6-4061-91B3-3F8E58347B9E@.microsoft.com...
> Don't use the header, but simply start the report with it
>

report header: how do i have underline

im using tablerow for header of my report and i want to have underline for the labels. how do i do that?

thanks

change the TextDecoration property to Underline.

Mark

Report Header woes

I am creating a report which consists of data in two columns. I've set the Body section to have 2 columns in it. This works fine until I start working with the report header. I do not want a page header that shows up on every page, I only want a header on the first page, a report header.

I first tried to hide the header by placing it in the Page Header section, then putting in the "page > 1" expression in the visibility property. This does what I want, except a large blank space is present at the top of my report (the header is about 1.2 "). This is unacceptable.

So my next test was to put the header into the Body section so that it only shows up in the beginning of the report. Because of this, I will need to remove the columns from the Body and make it only 1 column now. I decided to put the multi-column section into a subreport that has 2 columns and add this to the body of my report. But, this failed; the data appears in one column only. I can change the width the ensure it fits and set all other sorts of properties, nothing works.

I've researched both issues and found that these are both "bugs" in reporting services, although I cannot find thedocumented bugs.

Is there any workaround to putting a header onto only the first page of a multi-column report? Other reporting engines are able to handle this with ease, I'm suprised by Reporting Services lack of a "Report Header" section. I am an experienced .NET developer, so if there is a programmatic way through this, I am game.

Thanks,

Scott Steigerwald

I realize this thread is over a year and a half old. The point of this reply is to bring the issue back among the recent posts; a "bump" as it were.

I am struggling with a near identical problem, and have already tried all the workarounds proposed by Mr. Steigerwald, to no avail.

I hope that after 20 or so months, some solution has been discovered.

Thanks,

David Emigh

|||I am pretty confident there is still no solution to this.
I actually faked a multi column report by manipulating the dataset I was pulling across, but this was a failry specific situation where I knew approximately how many rows were always being returned.|||

Thanks.

Sadly, I rather suspected this. At least I know now to look for a solution outside of SQL Reporting Services.

|||

Um, folks... hold on a sec .

I saw the re-post on this question and I think I have succeeded in doing what you want. (Sorry I didn't get around to posting this last night.)

It's a little hacky and it probably won't work for *every* situation, but I did get the basic parameters down:

* -- I've got a Body section with two columns

* -- I have a header that appears to stretch over both areas

* -- it "collapses" after the first page.

Here is what I did:

I couldn't get it to work as a Page header, for reasons you've already discussed. So, instead, I worked out how you could have a Body header that disappeared properly and stretched across.

The first step is to figure out where to put it. In my example, and I am not sure how many other ways there might be to do it, it's a group header and I page breaked on group. I don't think this is relevant, but whatever your data region you need a body element that will be placed at the top of every column.

The background of this page header element should be transparent, because we're going to change its contents for the second column (or more, if it's > 2 cols) and we want it to look as seamless -across as possible.

For the same reason (making the header look seamless-across), you probably want to set the colum spacing as low as possible (you can add some extra padding on the right side by adding an extra blank column, and merge the layout element across, to compensate).

Note that, just as we'll make the contents different for each col, text alignment can be an expression, so using what I"m doing here you could (say) have the left col header left aligned and the right col header right aligned.

I set the expression for this header element to =Code.MyUDF().

You can see my code below. Notice that the ColNo var is incremented each time, so I can use it both for "which column am I in" and "which page am I on".

The tricky part was just figuring out when the Visibility of the element would be evaluated with respect to this function being invoked -- so that I could set the visibility properly. With how I'm doing it here, the visibility expression has to be =Code.ColNo > 1 (one less than you might think, IOW).

The visibility expression would be a little different if I invoked one function to increment earlier, say in an earlier no-height line, and a second function to figure out the expressions to show, but this combined design works okay as well.

I also set the CanShrink property of this element to True to and I set the Visibility of the row to the same expression as the layout element.

Here's the code I used:

Code Snippet

Public ColNo AS Integer = 0

Function MyUDF AS String
Dim myheadpart As String
ColNo = ColNo + 1
If ColNo = 1
myheadpart= "1st part of header"
ElseIf ColNo = 2
myheadpart= "2nd part"
Else
myheadpart = ""
End If
Return myheadpart
End Function

... HTH,

>L<

|||

Thank you!

I've bookmarked this thread for future reference, as this situation will almost certainly come up again.

Unfortunately, in my particular situation, it doesn't seem applicable. I have a requirement to include an image that is wider than the the sum of the widths of the first column and the space between it and the second. The image has a minimum size and a non-negotiable aspect ratio.

I wasn't the thread starter on this one, but if I were, I would mark this as "answered"

Thanks again,

David

|||

Hi David

I see what you mean about the image -- I know this whole idea is a bit dodgy but there might be some way to split the image up into two? First make a "full image" the full width of the page, I mean with a lot of filler-empty space on the right (or on both sides, if you're trying to center it horizontally on the page), and then split it down the middle for column one and column two. No guarantees that it will work, of course, but worth a shot?

>> I would mark it

Well, I can mark it as a moderator but I won't <g>. I won't do that with my own answers unless they prove out (actually help somebody) <g>.

>L<

|||

Maybe I am misundertanding things but generally when I want a first page only header I create a second dataset that returns a single record by using a statment like "select x=1". Then I just use a table set to the dummy header at the top of my body section. A typical example where I would use it is for invoices.

|||

Lisa,

Perhaps another moderator will come along and do the deed which modesty prevents you doing yourself

My manager has decided on a different approach to this problem, one which precludes any image editing, but I will keep that in mind also.

David

|||

Hi there,

Yeah, a first page header *alone* is not the problem here. (There are lots of ways to do that -- for example you can group on something that will never break as an outer group...)

The problem we're trying to solve here is the multi-column report and getting the page header to stretch across those columns without using a page header...

>L<

|||

Hi David,

If you don't mind my asking... what did you and your manager settle on? (It's always good to be able to remove the requirement if the requirement is a PITA <rofl>)

>L<

|||

Hey Lisa,

The requirement didn't go away, we just opted to generate the report header and report body separately, then merge them later (probably as an active .pdf via a web app).

|||

Ah.

Well, good to know. (I have posted about combining files in a PDF a couple of times, and recently found a nice utility for this to complement my usual low-level ghostcript PDF approach... give a shout if you have a difficulties.

FWIW there are many reasons to do this that have nothing to do with your current problem, for example different orientation of a cover page. Repeat after me: a report is a document section, not a document. Extended essay on this subject here: http://spacefold.com/lisa/post/Reporting-Convergences-Chapter-The-one-about-multiple-sets-of-printing-instructions.aspx.

>L<

Report Header woes

I've researched both issues and found that these are both "bugs" in reporting services, although I cannot find thedocumented bugs.

Thanks,

Scott Steigerwald

I realize this thread is over a year and a half old. The point of this reply is to bring the issue back among the recent posts; a "bump" as it were.

I am struggling with a near identical problem, and have already tried all the workarounds proposed by Mr. Steigerwald, to no avail.

I hope that after 20 or so months, some solution has been discovered.

Thanks,

David Emigh

Thanks.

Sadly, I rather suspected this. At least I know now to look for a solution outside of SQL Reporting Services.

|||

Um, folks... hold on a sec .

I saw the re-post on this question and I think I have succeeded in doing what you want. (Sorry I didn't get around to posting this last night.)

It's a little hacky and it probably won't work for *every* situation, but I did get the basic parameters down:

* -- I've got a Body section with two columns

* -- I have a header that appears to stretch over both areas

* -- it "collapses" after the first page.

Here is what I did:

I couldn't get it to work as a Page header, for reasons you've already discussed. So, instead, I worked out how you could have a Body header that disappeared properly and stretched across.

I set the expression for this header element to =Code.MyUDF().

You can see my code below. Notice that the ColNo var is incremented each time, so I can use it both for "which column am I in" and "which page am I on".

I also set the CanShrink property of this element to True to and I set the Visibility of the row to the same expression as the layout element.

Here's the code I used:

Code Snippet

Public ColNo AS Integer = 0

... HTH,

>L<

|||

Thank you!

I've bookmarked this thread for future reference, as this situation will almost certainly come up again.

I wasn't the thread starter on this one, but if I were, I would mark this as "answered"

Thanks again,

David

|||

Hi David

>> I would mark it

Well, I can mark it as a moderator but I won't <g>. I won't do that with my own answers unless they prove out (actually help somebody) <g>.

>L<

|||

Lisa,

Perhaps another moderator will come along and do the deed which modesty prevents you doing yourself

My manager has decided on a different approach to this problem, one which precludes any image editing, but I will keep that in mind also.

David

|||

Hi there,

Yeah, a first page header *alone* is not the problem here. (There are lots of ways to do that -- for example you can group on something that will never break as an outer group...)

The problem we're trying to solve here is the multi-column report and getting the page header to stretch across those columns without using a page header...

>L<

|||

Hi David,

If you don't mind my asking... what did you and your manager settle on? (It's always good to be able to remove the requirement if the requirement is a PITA <rofl>)

>L<

|||

Hey Lisa,

The requirement didn't go away, we just opted to generate the report header and report body separately, then merge them later (probably as an active .pdf via a web app).

|||

Ah.

>L<

Report Header woes

I've researched both issues and found that these are both "bugs" in reporting services, although I cannot find thedocumented bugs.

Thanks,

Scott Steigerwald

I realize this thread is over a year and a half old. The point of this reply is to bring the issue back among the recent posts; a "bump" as it were.

I am struggling with a near identical problem, and have already tried all the workarounds proposed by Mr. Steigerwald, to no avail.

I hope that after 20 or so months, some solution has been discovered.

Thanks,

David Emigh

Thanks.

Sadly, I rather suspected this. At least I know now to look for a solution outside of SQL Reporting Services.

|||

Um, folks... hold on a sec .

I saw the re-post on this question and I think I have succeeded in doing what you want. (Sorry I didn't get around to posting this last night.)

It's a little hacky and it probably won't work for *every* situation, but I did get the basic parameters down:

* -- I've got a Body section with two columns

* -- I have a header that appears to stretch over both areas

* -- it "collapses" after the first page.

Here is what I did:

I couldn't get it to work as a Page header, for reasons you've already discussed. So, instead, I worked out how you could have a Body header that disappeared properly and stretched across.

I set the expression for this header element to =Code.MyUDF().

You can see my code below. Notice that the ColNo var is incremented each time, so I can use it both for "which column am I in" and "which page am I on".

I also set the CanShrink property of this element to True to and I set the Visibility of the row to the same expression as the layout element.

Here's the code I used:

Code Snippet

Public ColNo AS Integer = 0

... HTH,

>L<

|||

Thank you!

I've bookmarked this thread for future reference, as this situation will almost certainly come up again.

I wasn't the thread starter on this one, but if I were, I would mark this as "answered"

Thanks again,

David

|||

Hi David

>> I would mark it

Well, I can mark it as a moderator but I won't <g>. I won't do that with my own answers unless they prove out (actually help somebody) <g>.

>L<

|||

Lisa,

Perhaps another moderator will come along and do the deed which modesty prevents you doing yourself

My manager has decided on a different approach to this problem, one which precludes any image editing, but I will keep that in mind also.

David

|||

Hi there,

Yeah, a first page header *alone* is not the problem here. (There are lots of ways to do that -- for example you can group on something that will never break as an outer group...)

The problem we're trying to solve here is the multi-column report and getting the page header to stretch across those columns without using a page header...

>L<

|||

Hi David,

If you don't mind my asking... what did you and your manager settle on? (It's always good to be able to remove the requirement if the requirement is a PITA <rofl>)

>L<

|||

Hey Lisa,

The requirement didn't go away, we just opted to generate the report header and report body separately, then merge them later (probably as an active .pdf via a web app).

|||

Ah.

>L<

Saturday, February 25, 2012

Blog Archive

About Me