Gaps in my reports

I have a report with 10 subreports in it. The initial report only contains 2
parameters which are used to populate the sub-reports.
The subreports are situated under eachother in the designer with nogaps in
between and the tables within the sub-reports have visibility set to hidden
when the RowCount of the dataset is 0... I would expect this to mean hide the
contents of the report when there is no data.
Why does Reporting Services add gaps where my sub-reports should be when the
main report renders ?.. Is there a way to stop this.
Help.. Ive been pulling my hair out over this one!.. ThanksJust an idea, you may want to try applying the expression in the Visibility
Properties for the ROW (not the subreport). Hopefully that will take care of
the blank row(s).
"DecksTerrorT" wrote:
> I have a report with 10 subreports in it. The initial report only contains 2
> parameters which are used to populate the sub-reports.
> The subreports are situated under eachother in the designer with nogaps in
> between and the tables within the sub-reports have visibility set to hidden
> when the RowCount of the dataset is 0... I would expect this to mean hide the
> contents of the report when there is no data.
> Why does Reporting Services add gaps where my sub-reports should be when the
> main report renders ?.. Is there a way to stop this.
> Help.. Ive been pulling my hair out over this one!.. Thanks|||Maybe my post was a litle cryptic.. let me try again.
I have a report.
That report has 10 subreports all underneath each other.
Within the subreports the all items have a visibility to false when RowCount
= 0.
So why does the main report display a gap once rendered where the sub-report
should be ?
Hope this makes it alittle clearer.
Decks.
"Nick P." wrote:
> Just an idea, you may want to try applying the expression in the Visibility
> Properties for the ROW (not the subreport). Hopefully that will take care of
> the blank row(s).
> "DecksTerrorT" wrote:
> > I have a report with 10 subreports in it. The initial report only contains 2
> > parameters which are used to populate the sub-reports.
> >
> > The subreports are situated under eachother in the designer with nogaps in
> > between and the tables within the sub-reports have visibility set to hidden
> > when the RowCount of the dataset is 0... I would expect this to mean hide the
> > contents of the report when there is no data.
> >
> > Why does Reporting Services add gaps where my sub-reports should be when the
> > main report renders ?.. Is there a way to stop this.
> >
> > Help.. Ive been pulling my hair out over this one!.. Thanks|||I had to overcome a similar "gap" problem where there could be a LOT of
hidden lines.... the only way I was able to do this was to :
1. select the entire "table" individually in each report
2. edit the size of each table by dividing the length by 10 (so 2.74
inches bacame .274 inches)
3. This gives a totally scrunched up view in the layout tab (which is a
pain for editing later on ... have to multiply by 10 to be able to view
it and then change it back again...)
4. But now ... when I get to actually view the result ... it looks a
LOT better.
5. This solution did not get rid of all the gaps all the time, but it
REALLY made a huge significant difference.
good luck...

Gaps in borders between columns...

I have a row in my report that has 6 columns. I want to outline the row with a border, but not the column lines in between. So, I went into BorderColor, changed the Default to white and my Top and Bottom colors as Black. The problem is that where the "white" column lines are, they are displaying 1pt gaps in my outline. I tried changing the BorderStyle to Zero, but it wouldn't take it.

See sample of the report here.

Any ideas?

Thanks,

James H.

I always had problems in formatting these kind of things. You will have to try different possibilities to get this right. Sometimes, It looks different on preview when compared to the ones when deployed. Try changing the Border style, and try chaning the border color of the columns, Good Luck|||

Hii

For this you can merge the cells in the row,then put one rectangle over there. then according to the width of each column put the text boxes inside the rectangle without giving any border style for the text boxes.finally give the top and bottom border for the whole rectangle.

Hope this works fine

Thanks

Mahasweta

|||

>> I tried changing the BorderStyle to Zero but it wouldn't take it

I assume you mean BorderWidth here...

You're right, you can't put in 0. But you can do this:

=IIF(false,1,0)

This, plus a BorderStyle of None, for each column, seems to do what you want... not 100% positive but it looks right, check it out...

>L<

|||This would work, but gets jacked up when exporting out to excel.|||

I ended up getting some help on this from a thread on sqlservercentral.com.

This is what I had to do to get a top and bottom border (no left or right borders) without the gaps in the top and bottom borders...

BorderColor

Default = White Left = White Right = White Top = Black Bottom = Blacksql

Gaps in borders between columns...

