report does not show a table

Hi,

We have a report published to two report servers ( same configuration). this report displays data in two side by side tables. on server 1, the left side table does not get displayed where as both the tables show fine on server 2.

What could be the reason for this

Any help greatly appreciated.

Thanks,

Phani

I assume that the reports are pointing to different databases, so is there any data being returned for the table that doesn't show?|||

Hi,

It is the same report deployed on two webservers so they pull from the same database. One report shows both the tables with data and the second one shows only the right side table with data and does not show left hand side table.

Thanks,

Phani

|||

are the two tables using the same datasource?

could it be a time out issue from sql?

Have you looked into the reportserver log files and compared the results from each report server?

sql

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 Dev

Hello,
I'am developping a report.
In this report, I have a big table.
I want to only one record per page.
How can tell Report Services ti put the second table on the second page,
the third ... ?
Thx
GaelSet up grouping so that each group only has one record. (group by the
primary key field) Then, just turn on page breaks after each group.
Mike G.
"Gael Le Mouhaer" <gael.lemouhaer@.linum.fr> wrote in message
news:XnF965AA802E5A0Egaellemouhaerlinumfr@.207.46.248.16...
> Hello,
> I'am developping a report.
> In this report, I have a big table.
> I want to only one record per page.
> How can tell Report Services ti put the second table on the second page,
> the third ... ?
> Thx
> Gael

Report Designer question

Hi gurus.
I have the following challenge:
Let's imagine you have a report containing a table/list of salespeople from
one table, their sales per year from another table, and thirdly for instance
the salesperson's zip code.
If a given salesperson have sales in 2003, 2004, 2005 and 2006, he will have
3 rows returned in the list, and the same zip code listed 4 times.
What you do not want for the report is that the total for the zip coded
column is summed up, so it shows 4 times the zip code. You just want the
single zip code returned on the summary line.
Is there an easy way to achieve this?
Kind Regards,
Torkild HagenYou can use First(Fields!fieldname.value) instead of Sum.
daw
"thagen" wrote:
> Hi gurus.
> I have the following challenge:
> Let's imagine you have a report containing a table/list of salespeople from
> one table, their sales per year from another table, and thirdly for instance
> the salesperson's zip code.
> If a given salesperson have sales in 2003, 2004, 2005 and 2006, he will have
> 3 rows returned in the list, and the same zip code listed 4 times.
> What you do not want for the report is that the total for the zip coded
> column is summed up, so it shows 4 times the zip code. You just want the
> single zip code returned on the summary line.
> Is there an easy way to achieve this?
> Kind Regards,
> Torkild Hagen

Report Design help for Newb - Regarding Date Paramaters

I have been trying very hard for days now to build a report using VS 2003.net
I just need to query an order table by a date range. ie) from date a to
date b. The order table contains PO's and have recorded dates. I just need
an example of how to report based upon date range. Seems simple enough,
just cant figure it out.I assume you mean you want the user to be able to specify the date range?
If you currently have a sql statement that returns all orders and have a
report that displays that data, simply modify the sql statement and specify:
where datefield between @.startdate and @.enddate
That will automatically create report parameters. When you preview the
report, it will prompt you to type in start and end dates.
For more info, just do searches for query parameters and report parameters.
Mike G.
"tdodd" <tdodd@.discussions.microsoft.com> wrote in message
news:6A7F2EE5-FAFA-4852-B771-35FBC9431D71@.microsoft.com...
>I have been trying very hard for days now to build a report using VS
>2003.net
> I just need to query an order table by a date range. ie) from date a to
> date b. The order table contains PO's and have recorded dates. I just
> need
> an example of how to report based upon date range. Seems simple enough,
> just cant figure it out.

Report Delivery problem with Data Driven Subscription

I created a data driven subscription for a report that will email a copy of the report based on a parameter passed from a table. The report runs successfully as indicated in Report Manager but the email is not delivered. I checked the ReportServerService log file and a message states that the report was sent to the proper email address. There is another log, ReportServerWebApp, that shows the message "The extension Report Server Email does not have a LocalizedNameAttribute" .

I created a regular subscription and it emails the report successfully so it can't be the SMTP server.

Any ideas how I can fix this problem with the Data Driven Subscription? Thank you.

David

I did some further checking. If I send off a second job, it delivers the emailed reports. However, the problem persists with the second job.That is, Reporting Services indicates that the job ran successfully and delivered the reports, but some of the reports are getting stuck in a queue somewhere between the reporting server, the remote SMTP server and Microsoft Exchange.

Is there anyone else having problems with Reporting Services reports not getting fully delivered by a remote SMTP server? Would using a local SMTP server fix the problem?

David

