dataset query based on a global parameter

I need to create a report that displays data based on the userid. all the
records have a userid field filled with many userid values. how can I use the
global paramerer userid in the criteria for my dataset. so when a user run
the report olny records with his or her userid are showncreate a parameter with default value the global parameter. then use that
parameter in the query. ofcourse you can't use caching for those reports
"DJJIII" wrote:
> I need to create a report that displays data based on the userid. all the
> records have a userid field filled with many userid values. how can I use the
> global paramerer userid in the criteria for my dataset. so when a user run
> the report olny records with his or her userid are shown|||You do not have to have a 1:1 mapping between query parameters and report
parameters. Doing it the way suggested here means you have to muck around
with hiding it (since you don't want people to change it).
Have your query parameter, let's call it @.UserID. RS will automatically
recreate a report parameter called UserID but we won't use it. Click on the
..., go to parameters. Map the query parameter @.UserID to the global . The
parameter dialog box has a name and value columns. On the value side switch
it to expression. This brings you to the expression builder where you can
pick the User!UserID global variable. One thing to note, this variable has
the domain as well as the user id, so if you don't want this you will have
to strip it off.
Now, go you your layout, Report->Parameters and delete the now unneeded
UserID report parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> create a parameter with default value the global parameter. then use that
> parameter in the query. ofcourse you can't use caching for those reports
> "DJJIII" wrote:
>> I need to create a report that displays data based on the userid. all
>> the
>> records have a userid field filled with many userid values. how can I use
>> the
>> global paramerer userid in the criteria for my dataset. so when a user
>> run
>> the report olny records with his or her userid are shown|||great tip, that is indeed a lot more practical
"Bruce L-C [MVP]" wrote:
> You do not have to have a 1:1 mapping between query parameters and report
> parameters. Doing it the way suggested here means you have to muck around
> with hiding it (since you don't want people to change it).
> Have your query parameter, let's call it @.UserID. RS will automatically
> recreate a report parameter called UserID but we won't use it. Click on the
> ..., go to parameters. Map the query parameter @.UserID to the global . The
> parameter dialog box has a name and value columns. On the value side switch
> it to expression. This brings you to the expression builder where you can
> pick the User!UserID global variable. One thing to note, this variable has
> the domain as well as the user id, so if you don't want this you will have
> to strip it off.
> Now, go you your layout, Report->Parameters and delete the now unneeded
> UserID report parameter.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> > create a parameter with default value the global parameter. then use that
> > parameter in the query. ofcourse you can't use caching for those reports
> >
> > "DJJIII" wrote:
> >
> >> I need to create a report that displays data based on the userid. all
> >> the
> >> records have a userid field filled with many userid values. how can I use
> >> the
> >> global paramerer userid in the criteria for my dataset. so when a user
> >> run
> >> the report olny records with his or her userid are shown
>
>

Dataset not filled if sql-query generates error

Hi,

I've a dataset which is filled using a stored procedure in either MSSQL2005 or 2000.

The procedure may return sql-errors (f.ex 208 if a table is not found), but the procedure will anyway return data which shall populate the dataset, independent on 208 errors.

The procedure works fine if running it from f.ex. SqlMgtStudio, and the dataset is filled if the procedure triggers no errors.

However, if the procedure triggers an error like 208, then I get an exception in the application and the dataset is not populated with the data returned by the procedure.

Is there a way of telling the DataSet/SqlDataAdapter to fill the dataset even if the sql-code genererates som errors?

Regards, Guttorm

Perhaps you could wrap your statements to avoid errors. For instance, if you have a table not found error you could wrap the line that generates it with if object_id('<table name>') is not null or some other sanity check. This would just silently work through the problem if there are errors though, you could add an else of course to handle when tables don't exist to create them and such.

Hope this helps,

John (MSFT)

|||

Thank you for your response.

Your outlined workaround might have been an option if not for the fact that I do not have much control of the sql-code.

The sqlcode is indirectly built by the user (and then saved in database) and verifying it would require me to first parse it ( to find tables, views) then name resolution etc, a rather time and resource consuming process.

My current workaround is to divide the task of filling the dataset into two tasks,

1) execute a command (using SqlCommand.ExecuteNonQuery) to populate a temp-table with the result

2) fill dataset (DataSet.Fill) by selecting the result from the temp-table

This works fine but makes to code a bit more complicated and requires an intermediate store in the database.

Guttorm

sql