Report Designer Dynamic Filter

I have a requirement to add the ability to filter a report by partial last name, at the report level (i.e. not at the database server). In case someone else needs to work out how to do this, here is what I did.

Add a Parameter to the report. In my case it is named LastName, is non-queried, allows Null or Blank, and has a default of null.

Add a filter to the dataset that contains the field to be searched. Set its Expression to:
=IIF(Parameters!LastName.Value = System.DBNull.Value OR Parameters!LastName.Value = "",
Fields!LastName.Value, Nothing) ,
its operator to "="
and its value to =IIF(Parameters!LastName.Value = Nothing OR Parameters!LastName.Value = "", Nothing, Parameters!LastName.Value & "*")

voila!

Helen

I'm not sure I fully understand. What exactly does this do?

Would this have the same effect as using a query with a parameter for lastname (@.lastname) where lastname LIKE @.lastname + "%"?

|||

It allows me to make entering a value for the parameter optional. If the user does not want to filter by last name, my filter expression still has to work. I got it to work by using an expression for both the filter and its value, and returning Nothing for both when the value is null or empty.

When using an expression for the filter, the LIKE option for the comparator is disabled, so I had to use = as the operator.

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

Dear Anyone,

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

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

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

Thanks,
Joseph

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

Report Builder: Filter drop down list unspecified

I have a report model which functioned correctly until I copied the project
over to another server and changed the data source for it. I can still build
a report that returns the correct data, but when I add a filter to the report
data, the ddl for the filter never loads the data and says 'unspecified'.
I'm not sure what the problem could be. Any ideas? ThanksAfter doing some hunting around I found this:
One of the most commonly used features in ad-hoc reporting is filtering. To
add filters to a report, select a field, and then click the Filter button. If
the InstanceSelection attribute enables drop-down, filter values will be
automatically populated. In article
http://msdn2.microsoft.com/en-us/library/aa964121.aspx#adhocrptsol_topic5
This is not the issue asthe InstanceSelection property is properly configured.
"Jason" wrote:
> I have a report model which functioned correctly until I copied the project
> over to another server and changed the data source for it. I can still build
> a report that returns the correct data, but when I add a filter to the report
> data, the ddl for the filter never loads the data and says 'unspecified'.
> I'm not sure what the problem could be. Any ideas? Thanks

Report Builder: Date filter, Relative Date

I have a date filter, and I default it to first day this month and last day this month under relative date, when I run it it givis me error:

The Value expression for the report parameter ‘FromDate’ contains an error: [BC30456] 'Date' is not a member of 'Integer'. (rsCompilerErrorInExpression)

Can anyone fix this problem?

But it works for Today or (n)months ago.

Thanks.

Post your expressions and indicate what the data types are for the referenced field(s).|||

OK, I narrow down what cause this, my date Fields are standard date type. I have 2 dates From Date and To Date, and I put those in filter with default values:

From Date : first day this month, To Date: last day this month

I also have a Textbox with value: =parameters!FromDate.Value

Because I need to display what is being filter on the output, but that is what cause the error, if I use Today or (n) months ago as default, it works.

|||

So are you saying this gives the error?

=parameters!FromDate.Value

I doubt that is causing the problem unless you maybe have a number format string for the textbox?

|||

If I remove the Textbox with value: =parameters!FromDate.Value, then it works fine with the From Date : first day this month, To Date: last day this month. But if I add the Textbox back with those value it gives me the above error?

|||And then if you put a new text box on the report and set the Expression to =Parameters!FromDate.Value and leave all other settings for the text box alone, what happens?

Report Builder Security Filter Examples