|||Sorry for the delay with replying this. From the SSRS prospective your data driven subscription has been delivered to SMTP server successfully. Then SMTP server failed to deliver e-mal. Any chance that e-mail TO address is build off a data-driven subscription query results and is incorrect? Per every successfully sent e-mail SSRS will write into the service logfile ReportServerService__<Date>.log a line: Email successfully sent to {0}. Can you double check that TO addresses are correct for the cases that you thin are failing?|||

All of the reports are being sent to the same email address. Some get there successfully, others don't make it out of the queue.

David

|||

Then it really looks like a problem with SMTP server. It’s likely not related to SSRS.

-Igor

Report Delivery problem with Data Driven Subscription

I created a data driven subscription for a report that will email a copy of the report based on a parameter passed from a table. The report runs successfully as indicated in Report Manager but the email is not delivered. I checked the ReportServerService log file and a message states that the report was sent to the proper email address. There is another log, ReportServerWebApp, that shows the message "The extension Report Server Email does not have a LocalizedNameAttribute" .

I created a regular subscription and it emails the report successfully so it can't be the SMTP server.

Any ideas how I can fix this problem with the Data Driven Subscription? Thank you.

David

I did some further checking. If I send off a second job, it delivers the emailed reports. However, the problem persists with the second job.That is, Reporting Services indicates that the job ran successfully and delivered the reports, but some of the reports are getting stuck in a queue somewhere between the reporting server, the remote SMTP server and Microsoft Exchange.

Is there anyone else having problems with Reporting Services reports not getting fully delivered by a remote SMTP server? Would using a local SMTP server fix the problem?

David

|||Sorry for the delay with replying this. From the SSRS prospective your data driven subscription has been delivered to SMTP server successfully. Then SMTP server failed to deliver e-mal. Any chance that e-mail TO address is build off a data-driven subscription query results and is incorrect? Per every successfully sent e-mail SSRS will write into the service logfile ReportServerService__<Date>.log a line: Email successfully sent to {0}. Can you double check that TO addresses are correct for the cases that you thin are failing?|||

All of the reports are being sent to the same email address. Some get there successfully, others don't make it out of the queue.

David

|||

Then it really looks like a problem with SMTP server. It’s likely not related to SSRS.

-Igor

Report Definition

Is it possible to control the number of table rows that can be displayed by modifying the report definition?Not sure if this is what you mean, but you can control the # of rows displayed by limiting the # of rows fetched from the data source. There's no way AFAIK to fetch N rows but only display N-M rows.|||

>> There's no way AFAIK to fetch N rows but only display N-M rows.

Yes, actually there is <s>.

You do need to give yourself appropriate information from the data source, but this doesn't necessarily mean limiting the rows! (because you might have another data region that shows a different count, in the same report, from the same data).

Here's a reporting query from my favorite ASP.NET add-on table (ELMAH error handler):

Code Snippet

SELECT ErrorId, Application, Host, Type, Source, Message,
[User], StatusCode, TimeUtc,

Sequence, AllXml

FROM ELMAH_Error ORDER BY ErrorId

... now suppose we add a column, as follows:

Code Snippet

SELECT ErrorId, Application, Host, Type, Source, Message,

[User], StatusCode, TimeUtc,

Sequence, AllXml, Row_Number() OVER (ORDER BY ErrorID) AS ItemID
FROM ELMAH_Error ORDER BY ErrorId

(you can add a counter like this different ways in other data sources)

... Now we can add the following filter to a table (or whatever):

Code Snippet

=Fields!ItemID.Value <= 8 ' or whatever maximum you have in mind,

' for example you can say:

=Fields!ItemID.Value <= =Parameters!MyMax.Value

... and readers please note, if you have trouble when you follow these instructions:

When I built this example I had to CInt() (cast) on both sides of the filter expression (including the literal 8!) to get it to work the first couple of times. It may be because I was building the query interactively for the dataset, and the designer doesn't support this clause so, while it processes the SQL, it is not sure about the data type of the result. IAC it does work.

>L<

|||Thanks for your help.

Report Corruption When Exported to Excel

Hello. Anyone please help me with my problem with regards to exporting reports to excel. My report consists of two tables, first table is just plain data displaying from the database; however, it has drilldowns. My second table consists of graphs. My report generates big size depends on the parameters selected by the user. The design of this report is the requirement of our clients, so i cannot do anything about it. So, here comes the problem when i try to export it to excel. The following error message is encountered:

"Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files\Content.IE5\VKP9ZCSW\MyStore? Performance (Estate).xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted."

