"where" in DTS

i try use "where" to filter record for result but when i run , there is a message error task.

select flattened

(select * from predict ([model name].[table name], include_statistic) where [$support] >0 ) as ph

from

[model name]

prediction join

.....

can onebody help me?

What is the error?|||It could be that you need to say "INCLUDE_STATISTICS" rather than "INCLUDE_STATISTIC"|||

error that it messesage: " there an error occur in task". no more information to know what error and where error in query.

what's different between INCLUDE_STATSTICS and INCLUDE_STATSTIC?

|||

The difference is one is a keyword, and the other is an error.

You can try to execute your query in SQL Management Studio to see the full error message, although it should be available from SSIS as well.

If you are doing this on the pipeline and you want to validate your DMX syntax simply eliminate the PREDICTION JOIN clause,

e.g.

select flattened

(select * from predict ([model name].[table name], include_statisticS) where [$support] >0 ) as ph

from

[model name]

|||

sory, but first, i writed like that but still have error. and it didn't message with an clear error to know what error. how can i do now?

i try a model to show other products when someone choose a product. my result have many record that same.

e.g:

productid otherProductid

1 34

1 74

1 89

2 34

2 74

2 89

3 34

3 74

3 89

4 56

4 101

4 73

....

what wrong with my model and how i should do to my event?

thanks

|||

This is not an error, rather a feature. This occurs when your model doesn't contain rules for the data in your input. The system is designed that if you ask for 5 recommendations, you will get 5 recommendations. If the input you provide doesn't resolve into any rules (or enough rules) you will get the most popular results. I.e. it looks like item 34 is the most popular in your training set.

There are a few ways to alleviate this issue. One is to change the minimum_support and minimum_probabilty such that you generate more rules. Another is to filter results that are not based on rules. A description on how to do this is at http://blogs.msdn.com/jamiemac/archive/2006/04/19/579409.aspx

Thanks

"Other" grouping or Row aggregate

AS 2005, SP1:

I have a dimension with 15 members which I can filter the Top N members by measure.
What I would like to do is append an additional Row as a group or aggreagate the reads "Other" that reflects the remaining measure value. For example,

Top 5 Widgets

W5 5000
W9 4000
W2 3000
W11 2000
W7 1000
+Other 6000

The "Other" Row would sum the remaining 10 widgets so the user could see 100% of the totals.
I can do the Top N but have no idea how to start this one. Thanks in advance for your help.

-Troy

Reed Jacobson wrote up the standard solution to this problem on his blog recently:
http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/08/03/22345.aspx

In AS2K this could be optimised, and I wrote up how on my blog here:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!170.entry
... however, you'd need to test whether this was still a good idea in AS2005 - I think it might actually harm performance.

HTH,

Chris

|||Sorry for the delay. This was exactly what I what was looking for.

Thanks Reed.

"Begins With" Filter in Report Builder?

Hello,

Anyone know how to setup a "Begins With" or "Ends With" Promptable Filter in Report Builder?

Thanks!!

-Brian

Can't you use the LIKE clause? ie, WHERE name LIKE 'A%' etc.?

dlesko|||As per the original post, I am using Report Builder, not Report Designer to build the reports. With Report Builder you are not using an actual SQL Query for the report's data source, you are using what is called a "Semantic Query".

When I specify a Filter criteria on a string field in Report Builder, there are no choices for "Begins With" or "Ends With". There is only a "Contains". I have tried using the standard Wild Card characters (%, *) with the "Equals" filter condition and parameter value (i.e. "A%"), but that doesn't seem to work.

"Begins With" has got to be a pretty standard filter criteria and I have a hard time believing that Microsoft left this ability out of Report Builder. Although they don't support the ability to specify a range for a single parameter, which is also pretty common (like a Date Range).

Any info is greatly appreciated...

Thanks!

-Brian|||Sorry, my bad. I always confuse the two names!

Anyway, I haven't done this but according to the docs you can create a filter condition for "begins with". See here:

