Report Formatting: creating

Showing posts with label creating. Show all posts

Monday, March 26, 2012

Report of bigger tables

hi, anyone have a script for creating a report of the bigger tables
of a database in SQL 2000?Yep;
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/12/21/41.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Gustavo Villaran" <gvillaran@.pandero.com.pe> wrote in message
news:u3JjepQfHHA.2396@.TK2MSFTNGP04.phx.gbl...
> hi, anyone have a script for creating a report of the bigger tables
> of a database in SQL 2000?

Report of bigger tables

hi, anyone have a script for creating a report of the bigger tables
of a database in SQL 2000?Yep;
http://blogs.sqlserver.org.au/blogs...4/12/21/41.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Gustavo Villaran" <gvillaran@.pandero.com.pe> wrote in message
news:u3JjepQfHHA.2396@.TK2MSFTNGP04.phx.gbl...
> hi, anyone have a script for creating a report of the bigger tables
> of a database in SQL 2000?sql

Friday, March 23, 2012

Report Model On Top Of Analysis Services Cube

Hello, Creating a report model on top of a analysis services cube is very straight forward, but is it possible to modify that model such as adding folder structure?

Thanks

I am also interested in any information on modifying a Report Model created from SSAS datasource/cube.

Report Model On Top Of Analysis Services Cube

Hello, Creating a report model on top of a analysis services cube is very straight forward, but is it possible to modify that model such as adding folder structure?

Thanks

I am also interested in any information on modifying a Report Model created from SSAS datasource/cube.

Report Model in VM environment

What are the limitation when creating report models in VM environment?

Not certain what you mean by "VM environment" but if you are referring to Virtual Machine/Server, yes report models are supported.

Hope that helps.

Thanks, Jon

|||

Jon - Correct, I was refering to "Virtual Machine/Server". When trying to create report models that sits on "Big" tables, it happens very often that I get "Timeout Error" which usually occurs after 5min. All the timeout parameters in Data Source are set to zero but it still happens.

Thanks

|||

I might suggest running with gather statistics=false. This should increase performance of model generation as it will not need to query the data.

Otherwise, in what context is the timeout error occuring. Does the error message denote a SQL or IIS timeout?

Thanks, Jon

|||

Jon - It's a SQL timeout and usually it occurs when column uniqueness is being calculated.

Thanks

Wednesday, March 21, 2012

Report Model Generating Out of Memory Error

I am creating a report model of a large database with a few hundred tables, the idea being that the users can report on any part of the database and create perspectives for the areas they want users to have access to (In the past the users have moaned about report models in a CA product called Eureka as the models are too small, perspectives seemed a way of allowing one large model but limiting each perspective to a set of users?). Unfortunately presumably due to the size of the model visual studio is giving me an out of memory error when adding new tables. I have a two dual core xeons with 4Gig of ram and I'm running XP Pro 64bit with the developer edition of 2005 with service pack 1 and VS2005 is using around 1/2 a Gb. Is this a known issue and will there be a work around?

Seems to occur when removing anything from the report model. Can reproduce by deleting calculated fields and selecting save.|||This sounds like a bug. Can you describe a list of steps that would repro the issue?|||Its fairly simple I only have to delete a few fields or tables from my report model and it triggers an out of memory error. I can forward the report model if thats helpful? I would like to regenerate the fields from the report model by using the autogenerate/get rid of some of the autogenerated calculated fields bu can't as I get this error each time.|||

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

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

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.Xml.XmlEncodedRawTextWriter.FlushBuffer()
at System.Xml.XmlEncodedRawTextWriter.RawText(Char* pSrcBegin, Char* pSrcEnd)
at System.Xml.XmlEncodedRawTextWriter.RawText(String s)
at System.Xml.XmlEncodedRawTextWriterIndent.WriteIndent()
at System.Xml.XmlEncodedRawTextWriterIndent.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWriter.WriteStartElement(String localName, String ns)
at Microsoft.ReportingServices.Modeling.AttributeRefNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.FunctionNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntity.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntityFolder.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteTo(XmlWriter xw, ModelingSerializationOptions options)
at Microsoft.ReportingServices.ModelDesigner.Serialization.ModelDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model Generating Out of Memory Error

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

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

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model Generating Out of Memory Error

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

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

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model from multiple data source

Creating a Data Source View from multiple Data Sources is no problem but what happens when you try to create the actuall Report Model. Using the primary Data Source doesn't give access to other databases, since each table name starts with dbo. Is there a option/flag to set to include database name?

Report Model DSVs only support one database, to pull in data from other databases you'll need to create a Named Query.

Analysis Services, on the other hand, don't have that problem, except for being limited to one database per DSV.

Note: I'm pretty sure I'm correct, but not 100%

sql

Report Model Filtering