Does anyone have any examples on how to use the security filters in
report builder models. Do you setup a standard filter. How do you pass
the user back to the server in the query?
ThanksI have the same type of question...I need to be able to pass parameters
(userid) back to my model due to security setup in our system...we have
several 'user-access' tables for varying levels of security. For
example, any given user has access to specific accounts, but only
certain members within that account and only specific coverages within
those members. I don't see how I can set this up using roles but
perhaps I'm just missing it.|||the getuserid() function can be used... but... reportbuilder keep in cache
the first user which access a filter object where the getuserid() function
is used!!!
so if an "administrator" try the model, then every other users use the same
access!!!
but using the getuserid() into reportbuilder himself (the user create a
report and explicitly setup a filter) then the ID used is the right one, but
its not a good solution.
I'll do some other tests and keep you informed.
"tim" <tbush@.ccmsi.com> wrote in message
news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
>I have the same type of question...I need to be able to pass parameters
> (userid) back to my model due to security setup in our system...we have
> several 'user-access' tables for varying levels of security. For
> example, any given user has access to specific accounts, but only
> certain members within that account and only specific coverages within
> those members. I don't see how I can set this up using roles but
> perhaps I'm just missing it.
>|||I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
shortly explaining how to use security filters.
FYI, the incorrect behavior of the GETUSERID() function is a known issue
that is fixed in SP1, which will be available shortly.
"Jéjé" wrote:
> the getuserid() function can be used... but... reportbuilder keep in cache
> the first user which access a filter object where the getuserid() function
> is used!!!
> so if an "administrator" try the model, then every other users use the same
> access!!!
> but using the getuserid() into reportbuilder himself (the user create a
> report and explicitly setup a filter) then the ID used is the right one, but
> its not a good solution.
> I'll do some other tests and keep you informed.
>
> "tim" <tbush@.ccmsi.com> wrote in message
> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
> >I have the same type of question...I need to be able to pass parameters
> > (userid) back to my model due to security setup in our system...we have
> > several 'user-access' tables for varying levels of security. For
> > example, any given user has access to specific accounts, but only
> > certain members within that account and only specific coverages within
> > those members. I don't see how I can set this up using roles but
> > perhaps I'm just missing it.
> >
>
>|||thanks!
your blog appear to be great and usefull.
favorites favorites... ;-)
"Bob Meyers - MSFT" <BobMeyersMSFT@.discussions.microsoft.com> wrote in
message news:D083E8EC-F89C-4E6A-AE99-0BEFDE4F877B@.microsoft.com...
>I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
> shortly explaining how to use security filters.
> FYI, the incorrect behavior of the GETUSERID() function is a known issue
> that is fixed in SP1, which will be available shortly.
> "Jéjé" wrote:
>> the getuserid() function can be used... but... reportbuilder keep in
>> cache
>> the first user which access a filter object where the getuserid()
>> function
>> is used!!!
>> so if an "administrator" try the model, then every other users use the
>> same
>> access!!!
>> but using the getuserid() into reportbuilder himself (the user create a
>> report and explicitly setup a filter) then the ID used is the right one,
>> but
>> its not a good solution.
>> I'll do some other tests and keep you informed.
>>
>> "tim" <tbush@.ccmsi.com> wrote in message
>> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
>> >I have the same type of question...I need to be able to pass parameters
>> > (userid) back to my model due to security setup in our system...we have
>> > several 'user-access' tables for varying levels of security. For
>> > example, any given user has access to specific accounts, but only
>> > certain members within that account and only specific coverages within
>> > those members. I don't see how I can set this up using roles but
>> > perhaps I'm just missing it.
>> >
>>|||Thanks Bob...I look forward to it...I am stumped.
J=E9j=E9 wrote:
> thanks!
> your blog appear to be great and usefull.
> favorites favorites... ;-)
> "Bob Meyers - MSFT" <BobMeyersMSFT@.discussions.microsoft.com> wrote in
> message news:D083E8EC-F89C-4E6A-AE99-0BEFDE4F877B@.microsoft.com...
> >I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
> > shortly explaining how to use security filters.
> >
> > FYI, the incorrect behavior of the GETUSERID() function is a known issue
> > that is fixed in SP1, which will be available shortly.
> >
> > "J=E9j=E9" wrote:
> >
> >> the getuserid() function can be used... but... reportbuilder keep in
> >> cache
> >> the first user which access a filter object where the getuserid()
> >> function
> >> is used!!!
> >>
> >> so if an "administrator" try the model, then every other users use the
> >> same
> >> access!!!
> >> but using the getuserid() into reportbuilder himself (the user create a
> >> report and explicitly setup a filter) then the ID used is the right on=e,
> >> but
> >> its not a good solution.
> >>
> >> I'll do some other tests and keep you informed.
> >>
> >>
> >> "tim" <tbush@.ccmsi.com> wrote in message
> >> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
> >> >I have the same type of question...I need to be able to pass paramete=rs
> >> > (userid) back to my model due to security setup in our system...we h=ave
> >> > several 'user-access' tables for varying levels of security. For
> >> > example, any given user has access to specific accounts, but only
> >> > certain members within that account and only specific coverages with=in
> >> > those members. I don't see how I can set this up using roles but
> >> > perhaps I'm just missing it.
> >> >
> >>
> >>
> >>|||how can I get SQL userid into the model rather than the Windows ID?|||to use the SQL Server user id , use SQL servers syntaxes (suser_sname or
user_name and other user_id functions)
"tim" <bush.timothy@.gmail.com> wrote in message
news:1144296426.518067.71590@.i40g2000cwc.googlegroups.com...
> how can I get SQL userid into the model rather than the Windows ID?
>|||thanks...was finally able to use SUSER_SNAME(SUSER_SID()) to get what I
needed. Now I'm trying to get the filters to work with it.|||Note that with the SP1 version of BOL (which will be available for download
when SP1 is released), there is a tutorial that walks you through how to set
up security filters using the GetUserID().
"tim" wrote:
> Thanks Bob...I look forward to it...I am stumped.
> Jéjé wrote:
> > thanks!
> >
> > your blog appear to be great and usefull.
> > favorites favorites... ;-)
> >
> > "Bob Meyers - MSFT" <BobMeyersMSFT@.discussions.microsoft.com> wrote in
> > message news:D083E8EC-F89C-4E6A-AE99-0BEFDE4F877B@.microsoft.com...
> > >I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
> > > shortly explaining how to use security filters.
> > >
> > > FYI, the incorrect behavior of the GETUSERID() function is a known issue
> > > that is fixed in SP1, which will be available shortly.
> > >
> > > "Jéjé" wrote:
> > >
> > >> the getuserid() function can be used... but... reportbuilder keep in
> > >> cache
> > >> the first user which access a filter object where the getuserid()
> > >> function
> > >> is used!!!
> > >>
> > >> so if an "administrator" try the model, then every other users use the
> > >> same
> > >> access!!!
> > >> but using the getuserid() into reportbuilder himself (the user create a
> > >> report and explicitly setup a filter) then the ID used is the right one,
> > >> but
> > >> its not a good solution.
> > >>
> > >> I'll do some other tests and keep you informed.
> > >>
> > >>
> > >> "tim" <tbush@.ccmsi.com> wrote in message
> > >> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
> > >> >I have the same type of question...I need to be able to pass parameters
> > >> > (userid) back to my model due to security setup in our system...we have
> > >> > several 'user-access' tables for varying levels of security. For
> > >> > example, any given user has access to specific accounts, but only
> > >> > certain members within that account and only specific coverages within
> > >> > those members. I don't see how I can set this up using roles but
> > >> > perhaps I'm just missing it.
> > >> >
> > >>
> > >>
> > >>
>