The report corruption is inconsistent, sometimes, the exported report is ok; however, there are some time where the report exported is corrupted. What could be the possible cause of this report corruption? Is this a known bug for the export feature of the Reporting Services? Does the file size matter? Moreover, i find this so weird, since, we use subscription feature to send reports to our clients and we chose to send these reports in .xls format. I have observed: a report is exported, same date and time, same parameters, two subscriptions were done for a single report; however, one report was corrupted but the other report was ok. What is the problem on this bug?

Anyway, we are using SP2.

Please help me. A reply for this will be very much appreciated. Thank you. May God bless you.

I have encountered this error as well.

Seems to be related to the amount of characters in a cell in my case. My report is all text and when the character count in a cell reaches around 4000 or so the report won't export to excel. It will export in Visual Studio but not when run from our report server.

Any help from Microsoft would be appreciated!!!

|||I dont think i reach that number of characters (4000) for every cell.|||I have a hunch. Try moving the contents of your paged footer to the report body. Make sure nothing is in the page footer.|||

In my case I have no information in the report footers.

The reports that I need to create are based on a database with a lot of textural information and when the character count reaches at or around 4000 characters in a cell then the report will not export to excel. It is interesting that the report will export to excel when run from my Visual Studio environment.

As a test I created a new report. Dropped a table on the body of the report and pasted some random words (4300 characters) in the “Detail” cell. I next created a dataset that would return one row so that the report would run. I next run the report from Visual Studio and export to excel. When I open the excel report everything is fine.

Now I publish the report to our report server (SQL 2K) and run it but when I export the report to excel I get the corrupt data error.

|||

We are also experiencing this problem. I also do not have anything in a report footer. The report we use has a drop down box with values for one of two parameters. The report renders and exports fine for most of the values, however one or two dont export correctly. It is not always the same values that cause the error, but sometimes it works and sometimes it doesnt.

The report does include quite a bit of information with 3 matrixes and 3 tables on top of each other, each with a page breaks so the information will be reported in several tabs in the same Excel worksheet. However, again, it works sometimes and not other times.

Another thing, I noticed in the thread that someone said it worked from the Visual Studio environment, but I did not find that to be true always.

Any help from Microsoft would be appreciated! This is a report that is run for executive management each month and this is the second month we have had a problem with it. We have tried using a different machine that had not been run today at all, in case it was too many windows open at a time, or memory usage, etc. But that did not work either. It finally just worked for the value we needed.

Thanks!

- Glenda

Regeneration Technologies, Inc.

ggable@.rtix.com

|||

It sounds very much like the report isn't being rendered improperly, but the file itself has become corrupt by the time it is opened on the client. Would it be possible to save the file on a client machine when that error occurs, and examine it outside of Excel? If it is possible to save the file, check if it is of a reasonable size. It should be virtually identical to a non-corrupt xls generated by the same request. If there is a large disparity, the file might be getting delivered in a corrupt state, as opposed to rendered in a corrupt state.

|||

There was a bug resolved in SP2 for RS 2000 that addressed issues where rendering would fail when a single textbox had 4000+ characters. Are you currently running SP2 on your server? It is entirely possible that your client has become updated while your server has not, leading it to work in Visual Studio but not on the server.

|||

I too have just encountered this problem on a report that worked successfully on 7/14/06. Is it possible that MS July patch introduced this problem. If not, then it may be my data. I have one LargeNarrative field in the report and there may be new data on the report that goes beyond 4K characters.

In my case does not work from VS 2003 either. Using VS2003 7.1.3088/, SS2005 with SP1, RS 2003 fully patched

|||I have the same problem, in my report, I have few graphs and the color was user defined color, when I change it to pre defined colors, it works. Any body know how to fix that? I need to use the user defined colorsql

Report Corruption When Exported to Excel

Hello. Anyone please help me with my problem with regards to exporting reports to excel. My report consists of two tables, first table is just plain data displaying from the database; however, it has drilldowns. My second table consists of graphs. My report generates big size depends on the parameters selected by the user. The design of this report is the requirement of our clients, so i cannot do anything about it. So, here comes the problem when i try to export it to excel. The following error message is encountered:

"Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files\Content.IE5\VKP9ZCSW\MyStore? Performance (Estate).xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted."

The report corruption is inconsistent, sometimes, the exported report is ok; however, there are some time where the report exported is corrupted. What could be the possible cause of this report corruption? Is this a known bug for the export feature of the Reporting Services? Does the file size matter? Moreover, i find this so weird, since, we use subscription feature to send reports to our clients and we chose to send these reports in .xls format. I have observed: a report is exported, same date and time, same parameters, two subscriptions were done for a single report; however, one report was corrupted but the other report was ok. What is the problem on this bug?

Anyway, we are using SP2.

Please help me. A reply for this will be very much appreciated. Thank you. May God bless you.

I have encountered this error as well.