When creating a filter with Report Model against my cube (or perhaps any
source) - it seems to force me to free form type how the filter will work.
For example -
Attribute: Does_Client_Smoke
Values: Yes, No, N/A, Won't Answer, Cannot Answer
When I create the filter it makes me type in one of the above values instead
of letting me select it from a pull-down such as how BIDS works - what am I
doing wrong here?Can someone help me on this question below. When using the report viewer,
there is a BACK button but when using browser, it won't page back correctly.
I'm using latest IE beta - maybe that's my issue?
"Joe" <hortoristic@.gmail dot com> wrote in message
news:%23ZxRAzSzGHA.772@.TK2MSFTNGP05.phx.gbl...
> When creating a filter with Report Model against my cube (or perhaps any
> source) - it seems to force me to free form type how the filter will work.
> For example -
> Attribute: Does_Client_Smoke
> Values: Yes, No, N/A, Won't Answer, Cannot Answer
> When I create the filter it makes me type in one of the above values
> instead of letting me select it from a pull-down such as how BIDS works -
> what am I doing wrong here?
>

Report Model Filter

Hi,
When creating a report model, is it possible to configure a field so
that a user must apply a filter when they create reports in Report
Builder?
In my case, I have a simple report model that contains summarized data
grouped by date and then by hour. Due to the amount of data in the
underlying table, I would like for users to be forced to filter on
either a specific date or a range of dates if possible. Even better
would be for me to be able to set a default date range for the date in
the model.
Does anyone know if what I am attempting is possible or has anyone
done something similar?
Thanks in advance,
Shane.You can use couple of things, depending on your requirement.
1. "Prompt" where it will wait for user input. but there is no multi select.
2. "From...to" this will be suitable for your requirement where in the user
can give the from and to date so that it will filter the required.
Amarnath, MCTS
"shanejokeeffe" wrote:
> Hi,
> When creating a report model, is it possible to configure a field so
> that a user must apply a filter when they create reports in Report
> Builder?
> In my case, I have a simple report model that contains summarized data
> grouped by date and then by hour. Due to the amount of data in the
> underlying table, I would like for users to be forced to filter on
> either a specific date or a range of dates if possible. Even better
> would be for me to be able to set a default date range for the date in
> the model.
> Does anyone know if what I am attempting is possible or has anyone
> done something similar?
> Thanks in advance,
> Shane.
>

Report Model Expressions

I would like to define a new field in one of my Report Model entities, by
creating an expression that references some attributes of an entity related
by a role. Is this possible?
Also, is there a kludge I can use to handle the fact that there is no IIF
function available?
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.comOK, I upgraded to SP2 (CTP) and now I can get it to work, but it is still
buggy. I need to combine 3 attributes. I was able to concatenate 2 of them
with a new field definition, and add the 3rd as a column expression. When I
tried to combine the 3 in the new field definitiion, I kept getting errors.
Also, I understand that one can inject one's own expression directly into
the rdl, so I am going to experiment with that as a means of getting IIF.
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com
"Helen Warn" wrote:
> I would like to define a new field in one of my Report Model entities, by
> creating an expression that references some attributes of an entity related
> by a role. Is this possible?
> Also, is there a kludge I can use to handle the fact that there is no IIF
> function available?
>
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com

Tuesday, March 20, 2012

Report Manager Performance (or lack thereof ?)

i am in the process of creating some new reports on a new Reporting Services site

reports developed in VisualStudio2005 pointing an an AnalysisServices Cube on a (development) SQL2005 database

when i Preview the reports in Visual Studio 2005 on my development box, the reports take about 12-15 seconds to run (about 4-5 seconds before the "Report is being generated" logo appears, then another 8-10 seconds before the report appears (report is a series (1-8) of charts))

when i run the same report (with the same report criteria) via the Report Manager (pointing at the same AnalysisServices Cube), it now takes about 45 seconds to run (about 15 -16 seconds before the "Report is being generated" logo appears, then another 30 seconds before the report appears)

(n.b. this is not due to initial Report Manager start-up delays, the timings are for the second and third run of the report)

can anyone out there point me in the direction of where to look ?

also

have just had a look at the entries in the ExcutionLog table of the ReportServer database

ran the report - it took c. 30 secs to run & render

in the table

TimeStart 18/04/2007 1:20:48 p.m.

TimeEnd, 18/04/2007 1:20:57 p.m.

TimeDataRetrieval 187

TimeProcessing 539

TimeRendering 210

dont know how reporting services thinks it did it in 9 seconds ?!?!?!?!

Friday, March 9, 2012

Report Manager Default Look

People are really very happy with the reports I'm creating using SQL 2005
Reporting Services. Great stuff!
One question though. When users go to the Report Manager:
http://MyServer/Reports/Pages/Report.aspx
They see "SQL Server Reporting Services" at the top of the form. Is there
any way to change that to a company name and perhaps a jpg company logo?
Thanks,
--
RandyYes, click on the Site link at the upper right hand side and change the
wording.
Next, from what I understand, you can change the site -> just take a look at
the CSS files in the ReportManager folder under: C:\Program Files\Microsoft
SQL Server\MSSQL.2\Reporting Services\ReportManager
Mine is stored under MSSQL.2 - but you might be using MSSQL.1 -> just look
around.
You will probably want to change the style sheet located under the Styles
folder.
Put your logo into the images folder.
=-Chris
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:01EFDDDC-14C3-478A-B1D0-0886433470F7@.microsoft.com...
> People are really very happy with the reports I'm creating using SQL 2005
> Reporting Services. Great stuff!
> One question though. When users go to the Report Manager:
> http://MyServer/Reports/Pages/Report.aspx
> They see "SQL Server Reporting Services" at the top of the form. Is there
> any way to change that to a company name and perhaps a jpg company logo?
> Thanks,
> --
> Randy|||Hmm, I don't see a "site" link on my installation of SQL Reporting Services
2005, are there other instructions for customizing the sql report server home
page? In particular I want to add a block of text describing how people can
request to join a particular Microsoft security group to view certain reports.