I have a row in my report that has 6 columns. I want to outline the row with a border, but not the column lines in between. So, I went into BorderColor, changed the Default to white and my Top and Bottom colors as Black. The problem is that where the "white" column lines are, they are displaying 1pt gaps in my outline. I tried changing the BorderStyle to Zero, but it wouldn't take it.

See sample of the report here.

Any ideas?

Thanks,

James H.

I always had problems in formatting these kind of things. You will have to try different possibilities to get this right. Sometimes, It looks different on preview when compared to the ones when deployed. Try changing the Border style, and try chaning the border color of the columns, Good Luck|||

Hii

For this you can merge the cells in the row,then put one rectangle over there. then according to the width of each column put the text boxes inside the rectangle without giving any border style for the text boxes.finally give the top and bottom border for the whole rectangle.

Hope this works fine

Thanks

Mahasweta

|||

>> I tried changing the BorderStyle to Zero but it wouldn't take it

I assume you mean BorderWidth here...

You're right, you can't put in 0. But you can do this:

=IIF(false,1,0)

This, plus a BorderStyle of None, for each column, seems to do what you want... not 100% positive but it looks right, check it out...

>L<

|||This would work, but gets jacked up when exporting out to excel.|||

I ended up getting some help on this from a thread on sqlservercentral.com.

This is what I had to do to get a top and bottom border (no left or right borders) without the gaps in the top and bottom borders...

BorderColor

Default = White Left = White Right = White Top = Black Bottom = Black

Gantt chart in SQL reporting service

Hi all,

I need creat a gannt chart for project server report in SQL reporting service. Is somebody know some good third party tool can help to implement or how to creat by hand?

Thanks in advance!

I use Dundas Chart for Reporting Services and can thoroughly recommend it and it supports Gannt Charts

|||Thanks Jasper! But Dundas chart is a little bit expensive solution for us. It supports SQL 2000 reporting service?|||I downloaded the Dundas Chart for Reporting Services and try to create a Gantt chart. I found it's very difficult to do, as there're many settings on it. Is there any tutorial provided? Thanks!

Gantt chart in SQL reporting service

Hi all,

I need creat a gannt chart for project server report in SQL reporting service. Is somebody know some good third party tool can help to implement or how to creat by hand?

Thanks in advance!

I use Dundas Chart for Reporting Services and can thoroughly recommend it and it supports Gannt Charts

|||Thanks Jasper! But Dundas chart is a little bit expensive solution for us. It supports SQL 2000 reporting service?|||I downloaded the Dundas Chart for Reporting Services and try to create a Gantt chart. I found it's very difficult to do, as there're many settings on it. Is there any tutorial provided? Thanks!

Gaining control over report parameters

Hi All,

I have not heard any news on this topic elsewhere so thought I would ask here....

I would like to gain control over parameters in terms of:

1. Parameter placement as in how many parameters on a row, not just 2. It would be nice to be able to specify a row number in the defintiion of a parameter for example.

2. Access to the parameter text via a custom assembly to support multi-langauge reports.

I know other folks have talked about these things and some of us 'hope they are coming one day'...I was wondering if that day might be any closer now?

Thanks

hhhmmm....seems like this is not happening any time soon....

Anyone heard any news about this? It's been 7 months since I asked...

Thanks

Peter

|||

Funny... I was asked if this was possible today , and havent seen anything to date....

|||

Hi Mark,

this topic is discussed at length in the hitch hikers guide to report services...which is a very good book by the way...not sure if they are doing one for 2005.....

The authors said it would be a 6 month job to be able to write something useful to control how many parameters appear on a line....and it would seem obvious that if some clever person did that and it was popular it would just go into the next release of RS...so it seemed to them (and to me) that the most sensible people to write this functionality is MSFT.

What I am looking for is to be able to tell the report how many parameters to place on the line so that parameters that should be grouped together are....and also expose the text of the parameters to custom assemblies so a single report can be written in multiple languages....since the parameters are text, they cannot be written into multiple languages in the 'out of the box' version of the report browser...

From the looks of it..we will just have to wait...

Gaining control over report parameters

Hi All,

I have not heard any news on this topic elsewhere so thought I would ask here....

I would like to gain control over parameters in terms of:

1. Parameter placement as in how many parameters on a row, not just 2. It would be nice to be able to specify a row number in the defintiion of a parameter for example.

2. Access to the parameter text via a custom assembly to support multi-langauge reports.