Seems to be related to the amount of characters in a cell in my case. My report is all text and when the character count in a cell reaches around 4000 or so the report won't export to excel. It will export in Visual Studio but not when run from our report server.

Any help from Microsoft would be appreciated!!!

|||I dont think i reach that number of characters (4000) for every cell.|||I have a hunch. Try moving the contents of your paged footer to the report body. Make sure nothing is in the page footer.|||

In my case I have no information in the report footers.

The reports that I need to create are based on a database with a lot of textural information and when the character count reaches at or around 4000 characters in a cell then the report will not export to excel. It is interesting that the report will export to excel when run from my Visual Studio environment.

As a test I created a new report. Dropped a table on the body of the report and pasted some random words (4300 characters) in the “Detail” cell. I next created a dataset that would return one row so that the report would run. I next run the report from Visual Studio and export to excel. When I open the excel report everything is fine.

Now I publish the report to our report server (SQL 2K) and run it but when I export the report to excel I get the corrupt data error.

|||

We are also experiencing this problem. I also do not have anything in a report footer. The report we use has a drop down box with values for one of two parameters. The report renders and exports fine for most of the values, however one or two dont export correctly. It is not always the same values that cause the error, but sometimes it works and sometimes it doesnt.

The report does include quite a bit of information with 3 matrixes and 3 tables on top of each other, each with a page breaks so the information will be reported in several tabs in the same Excel worksheet. However, again, it works sometimes and not other times.

Another thing, I noticed in the thread that someone said it worked from the Visual Studio environment, but I did not find that to be true always.

Any help from Microsoft would be appreciated! This is a report that is run for executive management each month and this is the second month we have had a problem with it. We have tried using a different machine that had not been run today at all, in case it was too many windows open at a time, or memory usage, etc. But that did not work either. It finally just worked for the value we needed.

Thanks!

- Glenda

Regeneration Technologies, Inc.

ggable@.rtix.com

|||

It sounds very much like the report isn't being rendered improperly, but the file itself has become corrupt by the time it is opened on the client. Would it be possible to save the file on a client machine when that error occurs, and examine it outside of Excel? If it is possible to save the file, check if it is of a reasonable size. It should be virtually identical to a non-corrupt xls generated by the same request. If there is a large disparity, the file might be getting delivered in a corrupt state, as opposed to rendered in a corrupt state.

|||

There was a bug resolved in SP2 for RS 2000 that addressed issues where rendering would fail when a single textbox had 4000+ characters. Are you currently running SP2 on your server? It is entirely possible that your client has become updated while your server has not, leading it to work in Visual Studio but not on the server.

|||

I too have just encountered this problem on a report that worked successfully on 7/14/06. Is it possible that MS July patch introduced this problem. If not, then it may be my data. I have one LargeNarrative field in the report and there may be new data on the report that goes beyond 4K characters.

In my case does not work from VS 2003 either. Using VS2003 7.1.3088/, SS2005 with SP1, RS 2003 fully patched

|||I have the same problem, in my report, I have few graphs and the color was user defined color, when I change it to pre defined colors, it works. Any body know how to fix that? I need to use the user defined color

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 code and dataset question

I have a strange (maybe not too strange) problem. I have data populating a report and everything is fine. This table also references another table where there might be zero to several prefixes associated with the name. If I have one row from the main table, but there are three prefixes that have to be concatenated to the beginning of the Name field (like a one to many situation), what would be the easiest way to do this?

I thought of doing a separate Report code and I have the following:

Function addPrefix(byval current as string, newToAdd as string) as string

dim deg as string

deg = current + " " + newToAdd

return deg

End Function

In the text box, I have it referencing it's own value. So the textboxes expression is

=code.addTitles(ReportItems.("textbox1").Value, Fields!NAM.Value)

I hope this makes some sort of sense. If the first record's value in the main table is THISNAME and it pulls the values VAL1 and VAL2 from the second dataset then I want the report to show VAL1 VAL2 THISNAME.

Thanks for the information

have you tried using sql-joins?
left/right-join should fulfill your needs if i understood your problem right|||

Yes, I have used joins. The record in the left table produces the desired results. However the first record pulls three records from the INNER JOIN. I need to attached those three records to the NAME field from the left table to that one record. So if the first record pulls 1, 2, and 3. I need the report to display as 1 2 3 NAME.

I was thinking of doing a for each statement in the code. Is there a way to pass the dataset to the code in a report?

|||

Why don't you sort it out in the select statement in the Dataset:

select min(p1.prefix)+min(p2.prefix)+min(p3.prefix)+p0.name

from name_file p0

inner join prefix_file p1 on p1.prefix > ' ' and p0.name_id = p1.name_id

inner join prefix_file p2 on p2.prefix > p1.prefix and p0.name_id = p2.name_id