http://msdn2.microsoft.com/en-us/library/ms189375(en-US,SQL.90).aspx

Before you can create the filter condition you have to drag a field onto the filter box which may make that option more obscure than it should be.

dlesko|||Thanks for your help, but that really didn't say much more than I already knew...

I even tried creating the filter as an expression, but there doesn't look like an easy way of doing it here either...

Still looking for a way to do "Begins With" in Report Builder or confirmation that it cannot be done in this release.

Thanks!

Brian|||What about creating a column with a left(columnname,1) and using the Equal to operator?
(Of course this would only work if you're looking for the first character.)|||Thanks for the info...I was thinking of something like this, but I guess I really just want confirmation now about whether or not there is a "Begins With" or "Ends With" paramter criteria for String values.

Thanks!

Brian|||

The documentation is incorrect. Begins With and Ends With are not currently supported as filter condition operators, although you can create an equivalent formula filter condition:

AND(LENGTH(MyField)>7, LEFT(MyField, 7)="MyValue")

Support for parameters in the formula dialog is very limited, though, and creating a formula filter condition that includes multiple references to the same parameter (as you would need in your scenario) is not possible in this version of Report Builder.|||

Does the Report Designer (not Report Builder) support the "Begins With" or "Ends With" filter?

I played a little with it last night and didn't see anything...

-Brian

|||All the .NET System.String functions are available in RDL expressions, so you can use String.StartsWith, etc.

"Begins With" Filter in Report Builder?

Hello,

Anyone know how to setup a "Begins With" or "Ends With" Promptable Filter in Report Builder?

Thanks!!

-Brian

Can't you use the LIKE clause? ie, WHERE name LIKE 'A%' etc.?

dlesko|||As per the original post, I am using Report Builder, not Report Designer to build the reports. With Report Builder you are not using an actual SQL Query for the report's data source, you are using what is called a "Semantic Query".

When I specify a Filter criteria on a string field in Report Builder, there are no choices for "Begins With" or "Ends With". There is only a "Contains". I have tried using the standard Wild Card characters (%, *) with the "Equals" filter condition and parameter value (i.e. "A%"), but that doesn't seem to work.

"Begins With" has got to be a pretty standard filter criteria and I have a hard time believing that Microsoft left this ability out of Report Builder. Although they don't support the ability to specify a range for a single parameter, which is also pretty common (like a Date Range).

Any info is greatly appreciated...

Thanks!

-Brian|||Sorry, my bad. I always confuse the two names!

Anyway, I haven't done this but according to the docs you can create a filter condition for "begins with". See here:

http://msdn2.microsoft.com/en-us/library/ms189375(en-US,SQL.90).aspx

Before you can create the filter condition you have to drag a field onto the filter box which may make that option more obscure than it should be.

dlesko|||Thanks for your help, but that really didn't say much more than I already knew...

I even tried creating the filter as an expression, but there doesn't look like an easy way of doing it here either...

Still looking for a way to do "Begins With" in Report Builder or confirmation that it cannot be done in this release.

Thanks!

Brian|||What about creating a column with a left(columnname,1) and using the Equal to operator?
(Of course this would only work if you're looking for the first character.)|||Thanks for the info...I was thinking of something like this, but I guess I really just want confirmation now about whether or not there is a "Begins With" or "Ends With" paramter criteria for String values.

Thanks!

Brian|||

The documentation is incorrect. Begins With and Ends With are not currently supported as filter condition operators, although you can create an equivalent formula filter condition:

AND(LENGTH(MyField)>7, LEFT(MyField, 7)="MyValue")

Support for parameters in the formula dialog is very limited, though, and creating a formula filter condition that includes multiple references to the same parameter (as you would need in your scenario) is not possible in this version of Report Builder.

|||

Does the Report Designer (not Report Builder) support the "Begins With" or "Ends With" filter?

I played a little with it last night and didn't see anything...

-Brian

|||All the .NET System.String functions are available in RDL expressions, so you can use String.StartsWith, etc.