Wednesday, March 7, 2012

Report Layout after deploy

Hi everybody,

I have problem to creating the report using report server. After report degining as report project, the report appeared proper in the layout . But it doesn't show with proper alignment, After deploy this report in the reporting server . How to rectify this.

Please anybody is in, share with your idea.

Good day

What do you mean by alignment ? Make sure that Reporting Services tries to guess (also in the designer) how you want to data to be displayed in the report, meaning that like in excel numeric values are aligned right and string values are aligned left by default.

HTH, Jens Suessmeyer.

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

Try viewing the report as a PDF - sometimes there are differences in the way it is displayed as a web page and PDF.

The PDF may be closer/the same as the way you designed it.

|||

It sounds like you have overlapping report items (rather than e.g. an item being contained within another item).

Overlapping items are only supported in PDF, image rendering, and preview, but not in HTML.

-- Robert

|||

Yes, You are right. The controls are overlapped in my report degin . After rectify this, the report display properly. After that, i m facing another problem for using the subreport. it hasn't display properly. If you have any solution, please provide to me

Thanks

Saturday, February 25, 2012

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<

Tuesday, February 21, 2012

Report grouping question

Hi all,

I am brand new to using reporting services. I am having a problem creating this simple report. Here is what I am trying to do:

1. At the top of each page on the lefthand side, I went to print the customer name.

2. Under the customer name, I want to print all the product this customer purchased.

3. I also want to be able to change the column widths for the product fields. Presently, if I click and drag the the column lines. the customer name field is also being made smaller which I don't want.

4. I want group totals for each customer.

Some of the above things I sort of got working. But the biggest problem is setting up the report so that when I change the column widths it doesn't change the size of the customer name. I have tried using groups, multiple tables, etc. Nothing seems to work. I have searched the internet and cannot find an example. I would think this is a common report. Like I said too, I am brand new to reporting services. Can anyone help?

Thanks,

Bob

what kind of container are you using? a list, matrix, table, etc?

if you're using a table, you can merge 2 (or more) cells for customer name to make it as wide as you need without affecting the products below customer name.

hope this helps - jen

Report from prcedure that return multiple recordsets

Hi there,

I am creating a report from a stored procedure that returns

multiples record sets. For an example, my stored procedure is as follows

CREATE PROCEDURE MYPROCEDURE
AS

BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END

Now lets say I want to create a report that will display the

result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How

can I do this? Is there any way to bind the second record set to the report

dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this

Thanks
Moim

yes,

you can do this in ur procedure itself. based upon what criteria you decide which query to run, pass that criteria to procedure as a parameter, and then use if condition to run the query

|||Suresh,
Thanks for your reply. But I am sorry I dont have a chance to modify the Stored procesdure. Those are written for a legacy software and they are running good for couple of years.
So can you or any one else find me an alternate to do this?

Anyway, thanks shuresh.

Thanks
Moim|||

Moim,

Unfortunately, SSRS doesn't support multiple recordsets being returned from a stored procedure. It will use the first resultset returned only.

Can you break up your master stored proc into 3 smaller stored procs, each called by separate reports with it's own resultset?

Jarret

|||Hmmm..seems I have to do that Jarret. Anyway thanks for your reply.|||

Can you mark this one as answered so others can see that multiple recordsets aren't supported?

Thanks.

Jarret

|||Sure. Thanks|||

Thanks Jarret for the answer. I did that earlier , but couldn't get my procedure to work by passing parameters to get the resulting resultset. Insted I have created different procedures and made it to work.

I didn't know that this is a constraint with SSRS.

Monday, March 26, 2012

Report of bigger tables

Report of bigger tables

Friday, March 23, 2012

Report Model On Top Of Analysis Services Cube

Report Model On Top Of Analysis Services Cube

Report Model in VM environment

Wednesday, March 21, 2012

Report Model Generating Out of Memory Error

Report Model Generating Out of Memory Error

Report Model Generating Out of Memory Error

Report Model from multiple data source

Report Model Filtering

Report Model Filter

Report Model Expressions

Tuesday, March 20, 2012

Report Manager Performance (or lack thereof ?)

Friday, March 9, 2012

Report Manager Default Look

Wednesday, March 7, 2012

Report Layout after deploy

Saturday, February 25, 2012

Report Header woes

Report Header woes

Report Header woes

Tuesday, February 21, 2012

Report grouping question

Report from prcedure that return multiple recordsets

Report Formatting

Blog Archive

About Me