inner join prefix_file p3 on p3.prefix > p2.prefix and p0.name_id = p3.name_id

It should give you the desired results.....

|||There can be zero or more prefixes. That is why I would like to pass the dataset onto the Report Code and then do a for each statement, however I don't think that is possible.

Report Builder?

I have a table with no Primary Key and during build the report model,
the message show me : Table dose not have a primary key.
So, the table wants to build as a report model must have primary key?
If I want the table that has no primary key and build as report model,
how could I do?
Thanks for any advice!
AngiIt is possible to manually create an entity, bind it to the table, add
fields etc.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angi" <enchiw@.msn.com> wrote in message
news:ua5mb4BtFHA.2912@.TK2MSFTNGP09.phx.gbl...
>I have a table with no Primary Key and during build the report model,
> the message show me : Table dose not have a primary key.
> So, the table wants to build as a report model must have primary key?
> If I want the table that has no primary key and build as report model,
> how could I do?
> Thanks for any advice!
> Angi
>

Report Builder: Is there a way to filter a filter?

Dear Anyone,

We have a report model that connects to a single table. Some of the columns of the table contains blank values which is regarded as a valid value in our reports.

We have a requirement that the filter selection should not contain any blank values. They do not want to include a blank value for the users to choose but if the users decide not to use a filter, those blank values should still be included in the report.

Does anyone have any suggestions on how to do this in RB? We already made this work in RS2k5 but we can't seem to find any way to do this in RB.

Thanks,
Joseph

No, there is no way to control the list of values presented to the user. It is always a list of all existing values in the underlying database.

Report Builder: Custom Aggregate

Hi All,
I have used Report Builder to enable user to do Ad-Hoc Report that has
been great so far, but I have unique requirement for below table/view:
Month Headcount
================== January-06 45
February-06 41
March-06 43
April-06 42
May-06 38
June-06 30
July-06 50
(etc...)
Dec-06 42
Above table represents how many employees, one company/department has
during Month column. In January-06, there is 45 employees,
February-06, there is 44 employees, and so on.
If user choose to show Headcount attribute, Report Builder should
display each month headcount. The current problem is on the aggregate.
for example, report should show Headcount for Quarter 1 as 43, which
is headcount for March-06 (how many employees I have at the end of
Quarter 1), Quarter 2 should display 30, which is headcount for
June-06, and for Year of 2006 should be 42 (at Dec-06). There will be
more complexity: if now is still in the month of November 06 and no
data for December yet, report should show headcount of November 06 as
headcount for Year of 2006.
I don't think default available aggregates which are: Total, Average,
Max, Min, able to do this.
Q1, correct value should be 40 (March-06)
If I use Total Aggregate, it will be 99.
If I use Average, it will be 24.75
If I use Max, it will be 45.
If I use Min, it will be 41.
I need an aggregate that show number from last record in the dataset.
Is there any possible way how to do this? Any input is appreciated.
Thank you.On Jun 19, 4:59 pm, Gunady <Gun...@.gmail.com> wrote:
> Hi All,
> I have used Report Builder to enable user to do Ad-Hoc Report that has
> been great so far, but I have unique requirement for below table/view:
> Month Headcount
> ==================> January-06 45
> February-06 41
> March-06 43
> April-06 42
> May-06 38
> June-06 30
> July-06 50
> (etc...)
> Dec-06 42
> Above table represents how many employees, one company/department has
> during Month column. In January-06, there is 45 employees,
> February-06, there is 44 employees, and so on.
> If user choose to show Headcount attribute, Report Builder should
> display each month headcount. The current problem is on the aggregate.
> for example, report should show Headcount for Quarter 1 as 43, which
> is headcount for March-06 (how many employees I have at the end of
> Quarter 1), Quarter 2 should display 30, which is headcount for
> June-06, and for Year of 2006 should be 42 (at Dec-06). There will be
> more complexity: if now is still in the month of November 06 and no
> data for December yet, report should show headcount of November 06 as
> headcount for Year of 2006.
> I don't think default available aggregates which are: Total, Average,
> Max, Min, able to do this.
> Q1, correct value should be 40 (March-06)
> If I use Total Aggregate, it will be 99.
> If I use Average, it will be 24.75
> If I use Max, it will be 45.
> If I use Min, it will be 41.
> I need an aggregate that show number from last record in the dataset.
> Is there any possible way how to do this? Any input is appreciated.
> Thank you.
Reporting Services actually supports "Last" Aggregate (using regular
RDL file, e.g. developed using Visual Studio), which I think can solve
the problem. I notice that we can use
Count,CountDistinct,StDev,StDevP,Var, or VarP, in the Report Builder
editor itself, but there is no First or Last available. Does anyone
knows how to use this "Last" aggregate in report builder?