I know other folks have talked about these things and some of us 'hope they are coming one day'...I was wondering if that day might be any closer now?

Thanks

hhhmmm....seems like this is not happening any time soon....

Anyone heard any news about this? It's been 7 months since I asked...

Thanks

Peter

|||

Funny... I was asked if this was possible today , and havent seen anything to date....

|||

Hi Mark,

this topic is discussed at length in the hitch hikers guide to report services...which is a very good book by the way...not sure if they are doing one for 2005.....

The authors said it would be a 6 month job to be able to write something useful to control how many parameters appear on a line....and it would seem obvious that if some clever person did that and it was popular it would just go into the next release of RS...so it seemed to them (and to me) that the most sensible people to write this functionality is MSFT.

What I am looking for is to be able to tell the report how many parameters to place on the line so that parameters that should be grouped together are....and also expose the text of the parameters to custom assemblies so a single report can be written in multiple languages....since the parameters are text, they cannot be written into multiple languages in the 'out of the box' version of the report browser...

From the looks of it..we will just have to wait...

Gaining control over report parameters

Hi All,

I have not heard any news on this topic elsewhere so thought I would ask here....

I would like to gain control over parameters in terms of:

1. Parameter placement as in how many parameters on a row, not just 2. It would be nice to be able to specify a row number in the defintiion of a parameter for example.

2. Access to the parameter text via a custom assembly to support multi-langauge reports.

I know other folks have talked about these things and some of us 'hope they are coming one day'...I was wondering if that day might be any closer now?

Thanks

hhhmmm....seems like this is not happening any time soon....

Anyone heard any news about this? It's been 7 months since I asked...

Thanks

Peter

|||

Funny... I was asked if this was possible today , and havent seen anything to date....

|||

Hi Mark,

this topic is discussed at length in the hitch hikers guide to report services...which is a very good book by the way...not sure if they are doing one for 2005.....

The authors said it would be a 6 month job to be able to write something useful to control how many parameters appear on a line....and it would seem obvious that if some clever person did that and it was popular it would just go into the next release of RS...so it seemed to them (and to me) that the most sensible people to write this functionality is MSFT.

What I am looking for is to be able to tell the report how many parameters to place on the line so that parameters that should be grouped together are....and also expose the text of the parameters to custom assemblies so a single report can be written in multiple languages....since the parameters are text, they cannot be written into multiple languages in the 'out of the box' version of the report browser...

From the looks of it..we will just have to wait...

FYI: SQL Reporting Services download is available!

http://www.microsoft.com/sql/report...tinfo/trial.asp
If you have a SQL 2000 license, you can use the software. I don't work for
Microsoft, but I am excited enough to see this that I wanted to send out the
link. I have been waiting a long time to move beyond the limits of
Infomaker or crystal reports to something that is more integrated with SQL.
It has pdf output capabilities btw. I am going to start trying this out
immediately, I suggest DBAs check this out.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************Just wanted to pass this along from my first attempt.
Note from the Readme file, I found you need VB .NET 2003 for the Report
designer.
Before you install the product, check for a file called authz.dll in
c:\winnt\system32. It'll hold up the install for the Reporting Services
server part if it is missing. It is a file that comes either from
contacting MS support for the file in Q320211 or from another Win 2k machine
that was upgraded from a previous service pack to SP 4 instead of SP 4
directly.
You know there always has to be prerequisites <g>
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************

Future of Report builder

Hi,
I am looking into letting users build their own reports. So far, it
looks good for most scenarios. However, some of the 'reports' I'd
like users to create are forms for our quotes, orders and invoices.
I can create these kinds of reports in VS 2005, but I can't in report
builder because of its lack of the List control. I use the list
control to display fields from the Document header data source, which
will only ever have one item in the list such as sold to information,
the document number, etc.
Will report builder gain the ability to use the List control so that i
can place item details arbitrary on the form? I'd like to see this
feature added, as it would allow my users to create any kind of
reports / forms they need so long as I provide them an appropriate
model.
For now, are there any workarounds?
Thanks
AndyBasically RB is for top management, who can create report on their own, for
forms you can try using "Infopath" for forms creation. Since it is a very
very earlier version, we expect MS will improve on it.
Amarnath
"Andy" wrote:
> Hi,
> I am looking into letting users build their own reports. So far, it
> looks good for most scenarios. However, some of the 'reports' I'd
> like users to create are forms for our quotes, orders and invoices.
> I can create these kinds of reports in VS 2005, but I can't in report
> builder because of its lack of the List control. I use the list
> control to display fields from the Document header data source, which
> will only ever have one item in the list such as sold to information,
> the document number, etc.
> Will report builder gain the ability to use the List control so that i
> can place item details arbitrary on the form? I'd like to see this
> feature added, as it would allow my users to create any kind of
> reports / forms they need so long as I provide them an appropriate
> model.
> For now, are there any workarounds?
> Thanks
> Andy
>

