Format Alignment: report

Showing posts with label report. Show all posts

Thursday, March 29, 2012

Filter in stored procedure

I am trying to edit this query i already have that is fully functional for another report in reporting services. The report has visits sales reps made to stores, broken down by period, week and then date. Now, i need to filter it out by Visits. They want the Sales Reps who had less then 6 visits a day. How would i code that out in my stored procedure? When i have Stores and Account Status( which makes it pretty broken down). Any suggestions. Thanks..heres the code.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

BEGIN

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

END

SET NOCOUNT OFF

If dbo.Qry_Visits.Visits stores the cumulative count of the visits made then you can add 'Qry_Visits.Visits < 6' to the where clause. To make it more generic instead of hard coding values, you may want to have to edit the signature of the proc to have an input parameter that represents the number of visits also.

|||

try:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.Visits >= @.visit_req

END

SET NOCOUNT OFF

|||it does not show the cumulative. It shows one visit per sales person, per date. So its really broken down. So if i put <6 it will show everything, because each record has one visit. I only want the ones with less then 6 visits by a Sales Rep. Which means the sum of visits per Sales Rep, per day. How would i get that?|||

I don't really understand the data structure, but is possible to just do an aggregate similar to:

Code Snippet

Pseudo Code:

SELECT YourColumns, SUM(Visits) AS Visits
FROM AllThoseTables
GROUP BY YourColumns
HAVING SUM(Visits) > 5

|||

Then do:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.[SR Code] NOT IN(select [SR Code] from dbo.Qry_Visits

group by [SR Code]

having count(Visits) < @.visit_req)

END

SET NOCOUNT OFF

filter in Reporting Services: Best way?

Hi there,
I'm a newbe to reportig services and try to make up a financial report:
store currentYear pastYear Delta DeltaIn%PastYear
I tried for several hours with the table control:
- Gain the date with a query from ssas
- set a parameter for the year
- put several tables on the report and set a filter to the year dimension
The problem I have with this approach is:
- Formatting: I would prefer one table
- I don't know how to calculate the deltas (currentYear-pastYear)
Regards
TomPressed the post button to fast...
My questions are:
- Can I set a filter on a column in a table control?
- Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
- Or is it better to use different controls (like the matrix or textboxes)?
Please help me - I have a deadline and running out of time!
Thanks in advance,
Tom
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Hi,
Just answering to your questions.
1. Can I set a filter on a column in a table control?
No for the full row you can set filter.
2. Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
Yes very much. using <Expressions> option.
Regards
Amarnath
"Tomilee" wrote:
> Pressed the post button to fast...
> My questions are:
> - Can I set a filter on a column in a table control?
> - Can I calculate in a table control a value, that derives from other
> columns in the same or different tables?
> - Or is it better to use different controls (like the matrix or textboxes)?
> Please help me - I have a deadline and running out of time!
> Thanks in advance,
> Tom
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom|||Tomilee,
to get store currentYear pastYear Delta DeltaIn%PastYear
you should do all of this in the MDX query ( you mentioned using SSAS)...
To get the PastYear info, look into the ParallelPeriod function in MDX. the
Delta is current - ParallelPeriod, etc
in answer to your other questions... you can NOT put a filter on a textbox,
but you can filter on a group, table, etc... But what you can do is use a
conditional if on the textbox expression... ie
IIF (Fields!fieldname.Value > 5,truepart, falsepart)
Using this you might can get the effect of a filter..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Thanks for your Help! Tom
"Wayne Snyder" wrote:
> Tomilee,
> to get store currentYear pastYear Delta DeltaIn%PastYear
> you should do all of this in the MDX query ( you mentioned using SSAS)...
> To get the PastYear info, look into the ParallelPeriod function in MDX. the
> Delta is current - ParallelPeriod, etc
> in answer to your other questions... you can NOT put a filter on a textbox,
> but you can filter on a group, table, etc... But what you can do is use a
> conditional if on the textbox expression... ie
> IIF (Fields!fieldname.Value > 5,truepart, falsepart)
> Using this you might can get the effect of a filter..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom

Filter Help! - Datatypes

Hello,
I am having trouble using a filter due to incorrect data types. I am trying to eliminate items with a dollar value of 0 from the report. The dollar field that I am using comes from a SQL server database and the data type of the field is decimal(17,5). What value do I need to put in the value field?
I have tried = 0, = 0.0, and = 0.00000
Help!Check this thread:
http://groups.google.com/groups?threadm=OuLkPagWEHA.3012%40tk2msftngp13.phx.gbl
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> Hello,
> I am having trouble using a filter due to incorrect data types. I am
trying to eliminate items with a dollar value of 0 from the report. The
dollar field that I am using comes from a SQL server database and the data
type of the field is decimal(17,5). What value do I need to put in the
value field?
> I have tried = 0, = 0.0, and = 0.00000
> Help!|||The data provider will return the field as System.Decimal. System.Decimal is
completely different than System.Double (i.e. =0.0) and therefore the
comparison fails.
Please try one of the following:
* change the filter expression to convert the decimal to a double:
=CDbl(...)
* or change the filter value to convert the constant into a decimal:
=CDec(0.0)
More details on the conversion functions are available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> Hello,
> I am having trouble using a filter due to incorrect data types. I am
trying to eliminate items with a dollar value of 0 from the report. The
dollar field that I am using comes from a SQL server database and the data
type of the field is decimal(17,5). What value do I need to put in the
value field?
> I have tried = 0, = 0.0, and = 0.00000
> Help!|||Thank you! Both of the options work great.
"Robert Bruckner [MSFT]" wrote:
> The data provider will return the field as System.Decimal. System.Decimal is
> completely different than System.Double (i.e. =0.0) and therefore the
> comparison fails.
> Please try one of the following:
> * change the filter expression to convert the decimal to a double:
> =CDbl(...)
> * or change the filter value to convert the constant into a decimal:
> =CDec(0.0)
> More details on the conversion functions are available at:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> > Hello,
> >
> > I am having trouble using a filter due to incorrect data types. I am
> trying to eliminate items with a dollar value of 0 from the report. The
> dollar field that I am using comes from a SQL server database and the data
> type of the field is decimal(17,5). What value do I need to put in the
> value field?
> >
> > I have tried = 0, = 0.0, and = 0.00000
> >
> > Help!
>
>

Filter Help Needed