Report Builder top x% clause

How to set up report (chart or table) with:
- top X
or
- top x%
clause to limit number of records displayed?
Is it possible?
Any ideas?
TIA,
KamelThis can be done by using the 'Top N' or 'Top %' in the filter tab for
the chart or table, where you set the expression, then select 'Top N'
ot 'Top %'as the Operator, followed by the actual number or pct.
Matt A|||I have following operators in filter definition:
- Is Empty
- Equals
- In a list
- Greater Than
...etc
But there are no Top N and Top % operators.
Whats wrong?
Kamel|||Can someone help with this?|||Put your "top 10 percent" clause into your query\stored procedure that feeds
the report...
"kamel" wrote:
> Can someone help with this?
>|||I am usinging report builder with report model.
Is it possible and how to use TOP N with report builder and report
model?
Kamel|||Ah right, sorry I havnt worked with the report model and report builder yet
so cant answer your question.
"kamel" wrote:
> I am usinging report builder with report model.
> Is it possible and how to use TOP N with report builder and report
> model?
> Kamel
>|||Matt,
are you sure this is possible?
I can not find TOP N operator in a filter :(
Kamel|||Has someone managed to do that with report builder?sql

Report Builder imported questions from a customer

Hi,
1. If a table has a relation to itself example :
employee id emp name manager id
1 Melissa 2
2 Steven 100
This means Steven is the manager of Melissa.
Is it possible to make a report in Report Builder to have a list with of the
managers and their employees?
2.Following example in report builder an organization with his employees you
will see in Report builder this : (organization will appear only once)
Microsoft Steven
Melissa
BMW Jan
Els
In export to xls the customer wants to see this :
Microsoft Steven
Microsoft Melissa
BMW Jan
BMW Els
Is this possible?
And if a certain organization does not have employees they want to see this
organization for the moment the report only list organizations with employees?
3. If the email field of a person (table 1) is empty they want to see in the
report the email field of the organization (table 2) is this possible with
the function if then else... in the creation of a new field without having
fields of the organization in the report displayed?"carolineb" <carolineb@.discussions.microsoft.com> wrote in message
news:FE5D33EF-66CC-4420-B60C-CC8AC82AA616@.microsoft.com...
> Hi,
> 1. If a table has a relation to itself example :
> employee id emp name manager id
> 1 Melissa 2
> 2 Steven 100
> This means Steven is the manager of Melissa.
> Is it possible to make a report in Report Builder to have a list with of
> the
> managers and their employees?
Write query like this:
select
c2.Name as ManagerMame,
c1.*
from Employees c1
left join Employees c2
on c2.EmployeeID = c1.ManagerID
order by c1.ReportsTo
> 2.Following example in report builder an organization with his employees
> you
> will see in Report builder this : (organization will appear only once)
> Microsoft Steven
> Melissa
> BMW Jan
> Els
> In export to xls the customer wants to see this :
> Microsoft Steven
> Microsoft Melissa
> BMW Jan
> BMW Els
> Is this possible?
> And if a certain organization does not have employees they want to see
> this
> organization for the moment the report only list organizations with
> employees?
As far as I know this is not possible (export should be WYSIWYG if it is
working correctly)
> 3. If the email field of a person (table 1) is empty they want to see in
> the
> report the email field of the organization (table 2) is this possible with
> the function if then else... in the creation of a new field without having
> fields of the organization in the report displayed?
>
Yes it is possible, you don't have to display all fields.
Regards,
Stjepan|||"Stjepan Puljko" wrote:
> "carolineb" <carolineb@.discussions.microsoft.com> wrote in message
> news:FE5D33EF-66CC-4420-B60C-CC8AC82AA616@.microsoft.com...
> > Hi,
> >
> > 1. If a table has a relation to itself example :
> >
> > employee id emp name manager id
> > 1 Melissa 2
> > 2 Steven 100
> >
> > This means Steven is the manager of Melissa.
> > Is it possible to make a report in Report Builder to have a list with of
> > the
> > managers and their employees?
> Write query like this:
> select
> c2.Name as ManagerMame,
> c1.*
> from Employees c1
> left join Employees c2
> on c2.EmployeeID = c1.ManagerID
> order by c1.ReportsTo
> caroline : we cannot write a select in report builder?
> >
> > 2.Following example in report builder an organization with his employees
> > you
> > will see in Report builder this : (organization will appear only once)
> >
> > Microsoft Steven
> > Melissa
> > BMW Jan
> > Els
> > In export to xls the customer wants to see this :
> > Microsoft Steven
> > Microsoft Melissa
> > BMW Jan
> > BMW Els
> > Is this possible?
> > And if a certain organization does not have employees they want to see
> > this
> > organization for the moment the report only list organizations with
> > employees?
> As far as I know this is not possible (export should be WYSIWYG if it is
> working correctly)
> > 3. If the email field of a person (table 1) is empty they want to see in
> > the
> > report the email field of the organization (table 2) is this possible with
> > the function if then else... in the creation of a new field without having
> > fields of the organization in the report displayed?
> >
> Yes it is possible, you don't have to display all fields.
caroline : I have tried to do this and was only allowed to use fields of one
table in the if then else function, he did allowed to select a field from
another table in the if then else but indicated with an error message that
the function is not valid?
> Regards,
> Stjepan
>
>

report builder how to

Is it possible to create following table report:
Col1: All Customers
Col2: Count of Orders in Last 12 months
my customers records get filtered when there is no order is last 12
months
but I'd like to show all customers and count of 0 where there is no
order in last 12 months
any idea?
KamelIf your SQL statement returns the two columns:
SELECT CustomerName, count(OrderID) as CountOfOrders
FROM ORDERTable
GROUP BY CustomerName
Then put those values into a table or list data region.
Is this what you are asking for?
"kamel" <kwiciak@.gmail.com> wrote in message
news:1148415235.959944.42070@.i39g2000cwa.googlegroups.com...
> Is it possible to create following table report:
> Col1: All Customers
> Col2: Count of Orders in Last 12 months
> my customers records get filtered when there is no order is last 12
> months
> but I'd like to show all customers and count of 0 where there is no
> order in last 12 months
> any idea?
> Kamel
>|||I talk about Report Builder with Report Model;
notice: Last 12 Months|||SQL which I would like to obtain in Report Builder:
select
CustomerName,
(select count(OrderID) from Orders where Orders.CustId =Customers.CustId and OrderDate > '2005-05-01')
from Customers
group by CustomerName
is it possible?|||Have you checked the cardinality of the relationship of the Customers entity
to the relationship of the Orders entity? It sounds like it should be
OptionalMany, meaning that there may be 0 or more Orders for each Customer.
If it is Many, then you are telling the model that each Customer has 1 or
more Orders.
Setting the cardinality from Many to OptionalMany should change the query
from something analogous to:
select
c.CustomerName,
count(o.OrderID)
from
Customers c
inner join
Orders o
on
c.CustId = o.CustId
where
o.OrderDate > '2005-05-01'
group by
c.CustomerName
to:
select
c.CustomerName,
count(o.OrderID)
from
Customers c
left outer join
Orders o
on
c.CustId = o.CustId
where
o.OrderDate > '2005-05-01'
group by
c.CustomerName
Having said this, my own experiments with Report Builder and Models show
that there may be a bug preventing the outer join from being used, even if
the cardinality is OptionalMany. However, the cardinality will have to be
set properly for it to work.|||Thank you!
It doesn't work with 3 entities as in my exaple (1 entity - "time" is
in a filter)
and relations are as:
Customer - Order - Time
Any ideas,
Kamel
larthallor wrote:
> Have you checked the cardinality of the relationship of the Customers entity
> to the relationship of the Orders entity? It sounds like it should be
> OptionalMany, meaning that there may be 0 or more Orders for each Customer.
> If it is Many, then you are telling the model that each Customer has 1 or
> more Orders.
> Setting the cardinality from Many to OptionalMany should change the query
> from something analogous to:
> select
> c.CustomerName,
> count(o.OrderID)
> from
> Customers c
> inner join
> Orders o
> on
> c.CustId = o.CustId
> where
> o.OrderDate > '2005-05-01'
> group by
> c.CustomerName
> to:
> select
> c.CustomerName,
> count(o.OrderID)
> from
> Customers c
> left outer join
> Orders o
> on
> c.CustId = o.CustId
> where
> o.OrderDate > '2005-05-01'
> group by
> c.CustomerName
> Having said this, my own experiments with Report Builder and Models show
> that there may be a bug preventing the outer join from being used, even if
> the cardinality is OptionalMany. However, the cardinality will have to be
> set properly for it to work.|||Refrash of topic.
I would like to create following query:
select
Customer.CustomerID,
Time.DayID,
count(Order.OrderID)
from
Customer LEFT OUTER JOIN
Time LEFT OUTER JOIN
Order
with ReportBuilder I can not obtain such a query.
RB puts table from whitch value is on matrix's data area on top, eg.
select
Customer.CustomerID,
Time.DayID,
count(Order.OrderID)
from
Order LEFT OUTER JOIN
Customer LEFT OUTER JOIN
Time
in this example count(Order.OrderID) is on data area of matrix type
report
Any ideas,
Kamel
kamel wrote:
> Thank you!
> It doesn't work with 3 entities as in my exaple (1 entity - "time" is
> in a filter)
> and relations are as:
> Customer - Order - Time
> Any ideas,
> Kamel
> larthallor wrote:
> > Have you checked the cardinality of the relationship of the Customers entity
> > to the relationship of the Orders entity? It sounds like it should be
> > OptionalMany, meaning that there may be 0 or more Orders for each Customer.
> > If it is Many, then you are telling the model that each Customer has 1 or
> > more Orders.
> >
> > Setting the cardinality from Many to OptionalMany should change the query
> > from something analogous to:
> >
> > select
> > c.CustomerName,
> > count(o.OrderID)
> >
> > from
> > Customers c
> > inner join
> > Orders o
> > on
> > c.CustId = o.CustId
> >
> > where
> > o.OrderDate > '2005-05-01'
> >
> > group by
> > c.CustomerName
> >
> > to:
> >
> > select
> > c.CustomerName,
> > count(o.OrderID)
> >
> > from
> > Customers c
> > left outer join
> > Orders o
> > on
> > c.CustId = o.CustId
> >
> > where
> > o.OrderDate > '2005-05-01'
> >
> > group by
> > c.CustomerName
> >
> > Having said this, my own experiments with Report Builder and Models show
> > that there may be a bug preventing the outer join from being used, even if
> > the cardinality is OptionalMany. However, the cardinality will have to be
> > set properly for it to work.

