Showing posts with label based. Show all posts
Showing posts with label based. Show all posts

Thursday, March 29, 2012

filter expression using measures

Hi guys,

I want to filter an MDX query based on a specific measure values, however, the MDX query designer allows you to filter only based on a dimension.

Is there any soulution to resolve this problem.

Sincerely,

--Amde

In the data tab for reporting services, select your dataset you want to filter by in the drop down list. Now, click the "..." to the right. In the filters tab, you can filter the dataset by an expression you provide.

For example, if you wanted to filter the results so that only customers with over 100 purchases were displayed, you could do this:

Expression: =Fields!NumberOfPurchases.value
Operator: >
Value: 100

You should be good to go. Good luck!!

|||thx!

Filter Expression

Hi

I have some data that need to be filtered based on a SET of Id's.
If it's about a single ID, then i would pass it as a parameter in a
stored procedure and use it within the the WHERE Clause, but here those
ID's are determined in run time and I can't simply create a a stored
procedure for an unknown amount of ID's.
I looked into the SQL Server 8.0 Manual but had no examples how to use
the Function Filter.
Generaly, how can filter some records based on a set of ID's?

Best regardscoosa (coosa76@.gmail.com) writes:
> I have some data that need to be filtered based on a SET of Id's.
> If it's about a single ID, then i would pass it as a parameter in a
> stored procedure and use it within the the WHERE Clause, but here those
> ID's are determined in run time and I can't simply create a a stored
> procedure for an unknown amount of ID's.
> I looked into the SQL Server 8.0 Manual but had no examples how to use
> the Function Filter.
> Generaly, how can filter some records based on a set of ID's?

FROM tbl t
JOIN iter_intlist_to_tbl(@.list_of_ids, DEFAULT) f ON t.id = f.number

iter_intlist_to_tbl is table-valued function that unpacks a space-
separared list of integers into table which you then join with.

Code at http://www.sommarskog.se/arrays-in-...ist-of-integers.
The rest of the article provides more discussion about this kind of problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Filter Data within a graph

I have a dilemma where I have a dataset that is composed of three columns:

Key, TimeStamp, Value

My chart needs to show different lines based on what the Key in the dataset is through a certain timeframe. Take for example my dataset has 4 keys. On the first key, I need to show the average of the values. On the second key, I need to show the max values. My third key, I need to show the min values. My 4th key I need to show the Median.

I need to be able to put 4 different entries in the Values list of my chart and have them filtered based on the criteria based on the same column within the dataset. I guess my question is: Can I put an IIF statement inside of the Value entry to get my desired result and if so, what would the syntax be?

Yes, you have to put 4 entries into the values list. For example:

1. =iif(Fields!Key.Value = "A", Fields!Value.Value, Nothing)
2. =iif(Fields!Key.Value = "B", Fields!Value.Value, Nothing)
3. =iif(Fields!Key.Value = "C", Fields!Value.Value, Nothing)
4. =iif(Fields!Key.Value = "D", Fields!Value.Value, Nothing)

Note: in a line chart, the chart control will only connect data points that have a value different than null (Nothing).

-- Robert

Filter Cursor With Dynamic SQL

I should know this, but having a brain cramp.

I am trying to filter the data a cursor has to go through based on dynamic sql, but can't seem to get the syntax correctly.

Basically

'tmp is filter clause brought into the stored proc.

Declare @.tmp varchar(2000)

Declare my CURSOR FAST_FORWARD

FOR

Select * from table where + @.tmp

OPEN my

...

...

...

Any ideas?

declare a table variable to hold the data
declare @.SqlStr varchar(8000)

set @.SQLStr = 'Select * from table where '+ @.tmp

insert into @.tableVariable (Col1, Col2,...)
exec (@.SqlStr)

declare my CURSOR FAST_FORWARD
FOR
select * from @.tableVariable

rest of your should work...

|||

Also, be sure to declare the cursor as LOCAL unless you need it to be global...

declare my CURSOR LOCAL FAST_FORWARD

Better yet, consider not using a cursor if possible Smile Tell us more about what you were doing and perhaps we can help.

