report does not display data

Hi friends
i created a very simple report (VS 2005 standard edition). which works fine.
i added a new column to the underlying table (actually its view) . this column's data is combinations of 2 other varchar columns.
when i added this column to my report, for some reason , report does not display any data when i run the report!! actually there is data as i run that view in query analyzer.
any ideas how to resolve this issue or investigate this problem ?
Thanks for your helplooks like i need to refresh "refesh fields" .after that everything worked nicelysql

Report Design Question two horizontal column headers one below other

I have a report requirement, i am new to reports.

I want to have two lines of column headers.( with 7 columns in parent columns header)

and 7 columns in child column header.

The first column header will show a parent record.

And after the parent record i want to show the next child related horizontal column headers and will show all child record related to the above parent record.

its a one to many: 1 parent record and below all child records for that parent record.

can i use subreports controls to show all child records of the parent.

Thank you very much for the information.

I would recommend that you a table report

Assume that you have a structure

Country A

Region A1

Region A2

Country B

Region B1

Region B2

Region B3

In this scenario create a table that groups records by country. In the group header/footer (depending on your need) add a new table that contains regions associated with the country.

HTH.

Report Column Headers from Database table

Please is it possible to get the reports column names from database table via stored proc or a select query.

Because reports needs to be localized between spanish and english users, so if a spanish user is logged into the system, it must bring all spanish column names from DB table and show in the headers, same with english users.

Thank you very much for the information.

I am using SQL 2K Reporting services.

Are the column names from a different dataset than the rest of the data in the report? If so, you have the following options:

