Named Query and Global Variables

Hello,
I want to filter records right in the Data Source View.
I did a "Replace Table with new Named Query" but how can I get it to filter
records by User!UserID?
Thanks,
-Will"I want to filter records right in the Data Source View."
What do you mean by this?
you want to filter your DATA? or do you want to set different
permissions for different users?|||I want to filter my data.
So "select * from table where field1=user!userid"
"sorcerdon@.gmail.com" wrote:
> "I want to filter records right in the Data Source View."
> What do you mean by this?
> you want to filter your DATA? or do you want to set different
> permissions for different users?
>|||Query parameters do not have to map to report parameters. RS automatically
creates a report parameter for every query parameter but you can delete them
and use an expression.
So, do this:
select * from mytable where field1 = @.User
RS will create automatically a report parameter called User. Instead of
using this mapping, you want to change it.
Click on the ... in the dataset designer, go to the parameters tab. On the
right side change from using a report parameter to using an expression. This
will bring you to the expression builder where you can set it to =User!UserID
Now, in the layout tab, report parameters menu, delete the report parameter
which was created for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>I want to filter my data.
> So "select * from table where field1=user!userid"
>
> "sorcerdon@.gmail.com" wrote:
>> "I want to filter records right in the Data Source View."
>> What do you mean by this?
>> you want to filter your DATA? or do you want to set different
>> permissions for different users?
>>|||I'm trying to use report models though.
So f I follow the example from msdn here:
http://msdn2.microsoft.com/en-us/library/ms156284.aspx
So after I create "Data Source View" I click on a Product table and click
replace with a named query, this let's me edit the sql or use the query
builder to filter records. In the "Filer" column for a field called user_id I
put =User!UserID, hoping that only products that user_id = User!UserId will
be returned. It does not like that filter, in fact I see nothing about global
variables or variables at all.
**The above is the way I'd like to do my filtering, but if not then:
I tried filtering in the "Report Model":
Looked at the fields, click "Add Group" and All must contain
dragged the user_id field into that "All must contain" box and set it to
user_id must contain GetUserID()
Not sure about the exact area's I clicked because I'm not by my development
pc. But I could not get the Report Model to Filter either.
I like the DataSource View filter because it's a universal way for anyone
who creates reports, in reportbuilder or dev. studio, to only have access to
records that have their userid.
-Will
.
"Bruce L-C [MVP]" wrote:
> Query parameters do not have to map to report parameters. RS automatically
> creates a report parameter for every query parameter but you can delete them
> and use an expression.
> So, do this:
> select * from mytable where field1 = @.User
> RS will create automatically a report parameter called User. Instead of
> using this mapping, you want to change it.
> Click on the ... in the dataset designer, go to the parameters tab. On the
> right side change from using a report parameter to using an expression. This
> will bring you to the expression builder where you can set it to => User!UserID
> Now, in the layout tab, report parameters menu, delete the report parameter
> which was created for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "WillG" <WillG@.discussions.microsoft.com> wrote in message
> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
> >I want to filter my data.
> > So "select * from table where field1=user!userid"
> >
> >
> > "sorcerdon@.gmail.com" wrote:
> >
> >> "I want to filter records right in the Data Source View."
> >>
> >> What do you mean by this?
> >>
> >> you want to filter your DATA? or do you want to set different
> >> permissions for different users?
> >>
> >>
>
>|||Sorry, I don't have any experience with report models. Can't help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillG" <WillG@.discussions.microsoft.com> wrote in message
news:E1041A66-B463-4011-9EF6-0C0D11BF2E12@.microsoft.com...
> I'm trying to use report models though.
> So f I follow the example from msdn here:
> http://msdn2.microsoft.com/en-us/library/ms156284.aspx
> So after I create "Data Source View" I click on a Product table and click
> replace with a named query, this let's me edit the sql or use the query
> builder to filter records. In the "Filer" column for a field called
> user_id I
> put =User!UserID, hoping that only products that user_id = User!UserId
> will
> be returned. It does not like that filter, in fact I see nothing about
> global
> variables or variables at all.
> **The above is the way I'd like to do my filtering, but if not then:
> I tried filtering in the "Report Model":
> Looked at the fields, click "Add Group" and All must contain
> dragged the user_id field into that "All must contain" box and set it to
> user_id must contain GetUserID()
> Not sure about the exact area's I clicked because I'm not by my
> development
> pc. But I could not get the Report Model to Filter either.
> I like the DataSource View filter because it's a universal way for anyone
> who creates reports, in reportbuilder or dev. studio, to only have access
> to
> records that have their userid.
> -Will
>
> .
> "Bruce L-C [MVP]" wrote:
>> Query parameters do not have to map to report parameters. RS
>> automatically
>> creates a report parameter for every query parameter but you can delete
>> them
>> and use an expression.
>> So, do this:
>> select * from mytable where field1 = @.User
>> RS will create automatically a report parameter called User. Instead of
>> using this mapping, you want to change it.
>> Click on the ... in the dataset designer, go to the parameters tab. On
>> the
>> right side change from using a report parameter to using an expression.
>> This
>> will bring you to the expression builder where you can set it to =>> User!UserID
>> Now, in the layout tab, report parameters menu, delete the report
>> parameter
>> which was created for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "WillG" <WillG@.discussions.microsoft.com> wrote in message
>> news:4DBBDA27-67CC-4873-97EF-38B90C1D1E9B@.microsoft.com...
>> >I want to filter my data.
>> > So "select * from table where field1=user!userid"
>> >
>> >
>> > "sorcerdon@.gmail.com" wrote:
>> >
>> >> "I want to filter records right in the Data Source View."
>> >>
>> >> What do you mean by this?
>> >>
>> >> you want to filter your DATA? or do you want to set different
>> >> permissions for different users?
>> >>
>> >>
>>sql