Good Day to All,
Need some help with Filters in SQL Reporting 2000.
Have a table report setup with a DS going to a Store Proc. need to add a
filter that is a bit complex. Don't know which filter section I should be
putting this in but in all cases I've run into errors.
Basically when the data is brought back from my Store Proc, I need to filter
out the data according to the following:
IF CBalance < 0
THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
ELSE (TTLBalance > (CBalance*0.25))
I keep getting this error that the filter comparison fails and I should
check the data Types returned by the filter expression.
Please help...you might want to cast your fields as varchar in your query. This has
been the answer to many filter comparison problems I have had in the
past.
Eric wrote:
> Good Day to All,
> Need some help with Filters in SQL Reporting 2000.
> Have a table report setup with a DS going to a Store Proc. need to add a
> filter that is a bit complex. Don't know which filter section I should be
> putting this in but in all cases I've run into errors.
> Basically when the data is brought back from my Store Proc, I need to filter
> out the data according to the following:
> IF CBalance < 0
> THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> ELSE (TTLBalance > (CBalance*0.25))
> I keep getting this error that the filter comparison fails and I should
> check the data Types returned by the filter expression.
> Please help...|||Thanks Topher that was it. Odd the filter section can't handle comparisons
other then varchar / string like values. I wonder if this carried over into
the newer version.
Thanks again!!!
"Topher" wrote:
> you might want to cast your fields as varchar in your query. This has
> been the answer to many filter comparison problems I have had in the
> past.
>
> Eric wrote:
> > Good Day to All,
> > Need some help with Filters in SQL Reporting 2000.
> > Have a table report setup with a DS going to a Store Proc. need to add a
> > filter that is a bit complex. Don't know which filter section I should be
> > putting this in but in all cases I've run into errors.
> >
> > Basically when the data is brought back from my Store Proc, I need to filter
> > out the data according to the following:
> > IF CBalance < 0
> > THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> > ELSE (TTLBalance > (CBalance*0.25))
> >
> > I keep getting this error that the filter comparison fails and I should
> > check the data Types returned by the filter expression.
> >
> > Please help...
>

filter expressions combined with OR instead of AND