1. Modify the query to return them in the same dataset (since you can't use two datasets in the same table).

2. Modify the report to include two tables, one only has the table header for the column names, the other (without header) displays the detail data, and align them appropriately.

3. Use subreport embedded in the table to display the detail data.

sql

Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder Hyperlink Column

If I have an entity that returns a column that has a URL in it like http://www.microsoft.com, can I make that a live hyperlink in a Report Builder tabular report?

No, this is not supported in this release.

You can create the report in Report Builder, then open it in Report Designer and modify it to get this behavior, however.

Report Builder Bug ?

Hi,
I have a table A with column say col1, col2,col3. I have a query which has self join on this table.
But I can not add same table twice to model from the list of tables ! When I click the table in left pane to right....its no more in left pane ...to select it again !!

somebody please help !
any idea ? or is it another bug of report builder ?

Report Builder - Table Triangles

When building a table in report builder from related entities, there appear two small upside-down moveable triangle flags above the column headers. Moving these flags seem to affect the automatic grouping and automatic joins of the table.

Does anyone know what this feature is called? I mean, it cannot really be referred to as "those triangle things." I've searched and cannot find any reference to it. Also, does anyone know how to really use this feature?

Thanks

These sliders control the equivalent of the "Freeze Panes" feature in Microsoft Excel. They should not affect the grouping structure of the table or the underlying query.

Report Builder - Format Point Labels

Is changing the display format of the Y axis of a column chart supported in the Sept CTP or is it going to be supported in the official release? For example I have built a report model with the numeric sales values set as 'C' for currency and have created a simple bar chart with year on the category axis and sales on the Y axis. The problem is that the Y axis displays as a general number (10000000) and would like to display it as a currency ($10,000,000). I can't for the life of me figure out how to display the Y axis as currency in Report Builder.
When I can create a report in BIDS and set the format to 'C' and upload it via the Reporting Services Web App the Y axis displays correctly.
I check the version of Report Builder I have installed and it is 9.0.1314.0.
Is this a know issue, bug, or not supported in this release of Reporting Services? Any help would be greatly appreciated.
Thanks,
Larry

This is true. If you format a field and display this field value on the point labels on a graph the format doesn't apply. In the Report Design if you go to the Chart properties -->Data Tab -->Values Edit --> Point Labels Tab-->there is an option "Format Code" to format the point labels. This isn't available in report builder.

Is there a work around?

Thanks, Susan

|||

I found a solution for my problem. I created a NEW field in Report Builder and I used the ROUND() Math function to round this field to two digits. I still think that you should be able to format the point labels in a graph in Report Builder. This is a bug.

Thanks, Susan

Report Builder - Format Chart Y Axis

Is changing the display format of the Y axis of a column chart supported in the Sept CTP or is it going to be supported in the official release? For example I have built a report model with the numeric sales values set as 'C' for currency and have created a simple bar chart with year on the category axis and sales on the Y axis. The problem is that the Y axis displays as a general number (10000000) and would like to display it as a currency ($10,000,000). I can't for the life of me figure out how to display the Y axis as currency in Report Builder.
When I can create a report in BIDS and set the format to 'C' and upload it via the Reporting Services Web App the Y axis displays correctly.
I check the version of Report Builder I have installed and it is 9.0.1314.0.
Is this a know issue, bug, or not supported in this release of Reporting Services? Any help would be greatly appreciated.
Thanks,
Larry

This is true. If you format a field and display this field value on the point labels on a graph the format doesn't apply. In the Report Design if you go to the Chart properties -->Data Tab -->Values Edit --> Point Labels Tab-->there is an option "Format Code" to format the point labels. This isn't available in report builder.

Is there a work around?

Thanks, Susan

|||

I found a solution for my problem. I created a NEW field in Report Builder and I used the ROUND() Math function to round this field to two digits. I still think that you should be able to format the point labels in a graph in Report Builder. This is a bug.

Thanks, Susan

Report Builder

Does anyone know how to get the column markers to show up in the report designer? MSDN documentation indicates that they can be used to freeze columns in the report. I can't seem to get them to appear.

Thanks in advance for the help.

Mark

If you need to freeze columns, you can try this:

1. Select the column you need to freeze. Then go to Properited and set the 'FixedHeader' property to TRUE..

That shud freeze the columns.

Report Builder

Does anyone know how to get the column markers to show up in the report designer? MSDN documentation indicates that they can be used to freeze columns in the report. I can't seem to get them to appear.

Thanks in advance for the help.

Mark

If you need to freeze columns, you can try this:

1. Select the column you need to freeze. Then go to Properited and set the 'FixedHeader' property to TRUE..

That shud freeze the columns.

ReporItems referring to a cell containing a subreport?

Hi guys,

i have a column in a report that must calculate a percentage based on two columns that follow them. I am aware that i can use the ReportItems! collection for this, but one of the columns i need to reference is actually a subreport because its data comes from a different source. It is just a single cell subreport, which makes it appear totally part of the table it is in. Is this possible? Just to be clear, what i want is an expression like this in my cell:

= FormatPercent( ReportItems!CurrentEarnings / ReportItems!BudgetedEarnings )

the BudgetedEarnings cell is actually a subreport, but all i want is the text it is showing.

Thanks for any help or suggestions!

sluggy

No, referring to a report item in a subreport is not allowed.

|||Dang! Thanks |||Hi, how did you get around this issue? I am trying to do the same thing.

ReporItems referring to a cell containing a subreport?

Hi guys,

i have a column in a report that must calculate a percentage based on two columns that follow them. I am aware that i can use the ReportItems! collection for this, but one of the columns i need to reference is actually a subreport because its data comes from a different source. It is just a single cell subreport, which makes it appear totally part of the table it is in. Is this possible? Just to be clear, what i want is an expression like this in my cell:

= FormatPercent( ReportItems!CurrentEarnings / ReportItems!BudgetedEarnings )

the BudgetedEarnings cell is actually a subreport, but all i want is the text it is showing.

Thanks for any help or suggestions!

sluggy

No, referring to a report item in a subreport is not allowed.

|||Dang! Thanks |||Hi, how did you get around this issue? I am trying to do the same thing.

Replication: Adding a column

Hi,
I have a table that is being replicated but one the the developers added a
column to the table WITHOUT using sp_repladdcolumn. Now, they are wondering
why this column is not on the subscriber. What is the easiest way to solve
this problem? (I am thinking of changing the subscription and adding the
column to the subscription and doing a snapshot but I hate to do this as it
takes a while on a 15 GB database).
Any fast way of solving this WITHOUT removing the column on the publisher?
--
I saw it work in a cartoon once so I am pretty sure I can do it.I would sp_dropScubscription/ sp_droparticle/
sp_addarticle/ start the snapshot agent.
Make sure that the sp_dropsubscription specifies that
article only. Do this first in Dev.
>--Original Message--
>Hi,
>I have a table that is being replicated but one the the
developers added a
>column to the table WITHOUT using sp_repladdcolumn. Now,
they are wondering
>why this column is not on the subscriber. What is the
easiest way to solve
>this problem? (I am thinking of changing the
subscription and adding the
>column to the subscription and doing a snapshot but I
hate to do this as it
>takes a while on a 15 GB database).
>Any fast way of solving this WITHOUT removing the
column on the publisher?
>--
>I saw it work in a cartoon once so I am pretty sure I
can do it.
>.
>