Conditional Page Breaks

Hi -
I'm trying to generate invoice reports where there are two types of
line items (with a different number of columns). I am basically looking
to have two sets of tables as follows:
Case A - at least one of each item
Table Items 1
<PAGE BREAK>
Table Items 2
Case B - items 2 exist, but no items 1
Table Items 2
Case C - items 1 exist, but no items 2
Table Items 1
I've spent hours trying to figure out how to accomplish this - I have
played with putting Table 1 in an object with conditional visibility,
but that doesn't seem to suppress a page break. I've tried a "hidden
group" but no luck there either. I've also tried having Table items 1
in a subreport.
I'm open to suggestion if you don't think I am going about this the
right way - I would really appreciate any help!
Thanks
BNo conditional page breaks. I've been asking for them since the first
release a couple years ago. It is the main reason we have not migrated. If
you can't control the layout of the report it's pointless. I've tried many
workarounds but it seems like the developers went out of their way to not let
you programatically control them. Be it if you want to remove them as in
HTML reports or insert them manually.
Good luck on your quest. If you find something let us know.
"bigbrorpi@.gmail.com" wrote:
> Hi -
> I'm trying to generate invoice reports where there are two types of
> line items (with a different number of columns). I am basically looking
> to have two sets of tables as follows:
> Case A - at least one of each item
> Table Items 1
> <PAGE BREAK>
> Table Items 2
> Case B - items 2 exist, but no items 1
> Table Items 2
> Case C - items 1 exist, but no items 2
> Table Items 1
>
> I've spent hours trying to figure out how to accomplish this - I have
> played with putting Table 1 in an object with conditional visibility,
> but that doesn't seem to suppress a page break. I've tried a "hidden
> group" but no luck there either. I've also tried having Table items 1
> in a subreport.
> I'm open to suggestion if you don't think I am going about this the
> right way - I would really appreciate any help!
> Thanks
> B
>|||See if this can help :
You can insert a rectangle into a rectangle, and set a page break in the
inner rectangle and visibility in the outer and paste this empty nested
rectangles
in between subreports or tables . If Visible expression is True, the page
break will
work. if Visible expression is False , it will ignore a Page Break. So,
this nested construction will work as a conditional page break ( for RSS
2000) .

Conditional Full-text search

Is there any way to use full-text search conditionally? For example, I have a query:

Code Snippet

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]
where
Items.ItemId = 1
and
Items.Type >= 0
order by
Search.Rank DESC

How can I put where clause to optimize my query? I want where clause to be executed before freetexttable, for more perfomance. It's obvious, that full-text search is not optimized well in my case, because where is executed after full-text search.

Is there any way to do it? Thank you in advance.

And if I have a query

select
Search.Rank,
Items.*
from
Items
inner join
freetexttable(Items, *, 'blablabla') as Search on
Items.ItemId = Search.[Key]

Is there any way to force freetexttable return only first 10 results, but not all of them? (I know, I can do it after freetexttable with 'select top 10', but I want inside freetexttable, to minimize the load of my SQL Server).
|||Read
"Items.ItemId = 1"
like
"Items.ItemId in (1, 2, 3, 4, 5, 10"
|||Unfortunately in this version of SQL Server, freetext search and SQL Server is separated from each other. Therefore the query optimizer is not able to optimize both queries within one step. You will need to use the optional top parameters provided for freetext search. Look in the BOL:

top_n_by_rank

When an integer value, n, is specified, FREETEXTTABLE returns only the top n matches, ordered by rank.

If filtering is performed in addition to the FREETEXTTABLE predicate, the filter is applied to the top n rows and fewer than top_n_by_rank rows will be returned. Enabling the precompute rank option in the sp_configure stored procedure can increase the prerformance of FREETEXTTABLE queries that use the top_n_by_rank parameter. For more information, see sp_configure (Transact-SQL) and sp_fulltext_service (Transact-SQL).

Jens K. Suessmeyer.

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

Hi Zhuravl,

As said by Jean, you can use top_n_by_rank to limit resultset. This is in the BOL:

" Limiting Result Sets to Return the Most Relevant Results

In many full-text queries, the number of items matching the search condition is very large. To prevent queries from returning too many matches, use the optional argument, top_n_by_rank, in CONTAINSTABLE and FREETEXTTABLE to specify the number of matches according to rank you want returned.

Note:

Using the top_n_by_rank argument returns a subset of rows that satisfy the full-text query. If top_n_by_rank is combined with other predicates, the query could return fewer rows than the number of rows that actually match all the predicates.
With this information, Microsoft SQL Server orders the matches by rank and returns only up to the specified number. This choice can result in a dramatic increase in performance. For example, a query that would normally return 100,000 rows from a table of one million rows are processed more quickly if only the top 100 rows are requested.

If you want only the top 3 matches returned on an earlier example using CONTAINSTABLE, the query looks like the following:

USE Northwind; GO SELECT K.RANK, CompanyName, ContactName, Address FROM Customers AS C INNER JOIN CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*", Rue WEIGHT(0.5), Bouchers WEIGHT(0.9))', 3) AS K ON C.CustomerID = K.[KEY]; GO

Here is the result set:

RANK CompanyName ContactName address
- -- -
123 Bon app' Laurence Lebihan 12, rue des Bouchers 65 Du monde entier Janine Labrune 67, rue des Cinquante Otages 15 France restauration Carine Schmitt 54, rue Royale

This example returns the description and category name of the top 10 food categories where the Description column contains the words "sweet and savory" near either the word "sauces" or the word "candies."

SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE (Categories, Description, '("sweet and savory" NEAR sauces) OR ("sweet and savory" NEAR candies)' , 10 ) AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY]; GO
"

Other references:

http://www.developmentnow.com/blog/SQL+Server+2005+Full+Text+Search+On+HTML+Documents.aspx

Regards

Nilton Pinheiro

www.mcdbabrasil.com.br

Conditional Formatting - Export

Using the web viewer control in VS 2005, is there a way to conditionally format items for export only?

For example, user runs a report which gets displayed in the viewer. The report contains small images and or decorated text for links to additional reports. If user selects pdf from the export dropdown list and clicks export, I would like hide the images in the pdf and or format the decorated text differently.

If the answer is no, is it due to the report already being in its intermediate state?

The answer to your first question is no. One way of getting close to the desired behavior is to either use different report definitions or use a report parameter to determine if images should be shown etc.

The answer to the second question is yes. When you export the report to PDF, it will just re-render the report from the already processed (and output-format independent) intermediate format.

-- Robert

Concerning .net and SQL Procedures

Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter.

Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else.

The problem i am recieving is when i access the code from a .net windows application it tells me:

Error Message:
Insert Error: Column name or number of supplied values does not match table definition.
Insert Error: Column name or number of supplied values does not match table definition.

Procedure Errored On: val_GetDuplicateItemsFromAssignment
Line Number: 16

However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values.

Does anyone know why this could be happening?

Please do show the code used to insert the values.