|||

Here's the skinny.

I created a view to consolidate a bunch of data across tables. Once this table is consolidated I need to perform calculations on a row by row basis as a previous row's calculation is used in future rows based upon business rules.

I have written this logic using an inline view, TSQL Stored Proc (utilizing cursors), CLR Stored Proc, and traditional .NET business logic. I found that of the four options TSQL Stored Proc and CLR Stored Proc perform the best when running remotely. I now am trying to filter down the result set of the TSQL Stored Proc based upon parameters a user will provide. After following Bashan's advice I am able to filter it down and the speed is great; however, if I run it without filtering down the results then the logic doubled the processing time of the stored proc.

I would love to create this logic without using cursors but not sure how to perform row by row complex logic inside the database without using them.

Thanks for your help. I look forward to receiving any advice.

|||

The question is always "what is the complex logic"? Usually, the complex logic can be turned into where clauses, subqueries, and occasionally a function or two. It is also possible that the cursor is the best way do things if you need ordered data: http://blogs.sqlservercentral.com/blogs/amachanic/archive/2006/02/28/508.aspx

Using row_number() you can often do the kind of rollups based on ordering and previous values, but it can also be hard to do. So without much more work/knowledge of your system, if the cursor works I would go with that. But if you want some more advice/ideas, give us the gist of the problem and we can take a look and see what you might could change.

sql

Filter Cursor With Dynamic SQL

I should know this, but having a brain cramp.

I am trying to filter the data a cursor has to go through based on dynamic sql, but can't seem to get the syntax correctly.

Basically

'tmp is filter clause brought into the stored proc.

Declare @.tmp varchar(2000)

Declare my CURSOR FAST_FORWARD

FOR

Select * from table where + @.tmp

OPEN my

...

...

...

Any ideas?

declare a table variable to hold the data
declare @.SqlStr varchar(8000)

set @.SQLStr = 'Select * from table where '+ @.tmp

insert into @.tableVariable (Col1, Col2,...)
exec (@.SqlStr)

declare my CURSOR FAST_FORWARD
FOR
select * from @.tableVariable

rest of your should work...

|||

Also, be sure to declare the cursor as LOCAL unless you need it to be global...

declare my CURSOR LOCAL FAST_FORWARD

Better yet, consider not using a cursor if possible Smile Tell us more about what you were doing and perhaps we can help.

|||

Here's the skinny.

I created a view to consolidate a bunch of data across tables. Once this table is consolidated I need to perform calculations on a row by row basis as a previous row's calculation is used in future rows based upon business rules.

I have written this logic using an inline view, TSQL Stored Proc (utilizing cursors), CLR Stored Proc, and traditional .NET business logic. I found that of the four options TSQL Stored Proc and CLR Stored Proc perform the best when running remotely. I now am trying to filter down the result set of the TSQL Stored Proc based upon parameters a user will provide. After following Bashan's advice I am able to filter it down and the speed is great; however, if I run it without filtering down the results then the logic doubled the processing time of the stored proc.

I would love to create this logic without using cursors but not sure how to perform row by row complex logic inside the database without using them.

Thanks for your help. I look forward to receiving any advice.

|||

The question is always "what is the complex logic"? Usually, the complex logic can be turned into where clauses, subqueries, and occasionally a function or two. It is also possible that the cursor is the best way do things if you need ordered data: http://blogs.sqlservercentral.com/blogs/amachanic/archive/2006/02/28/508.aspx

Using row_number() you can often do the kind of rollups based on ordering and previous values, but it can also be hard to do. So without much more work/knowledge of your system, if the cursor works I would go with that. But if you want some more advice/ideas, give us the gist of the problem and we can take a look and see what you might could change.

Tuesday, March 27, 2012

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,
> >>
> >>
> >>
>
>

Filling out gaps in a date based select statement