Report Builder Error

I have created a view with one table (CASEMASTER). When I look at the Visual
Studioâ's property of the Case Number field within table CASEMASTER, it is set
to AllowNull = False, which correctly corresponds with the property of this
field on the SQL Server.
However, I need this table to be replaced with a new named query because I
need to limit the data to only one county, plus I need to link other tables.
When I set it as a query, it changes the property of the Case Number field to
AllowNull = True. Consequently, when I try to deploy it, I get a "Nullable
Property Error".
Is there any way to change the AllowNull property OR prevent this from
happening so that I can deploy the view/model without errors?
Any suggestions are appreciated.
--
K. ThomasI removed the minOccurs="0" property in the code to get this to work properly.
--
K. Thomas
"K. Thomas" wrote:
> I have created a view with one table (CASEMASTER). When I look at the Visual
> Studioâ's property of the Case Number field within table CASEMASTER, it is set
> to AllowNull = False, which correctly corresponds with the property of this
> field on the SQL Server.
> However, I need this table to be replaced with a new named query because I
> need to limit the data to only one county, plus I need to link other tables.
> When I set it as a query, it changes the property of the Case Number field to
> AllowNull = True. Consequently, when I try to deploy it, I get a "Nullable
> Property Error".
> Is there any way to change the AllowNull property OR prevent this from
> happening so that I can deploy the view/model without errors?
> Any suggestions are appreciated.
> --
> K. Thomas