future enhancement

I would like to see bread crumbs in report manager for linked reports and drill through to make it easier for users to navigate back to the parent and intermediary reports. The browser back button can be a little flakey. It would be REALLY nice if this could be put into the new report viewer control in VS 2005 also.

I would also like to see a "My Reports" feature that would allow a user to create a list of reports they most frequently access. I have created a custom solution to do this and only told a few users about it; now there are 276 entries in it. We have about 80 users total. I looked at the subscription option but it requires to many settings. Most of our reports have parameters, a date range being the most common, that are not static and therefore subscriptions would be a little tedious to use.

By the way, I am not sure if there is a better forum for this.

Funky Problem with Invisible Chart

So, I'm pretty new to Reporting Services and this is my first post in this forum. So basically I was creating a report which contains a chart. I configure the dataset, chart values, etc and the chart shows up blank. The title of the chart shows, but nothing else shows. The way I created the report in the designer is no different that another report that actually works.

I've searched google with every word I could think of and I haven't been able to find this issue anywhere. Is this some crazy Microsoft hiccup?

Here is the Stored Procedure that I am using as my dataset:

CREATE PROCEDURE YearlyTotalsInPercentages(@.Yearint) ASBEGINDECLARE @.TotalSumintSELECT SUM(dbo.Main.Hours) AS CBDCYearlyTotals, dbo.Project.ProductLine AS ProductLineINTO #tempTotalsFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @.TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, ROUND((t.CBDCYearlyTotals/@.TotalSum) * 100, 1) AS Percentage FROM #tempTotals tENDGO

I can present the rdl if necessary.

Muchos Gracias

So, in doing deeper searching I found another forum that said temp tables may not be supported and that table variables should be used instead. So, I'm attempting that route, but running into problems with the query when it comes to dividing by @.TotalSum. It returns 0 now and not the number that I had gotten previously.

Just one road block after the other. Ah, the joys of programming.

Anybody got any clues as to why this would happen?

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Function to Convert Amount to Words for Check Printing

I am thinking someone out there must have already developed this....

I am using Report Services to generate checks and need to convert the $ amount into words on the "Pay To" line.

I searched Google and found a couple of VB/ASP routines that do this, but I would need to do it via a SQL Proc since the reporter does not provide the capability to reference and/or call custom VB functions from within.

Does anyone have / know of SQL function that may be publicly available or are willing to share that does this or perhaps can suggest a better way to handle?

Thanks.

If you use SQL Server Reporting Services, then you could add VB Code functions and then use it in your Expressions.

Also you could create .NET Stored procedures/user defined functions and use it in you Transact-SQL code.

|||

It is absolutely amazing. Just by typing in 'sql function amount to words' into the Google? search bar, I got back dozens of hits, these look like good possibilities.

http://www.novicksoftware.com/udfofweek/Vol1/T-SQL-UDF-Volume-1-Number-46-NumberToWords.htm
http://www.devx.com/vb2themax/Tip/19053
http://code.filehungry.com/product/languages/sql/miscellaneous/123_tsql_functions

Google? is your friend...|||

Thanks. I figured out that even though the project explorer didn't list class as a new type of item which could be added, I could add a class to the reports project via the menu.

Appreciate your help,

Annette

|||

Funny how I did exactly the same Google search and didn't get the same results (or perhaps they were not listed on the first few pages I checked (there were 10 pages of results).

Google is definitely our friend and so are people like yourself who take the time to answer these posts.

Thank you,

Annette

function or formula that convert numbers into words

Hello
I am using crystal report 9. i get total of all item purchsed by customer. what is need a any function or formula that convert numbers into words. i.e 32125 should be converted to thirty two thousand one hundred twenty five only.
With regardsCreate a formula and in forumula section (module) there were so many options like functions conversions etc and in that section look for numbers to text option and using that you can achieve what you are looking for.|||I'm not sure about CR9, but in CR10 you can use the ToWords function.