Hi,
I have a query which simply returns a count of a particular field by w
number. My query as it works fine however for ws where there are no data
it obviously doesn't return a row. My problem is that is I wish for the
statement to return a zero for those ws with no data - I'm just not sure
how to do this.
I realise that I can achieve it by creating a table listing w numbers and
joining my query on that table. However I'd rather not create a table like
that. Is there another approach that will let me achieve the same thing?
Currently my query is:
SELECT datepart(wk, datein) AS Wno , COUNT(movementno) AS ContainersIn
STOCK stock
WHERE year(datein) = '2005'
GROUP BY datepart(wk, datein)
which returns:
W ContainersIn
29 3
31 1
34 5
35 4
36 8
37 6
38 3
39 1
40 12
Thanks,
Chris.See if this helps you
http://weblogs.sqlteam.com/jeffs/ar...09/12/7755.aspx
Madhivanan|||Why don't you want to create a table for this? I would recommend you
keep an auxiliary numbers table (a table integers from 0 to some
arbitrarily large number) for exactly such an application. A Calendar
table is also very useful to have around.
A SELECT statement can't create data out of nothing. Some other options
are: use a derived table in your query with a UNION of the numbers 1 -
52 ; create a view containing those numbers ; create a table-valued
function that returns a parameterized range of numbers ; extract the
numbers from system table(s) (not recommended). Creating the permanent
table would be my favourite - it's fast, easy, portable and much more
concise and transparent than any of the others.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128332658.694445.44410@.g47g2000cwa.googlegroups.com...
> Why don't you want to create a table for this? I would recommend you
> keep an auxiliary numbers table (a table integers from 0 to some
> arbitrarily large number) for exactly such an application. A Calendar
> table is also very useful to have around.
> A SELECT statement can't create data out of nothing. Some other options
> are: use a derived table in your query with a UNION of the numbers 1 -
> 52 ; create a view containing those numbers ; create a table-valued
> function that returns a parameterized range of numbers ; extract the
> numbers from system table(s) (not recommended). Creating the permanent
> table would be my favourite - it's fast, easy, portable and much more
> concise and transparent than any of the others.
> --
> David Portas
> SQL Server MVP
> --
>
Thank your for your help David - most appreciated. I guess I find the idea
of creating a new table "messy". No idea why - just something that has
always struck me as something to avoid. A habit I will have to get out of!
thanks again
Chris.

filling in missing values

I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9To have all the dealers needed:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
RIGHT OUTER join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

To have all the dates, that is something else ...
SQL Server can only base itself on what it finds in the database ...
If there is notking in tblstats (s) for that moment and you get your months from that table it will not work ...
Create an extra table (or an temp table) that will store all months and create an outer join on that table

Friday, March 9, 2012

filegroup auto-growth - know when it happens?

Hey guys,

I'm running SQLServer 2000. I am in the process of looking over our current filegroups and providing reports based on file size increases.

One thing I'd like to know is when (date and time) a filegroup last "auto-grew". Is there a way to know when this occurs?

thanks,

Jason

SQL Profiler, Events, Database...

Data File Auto Grow

Data File Auto Shrink

Log File Auto Grow

Log File Auto Shrink

|||

Derek,

Thanks for the info. I should have re-worded my question I think:

Is there a way to know when the date/time a filegroup last auto-resized? Let's say it auto-resized last friday, is this timestamp stored somewhere? Profiler would tell me only if I was running it at the time of the resize.

I'm trying to correlate the time of an error with the last time a large filegroup resized.

thanks

Jason

|||No. In general if you do not trace database activity it is lost. SQL Server has a builtin error log but it collects exceptions.|||

Thanks for the info. Appreciate the help.

Jason

Friday, February 24, 2012

File Name to be changed

Hello,

I have developed a report based on a stored procedure which will generate an excel file. There is a requirement that this file needs to be sent to an external domain with the name changed accordingly i.e., a date is to be suffixed to the file after the name has been changed based on their naming convention.

I would appreciate, if anybody has done so and can give me some steps for me to work out.

TIA

Hello,

Take a look at data-driven subscriptions in Books Online. I believe this is the only way to update the filename without writing a custom app to handle it.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks for your reply. I apologize if my information is not correct.

I would like to change the filename which is sent as an attachment in an email and not the filename as it is.

Thanks