Report Builder Reports Filtering or Grouping at runtime possible?

I have a problem and i wonder whether it is possible select filter and sort
options at execution time not design time for report builder reportsGo to
http://www.msbicentral.com/Downloads/ReportingServices/RDLFiles/tabid/34/Default.aspx.
You'll have to register for a free login. There is an example of sorting at
run time, there may be one for filtering also.
"Yuksel" wrote:
> I have a problem and i wonder whether it is possible select filter and sort
> options at execution time not design time for report builder reports|||but i am not be able to download sample rdl files
"daw" wrote:
> Go to
> http://www.msbicentral.com/Downloads/ReportingServices/RDLFiles/tabid/34/Default.aspx.
> You'll have to register for a free login. There is an example of sorting at
> run time, there may be one for filtering also.
>
> "Yuksel" wrote:
> > I have a problem and i wonder whether it is possible select filter and sort
> > options at execution time not design time for report builder reports

Report Builder MDX on filter

Please interpret this MDX statement generated by Report Builder for me. I am not sure why it is returning all years in cube when I just want 2005:

SELECT

{ ( Extract( filter( { [Accounting Date].[MDY Date].Levels(1).Members * { [Accounting Date].[Date].[Calendar] } }, ( ( [Accounting Date].[MDY Date].CurrentMember.membervalue >= "09/01/2005" ) AND ( [Accounting Date].[MDY Date].CurrentMember.membervalue <= "09/30/2005" ) ) ), [Accounting Date].[MDY Date] ) , * ) } on 0

