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

hi, most of a column values are like xx.00, how to find out the value have
more than 2 decimal (xx.001 or xx.xxx...) Thanks.Are the records not showing up or you only want the records that have more
than 2 decimal places?
What is the data type for the column?
HTH
Jerry
"js" <js@.someone@.hotmail.com> wrote in message
news:eDCvWkSyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> hi, most of a column values are like xx.00, how to find out the value
> have more than 2 decimal (xx.001 or xx.xxx...) Thanks.
>|||What is the datatype of this column? Please include DDL in future so that we
don't have to guess. Try:
SELECT x
FROM t1
WHERE ROUND(x,2,1) <> x
David Portas
SQL Server MVP
--
"js" <js@.someone@.hotmail.com> wrote in message
news:eDCvWkSyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> hi, most of a column values are like xx.00, how to find out the value
> have more than 2 decimal (xx.001 or xx.xxx...) Thanks.
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> Are the records not showing up or you only want the records that have more
> than 2 decimal places?
Yes. I only want to show the records have more than 2 decimal places.
> What is the data type for the column?
Real
Thanks.

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

Filling a DataTable from SqlQuery : If SqlQuery returns null values problem ocurrs with Da

Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.

Is it possible using DataTable with some null values in it?

Thanks

It's perfectly fine to fill a datatable with some (or all) NULL values... the problem is probably that you are trying to use a field that is not there (because it's null).

Example:

DataTable myDT = new DataTable();

myDataAdapter.Fill(myDT); // <-- there's no problem if there are nulls in there

myDT.Rows[3]["SomeFieldWhichCanBeNull"].ToString(); // <--BANG, there's your error!

|||

You can store the NULL values and to avoid the error, you may check Is it null or not by usingIsNull, like in this example:

1if (!dr.IsNull("MyColumn")2{3 MyColumn = dr["MyColumn"].ToString();4}5

Good luck.

Fill data

INSERT INTO Table1(Title) VALUES('Somedata1')
INSERT INTO Table1(Title) VALUES('Somedata2')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(1,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(1,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,2,200,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,2,100,'Somedata
')
> For each Table1.Id I want to Select Top 1 Table2.Id
> where Table2.Table1Id = Table.Id And Table2.ParentId = 0
> Order By Table2.Type Desc och Table2.Id Desc.
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)

> With these Top 1 Table2.Id:s I finally want to Select all data
> From Table2 Where each of the Id:s are equal to either Table2.Id Or
> Table2.ParentId
SELECT Id, ParentId, Table1Id, Type, Title
FROM Table2
WHERE Id IN
(
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)
)
OR ParentId IN
(
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)
)|||That works great. You made my day. A big thank you for your time and knowled
ge.
/ Senna
"markc600@.hotmail.com" wrote:

>
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
>
> SELECT Id, ParentId, Table1Id, Type, Title
> FROM Table2
> WHERE Id IN
> (
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
> )
> OR ParentId IN
> (
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
> )
>