Report Builder Conditional IF Function Problem

Hi everyone,

I have created a view from the exisiting table to use in the report builder model and in the table whenever there is a null(when users does not enter a date for this particular field in the application) for datetime field either of these 2 ("1/1/1753" and "12/31/9999") dates are stored . Since I am creating my views based on these tables these dates will be in my views as well. Checking for this at the application level and cleaning up is not a option for me at this time.

So what I am trying to do is to check for these values and replace it with Null or blank in the model designer expression property. So that when user creates a report using this field they will not see these sql standard dates.

I tried using conditional IF in Model designer .

EX:

IF(Next MCR Review Date = 1/1/1753,EMPTY,Next MCR Review Date)

IF(Next MCR Review Date = 1/1/1753,NULL,Next MCR Review Date)

Both of these gives me errors.

Can anyone tell me what I am doing wrong and also are there any other ways to get to what I want.

I have already spent a lot of time digging for documentation but no luck any help is appreciated.

Thanks a Lot

Ashwini

I struggled with this for a while, it's a good question... and the model builder stuff really is a PITA.

Here is what I suggest: include the appropriate information in your view as an extra field and use that field instead of your "real" date" in your model.

In the view you can use something like

Code Snippet

CASE WHEN [Next MCR Review Date] = '1/1/1753' THEN NULL

ELSE [Next MCR Review Date] END AS ModelReviewDate

.. you can leave the original value in as a separate column in the view for additional purposes, comparison, etc.

>L<

|||

Thank you so much Lisa this works like a charm. I had tried this before but silly me I had not put the quotes '1/1/1753' like this Instead I was just typing it this way

Code Snippet

[Next MCR Review Date] = CASE TME.next_assessment_dt
WHEN 1/1/1753 THEN ' '

WHEN 12/31/9999 THEN ' '

ELSE TME.next_assessment_dt
END

Your code Snippet rang the bell Thanks again so much!!! You have made my day.

Ashwini