I need to combine filter expressions for my report item with the OR-logic -
but the Reporting Services dialog window is set on the AND-logic. How can I
get past this?
Example
======
What I need is the following:
Fields!Name1 = Parameters!Name
OR
Fields!Name2 = Parameters!Name
What I get from Reporting Services is the following:
Fields!Name1 = Parameters!Name
AND
Fields!Name2 = Parameters!Name
Thank you for your help.I think you can set that in the Expression builder, there is a column called
condition... But if for some reason it doesn't work properly go to the
generic query builder and simply type your expression
Sorry - you are talking about the filter... You could make a function in
the code section, pass both of the field values and parameter values into
the function and return 1 to include the row and 0 to exclude it...Then your
filter expression would be
GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
> I need to combine filter expressions for my report item with the
OR-logic -
> but the Reporting Services dialog window is set on the AND-logic. How can
I
> get past this?
> Example
> ======> What I need is the following:
> Fields!Name1 = Parameters!Name
> OR
> Fields!Name2 = Parameters!Name
> What I get from Reporting Services is the following:
> Fields!Name1 = Parameters!Name
> AND
> Fields!Name2 = Parameters!Name
> Thank you for your help.
>
>|||Dear Wayne
Thank you for your advice. However, I am a controller, not a programmer.
When I am working with RS, I only use the graphic interface - never the code
section... (-;
Is there not a more user-friendly (controller-friendly...) way around this
problem?
Thanks again.
"Wayne Snyder" wrote:
> I think you can set that in the Expression builder, there is a column called
> condition... But if for some reason it doesn't work properly go to the
> generic query builder and simply type your expression
> Sorry - you are talking about the filter... You could make a function in
> the code section, pass both of the field values and parameter values into
> the function and return 1 to include the row and 0 to exclude it...Then your
> filter expression would be
> GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
> news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
> > I need to combine filter expressions for my report item with the
> OR-logic -
> > but the Reporting Services dialog window is set on the AND-logic. How can
> I
> > get past this?
> >
> > Example
> > ======> >
> > What I need is the following:
> >
> > Fields!Name1 = Parameters!Name
> > OR
> > Fields!Name2 = Parameters!Name
> >
> > What I get from Reporting Services is the following:
> >
> > Fields!Name1 = Parameters!Name
> > AND
> > Fields!Name2 = Parameters!Name
> >
> > Thank you for your help.
> >
> >
> >
>
>|||Hi,
I also had this problem and discovered that if you put the common filed
first Reporting Services will give you an OR.
So if youy enter your parameters as follows:
Parameters!Name = Fields!Name1
then
Parameters!Name = Fields!Name2
Reporting Services will generate:
Parameters!Name = Fields!Name1
OR
Parameters!Name = Fields!Name2
Steve
"j schuetz" wrote:
> I need to combine filter expressions for my report item with the OR-logic -
> but the Reporting Services dialog window is set on the AND-logic. How can I
> get past this?
> Example
> ======> What I need is the following:
> Fields!Name1 = Parameters!Name
> OR
> Fields!Name2 = Parameters!Name
> What I get from Reporting Services is the following:
> Fields!Name1 = Parameters!Name
> AND
> Fields!Name2 = Parameters!Name
> Thank you for your help.
>
>|||Too bad, but at least you know the differences between AND and OR :))
In any case, you'd be in a much better shape if you do filtering in
your SQL query, not in the report itself. RS filtering is evil,
because it makes SQL server pull ALL the data from the query, just to
throw away some of it later. It would be much more efficient (and
easier) to apply your filtering in your query (dataset):
SELECT ...
FROM ...
WHERE Name1 = @.paramName OR Name2 = @.paramName
On Mon, 24 Jan 2005 07:13:06 -0800, j schuetz
<jschuetz@.discussions.microsoft.com> wrote:
>Dear Wayne
>Thank you for your advice. However, I am a controller, not a programmer.
>When I am working with RS, I only use the graphic interface - never the code
>section... (-;
>Is there not a more user-friendly (controller-friendly...) way around this
>problem?
>Thanks again.
>
>"Wayne Snyder" wrote:
>> I think you can set that in the Expression builder, there is a column called
>> condition... But if for some reason it doesn't work properly go to the
>> generic query builder and simply type your expression
>> Sorry - you are talking about the filter... You could make a function in
>> the code section, pass both of the field values and parameter values into
>> the function and return 1 to include the row and 0 to exclude it...Then your
>> filter expression would be
>> GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
>> news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
>> > I need to combine filter expressions for my report item with the
>> OR-logic -
>> > but the Reporting Services dialog window is set on the AND-logic. How can
>> I
>> > get past this?
>> >
>> > Example
>> > ======>> >
>> > What I need is the following:
>> >
>> > Fields!Name1 = Parameters!Name
>> > OR
>> > Fields!Name2 = Parameters!Name
>> >
>> > What I get from Reporting Services is the following:
>> >
>> > Fields!Name1 = Parameters!Name
>> > AND
>> > Fields!Name2 = Parameters!Name
>> >
>> > Thank you for your help.
>> >
>> >
>> >
>>|||Thanks to Steve and Usenet User for their helpful piece of advice. Now I am
getting along!

filter expression "LIKE" together with report paramter in RS

Hi there
I have a text field that I would like to filter with a LIKE expression, i.e.:
WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
When I use this filter expression in the data query of Reporting Services
everything goes well.
But when I try to use this filter expression in the the report item (table)
I don't get this filter expression working. How do I write this filter
expression in the filter definition in the properties of my table? How
exactly is the right way to write the filter value (BSK) so that the filter
gets me every text field that has BSK somewhere in its contents?
Thanks a lot for your help!
Judith.If you are using a parameter in the Like expression with a wild card try it
like this:
WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%' + @.BSK + '%')
"j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
news:52BC887E-C77F-4FC7-9DDE-ED5B20826EAF@.microsoft.com...
> Hi there
> I have a text field that I would like to filter with a LIKE expression,
> i.e.:
> WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
> When I use this filter expression in the data query of Reporting Services
> everything goes well.
> But when I try to use this filter expression in the the report item
> (table)
> I don't get this filter expression working. How do I write this filter
> expression in the filter definition in the properties of my table? How
> exactly is the right way to write the filter value (BSK) so that the
> filter
> gets me every text field that has BSK somewhere in its contents?
> Thanks a lot for your help!
> Judith.|||Thank you for your input. I know now how to solve the problem. The thing is
that the syntax of the filter expression in the table properties window is
different from the syntax in the query.
The value field in the filter expression of the table properties has to have
the following syntax: [= "*" & Parameters!Stichwort.Value & "*"], i.e. the
wild card symbol is * instead of %.
With this syntax the LIKE operator in the table properties window works just
fine!
"Steve Dearman" wrote:
> If you are using a parameter in the Like expression with a wild card try it
> like this:
> WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%' + @.BSK + '%')
>
> "j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
> news:52BC887E-C77F-4FC7-9DDE-ED5B20826EAF@.microsoft.com...
> > Hi there
> >
> > I have a text field that I would like to filter with a LIKE expression,
> > i.e.:
> > WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
> >
> > When I use this filter expression in the data query of Reporting Services
> > everything goes well.
> >
> > But when I try to use this filter expression in the the report item
> > (table)
> > I don't get this filter expression working. How do I write this filter
> > expression in the filter definition in the properties of my table? How
> > exactly is the right way to write the filter value (BSK) so that the
> > filter
> > gets me every text field that has BSK somewhere in its contents?
> >
> > Thanks a lot for your help!
> >
> > Judith.
>
>

Filter Error: "..processing of filter expression..cannot be perfor

My results return a tinyint column which is either 0,1,2.
When adding a filter to the matrix it seems ok
blah.value = 2
but when running the report i get the following error:
--error--
An error has occured during report processing.
The processing of filter expression for the matrix 'matrix1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
--enderror--
i have also tried
blah.value = "2"
with no success
anyone know why?a-ha!
this works
Expression
=CInt(Fields!salesGroup.Value)
Operator
=
Value
=2
frankly this is crap
i am returning a number and comparing to a number
- why should i have to convert a number to er a er number?
- why do i have to put an equals in front of the number?
"adolf garlic" wrote:
> My results return a tinyint column which is either 0,1,2.
> When adding a filter to the matrix it seems ok
> blah.value = 2
> but when running the report i get the following error:
> --error--
> An error has occured during report processing.
> The processing of filter expression for the matrix 'matrix1' cannot be
> performed. The comparison failed. Please check the data type returned by the
> filter expression.
> --enderror--
>
> i have also tried
> blah.value = "2"
> with no success
> anyone know why?
>

Filter Error

I have No Filters in Place but I Keep getting this error on a couple reports.

An error has occurred during report processing.

The processing of SortExpression for the table ‘table2’ cannot be performed. The comparison failed. Please check the data type returned by the SortExpression.

What is table2

Have you configured interactive sorting to text field

sql

Filter duplicate records

Hi

Have been given the task of trying to write reports in Crystal after someone found out I new one of Access from the other.

The report is taking data from SAGE database with custom written tables. The problem I have is that one of the custom table has not been well written allowing multiple identical entries.

The table in question holds data for each delivery note posted on SAGE. In this table are field I need relating to Product Number, Quantity and Nett Weight. Due to the way data has been stored I can have up to 16 records (effectively duplicates - same part number, quantity, nett weight) when I only want one record displayed.

Is it possible to write a filter that basically says that if for a given delivery number there is more than 1 record with the same Product Number, Quantity then filter the number of records down to one? If this is not possible, would it be possible to do the same but specifying to pick the record with the highest nett weight? assuming that if there were two identical records for all of the aforementioned fields that it could still bring this down to one?You can do it in your Query:

SELECT ProductNumber, Quantity, 'NettWeight' = MAX(NettWeight)
FROM TableName
GROUP BY ProductNumber, Quantity

(I use SQL Server 7, so you may need to adjust the syntax a bit to work in your database.)

- or -

You can do it in Crystal Reports. There should be a property called SuppressDuplicates (or something similar, I don't have CR in front of me). If you set it to True, Crystal should display only unique records. I think that property is available for fields as well as sections, but I'm not sure. Also, I use CR 8.5, so if you're using a different version, SuppressDuplicates may not exist at all or it may be called something different.

filter detail section of matrix report

Hi,
is there a way to filter details section of the report created using Matrix report type?
I just want to see rows that contain positive numbers.

Thanks,
Igor

Have you tried using the filter tab in the matrix properties dialog box.

I'd be careful though as each matrix cell is an aggregation of 1 or more detail rows. Depending on the nature of the source data and whether any aggregation is actually occuring you'll want to watch out for filtering underlying negative values vs a negative result of an aggregate.

Can you not do this in the source query?

Filter by values from outer list region

Hello,
I am creating a report where I am using a list region to create report pages
with a few tables and graphs on each page. The dataset for the list region
contains categories of the values i want do display in the tables and
charts. The tables and charts have their own datasets, which all should be
filtered based on the category from the list region. Which changes from page
to page.
My problem is that I don't see how the inner tables and charts can be
filtered by the value of the list region?
Many thanks in advance for any help on this.
Best regards,
VemundHi Vemund Haga,
Thanks for your post.
From your descriptions, I understood that you would like to create Report
Pages dynamically. However, I am not very sure about what you want the
pages depend on, is it possible for your to generate some database sample
scripts and send a sample RDL files to me. Would you please share me more
detailed about the whole process? I would love to reproduce it on my side,
which I believe, will make us closer and quicker to the resolution.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Vemund Haga,
I haven't heard back from you yet and I'm just writing in to see if you
have had an opportunity to collect the information. If you could get back
to me at your earliest convenience, we will be able to go ahead. If there
was some part of my post that you didn't understand, please feel free to
post here. I look forward to hearing from you.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

filter by UserID

I have many reports that are filtered by the userID of the person running
the report. Aside from rolling my own security is there a way for me to use
RS to pass this ID. Since the user is logging in via Windows Authentication
I have their login name. I was thinking of using this to query the UserID
from a custom table and then use that as a hidden parameter for all the
sp's. The problem I have though is how to get that UserId prior to anything
on the report happening.
Thanks,
ShawnThe user!userid global variable can be used as a input to your query. Do the
following, create a query parameter. Click on the ..., go to parameters and
then map the query parameter to the global variable (it has domain so you
might want to strip off the domain). To map it choose expressions and that
brings up the expression builder. Next go to layout, report parameters and
remove the parameter that was automatically created for you by RS when you
created the query parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn Mason" <shawn@.issda.com> wrote in message
news:%23YFHG1L$EHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have many reports that are filtered by the userID of the person running
> the report. Aside from rolling my own security is there a way for me to
use
> RS to pass this ID. Since the user is logging in via Windows
Authentication
> I have their login name. I was thinking of using this to query the UserID
> from a custom table and then use that as a hidden parameter for all the
> sp's. The problem I have though is how to get that UserId prior to
anything
> on the report happening.
> Thanks,
> Shawn
>|||Here's something I did, to strip off the domain, as Bruce says:
SELECT e.EMPLID, e.NAME
FROM EMPLTABLE e
WHERE (e.ID = RIGHT(@.UserID, LEN(@.UserID) - CHARINDEX('\', @.UserID)))
It will use whatever you write after the \ in a domain\username scenario.
Kaisa M. Lindahl
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OiMDkHM$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> The user!userid global variable can be used as a input to your query. Do
the
> following, create a query parameter. Click on the ..., go to parameters
and
> then map the query parameter to the global variable (it has domain so you
> might want to strip off the domain). To map it choose expressions and that
> brings up the expression builder. Next go to layout, report parameters and
> remove the parameter that was automatically created for you by RS when you
> created the query parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shawn Mason" <shawn@.issda.com> wrote in message
> news:%23YFHG1L$EHA.3416@.TK2MSFTNGP09.phx.gbl...
> > I have many reports that are filtered by the userID of the person
running
> > the report. Aside from rolling my own security is there a way for me to
> use
> > RS to pass this ID. Since the user is logging in via Windows
> Authentication
> > I have their login name. I was thinking of using this to query the
UserID
> > from a custom table and then use that as a hidden parameter for all the
> > sp's. The problem I have though is how to get that UserId prior to
> anything
> > on the report happening.
> >
> > Thanks,
> >
> > Shawn
> >
> >
>

Tuesday, March 27, 2012

Filter before export

Hi,
I'm in the process of automating the export of a report to a PDF file.
The number of files is enormous and to prevent the query from executing over
and over again I was wondering whether the following scenario is possible.
- Get all records for a given report
- Filter by a specific value and export, repeat process for all necessary
values of that field.
Thanks for any input
Kind regardsYou could definitely do it programmatically from code that calls the RS Web
service...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Thanks for the answer.
Do you have any idea in which Namespace I can find the Filter property?
Thanks
"Wayne Snyder" schreef:
> You could definitely do it programmatically from code that calls the RS Web
> service...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>|||I would use a parameter, passed in via URL. THen use the parameter to do the
grouping...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> Hi,
> I'm in the process of automating the export of a report to a PDF file.
> The number of files is enormous and to prevent the query from executing
over
> and over again I was wondering whether the following scenario is possible.
> - Get all records for a given report
> - Filter by a specific value and export, repeat process for all necessary
> values of that field.
> Thanks for any input
> Kind regards
>|||Hi,
Wouldn't this trigger the query every time?
I'll try to give some more information.
We have about 1500 offices which all need a bunch of pre generated PDF
reports.
I want a report that returns only a couple rows per office to come back as 1
big Dataset that can be filtered clientside and then exported. So every
'filter' operation extracts the information for a specific office and then
exports it to a PDF. I do not want these queries to execute 1500 times
creating all the connection overhead.
eg.
Report returns 10 records per office
Returns 15000 records in the RS Dataset
The C# program (using Reporting Services WebService) filters out the 10
records and exports these to a PDF for every office
Kind regards,
Wesley
"Wayne Snyder" wrote:
> I would use a parameter, passed in via URL. THen use the parameter to do the
> grouping...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "WesleyB" <WesleyB@.discussions.microsoft.com> wrote in message
> news:FB4303BE-E960-4493-B782-B1DFCA2F997A@.microsoft.com...
> > Hi,
> >
> > I'm in the process of automating the export of a report to a PDF file.
> > The number of files is enormous and to prevent the query from executing
> over
> > and over again I was wondering whether the following scenario is possible.
> >
> > - Get all records for a given report
> > - Filter by a specific value and export, repeat process for all necessary
> > values of that field.
> >
> > Thanks for any input
> >
> > Kind regards
> >
> >
>
>

Filter based on a parameter

Hi all,
How would you go about creatig a filter that is used based on the value of a
parameter? Can this be done?
Lets say we have a report parameter called 'Show cases missing <attribute>
data?'
If the user sets this to true, then the filter would kick in, which is a
filter that would check for this.field Is Nothing AND that.Field Is Nothing
OR thisother.field Is Nothing Or thatother.field Is Nothing --and show only
the rows that meet the criteria above.
I can create the filter, but I am not sure how to get it to toggle based on
the selection of the parameter by the user...is this possible?
Thanks,Do you want to filter out the data, or to hide it?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
> Hi all,
> How would you go about creatig a filter that is used based on the value of
> a
> parameter? Can this be done?
> Lets say we have a report parameter called 'Show cases missing <attribute>
> data?'
> If the user sets this to true, then the filter would kick in, which is a
> filter that would check for this.field Is Nothing AND that.Field Is
> Nothing
> OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
> only
> the rows that meet the criteria above.
> I can create the filter, but I am not sure how to get it to toggle based
> on
> the selection of the parameter by the user...is this possible?
> Thanks,|||Filter it out.
And yes, it should probably be done in the SP. But, can you use filters
like that? We would like to mimc all the existing crytal reports which
entails changing as little as possible (in the SP) for now. I am not worried
about the overhead data associated with this.
"Lev Semenets [MSFT]" wrote:
> Do you want to filter out the data, or to hide it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
> > Hi all,
> >
> > How would you go about creatig a filter that is used based on the value of
> > a
> > parameter? Can this be done?
> >
> > Lets say we have a report parameter called 'Show cases missing <attribute>
> > data?'
> >
> > If the user sets this to true, then the filter would kick in, which is a
> > filter that would check for this.field Is Nothing AND that.Field Is
> > Nothing
> > OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
> > only
> > the rows that meet the criteria above.
> >
> > I can create the filter, but I am not sure how to get it to toggle based
> > on
> > the selection of the parameter by the user...is this possible?
> >
> > Thanks,
>
>|||Assuming there is an Attribute parameter which represents the actual field
name you want to filter on, the filter would look similar to this:
Filter expression: =Fields(Parameters!Attribute.Value).Value is Nothing
Filter operator: =Filter value: =True
Note: I'm using the collection syntax to dynamically determine the field
name and access the fields collection based on the selected parameter value.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:24251135-8BE2-4607-B923-6F0C5749AB4A@.microsoft.com...
> Filter it out.
> And yes, it should probably be done in the SP. But, can you use filters
> like that? We would like to mimc all the existing crytal reports which
> entails changing as little as possible (in the SP) for now. I am not
> worried
> about the overhead data associated with this.
> "Lev Semenets [MSFT]" wrote:
>> Do you want to filter out the data, or to hide it?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Myles" <Myles@.discussions.microsoft.com> wrote in message
>> news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
>> > Hi all,
>> >
>> > How would you go about creatig a filter that is used based on the value
>> > of
>> > a
>> > parameter? Can this be done?
>> >
>> > Lets say we have a report parameter called 'Show cases missing
>> > <attribute>
>> > data?'
>> >
>> > If the user sets this to true, then the filter would kick in, which is
>> > a
>> > filter that would check for this.field Is Nothing AND that.Field Is
>> > Nothing
>> > OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
>> > only
>> > the rows that meet the criteria above.
>> >
>> > I can create the filter, but I am not sure how to get it to toggle
>> > based
>> > on
>> > the selection of the parameter by the user...is this possible?
>> >
>> > Thanks,
>>|||Thanks Robert -
no, the parameter is just a simple yes/no question and is does not directly
correlate to any single attribute or field in the report - it does indirectly
take in to consideration the Null values of three fields in the report.
"Robert Bruckner [MSFT]" wrote:
> Assuming there is an Attribute parameter which represents the actual field
> name you want to filter on, the filter would look similar to this:
> Filter expression: =Fields(Parameters!Attribute.Value).Value is Nothing
> Filter operator: => Filter value: =True
> Note: I'm using the collection syntax to dynamically determine the field
> name and access the fields collection based on the selected parameter value.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:24251135-8BE2-4607-B923-6F0C5749AB4A@.microsoft.com...
> > Filter it out.
> >
> > And yes, it should probably be done in the SP. But, can you use filters
> > like that? We would like to mimc all the existing crytal reports which
> > entails changing as little as possible (in the SP) for now. I am not
> > worried
> > about the overhead data associated with this.
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> Do you want to filter out the data, or to hide it?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> >> news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
> >> > Hi all,
> >> >
> >> > How would you go about creatig a filter that is used based on the value
> >> > of
> >> > a
> >> > parameter? Can this be done?
> >> >
> >> > Lets say we have a report parameter called 'Show cases missing
> >> > <attribute>
> >> > data?'
> >> >
> >> > If the user sets this to true, then the filter would kick in, which is
> >> > a
> >> > filter that would check for this.field Is Nothing AND that.Field Is
> >> > Nothing
> >> > OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
> >> > only
> >> > the rows that meet the criteria above.
> >> >
> >> > I can create the filter, but I am not sure how to get it to toggle
> >> > based
> >> > on
> >> > the selection of the parameter by the user...is this possible?
> >> >
> >> > Thanks,
> >>
> >>
> >>
>
>

Filter and Sort Priority

I have a report with a category that filters for "top N" categories, but it is preventing the entire data set from being evaluated so that the series subtotals are incorrect. Is there a way to change the precedence, so that the subtotals are computed across the entire data set, and the "top N" is evaluated afterwards?

I attempted to solve this problem, or work around it, by having all relevant computation performed in the data source (which required dynamic sql with nested selects and window functions). Even with the data perfectly arranged and sorted in advance, the "TopN" feature of reporting services STILL managed to screw up the results. I've concluded that "TopN" is broken.

However, I found a useable work around. I added still another 'select' layer on my datasource with a dense_rank() function, and then used its result in the filter expressions of the relevant 'category.' Problem solved.

|||

I ran into the same problem and I found that this could be resolved though Reporting Services by adding the following to the group's visibility expression:

=IIF(RUNNINGVALUE(Fields!User.Value,COUNTDISTINCT,"table1_Domain")<11,False,True)

This report is counting top users of a web site that stores hits in a database table which is group by domain, then user.

In the report, I created a field: COUNT(Fields!User.Value). The inner group is then is sorted by this field (descending).

So, the logic in this expression is: Every time the user name changes it keeps that in the running total. The running total is reset when the group above it (the user's domain) changes. IIF the running total is less than 11, then Hidden = False.

So, while all values may still be processed, it only shows the top 10.

Hope this helps others

BTW, there is one drawback - since I'm using an expression for the visibility, I can't make this a drill-down field (since drill-down is also a function of visibility). If I select the "Visibility can be toggled by another report item", the report still displays as expected when first rendered, but if it's collapsed then expanded, ALL values for the group will appear, not just the top 10. I guess the visibility expression is only process at initial report rendering time, and not each time the group is collapsed/expanded.

|||

Okay, I'm a but dense today - there is another way to do this. The above will guarantee that ONLY 10 values are returned, but if you remove the sort, and use only the filter for TopN, you'll get the top 10 VALUES (some may duplicate). So, the above solution may return

DOMAIN Logon Count

~~~~~~~~~~~~~~~~~~~~~~~~

Domain1

UserA 10

UserB 10

UserC 9

........etc...upto

UserJ 2

Domain2

etc

However, using only TopN (no sort), may return

Domain1

UserA 10

....

UserJ 2

UserK 2

UserL 2

Domain2

etc

So, TopN by itself (without Sort) can return more than 10 values because there are multiples of the last entry with the same value. My solution in the previous posting depends on Sort, and simply hides everything after the 10th entry.

If someone else finds a way to list just the first 10 entries in a sorted list, please post. I don't like my solution too much because I can't further drill-down into the entries.

sql

Filter and Sort Priority

|||

I ran into the same problem and I found that this could be resolved though Reporting Services by adding the following to the group's visibility expression:

=IIF(RUNNINGVALUE(Fields!User.Value,COUNTDISTINCT,"table1_Domain")<11,False,True)

This report is counting top users of a web site that stores hits in a database table which is group by domain, then user.

In the report, I created a field: COUNT(Fields!User.Value). The inner group is then is sorted by this field (descending).

So, while all values may still be processed, it only shows the top 10.

Hope this helps others

|||

DOMAIN Logon Count

~~~~~~~~~~~~~~~~~~~~~~~~

Domain1

UserA 10

UserB 10

UserC 9

........etc...upto

UserJ 2

Domain2

etc

However, using only TopN (no sort), may return

Domain1

UserA 10

....

UserJ 2

UserK 2

UserL 2

Domain2

etc

If someone else finds a way to list just the first 10 entries in a sorted list, please post. I don't like my solution too much because I can't further drill-down into the entries.

Filter and show TOP N rows

Hi all,
I´m designing a report with a kind of Ranking. I have a list of products and
I have to show the totals by product.
What I need to show in my ranking table is only the Top 10 records so I need
to filter by something like "Top 10". My questions are:
- Which is the correct expresion to write in the group filter?
- Is it possible to add a column to show the row number?
(Additional information)
My result should be something like:
Position > Product > Total
1 > Product1 > 1000
2 > Product2 > 900
3 > Product3 > 850
4 > Product4 > 725
5 > Product5 > 700
6 > Product6 > 680
7 > Product7 > 500
8 > Product8 > 330
9 > Product8 > 210
10 > Product10 > 200
I know how to do it creating a new Dataset using SQL syntax, but I can´t
touch this. The dataset available contains the complete list with all the
values, I mean, my dataset is like:
Product > Value
Product1 > 3
Product1 > 4
Product2 > 1
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
Product6 > 4
Product6 > 1
Product7 > 6
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
Product6 > 4
Product6 > 1
Product7 > 6
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
[more records...]
So I have to group data and sum values to show totals.
Could anybody help me?
Thank you in advanced.
MónicaOn Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Hi all,
> I=B4m designing a report with a kind of Ranking. I have a list of product=s and
> I have to show the totals by product.
> What I need to show in my ranking table is only the Top 10 records so I n=eed
> to filter by something like "Top 10". My questions are:
> - Which is the correct expresion to write in the group filter?
> - Is it possible to add a column to show the row number?
> (Additional information)
> My result should be something like:
> Position > Product > Total
> 1 > Product1 > 1000
> 2 > Product2 > 900
> 3 > Product3 > 850
> 4 > Product4 > 725
> 5 > Product5 > 700
> 6 > Product6 > 680
> 7 > Product7 > 500
> 8 > Product8 > 330
> 9 > Product8 > 210
> 10 > Product10 > 200
> I know how to do it creating a new Dataset using SQL syntax, but I can=B4t
> touch this. The dataset available contains the complete list with all the
> values, I mean, my dataset is like:
> Product > Value
> Product1 > 3
> Product1 > 4
> Product2 > 1
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> [more records...]
> So I have to group data and sum values to show totals.
> Could anybody help me?
> Thank you in advanced.
> M=F3nica
Right-click the table/matrix control and select 'Properties' -> select
the 'Groups' tab -> select the 'Edit...' button -> select the
'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
your sort order you can control which items are top 10. To get the row
number you can use the expression =3DRowNumber(Nothing) in a new
column.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you!
Top N filter works but not the RowNumber function :(.
If I add =RowNumber(Nothing) in a new column it shows the product RowNumber,
counting all the rows grouped.
i.e.
Product > Total > RowNumber
Product1 > 73 > 73
Product2 > 12 > 85
Product3 > 12 > 97
Product4 > 7 > 104
Product5 > 3 > 107
Product6 > 3 > 110
Product7 > 3 > 113
Product8 > 2 > 115
Product9 > 1 > 116
Product10 > 1 > 117
Note that every RowNumber is the sum of the 2 previous totals, so we can say
it shows the row number of the product, counting all the rows grouped.
How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
Another question about this. I´ve tried to show the total in the table
footer but the total shown does not filter my subtotals.
What I really need is to have the following:
Ranking >Product > SubTotal
1 > Product1 > 73
2 > Product2 > 12
3 > Product3 > 12
4 > Product4 > 7
5 > Product5 > 3
6 > Product6 > 3
7 > Product7 > 3
8 > Product8 > 2
9 > Product9 > 1
10 > Product10 > 1
TOTAL TOP 10 > 117
TOTAL OTHER > 550
TOTAL > 667
Could anybody tell me how to do it?
My needs are:
- To be able to SUM only filtered rows
- To be able to show RowNumber as a Ranking position
- To be able to SUM products not included in the filter
(TOTAL is the only field I have with no problem :D)
Thank you
Regards,
Mónica
"EMartinez" <emartinez.pr1@.gmail.com> escribió en el mensaje
news:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
On Aug 29, 11:18 am, "Mónica" <monica.d...@.augure.com> wrote:
> Hi all,
> I´m designing a report with a kind of Ranking. I have a list of products
> and
> I have to show the totals by product.
> What I need to show in my ranking table is only the Top 10 records so I
> need
> to filter by something like "Top 10". My questions are:
> - Which is the correct expresion to write in the group filter?
> - Is it possible to add a column to show the row number?
> (Additional information)
> My result should be something like:
> Position > Product > Total
> 1 > Product1 > 1000
> 2 > Product2 > 900
> 3 > Product3 > 850
> 4 > Product4 > 725
> 5 > Product5 > 700
> 6 > Product6 > 680
> 7 > Product7 > 500
> 8 > Product8 > 330
> 9 > Product8 > 210
> 10 > Product10 > 200
> I know how to do it creating a new Dataset using SQL syntax, but I can´t
> touch this. The dataset available contains the complete list with all the
> values, I mean, my dataset is like:
> Product > Value
> Product1 > 3
> Product1 > 4
> Product2 > 1
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> [more records...]
> So I have to group data and sum values to show totals.
> Could anybody help me?
> Thank you in advanced.
> Mónica
Right-click the table/matrix control and select 'Properties' -> select
the 'Groups' tab -> select the 'Edit...' button -> select the
'Filters' tab -> below 'Expression' select '=Fields!Product.Value' ->
below 'Operator' select 'Top N' -> below 'Value' enter =10. Based on
your sort order you can control which items are top 10. To get the row
number you can use the expression =RowNumber(Nothing) in a new
column.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 30, 9:51 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Thank you!
> Top N filter works but not the RowNumber function :(.
> If I add =3DRowNumber(Nothing) in a new column it shows the product RowNu=mber,
> counting all the rows grouped.
> i.e.
> Product > Total > RowNumber
> Product1 > 73 > 73
> Product2 > 12 > 85
> Product3 > 12 > 97
> Product4 > 7 > 104
> Product5 > 3 > 107
> Product6 > 3 > 110
> Product7 > 3 > 113
> Product8 > 2 > 115
> Product9 > 1 > 116
> Product10 > 1 > 117
> Note that every RowNumber is the sum of the 2 previous totals, so we can =say
> it shows the row number of the product, counting all the rows grouped.
> How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> Another question about this. I=B4ve tried to show the total in the table
> footer but the total shown does not filter my subtotals.
> What I really need is to have the following:
> Ranking >Product > SubTotal
> 1 > Product1 > 73
> 2 > Product2 > 12
> 3 > Product3 > 12
> 4 > Product4 > 7
> 5 > Product5 > 3
> 6 > Product6 > 3
> 7 > Product7 > 3
> 8 > Product8 > 2
> 9 > Product9 > 1
> 10 > Product10 > 1
> TOTAL TOP 10 > 117
> TOTAL OTHER > 550
> TOTAL > 667
> Could anybody tell me how to do it?
> My needs are:
> - To be able to SUM only filtered rows
> - To be able to show RowNumber as a Ranking position
> - To be able to SUM products not included in the filter
> (TOTAL is the only field I have with no problem :D)
> Thank you
> Regards,
> M=F3nica
> "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el mensajenews:118843=0691.619390.58280@.57g2000hsv.googlegroups.com...
> On Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
>
> > Hi all,
> > I=B4m designing a report with a kind of Ranking. I have a list of produ=cts
> > and
> > I have to show the totals by product.
> > What I need to show in my ranking table is only the Top 10 records so I
> > need
> > to filter by something like "Top 10". My questions are:
> > - Which is the correct expresion to write in the group filter?
> > - Is it possible to add a column to show the row number?
> > (Additional information)
> > My result should be something like:
> > Position > Product > Total
> > 1 > Product1 > 1000
> > 2 > Product2 > 900
> > 3 > Product3 > 850
> > 4 > Product4 > 725
> > 5 > Product5 > 700
> > 6 > Product6 > 680
> > 7 > Product7 > 500
> > 8 > Product8 > 330
> > 9 > Product8 > 210
> > 10 > Product10 > 200
> > I know how to do it creating a new Dataset using SQL syntax, but I can==B4t
> > touch this. The dataset available contains the complete list with all t=he
> > values, I mean, my dataset is like:
> > Product > Value
> > Product1 > 3
> > Product1 > 4
> > Product2 > 1
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > [more records...]
> > So I have to group data and sum values to show totals.
> > Could anybody help me?
> > Thank you in advanced.
> > M=F3nica
> Right-click the table/matrix control and select 'Properties' -> select
> the 'Groups' tab -> select the 'Edit...' button -> select the
> 'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
> below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
> your sort order you can control which items are top 10. To get the row
> number you can use the expression =3DRowNumber(Nothing) in a new
> column.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
RowNumber should give you what you need. You just want to set the
scope for it: RowNumber(Scope). To get the correct sums, you will want
to create separate datasets and then reference them via: =3DSum(Fields!
Total.Value, "DataSetName") as the expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Yes, I know... The problem is that I can not create new Datasets or touch
the one I have.
I'll try a workarround... :(
Thank you anyway.
"EMartinez" <emartinez.pr1@.gmail.com> escribió en el mensaje
news:1188526929.514501.144250@.i13g2000prf.googlegroups.com...
On Aug 30, 9:51 am, "Mónica" <monica.d...@.augure.com> wrote:
> Thank you!
> Top N filter works but not the RowNumber function :(.
> If I add =RowNumber(Nothing) in a new column it shows the product
> RowNumber,
> counting all the rows grouped.
> i.e.
> Product > Total > RowNumber
> Product1 > 73 > 73
> Product2 > 12 > 85
> Product3 > 12 > 97
> Product4 > 7 > 104
> Product5 > 3 > 107
> Product6 > 3 > 110
> Product7 > 3 > 113
> Product8 > 2 > 115
> Product9 > 1 > 116
> Product10 > 1 > 117
> Note that every RowNumber is the sum of the 2 previous totals, so we can
> say
> it shows the row number of the product, counting all the rows grouped.
> How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> Another question about this. I´ve tried to show the total in the table
> footer but the total shown does not filter my subtotals.
> What I really need is to have the following:
> Ranking >Product > SubTotal
> 1 > Product1 > 73
> 2 > Product2 > 12
> 3 > Product3 > 12
> 4 > Product4 > 7
> 5 > Product5 > 3
> 6 > Product6 > 3
> 7 > Product7 > 3
> 8 > Product8 > 2
> 9 > Product9 > 1
> 10 > Product10 > 1
> TOTAL TOP 10 > 117
> TOTAL OTHER > 550
> TOTAL > 667
> Could anybody tell me how to do it?
> My needs are:
> - To be able to SUM only filtered rows
> - To be able to show RowNumber as a Ranking position
> - To be able to SUM products not included in the filter
> (TOTAL is the only field I have with no problem :D)
> Thank you
> Regards,
> Mónica
> "EMartinez" <emartinez...@.gmail.com> escribió en el
> mensajenews:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
> On Aug 29, 11:18 am, "Mónica" <monica.d...@.augure.com> wrote:
>
> > Hi all,
> > I´m designing a report with a kind of Ranking. I have a list of products
> > and
> > I have to show the totals by product.
> > What I need to show in my ranking table is only the Top 10 records so I
> > need
> > to filter by something like "Top 10". My questions are:
> > - Which is the correct expresion to write in the group filter?
> > - Is it possible to add a column to show the row number?
> > (Additional information)
> > My result should be something like:
> > Position > Product > Total
> > 1 > Product1 > 1000
> > 2 > Product2 > 900
> > 3 > Product3 > 850
> > 4 > Product4 > 725
> > 5 > Product5 > 700
> > 6 > Product6 > 680
> > 7 > Product7 > 500
> > 8 > Product8 > 330
> > 9 > Product8 > 210
> > 10 > Product10 > 200
> > I know how to do it creating a new Dataset using SQL syntax, but I can´t
> > touch this. The dataset available contains the complete list with all
> > the
> > values, I mean, my dataset is like:
> > Product > Value
> > Product1 > 3
> > Product1 > 4
> > Product2 > 1
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > [more records...]
> > So I have to group data and sum values to show totals.
> > Could anybody help me?
> > Thank you in advanced.
> > Mónica
> Right-click the table/matrix control and select 'Properties' -> select
> the 'Groups' tab -> select the 'Edit...' button -> select the
> 'Filters' tab -> below 'Expression' select '=Fields!Product.Value' ->
> below 'Operator' select 'Top N' -> below 'Value' enter =10. Based on
> your sort order you can control which items are top 10. To get the row
> number you can use the expression =RowNumber(Nothing) in a new
> column.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
RowNumber should give you what you need. You just want to set the
scope for it: RowNumber(Scope). To get the correct sums, you will want
to create separate datasets and then reference them via: =Sum(Fields!
Total.Value, "DataSetName") as the expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 31, 2:20 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Yes, I know... The problem is that I can not create new Datasets or touch
> the one I have.
> I'll try a workarround... :(
> Thank you anyway.
> "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el mensajenews:118852=6929.514501.144250@.i13g2000prf.googlegroups.com...
> On Aug 30, 9:51 am, "M=F3nica" <monica.d...@.augure.com> wrote:
>
> > Thank you!
> > Top N filter works but not the RowNumber function :(.
> > If I add =3DRowNumber(Nothing) in a new column it shows the product
> > RowNumber,
> > counting all the rows grouped.
> > i.e.
> > Product > Total > RowNumber
> > Product1 > 73 > 73
> > Product2 > 12 > 85
> > Product3 > 12 > 97
> > Product4 > 7 > 104
> > Product5 > 3 > 107
> > Product6 > 3 > 110
> > Product7 > 3 > 113
> > Product8 > 2 > 115
> > Product9 > 1 > 116
> > Product10 > 1 > 117
> > Note that every RowNumber is the sum of the 2 previous totals, so we can
> > say
> > it shows the row number of the product, counting all the rows grouped.
> > How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> > product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> > Another question about this. I=B4ve tried to show the total in the table
> > footer but the total shown does not filter my subtotals.
> > What I really need is to have the following:
> > Ranking >Product > SubTotal
> > 1 > Product1 > 73
> > 2 > Product2 > 12
> > 3 > Product3 > 12
> > 4 > Product4 > 7
> > 5 > Product5 > 3
> > 6 > Product6 > 3
> > 7 > Product7 > 3
> > 8 > Product8 > 2
> > 9 > Product9 > 1
> > 10 > Product10 > 1
> > TOTAL TOP 10 > 117
> > TOTAL OTHER > 550
> > TOTAL > 667
> > Could anybody tell me how to do it?
> > My needs are:
> > - To be able to SUM only filtered rows
> > - To be able to show RowNumber as a Ranking position
> > - To be able to SUM products not included in the filter
> > (TOTAL is the only field I have with no problem :D)
> > Thank you
> > Regards,
> > M=F3nica
> > "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el
> > mensajenews:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
> > On Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> > > Hi all,
> > > I=B4m designing a report with a kind of Ranking. I have a list of pro=ducts
> > > and
> > > I have to show the totals by product.
> > > What I need to show in my ranking table is only the Top 10 records so= I
> > > need
> > > to filter by something like "Top 10". My questions are:
> > > - Which is the correct expresion to write in the group filter?
> > > - Is it possible to add a column to show the row number?
> > > (Additional information)
> > > My result should be something like:
> > > Position > Product > Total
> > > 1 > Product1 > 1000
> > > 2 > Product2 > 900
> > > 3 > Product3 > 850
> > > 4 > Product4 > 725
> > > 5 > Product5 > 700
> > > 6 > Product6 > 680
> > > 7 > Product7 > 500
> > > 8 > Product8 > 330
> > > 9 > Product8 > 210
> > > 10 > Product10 > 200
> > > I know how to do it creating a new Dataset using SQL syntax, but I ca=n=B4t
> > > touch this. The dataset available contains the complete list with all
> > > the
> > > values, I mean, my dataset is like:
> > > Product > Value
> > > Product1 > 3
> > > Product1 > 4
> > > Product2 > 1
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > Product6 > 4
> > > Product6 > 1
> > > Product7 > 6
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > Product6 > 4
> > > Product6 > 1
> > > Product7 > 6
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > [more records...]
> > > So I have to group data and sum values to show totals.
> > > Could anybody help me?
> > > Thank you in advanced.
> > > M=F3nica
> > Right-click the table/matrix control and select 'Properties' -> select
> > the 'Groups' tab -> select the 'Edit...' button -> select the
> > 'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
> > below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
> > your sort order you can control which items are top 10. To get the row
> > number you can use the expression =3DRowNumber(Nothing) in a new
> > column.
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> RowNumber should give you what you need. You just want to set the
> scope for it: RowNumber(Scope). To get the correct sums, you will want
> to create separate datasets and then reference them via: =3DSum(Fields!
> Total.Value, "DataSetName") as the expression. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Another alternative for the sums might be to group the dataset based
on the top N and bottom N and then try to use the InScope function in
a sum expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Filter and Aggregat function

Hi all,
I am creating a report in BI Dev Studio and use the grouping
functionality to show the number of leads for different sales
representatives. I also use "drill down" to show the leads in detail.
The report also uses a filter to filter out certain time period.
On the sales persons level I use "Rowcount" to show the number of
leads. this works fine as long as I do not use any filter. If I use the
filter, the "Rowcount" function still shows the number of leads for the
whole table, although the sub-group shows the right number of entries.
Where is my mistake?You could place your filter in the WHERE statement under the data tab.
<leebm@.sms.at> wrote in message
news:1156789789.405721.156960@.h48g2000cwc.googlegroups.com...
> Hi all,
> I am creating a report in BI Dev Studio and use the grouping
> functionality to show the number of leads for different sales
> representatives. I also use "drill down" to show the leads in detail.
> The report also uses a filter to filter out certain time period.
> On the sales persons level I use "Rowcount" to show the number of
> leads. this works fine as long as I do not use any filter. If I use the
> filter, the "Rowcount" function still shows the number of leads for the
> whole table, although the sub-group shows the right number of entries.
> Where is my mistake?
>|||Ben Watts schrieb:
> You could place your filter in the WHERE statement under the data tab.
> <leebm@.sms.at> wrote in message
> news:1156789789.405721.156960@.h48g2000cwc.googlegroups.com...
> > Hi all,
> > I am creating a report in BI Dev Studio and use the grouping
> > functionality to show the number of leads for different sales
> > representatives. I also use "drill down" to show the leads in detail.
> > The report also uses a filter to filter out certain time period.
> > On the sales persons level I use "Rowcount" to show the number of
> > leads. this works fine as long as I do not use any filter. If I use the
> > filter, the "Rowcount" function still shows the number of leads for the
> > whole table, although the sub-group shows the right number of entries.
> >
> > Where is my mistake?
> >
Thanks for the answer, but how do I place the filter in the sql
statment exactly? I tried something like: select * from
Adressenherkunft where insertdate = 'Parameters!von.Value' but this
does not work?|||Hi,
The syntax is like
insertdate = @.von
where "von" is the name of the parameter exactly as it shows in the report
parameters dialogbox.
HTH,
Jordi Rambla
MVP SQL Server (Reporting Services)
Solid Quality Learning (http://www.solidqualitylearning.com)
"Markus" <leebm@.sms.at> escribió en el mensaje
news:1156850392.248071.158990@.75g2000cwc.googlegroups.com...
> Ben Watts schrieb:
>> You could place your filter in the WHERE statement under the data tab.
>> <leebm@.sms.at> wrote in message
>> news:1156789789.405721.156960@.h48g2000cwc.googlegroups.com...
>> > Hi all,
>> > I am creating a report in BI Dev Studio and use the grouping
>> > functionality to show the number of leads for different sales
>> > representatives. I also use "drill down" to show the leads in detail.
>> > The report also uses a filter to filter out certain time period.
>> > On the sales persons level I use "Rowcount" to show the number of
>> > leads. this works fine as long as I do not use any filter. If I use the
>> > filter, the "Rowcount" function still shows the number of leads for the
>> > whole table, although the sub-group shows the right number of entries.
>> >
>> > Where is my mistake?
>> >
> Thanks for the answer, but how do I place the filter in the sql
> statment exactly? I tried something like: select * from
> Adressenherkunft where insertdate = 'Parameters!von.Value' but this
> does not work?
>

Filter a Report's Table using multiple Like conditions using Or

I would like to filter a report using multiple Like conditions

or - Change a filter in Reporting Services to OR rather than AND..

Example: (SameFieldName Like *1100) or (SameFieldName Like *1200).

When I try doing this on the Report's table properties - Filters tab - the And/Or automatically changes to "And" with using the "Like" Operator.

The only time "Or" appears is when I use the " = " Operator.

Or can someone show me how to use an expression to filter on multiple Like conditions.

Thanks!

Try the following filter:

Filter expression:
=(Fields!FName.Value like "*1100" OR Fields!FName.Value like "*1200")

Filter operator:
=

Filter value:
=True

-- Robert

|||You got it! It works!

Thank you

BBK

Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.

|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Thursday, March 29, 2012

Filter in stored procedure

filter in Reporting Services: Best way?

Filter Help! - Datatypes

Filter Help Needed

filter expressions combined with OR instead of AND

filter expression "LIKE" together with report paramter in RS

Filter Error: "..processing of filter expression..cannot be perfor

Filter Error

Filter duplicate records

filter detail section of matrix report

Filter by values from outer list region

filter by UserID

Tuesday, March 27, 2012

Filter before export

Filter based on a parameter

Filter and Sort Priority

Filter and Sort Priority

Filter and show TOP N rows

Filter and Aggregat function

Filter a Report's Table using multiple Like conditions using Or

Filter a Model Table?

Format Alignment

Blog Archive

About Me