FROM

[Cube]

It is filtering the data on the month and day portion of the date range just fine, just not filtering on the year. Same report in Reporting Services generates different MDX and returns 2005 only, so this rules out cube problem.

Could you please add CDate() around the begin and end dates, so as comparisons are made using the proper time ordering?

For instance:

... >= CDate("09/01/2005") ...

Report Builder mdx filter returns too many rows

I captured this mdx statement issued by Report Builder in SQL Profiler to
figure out why it was returning more than one year:
(SELECT
{ ( Extract( filter( { [Accounting Date].[MDY Date].Levels(1).Members * {
[Accounting Date].[Date].[Calendar] } },
( ( [Accounting Date].[MDY Date].CurrentMember.membervalue >= "09/01/2005" )
AND ( [Accounting Date].[MDY Date].CurrentMember.membervalue <= "09/30/2005" ) ) ),
[Accounting Date].[MDY Date] ) , * ) } on 0
FROM
[cube]
I just want September 2005 data, but it is returning September data for all
years in the cube.same filter in select statement rendered in Reporting Services Wizard:
FROM ( SELECT ( STRTOMEMBER(@.FromAccountingDateMDYDate, CONSTRAINED) :
STRTOMEMBER(@.ToAccountingDateMDYDate, CONSTRAINED) ) ON COLUMNS FROM [cube])
This works, just can't save report due to known bug using wizard against
Analysis Services 2005.
"bhorwatt" wrote:
> I captured this mdx statement issued by Report Builder in SQL Profiler to
> figure out why it was returning more than one year:
> (SELECT
> { ( Extract( filter( { [Accounting Date].[MDY Date].Levels(1).Members * {
> [Accounting Date].[Date].[Calendar] } },
> ( ( [Accounting Date].[MDY Date].CurrentMember.membervalue >= "09/01/2005" )
> AND ( [Accounting Date].[MDY Date].CurrentMember.membervalue <=> "09/30/2005" ) ) ),
> [Accounting Date].[MDY Date] ) , * ) } on 0
> FROM
> [cube]
> I just want September 2005 data, but it is returning September data for all
> years in the cube.
>

Report Builder Filter Prompt...

Is there a way to pass a NULL to a filter prompt in report builder much like in report designer where it allows you to check or uncheck the NULL checkbox?

Thanks.

Report Builder only enables the NULL checkbox for a report parameter if the corresponding field can have NULL values (according to the Attribute.Nullable property in the report model). Are you seeing different behavior?|||This answered my questions. Thanks.

Report Builder Filter Newbie Question

Sorry for the newbie question.

I've just started building a report to display my IIS logs. I want to filter out all internal IP's, how would I do this?

Thanx

I've figured out NOT but how do I use a range of IP's?

Could I use a wild card

<>"192.168.1.*"

Or should I give it a range'

<>"192.168.1.1-192.168.1.254"

Thanx

|||

Try this

In Filter list

Use an expression instead of selecting the field/column itself like

if u have IPField

left(IPField,10) (this gives value - 192.168.1. (10 chars))

In Operator use != (Not equals)

In Value use an expression = "192.168.1."

Hope this helps u

Report Builder filter dropdown list issues

How can I create a report in Report Builder with a date-type parameter that displays a dropdown list of month names displayed in the correct chronological order (i.e. January, February, etc.)? More generally, how does one sort filter values in the dropdown list?

On a related note, how do I get Report Builder to display (in the report) which values were selected from the dropdown list? Currently I get:

Filter: Transactions with: All of (Month = (prompted))

when what I really need is the month name(s) selected from the dropdown list.

Thanks!

Paul.

Paul Rayner wrote:

On a related note, how do I get Report Builder to display (in the report) which values were selected from the dropdown list? Currently I get:

Filter: Transactions with: All of (Month = (prompted))

when what I really need is the month name(s) selected from the dropdown list.
Thanks!
Paul.

Names will appear in dropdown if ValueSelection property of attribute Transaction is set to "Dropdown".

|||

Thanks for the reply. Perhaps I was insufficiently clear. I know how to get a dropdown list. What I don't know how to do is how to sort the date values (when displayed as month names) in a dropdown list. Month names are being displayed as April, August, etc. instead of January, February, etc.

Paul.

Report Builder Filter drop down list - when the list is more than 500 values

Hi All,

I build a model (either from Cube or from tables) and open it in report builder. When I select a FIELTER on field which has few distinct values like state code, I get a drop down list. But when I select a filter on 'Dept Name', the drop down list on filter disapears. I have to manually key the filter value.

Can any one help me in getting a drop down list on filter, when distinct values in that column are more than few hundred.

Regards

Bobba

This is controlled by a property InstanceSelection, that can be set on each attribute of a dimension (within the dimension Editor).

The cube/dimension wizard initially sets this property, but then it can be changed as required.

Report Builder Error - Novice please suggest !!

I created report model based of three relational tables(SQL Db). When I try to run it gives me error 'cannot run this report - A filter must be specified'. When I supply filter it will go head and run.

Basically I have three relational tables from SQL database. one is the Master say A and other two are related tables say B and C. I joined with id1 to B from A and id2 to C from A.

A(id1) -> B(id1)

A(id2) -> C(id2)

1.Why is it asking me the enter filter?

2.Also, when I created a report based on fields from A,B and C I tried to do the click through.

I can able to click through to table B by clicking on the fields of B but similarly I am unable to do for the fields from the C.

3.Can I create a report model based on tables from different databases rather than from single database.?

I am novice to the Reporting Services arena. Your suggestions/help is greatly appreciated.

Thanks in advance.

Prakash

1. Report Builder requires you to specify a filter if Entity.InstanceSelection=MandatoryFilter in the report model. The model generation wizard will set this value if the underlying table has a very large number of rows in it. If you don't like the setting, you can change it in Model Designer.

2. Only certain fields have EnableDrillthrough=True by default. I believe the model generation wizard sets this only for highly unique fields and/or fields that are part of the primary key in the underyling table. If you want Report Builder to create Clickthrough links for more fields, you can change this property in Model Designer.

3. The DSV that describes the database over which the report model is built can only have one datasource. However, you can use linked servers to access data on other servers.

Hope that helps!

|||

Thanks for your response.

Please tell me where I can find these properties. I am unable to find them(I guess I am dumb). Your help is greatly appreciated.

Thanks !!

Report Builder Error - Novice please suggest !!

I created report model based of three relational tables(SQL Db). When I try to run it gives me error 'cannot run this report - A filter must be specified'. When I supply filter it will go head and run.

Basically I have three relational tables from SQL database. one is the Master say A and other two are related tables say B and C. I joined with id1 to B from A and id2 to C from A.

A(id1) -> B(id1)

A(id2) -> C(id2)

1.Why is it asking me the enter filter?

2.Also, when I created a report based on fields from A,B and C I tried to do the click through.

I can able to click through to table B by clicking on the fields of B but similarly I am unable to do for the fields from the C.

3.Can I create a report model based on tables from different databases rather than from single database.?

I am novice to the Reporting Services arena. Your suggestions/help is greatly appreciated.

Thanks in advance.

Prakash

1. Report Builder requires you to specify a filter if Entity.InstanceSelection=MandatoryFilter in the report model. The model generation wizard will set this value if the underlying table has a very large number of rows in it. If you don't like the setting, you can change it in Model Designer.

2. Only certain fields have EnableDrillthrough=True by default. I believe the model generation wizard sets this only for highly unique fields and/or fields that are part of the primary key in the underyling table. If you want Report Builder to create Clickthrough links for more fields, you can change this property in Model Designer.

3. The DSV that describes the database over which the report model is built can only have one datasource. However, you can use linked servers to access data on other servers.

Hope that helps!

|||

Thanks for your response.

Please tell me where I can find these properties. I am unable to find them(I guess I am dumb). Your help is greatly appreciated.

Thanks !!

Report Builder Dialog (Filter)

Anyone know what conditions cause the dialog box that says "A filter must be specified." to come up? I am joining two underlying views and cannot figure out why sometimes this comes up and other times it does not.

I also have this problem; it appears when the result of running the report is large. If this error message is triggered, you can still get the report to run by putting on a filter which does not actually filter out anything (!?). I experimented with a moderately large table of 9951 rows. If I select a filter that doesn't filter out any rows, I will still be able to produce a 199 page report containing 9951 rows.

But I still do not know what is the maximum amount of data that causes this error message to be triggered. Also, this error message is a little vague.|||Has anyone got a clue for the reason of above behaviour? Its pretty annoying to see the filter pop-up coming up when you don't want to filter any data. One workaround is to create a dummy filter which won't actually filter any rows from the output.|||

well !

Got it! The Instance Selection property of an entity in the model determines whether filter must be specified.

Report Builder Dialog (Filter)

Anyone know what conditions cause the dialog box that says "A filter must be specified." to come up? I am joining two underlying views and cannot figure out why sometimes this comes up and other times it does not.

I also have this problem; it appears when the result of running the report is large. If this error message is triggered, you can still get the report to run by putting on a filter which does not actually filter out anything (!?). I experimented with a moderately large table of 9951 rows. If I select a filter that doesn't filter out any rows, I will still be able to produce a 199 page report containing 9951 rows.

But I still do not know what is the maximum amount of data that causes this error message to be triggered. Also, this error message is a little vague.
|||Has anyone got a clue for the reason of above behaviour? Its pretty annoying to see the filter pop-up coming up when you don't want to filter any data. One workaround is to create a dummy filter which won't actually filter any rows from the output.|||

well !

Got it! The Instance Selection property of an entity in the model determines whether filter must be specified.

Report Builder Dialog (Filter)

Anyone know what conditions cause the dialog box that says "A filter must be specified." to come up? I am joining two underlying views and cannot figure out why sometimes this comes up and other times it does not.

I also have this problem; it appears when the result of running the report is large. If this error message is triggered, you can still get the report to run by putting on a filter which does not actually filter out anything (!?). I experimented with a moderately large table of 9951 rows. If I select a filter that doesn't filter out any rows, I will still be able to produce a 199 page report containing 9951 rows.

But I still do not know what is the maximum amount of data that causes this error message to be triggered. Also, this error message is a little vague.|||Has anyone got a clue for the reason of above behaviour? Its pretty annoying to see the filter pop-up coming up when you don't want to filter any data. One workaround is to create a dummy filter which won't actually filter any rows from the output.|||

well !

Got it! The Instance Selection property of an entity in the model determines whether filter must be specified.

Report Builder Date Range Filter

When selecting From To date range filter you can no longer choose
prompt for input. Why is this? Is there a fix for it.
I can replicate the required functionality by using On or Before and On
or After but it seems a bit messy.
Regards
TobiThis is a known issue and is under consideration to be fixed in a future
release.
"Tobi" wrote:
> When selecting From To date range filter you can no longer choose
> prompt for input. Why is this? Is there a fix for it.
> I can replicate the required functionality by using On or Before and On
> or After but it seems a bit messy.
> Regards
> Tobi
>

Report Builder - Filter on an aggreated measure possible ?

Is it possible to use filter on an aggregated measure without bringing the server to its knees ?

I've got a 2005 UDM with a single fact table:

-accidentID

-accidentTypeID

-statusID

-countryID

-areaID

-operationID

-consequenceID

-lightConditionsID

...(18 more FKs to simple dimensions)

-Exits (always = 1 to indicate existens)

In addition, I've added a calulation to the cube

-NumberOfAccidents = DistinctCount of all records with -accidentTypeID = A

On top of this, I've generated a Report Model.

Building reports using Report Builder is a breeze, but if I set a filter on the NumberOfAccidents (the aggregated measure); Analysis Services goes off and never returns.

For instance, a matrix report with Country on one axis and Operation on the other and NumberOfAccidents as the only member will run as expected and quite fast.

However, if I set a filter on NumberOfAccidents to > 10, I'm lost. This query will basically run forever and completely halt Analysis Services.

The fact table contains approximately 270000 records.

Any insight ?

Trond

Sorry for the delay in replying.

Firstly, I suspect that the filter you are actually applying is not the one you expect. It will be filtering all of the rows in the fact table according to the criteria, before grouping by Country & Operation. Given that I don’t think NumberOfAccidents can be >1 for a fact row, I imagine that you intended to filter the results after grouping (which I don’t believe is possible in Report Builder, in a matrix).

Without knowing the details of the calculation, I can't say for sure why performance is so poor. I believe that it would be because the calculation is being evaluated for all combinations of the dimensions related to the measure group. There are two ways to make this much more efficient:

1. Make sure that NON_EMPTY_BEHAVIOR is defined for the calculation. This is a property of a calculated measure (say MC), and states which other measure (say M) where if M is Null, then MC is certainly Null. So if there is some simple Count measure on this measure group, then setting NON_EMPTY_BEHAVIOR to that Count measure, on NumberOfAccidents, should greatly improve

or

2.If this measure is simply a count of records where Accident type = A, then I would suggest simply having a new (non-calculated) measure based on that condition. So in the DSV, add a calculated column that is 1 if Type = A and 0 otherwise. Then have a regular measure built off that column

Report Builder - Filter on an aggreated measure possible ?

Is it possible to use filter on an aggregated measure without bringing the server to its knees ?

I've got a 2005 UDM with a single fact table:

-accidentID

-accidentTypeID

-statusID

-countryID

-areaID

-operationID

-consequenceID

-lightConditionsID

...(18 more FKs to simple dimensions)

-Exits (always = 1 to indicate existens)

In addition, I've added a calulation to the cube

-NumberOfAccidents = DistinctCount of all records with -accidentTypeID = A

On top of this, I've generated a Report Model.

Building reports using Report Builder is a breeze, but if I set a filter on the NumberOfAccidents (the aggregated measure); Analysis Services goes off and never returns.

For instance, a matrix report with Country on one axis and Operation on the other and NumberOfAccidents as the only member will run as expected and quite fast.

However, if I set a filter on NumberOfAccidents to > 10, I'm lost. This query will basically run forever and completely halt Analysis Services.

The fact table contains approximately 270000 records.

Any insight ?

Trond

Sorry for the delay in replying.

Firstly, I suspect that the filter you are actually applying is not the one you expect. It will be filtering all of the rows in the fact table according to the criteria, before grouping by Country & Operation. Given that I don’t think NumberOfAccidents can be >1 for a fact row, I imagine that you intended to filter the results after grouping (which I don’t believe is possible in Report Builder, in a matrix).

Without knowing the details of the calculation, I can't say for sure why performance is so poor. I believe that it would be because the calculation is being evaluated for all combinations of the dimensions related to the measure group. There are two ways to make this much more efficient:

1. Make sure that NON_EMPTY_BEHAVIOR is defined for the calculation. This is a property of a calculated measure (say MC), and states which other measure (say M) where if M is Null, then MC is certainly Null. So if there is some simple Count measure on this measure group, then setting NON_EMPTY_BEHAVIOR to that Count measure, on NumberOfAccidents, should greatly improve

or

2.If this measure is simply a count of records where Accident type = A, then I would suggest simply having a new (non-calculated) measure based on that condition. So in the DSV, add a calculated column that is 1 if Type = A and 0 otherwise. Then have a regular measure built off that column