If I specifiy a parameter in my report dataset like @.status to filter the
report based on a status selected by the user - I can't figure out how to
give them the "ALL" option - so that the report ignores the status and
displays all the records.
--
ArkayTry this
SELECT NULL AS StatusValue, '<All>' AS Status
UNION
SELECT StatusValue, Status
FROM Status_mstr
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:7E0C2DD8-A1E9-407C-BDDD-AD0B110C5775@.microsoft.com...
> If I specifiy a parameter in my report dataset like @.status to filter the
> report based on a status selected by the user - I can't figure out how to
> give them the "ALL" option - so that the report ignores the status and
> displays all the records.
>
> --
> Arkay
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Friday, March 30, 2012
Report Parameter Problem
I have a report based on a stored procedure with three parameters: Start
Date, End Date, and Salesperson.
The first two are required, and the Salesperson is not. In the report, the
Salesperson is based on a query which provides a drop down for choosing a
name. It is set to allow nulls and blanks.
When I run the report in Visual Studio preview, if no Salesperson is chosen,
and both dates are entered, no data is returned. Doing the same thing via
debug in a browser, (the drop down is not blank, but says <Selelct a value>),
I get prompted to supply the Salesperson parameter.
Supplying a value for Salesperson in both cases works fine, but I want the
user to have the option to get all the data, not just for a particular
person. I've covered this case in the stored procedure, so I'm not sure what
is getting passed in the previewer when nothing is entered and no data is
returned.
If I run the query (sp) via the Data tab, and provide exactly the same
values for the parameters, I get the data I expect, and no prompt for the
Salesperson.
If I put a default value in for the Salesperson, (non-queried), e.g. "All",
the preview in VS goes crazy, and locks up.
I would have thought with allowing nulls and/or blanks, this should work. Am
I missing something?
Thanks for any help,
TomIf Salesperson parameter uses available values from query, then the query
must return null or blank as one of available values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
>I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is
> chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a
> value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure
> what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g.
> "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work.
> Am
> I missing something?
> Thanks for any help,
> Tom|||Lev, thanks, I was assuming if no choice was made (drop down blank) a null
would be passed. I fixed it up to provide a value simulating a null
situation, and it now works fine.
Thanks for your quick response...
Tom
"Lev Semenets [MSFT]" wrote:
> If Salesperson parameter uses available values from query, then the query
> must return null or blank as one of available values.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> >I have a report based on a stored procedure with three parameters: Start
> > Date, End Date, and Salesperson.
> >
> > The first two are required, and the Salesperson is not. In the report, the
> > Salesperson is based on a query which provides a drop down for choosing a
> > name. It is set to allow nulls and blanks.
> >
> > When I run the report in Visual Studio preview, if no Salesperson is
> > chosen,
> > and both dates are entered, no data is returned. Doing the same thing via
> > debug in a browser, (the drop down is not blank, but says <Selelct a
> > value>),
> > I get prompted to supply the Salesperson parameter.
> >
> > Supplying a value for Salesperson in both cases works fine, but I want the
> > user to have the option to get all the data, not just for a particular
> > person. I've covered this case in the stored procedure, so I'm not sure
> > what
> > is getting passed in the previewer when nothing is entered and no data is
> > returned.
> >
> > If I run the query (sp) via the Data tab, and provide exactly the same
> > values for the parameters, I get the data I expect, and no prompt for the
> > Salesperson.
> >
> > If I put a default value in for the Salesperson, (non-queried), e.g.
> > "All",
> > the preview in VS goes crazy, and locks up.
> >
> > I would have thought with allowing nulls and/or blanks, this should work.
> > Am
> > I missing something?
> >
> > Thanks for any help,
> >
> > Tom
>
>|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g. "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work. Am
> I missing something?
> Thanks for any help,
> Tom|||Did you set default value for the parameter?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:7C84F377-9ECA-4A87-AC8F-E936353B917A@.microsoft.com...
> TomT
> Could you please tell me how did you simulate a value of null. I am
> running
> into exactly same problem as yours where I cant make paramter optional as
> Report Manager forces me to choose a value although I have defined null
> and
> blank as options for the parameter.
> TIA
> "TomT" wrote:
>> I have a report based on a stored procedure with three parameters: Start
>> Date, End Date, and Salesperson.
>> The first two are required, and the Salesperson is not. In the report,
>> the
>> Salesperson is based on a query which provides a drop down for choosing a
>> name. It is set to allow nulls and blanks.
>> When I run the report in Visual Studio preview, if no Salesperson is
>> chosen,
>> and both dates are entered, no data is returned. Doing the same thing via
>> debug in a browser, (the drop down is not blank, but says <Selelct a
>> value>),
>> I get prompted to supply the Salesperson parameter.
>> Supplying a value for Salesperson in both cases works fine, but I want
>> the
>> user to have the option to get all the data, not just for a particular
>> person. I've covered this case in the stored procedure, so I'm not sure
>> what
>> is getting passed in the previewer when nothing is entered and no data is
>> returned.
>> If I run the query (sp) via the Data tab, and provide exactly the same
>> values for the parameters, I get the data I expect, and no prompt for the
>> Salesperson.
>> If I put a default value in for the Salesperson, (non-queried), e.g.
>> "All",
>> the preview in VS goes crazy, and locks up.
>> I would have thought with allowing nulls and/or blanks, this should work.
>> Am
>> I missing something?
>> Thanks for any help,
>> Tom
Date, End Date, and Salesperson.
The first two are required, and the Salesperson is not. In the report, the
Salesperson is based on a query which provides a drop down for choosing a
name. It is set to allow nulls and blanks.
When I run the report in Visual Studio preview, if no Salesperson is chosen,
and both dates are entered, no data is returned. Doing the same thing via
debug in a browser, (the drop down is not blank, but says <Selelct a value>),
I get prompted to supply the Salesperson parameter.
Supplying a value for Salesperson in both cases works fine, but I want the
user to have the option to get all the data, not just for a particular
person. I've covered this case in the stored procedure, so I'm not sure what
is getting passed in the previewer when nothing is entered and no data is
returned.
If I run the query (sp) via the Data tab, and provide exactly the same
values for the parameters, I get the data I expect, and no prompt for the
Salesperson.
If I put a default value in for the Salesperson, (non-queried), e.g. "All",
the preview in VS goes crazy, and locks up.
I would have thought with allowing nulls and/or blanks, this should work. Am
I missing something?
Thanks for any help,
TomIf Salesperson parameter uses available values from query, then the query
must return null or blank as one of available values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
>I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is
> chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a
> value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure
> what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g.
> "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work.
> Am
> I missing something?
> Thanks for any help,
> Tom|||Lev, thanks, I was assuming if no choice was made (drop down blank) a null
would be passed. I fixed it up to provide a value simulating a null
situation, and it now works fine.
Thanks for your quick response...
Tom
"Lev Semenets [MSFT]" wrote:
> If Salesperson parameter uses available values from query, then the query
> must return null or blank as one of available values.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> >I have a report based on a stored procedure with three parameters: Start
> > Date, End Date, and Salesperson.
> >
> > The first two are required, and the Salesperson is not. In the report, the
> > Salesperson is based on a query which provides a drop down for choosing a
> > name. It is set to allow nulls and blanks.
> >
> > When I run the report in Visual Studio preview, if no Salesperson is
> > chosen,
> > and both dates are entered, no data is returned. Doing the same thing via
> > debug in a browser, (the drop down is not blank, but says <Selelct a
> > value>),
> > I get prompted to supply the Salesperson parameter.
> >
> > Supplying a value for Salesperson in both cases works fine, but I want the
> > user to have the option to get all the data, not just for a particular
> > person. I've covered this case in the stored procedure, so I'm not sure
> > what
> > is getting passed in the previewer when nothing is entered and no data is
> > returned.
> >
> > If I run the query (sp) via the Data tab, and provide exactly the same
> > values for the parameters, I get the data I expect, and no prompt for the
> > Salesperson.
> >
> > If I put a default value in for the Salesperson, (non-queried), e.g.
> > "All",
> > the preview in VS goes crazy, and locks up.
> >
> > I would have thought with allowing nulls and/or blanks, this should work.
> > Am
> > I missing something?
> >
> > Thanks for any help,
> >
> > Tom
>
>|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g. "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work. Am
> I missing something?
> Thanks for any help,
> Tom|||Did you set default value for the parameter?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:7C84F377-9ECA-4A87-AC8F-E936353B917A@.microsoft.com...
> TomT
> Could you please tell me how did you simulate a value of null. I am
> running
> into exactly same problem as yours where I cant make paramter optional as
> Report Manager forces me to choose a value although I have defined null
> and
> blank as options for the parameter.
> TIA
> "TomT" wrote:
>> I have a report based on a stored procedure with three parameters: Start
>> Date, End Date, and Salesperson.
>> The first two are required, and the Salesperson is not. In the report,
>> the
>> Salesperson is based on a query which provides a drop down for choosing a
>> name. It is set to allow nulls and blanks.
>> When I run the report in Visual Studio preview, if no Salesperson is
>> chosen,
>> and both dates are entered, no data is returned. Doing the same thing via
>> debug in a browser, (the drop down is not blank, but says <Selelct a
>> value>),
>> I get prompted to supply the Salesperson parameter.
>> Supplying a value for Salesperson in both cases works fine, but I want
>> the
>> user to have the option to get all the data, not just for a particular
>> person. I've covered this case in the stored procedure, so I'm not sure
>> what
>> is getting passed in the previewer when nothing is entered and no data is
>> returned.
>> If I run the query (sp) via the Data tab, and provide exactly the same
>> values for the parameters, I get the data I expect, and no prompt for the
>> Salesperson.
>> If I put a default value in for the Salesperson, (non-queried), e.g.
>> "All",
>> the preview in VS goes crazy, and locks up.
>> I would have thought with allowing nulls and/or blanks, this should work.
>> Am
>> I missing something?
>> Thanks for any help,
>> Tom
Report Parameter not working in subquery?
Hi All,
There are two parameters in our DataSet based on the query below.
@.Year causes "syntax or access violation" error, because it's in subquery.
@.Parameter2 works (in the main query)
----
SELECT *,
( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
THEN 'Green'
WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
WHEN Av_MTD < ISNULL(Av_Comm,0)
THEN 'Red' ELSE 'Black'
END) AS Avail_color_MTD,
( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
THEN 'Red' ELSE 'Black' END
) AS Resp_color_MTD
FROM (
SELECT
Sla_Name_By_Loc,
MAX(Sla_Definition) AS Sla_Definition,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Commitment_By_Loc END) AS Av_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Commitment_By_Loc END) AS Re_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Result_by_loc END) AS Av_MTD,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Result_by_loc END) AS Re_MTD
FROM
Sla_Results_Trans_BY_LOC
WHERE (Sla_Category_id_By_Loc = 2)
AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
AND (Sla_Month_by_loc = 9)
AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
GROUP BY
Sla_Name_By_Loc) Y
where Y.Re_Comm = @.Parameter2 -- works!
ORDER BY Sla_Name_By_Loc
---
Please help!
TaoMake sure that you have two report parameters. In layout, menu
reports->report parameter. Next go to your dataset, ... , parameters tab and
make sure that each of your query parameters are mapped to a report
parameter. Being a subquery or not really doesn't matter.
Bruce L-C
"Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> Hi All,
> There are two parameters in our DataSet based on the query below.
> @.Year causes "syntax or access violation" error, because it's in subquery.
> @.Parameter2 works (in the main query)
> ----
> SELECT *,
> ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> THEN 'Green'
> WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> WHEN Av_MTD < ISNULL(Av_Comm,0)
> THEN 'Red' ELSE 'Black'
> END) AS Avail_color_MTD,
> ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> THEN 'Red' ELSE 'Black' END
> ) AS Resp_color_MTD
> FROM (
> SELECT
> Sla_Name_By_Loc,
> MAX(Sla_Definition) AS Sla_Definition,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Commitment_By_Loc END) AS Av_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Commitment_By_Loc END) AS Re_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Result_by_loc END) AS Av_MTD,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Result_by_loc END) AS Re_MTD
> FROM
> Sla_Results_Trans_BY_LOC
> WHERE (Sla_Category_id_By_Loc = 2)
> AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> AND (Sla_Month_by_loc = 9)
> AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> GROUP BY
> Sla_Name_By_Loc) Y
> where Y.Re_Comm = @.Parameter2 -- works!
> ORDER BY Sla_Name_By_Loc
> ---
> Please help!
> Tao|||Such a dataset would fail:
(pubs database)
SELECT au_lname
FROM authors
WHERE (au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id = @.title))
Error message: "Parameter Information cannot be derived from SQL statements
with sub-select queries. Set parameter information before preparing command."
But the parametere exists under Layout/Reports/Report Parameters and the
parameter mapping is there in the Dataset/Edit/Parameters tab:
@.title =Parameters!title.Value
Can some one try this out to see if you can succeed?
Thanks,
Tao
"Bruce Loehle-Conger" wrote:
> Make sure that you have two report parameters. In layout, menu
> reports->report parameter. Next go to your dataset, ... , parameters tab and
> make sure that each of your query parameters are mapped to a report
> parameter. Being a subquery or not really doesn't matter.
> Bruce L-C
> "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > Hi All,
> >
> > There are two parameters in our DataSet based on the query below.
> >
> > @.Year causes "syntax or access violation" error, because it's in subquery.
> > @.Parameter2 works (in the main query)
> > ----
> > SELECT *,
> > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > THEN 'Green'
> > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > THEN 'Red' ELSE 'Black'
> > END) AS Avail_color_MTD,
> > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > THEN 'Red' ELSE 'Black' END
> > ) AS Resp_color_MTD
> > FROM (
> > SELECT
> > Sla_Name_By_Loc,
> > MAX(Sla_Definition) AS Sla_Definition,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Commitment_By_Loc END) AS Av_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Commitment_By_Loc END) AS Re_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Result_by_loc END) AS Av_MTD,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Result_by_loc END) AS Re_MTD
> > FROM
> > Sla_Results_Trans_BY_LOC
> > WHERE (Sla_Category_id_By_Loc = 2)
> > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > AND (Sla_Month_by_loc = 9)
> > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > GROUP BY
> > Sla_Name_By_Loc) Y
> > where Y.Re_Comm = @.Parameter2 -- works!
> > ORDER BY Sla_Name_By_Loc
> > ---
> >
> > Please help!
> >
> > Tao
>
>|||Got the solution for you. If you use the generic query instead (click on the
button next to the !) then it will work. I think you have come across a
limitation of the graphical designer to deal with subqueries and parameters.
Works just the way you want from the query designer.
Bruce L-C [MVP Reporting Services]
"Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> Such a dataset would fail:
> (pubs database)
> SELECT au_lname
> FROM authors
> WHERE (au_id IN
> (SELECT au_id
> FROM titleauthor
> WHERE title_id = @.title))
> Error message: "Parameter Information cannot be derived from SQL
statements
> with sub-select queries. Set parameter information before preparing
command."
> But the parametere exists under Layout/Reports/Report Parameters and the
> parameter mapping is there in the Dataset/Edit/Parameters tab:
> @.title =Parameters!title.Value
> Can some one try this out to see if you can succeed?
> Thanks,
> Tao
> "Bruce Loehle-Conger" wrote:
> > Make sure that you have two report parameters. In layout, menu
> > reports->report parameter. Next go to your dataset, ... , parameters tab
and
> > make sure that each of your query parameters are mapped to a report
> > parameter. Being a subquery or not really doesn't matter.
> >
> > Bruce L-C
> >
> > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > Hi All,
> > >
> > > There are two parameters in our DataSet based on the query below.
> > >
> > > @.Year causes "syntax or access violation" error, because it's in
subquery.
> > > @.Parameter2 works (in the main query)
> > > ----
> > > SELECT *,
> > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > THEN 'Green'
> > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > THEN 'Red' ELSE 'Black'
> > > END) AS Avail_color_MTD,
> > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > THEN 'Red' ELSE 'Black' END
> > > ) AS Resp_color_MTD
> > > FROM (
> > > SELECT
> > > Sla_Name_By_Loc,
> > > MAX(Sla_Definition) AS Sla_Definition,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Result_by_loc END) AS Av_MTD,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Result_by_loc END) AS Re_MTD
> > > FROM
> > > Sla_Results_Trans_BY_LOC
> > > WHERE (Sla_Category_id_By_Loc = 2)
> > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > AND (Sla_Month_by_loc = 9)
> > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > GROUP BY
> > > Sla_Name_By_Loc) Y
> > > where Y.Re_Comm = @.Parameter2 -- works!
> > > ORDER BY Sla_Name_By_Loc
> > > ---
> > >
> > > Please help!
> > >
> > > Tao
> >
> >
> >|||I am having the same problem.
I have selected the Generic Query pane and it let me then enter a parameter
value.
It ran but showed no data. The field names were shown but no data was shown.
Any help would be appreciated. Thank you.
"Bruce Loehle-Conger [MVP]" wrote:
> Got the solution for you. If you use the generic query instead (click on the
> button next to the !) then it will work. I think you have come across a
> limitation of the graphical designer to deal with subqueries and parameters.
> Works just the way you want from the query designer.
> Bruce L-C [MVP Reporting Services]
> "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > Such a dataset would fail:
> >
> > (pubs database)
> > SELECT au_lname
> > FROM authors
> > WHERE (au_id IN
> > (SELECT au_id
> > FROM titleauthor
> > WHERE title_id = @.title))
> >
> > Error message: "Parameter Information cannot be derived from SQL
> statements
> > with sub-select queries. Set parameter information before preparing
> command."
> >
> > But the parametere exists under Layout/Reports/Report Parameters and the
> > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > @.title =Parameters!title.Value
> >
> > Can some one try this out to see if you can succeed?
> >
> > Thanks,
> >
> > Tao
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Make sure that you have two report parameters. In layout, menu
> > > reports->report parameter. Next go to your dataset, ... , parameters tab
> and
> > > make sure that each of your query parameters are mapped to a report
> > > parameter. Being a subquery or not really doesn't matter.
> > >
> > > Bruce L-C
> > >
> > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > Hi All,
> > > >
> > > > There are two parameters in our DataSet based on the query below.
> > > >
> > > > @.Year causes "syntax or access violation" error, because it's in
> subquery.
> > > > @.Parameter2 works (in the main query)
> > > > ----
> > > > SELECT *,
> > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > THEN 'Green'
> > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > THEN 'Red' ELSE 'Black'
> > > > END) AS Avail_color_MTD,
> > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > THEN 'Red' ELSE 'Black' END
> > > > ) AS Resp_color_MTD
> > > > FROM (
> > > > SELECT
> > > > Sla_Name_By_Loc,
> > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Result_by_loc END) AS Re_MTD
> > > > FROM
> > > > Sla_Results_Trans_BY_LOC
> > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > AND (Sla_Month_by_loc = 9)
> > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > GROUP BY
> > > > Sla_Name_By_Loc) Y
> > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > ORDER BY Sla_Name_By_Loc
> > > > ---
> > > >
> > > > Please help!
> > > >
> > > > Tao
> > >
> > >
> > >
>
>|||Not the same problem. He was getting an error when trying to execute it. If
you are getting the field list back then that means that it is executing
correctly. Most likely something is not being specified correctly for your
parameter when you are prompted. What database are you going against (SQL
Server 2000?), what is the datatype of the field you are querying against?
How are you putting in the value when prompted for the parameter when you
execute it from the generic query pane.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> I am having the same problem.
> I have selected the Generic Query pane and it let me then enter a
parameter
> value.
> It ran but showed no data. The field names were shown but no data was
shown.
> Any help would be appreciated. Thank you.
> "Bruce Loehle-Conger [MVP]" wrote:
> > Got the solution for you. If you use the generic query instead (click on
the
> > button next to the !) then it will work. I think you have come across a
> > limitation of the graphical designer to deal with subqueries and
parameters.
> > Works just the way you want from the query designer.
> >
> > Bruce L-C [MVP Reporting Services]
> >
> > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > Such a dataset would fail:
> > >
> > > (pubs database)
> > > SELECT au_lname
> > > FROM authors
> > > WHERE (au_id IN
> > > (SELECT au_id
> > > FROM titleauthor
> > > WHERE title_id = @.title))
> > >
> > > Error message: "Parameter Information cannot be derived from SQL
> > statements
> > > with sub-select queries. Set parameter information before preparing
> > command."
> > >
> > > But the parametere exists under Layout/Reports/Report Parameters and
the
> > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > @.title =Parameters!title.Value
> > >
> > > Can some one try this out to see if you can succeed?
> > >
> > > Thanks,
> > >
> > > Tao
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > Make sure that you have two report parameters. In layout, menu
> > > > reports->report parameter. Next go to your dataset, ... , parameters
tab
> > and
> > > > make sure that each of your query parameters are mapped to a report
> > > > parameter. Being a subquery or not really doesn't matter.
> > > >
> > > > Bruce L-C
> > > >
> > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > Hi All,
> > > > >
> > > > > There are two parameters in our DataSet based on the query below.
> > > > >
> > > > > @.Year causes "syntax or access violation" error, because it's in
> > subquery.
> > > > > @.Parameter2 works (in the main query)
> > > > > ----
> > > > > SELECT *,
> > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > THEN 'Green'
> > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > THEN 'Red' ELSE 'Black'
> > > > > END) AS Avail_color_MTD,
> > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > THEN 'Red' ELSE 'Black' END
> > > > > ) AS Resp_color_MTD
> > > > > FROM (
> > > > > SELECT
> > > > > Sla_Name_By_Loc,
> > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > FROM
> > > > > Sla_Results_Trans_BY_LOC
> > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > AND (Sla_Month_by_loc = 9)
> > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > GROUP BY
> > > > > Sla_Name_By_Loc) Y
> > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > ORDER BY Sla_Name_By_Loc
> > > > > ---
> > > > >
> > > > > Please help!
> > > > >
> > > > > Tao
> > > >
> > > >
> > > >
> >
> >
> >|||Thank you very much for responding.
Problem fixed. When I clicked back from the Generic SQL Query and added more
fields, my query formatting changed somehow. Now it works great.
Thanks alot.
"Bruce L-C [MVP]" wrote:
> Not the same problem. He was getting an error when trying to execute it. If
> you are getting the field list back then that means that it is executing
> correctly. Most likely something is not being specified correctly for your
> parameter when you are prompted. What database are you going against (SQL
> Server 2000?), what is the datatype of the field you are querying against?
> How are you putting in the value when prompted for the parameter when you
> execute it from the generic query pane.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
> news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> > I am having the same problem.
> > I have selected the Generic Query pane and it let me then enter a
> parameter
> > value.
> > It ran but showed no data. The field names were shown but no data was
> shown.
> > Any help would be appreciated. Thank you.
> >
> > "Bruce Loehle-Conger [MVP]" wrote:
> >
> > > Got the solution for you. If you use the generic query instead (click on
> the
> > > button next to the !) then it will work. I think you have come across a
> > > limitation of the graphical designer to deal with subqueries and
> parameters.
> > > Works just the way you want from the query designer.
> > >
> > > Bruce L-C [MVP Reporting Services]
> > >
> > > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > > Such a dataset would fail:
> > > >
> > > > (pubs database)
> > > > SELECT au_lname
> > > > FROM authors
> > > > WHERE (au_id IN
> > > > (SELECT au_id
> > > > FROM titleauthor
> > > > WHERE title_id = @.title))
> > > >
> > > > Error message: "Parameter Information cannot be derived from SQL
> > > statements
> > > > with sub-select queries. Set parameter information before preparing
> > > command."
> > > >
> > > > But the parametere exists under Layout/Reports/Report Parameters and
> the
> > > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > > @.title =Parameters!title.Value
> > > >
> > > > Can some one try this out to see if you can succeed?
> > > >
> > > > Thanks,
> > > >
> > > > Tao
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > Make sure that you have two report parameters. In layout, menu
> > > > > reports->report parameter. Next go to your dataset, ... , parameters
> tab
> > > and
> > > > > make sure that each of your query parameters are mapped to a report
> > > > > parameter. Being a subquery or not really doesn't matter.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > > Hi All,
> > > > > >
> > > > > > There are two parameters in our DataSet based on the query below.
> > > > > >
> > > > > > @.Year causes "syntax or access violation" error, because it's in
> > > subquery.
> > > > > > @.Parameter2 works (in the main query)
> > > > > > ----
> > > > > > SELECT *,
> > > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > > THEN 'Green'
> > > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > > THEN 'Red' ELSE 'Black'
> > > > > > END) AS Avail_color_MTD,
> > > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > > THEN 'Red' ELSE 'Black' END
> > > > > > ) AS Resp_color_MTD
> > > > > > FROM (
> > > > > > SELECT
> > > > > > Sla_Name_By_Loc,
> > > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > > FROM
> > > > > > Sla_Results_Trans_BY_LOC
> > > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > > AND (Sla_Month_by_loc = 9)
> > > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > > GROUP BY
> > > > > > Sla_Name_By_Loc) Y
> > > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > > ORDER BY Sla_Name_By_Loc
> > > > > > ---
> > > > > >
> > > > > > Please help!
> > > > > >
> > > > > > Tao
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
There are two parameters in our DataSet based on the query below.
@.Year causes "syntax or access violation" error, because it's in subquery.
@.Parameter2 works (in the main query)
----
SELECT *,
( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
THEN 'Green'
WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
WHEN Av_MTD < ISNULL(Av_Comm,0)
THEN 'Red' ELSE 'Black'
END) AS Avail_color_MTD,
( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
THEN 'Red' ELSE 'Black' END
) AS Resp_color_MTD
FROM (
SELECT
Sla_Name_By_Loc,
MAX(Sla_Definition) AS Sla_Definition,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Commitment_By_Loc END) AS Av_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Commitment_By_Loc END) AS Re_Comm,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
Sla_Result_by_loc END) AS Av_MTD,
MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
Sla_Result_by_loc END) AS Re_MTD
FROM
Sla_Results_Trans_BY_LOC
WHERE (Sla_Category_id_By_Loc = 2)
AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
AND (Sla_Month_by_loc = 9)
AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
GROUP BY
Sla_Name_By_Loc) Y
where Y.Re_Comm = @.Parameter2 -- works!
ORDER BY Sla_Name_By_Loc
---
Please help!
TaoMake sure that you have two report parameters. In layout, menu
reports->report parameter. Next go to your dataset, ... , parameters tab and
make sure that each of your query parameters are mapped to a report
parameter. Being a subquery or not really doesn't matter.
Bruce L-C
"Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> Hi All,
> There are two parameters in our DataSet based on the query below.
> @.Year causes "syntax or access violation" error, because it's in subquery.
> @.Parameter2 works (in the main query)
> ----
> SELECT *,
> ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> THEN 'Green'
> WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> WHEN Av_MTD < ISNULL(Av_Comm,0)
> THEN 'Red' ELSE 'Black'
> END) AS Avail_color_MTD,
> ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> THEN 'Red' ELSE 'Black' END
> ) AS Resp_color_MTD
> FROM (
> SELECT
> Sla_Name_By_Loc,
> MAX(Sla_Definition) AS Sla_Definition,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Commitment_By_Loc END) AS Av_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Commitment_By_Loc END) AS Re_Comm,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> Sla_Result_by_loc END) AS Av_MTD,
> MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> Sla_Result_by_loc END) AS Re_MTD
> FROM
> Sla_Results_Trans_BY_LOC
> WHERE (Sla_Category_id_By_Loc = 2)
> AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> AND (Sla_Month_by_loc = 9)
> AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> GROUP BY
> Sla_Name_By_Loc) Y
> where Y.Re_Comm = @.Parameter2 -- works!
> ORDER BY Sla_Name_By_Loc
> ---
> Please help!
> Tao|||Such a dataset would fail:
(pubs database)
SELECT au_lname
FROM authors
WHERE (au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id = @.title))
Error message: "Parameter Information cannot be derived from SQL statements
with sub-select queries. Set parameter information before preparing command."
But the parametere exists under Layout/Reports/Report Parameters and the
parameter mapping is there in the Dataset/Edit/Parameters tab:
@.title =Parameters!title.Value
Can some one try this out to see if you can succeed?
Thanks,
Tao
"Bruce Loehle-Conger" wrote:
> Make sure that you have two report parameters. In layout, menu
> reports->report parameter. Next go to your dataset, ... , parameters tab and
> make sure that each of your query parameters are mapped to a report
> parameter. Being a subquery or not really doesn't matter.
> Bruce L-C
> "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > Hi All,
> >
> > There are two parameters in our DataSet based on the query below.
> >
> > @.Year causes "syntax or access violation" error, because it's in subquery.
> > @.Parameter2 works (in the main query)
> > ----
> > SELECT *,
> > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > THEN 'Green'
> > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > THEN 'Red' ELSE 'Black'
> > END) AS Avail_color_MTD,
> > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > THEN 'Red' ELSE 'Black' END
> > ) AS Resp_color_MTD
> > FROM (
> > SELECT
> > Sla_Name_By_Loc,
> > MAX(Sla_Definition) AS Sla_Definition,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Commitment_By_Loc END) AS Av_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Commitment_By_Loc END) AS Re_Comm,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%' THEN
> > Sla_Result_by_loc END) AS Av_MTD,
> > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%' THEN
> > Sla_Result_by_loc END) AS Re_MTD
> > FROM
> > Sla_Results_Trans_BY_LOC
> > WHERE (Sla_Category_id_By_Loc = 2)
> > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > AND (Sla_Month_by_loc = 9)
> > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > GROUP BY
> > Sla_Name_By_Loc) Y
> > where Y.Re_Comm = @.Parameter2 -- works!
> > ORDER BY Sla_Name_By_Loc
> > ---
> >
> > Please help!
> >
> > Tao
>
>|||Got the solution for you. If you use the generic query instead (click on the
button next to the !) then it will work. I think you have come across a
limitation of the graphical designer to deal with subqueries and parameters.
Works just the way you want from the query designer.
Bruce L-C [MVP Reporting Services]
"Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> Such a dataset would fail:
> (pubs database)
> SELECT au_lname
> FROM authors
> WHERE (au_id IN
> (SELECT au_id
> FROM titleauthor
> WHERE title_id = @.title))
> Error message: "Parameter Information cannot be derived from SQL
statements
> with sub-select queries. Set parameter information before preparing
command."
> But the parametere exists under Layout/Reports/Report Parameters and the
> parameter mapping is there in the Dataset/Edit/Parameters tab:
> @.title =Parameters!title.Value
> Can some one try this out to see if you can succeed?
> Thanks,
> Tao
> "Bruce Loehle-Conger" wrote:
> > Make sure that you have two report parameters. In layout, menu
> > reports->report parameter. Next go to your dataset, ... , parameters tab
and
> > make sure that each of your query parameters are mapped to a report
> > parameter. Being a subquery or not really doesn't matter.
> >
> > Bruce L-C
> >
> > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > Hi All,
> > >
> > > There are two parameters in our DataSet based on the query below.
> > >
> > > @.Year causes "syntax or access violation" error, because it's in
subquery.
> > > @.Parameter2 works (in the main query)
> > > ----
> > > SELECT *,
> > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > THEN 'Green'
> > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > THEN 'Red' ELSE 'Black'
> > > END) AS Avail_color_MTD,
> > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > THEN 'Red' ELSE 'Black' END
> > > ) AS Resp_color_MTD
> > > FROM (
> > > SELECT
> > > Sla_Name_By_Loc,
> > > MAX(Sla_Definition) AS Sla_Definition,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
THEN
> > > Sla_Result_by_loc END) AS Av_MTD,
> > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
THEN
> > > Sla_Result_by_loc END) AS Re_MTD
> > > FROM
> > > Sla_Results_Trans_BY_LOC
> > > WHERE (Sla_Category_id_By_Loc = 2)
> > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > AND (Sla_Month_by_loc = 9)
> > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > GROUP BY
> > > Sla_Name_By_Loc) Y
> > > where Y.Re_Comm = @.Parameter2 -- works!
> > > ORDER BY Sla_Name_By_Loc
> > > ---
> > >
> > > Please help!
> > >
> > > Tao
> >
> >
> >|||I am having the same problem.
I have selected the Generic Query pane and it let me then enter a parameter
value.
It ran but showed no data. The field names were shown but no data was shown.
Any help would be appreciated. Thank you.
"Bruce Loehle-Conger [MVP]" wrote:
> Got the solution for you. If you use the generic query instead (click on the
> button next to the !) then it will work. I think you have come across a
> limitation of the graphical designer to deal with subqueries and parameters.
> Works just the way you want from the query designer.
> Bruce L-C [MVP Reporting Services]
> "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > Such a dataset would fail:
> >
> > (pubs database)
> > SELECT au_lname
> > FROM authors
> > WHERE (au_id IN
> > (SELECT au_id
> > FROM titleauthor
> > WHERE title_id = @.title))
> >
> > Error message: "Parameter Information cannot be derived from SQL
> statements
> > with sub-select queries. Set parameter information before preparing
> command."
> >
> > But the parametere exists under Layout/Reports/Report Parameters and the
> > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > @.title =Parameters!title.Value
> >
> > Can some one try this out to see if you can succeed?
> >
> > Thanks,
> >
> > Tao
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Make sure that you have two report parameters. In layout, menu
> > > reports->report parameter. Next go to your dataset, ... , parameters tab
> and
> > > make sure that each of your query parameters are mapped to a report
> > > parameter. Being a subquery or not really doesn't matter.
> > >
> > > Bruce L-C
> > >
> > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > Hi All,
> > > >
> > > > There are two parameters in our DataSet based on the query below.
> > > >
> > > > @.Year causes "syntax or access violation" error, because it's in
> subquery.
> > > > @.Parameter2 works (in the main query)
> > > > ----
> > > > SELECT *,
> > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > THEN 'Green'
> > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > THEN 'Red' ELSE 'Black'
> > > > END) AS Avail_color_MTD,
> > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > THEN 'Red' ELSE 'Black' END
> > > > ) AS Resp_color_MTD
> > > > FROM (
> > > > SELECT
> > > > Sla_Name_By_Loc,
> > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Avail%'
> THEN
> > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE 'Respo%'
> THEN
> > > > Sla_Result_by_loc END) AS Re_MTD
> > > > FROM
> > > > Sla_Results_Trans_BY_LOC
> > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > AND (Sla_Month_by_loc = 9)
> > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > GROUP BY
> > > > Sla_Name_By_Loc) Y
> > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > ORDER BY Sla_Name_By_Loc
> > > > ---
> > > >
> > > > Please help!
> > > >
> > > > Tao
> > >
> > >
> > >
>
>|||Not the same problem. He was getting an error when trying to execute it. If
you are getting the field list back then that means that it is executing
correctly. Most likely something is not being specified correctly for your
parameter when you are prompted. What database are you going against (SQL
Server 2000?), what is the datatype of the field you are querying against?
How are you putting in the value when prompted for the parameter when you
execute it from the generic query pane.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> I am having the same problem.
> I have selected the Generic Query pane and it let me then enter a
parameter
> value.
> It ran but showed no data. The field names were shown but no data was
shown.
> Any help would be appreciated. Thank you.
> "Bruce Loehle-Conger [MVP]" wrote:
> > Got the solution for you. If you use the generic query instead (click on
the
> > button next to the !) then it will work. I think you have come across a
> > limitation of the graphical designer to deal with subqueries and
parameters.
> > Works just the way you want from the query designer.
> >
> > Bruce L-C [MVP Reporting Services]
> >
> > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > Such a dataset would fail:
> > >
> > > (pubs database)
> > > SELECT au_lname
> > > FROM authors
> > > WHERE (au_id IN
> > > (SELECT au_id
> > > FROM titleauthor
> > > WHERE title_id = @.title))
> > >
> > > Error message: "Parameter Information cannot be derived from SQL
> > statements
> > > with sub-select queries. Set parameter information before preparing
> > command."
> > >
> > > But the parametere exists under Layout/Reports/Report Parameters and
the
> > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > @.title =Parameters!title.Value
> > >
> > > Can some one try this out to see if you can succeed?
> > >
> > > Thanks,
> > >
> > > Tao
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > Make sure that you have two report parameters. In layout, menu
> > > > reports->report parameter. Next go to your dataset, ... , parameters
tab
> > and
> > > > make sure that each of your query parameters are mapped to a report
> > > > parameter. Being a subquery or not really doesn't matter.
> > > >
> > > > Bruce L-C
> > > >
> > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > Hi All,
> > > > >
> > > > > There are two parameters in our DataSet based on the query below.
> > > > >
> > > > > @.Year causes "syntax or access violation" error, because it's in
> > subquery.
> > > > > @.Parameter2 works (in the main query)
> > > > > ----
> > > > > SELECT *,
> > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > THEN 'Green'
> > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > THEN 'Red' ELSE 'Black'
> > > > > END) AS Avail_color_MTD,
> > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > THEN 'Red' ELSE 'Black' END
> > > > > ) AS Resp_color_MTD
> > > > > FROM (
> > > > > SELECT
> > > > > Sla_Name_By_Loc,
> > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Avail%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
'Respo%'
> > THEN
> > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > FROM
> > > > > Sla_Results_Trans_BY_LOC
> > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > AND (Sla_Month_by_loc = 9)
> > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > GROUP BY
> > > > > Sla_Name_By_Loc) Y
> > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > ORDER BY Sla_Name_By_Loc
> > > > > ---
> > > > >
> > > > > Please help!
> > > > >
> > > > > Tao
> > > >
> > > >
> > > >
> >
> >
> >|||Thank you very much for responding.
Problem fixed. When I clicked back from the Generic SQL Query and added more
fields, my query formatting changed somehow. Now it works great.
Thanks alot.
"Bruce L-C [MVP]" wrote:
> Not the same problem. He was getting an error when trying to execute it. If
> you are getting the field list back then that means that it is executing
> correctly. Most likely something is not being specified correctly for your
> parameter when you are prompted. What database are you going against (SQL
> Server 2000?), what is the datatype of the field you are querying against?
> How are you putting in the value when prompted for the parameter when you
> execute it from the generic query pane.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "wilerwin" <wilerwin@.discussions.microsoft.com> wrote in message
> news:514A5CDE-79F4-47F0-AB3B-EDAB30A19895@.microsoft.com...
> > I am having the same problem.
> > I have selected the Generic Query pane and it let me then enter a
> parameter
> > value.
> > It ran but showed no data. The field names were shown but no data was
> shown.
> > Any help would be appreciated. Thank you.
> >
> > "Bruce Loehle-Conger [MVP]" wrote:
> >
> > > Got the solution for you. If you use the generic query instead (click on
> the
> > > button next to the !) then it will work. I think you have come across a
> > > limitation of the graphical designer to deal with subqueries and
> parameters.
> > > Works just the way you want from the query designer.
> > >
> > > Bruce L-C [MVP Reporting Services]
> > >
> > > "Tao Zuo" <TaoZuo@.discussions.microsoft.com> wrote in message
> > > news:0E099BE5-CD05-4B94-BD2C-EB9F0DB5D908@.microsoft.com...
> > > > Such a dataset would fail:
> > > >
> > > > (pubs database)
> > > > SELECT au_lname
> > > > FROM authors
> > > > WHERE (au_id IN
> > > > (SELECT au_id
> > > > FROM titleauthor
> > > > WHERE title_id = @.title))
> > > >
> > > > Error message: "Parameter Information cannot be derived from SQL
> > > statements
> > > > with sub-select queries. Set parameter information before preparing
> > > command."
> > > >
> > > > But the parametere exists under Layout/Reports/Report Parameters and
> the
> > > > parameter mapping is there in the Dataset/Edit/Parameters tab:
> > > > @.title =Parameters!title.Value
> > > >
> > > > Can some one try this out to see if you can succeed?
> > > >
> > > > Thanks,
> > > >
> > > > Tao
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > Make sure that you have two report parameters. In layout, menu
> > > > > reports->report parameter. Next go to your dataset, ... , parameters
> tab
> > > and
> > > > > make sure that each of your query parameters are mapped to a report
> > > > > parameter. Being a subquery or not really doesn't matter.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "Tao Zuo" <Tao Zuo@.discussions.microsoft.com> wrote in message
> > > > > news:69F8B719-B789-4DFC-8D6E-20ECD6430B7E@.microsoft.com...
> > > > > > Hi All,
> > > > > >
> > > > > > There are two parameters in our DataSet based on the query below.
> > > > > >
> > > > > > @.Year causes "syntax or access violation" error, because it's in
> > > subquery.
> > > > > > @.Parameter2 works (in the main query)
> > > > > > ----
> > > > > > SELECT *,
> > > > > > ( CASE WHEN Av_MTD >= ISNULL(Av_Comm, 0)
> > > > > > THEN 'Green'
> > > > > > WHEN Av_MTD >= (Av_Comm) * .9 THEN 'Yellow'
> > > > > > WHEN Av_MTD < ISNULL(Av_Comm,0)
> > > > > > THEN 'Red' ELSE 'Black'
> > > > > > END) AS Avail_color_MTD,
> > > > > > ( CASE WHEN Re_MTD <= ISNULL(Re_Comm, 0)
> > > > > > THEN 'Green' WHEN Re_MTD <= (Re_Comm) * 1.1
> > > > > > THEN 'Yellow' WHEN Re_MTD > ISNULL(Re_Comm, 0)
> > > > > > THEN 'Red' ELSE 'Black' END
> > > > > > ) AS Resp_color_MTD
> > > > > > FROM (
> > > > > > SELECT
> > > > > > Sla_Name_By_Loc,
> > > > > > MAX(Sla_Definition) AS Sla_Definition,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Av_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Commitment_By_Loc END) AS Re_Comm,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Avail%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Av_MTD,
> > > > > > MAX(CASE WHEN Sla_Type_By_Loc LIKE
> 'Respo%'
> > > THEN
> > > > > > Sla_Result_by_loc END) AS Re_MTD
> > > > > > FROM
> > > > > > Sla_Results_Trans_BY_LOC
> > > > > > WHERE (Sla_Category_id_By_Loc = 2)
> > > > > > AND (Sla_Year_By_Loc = @.Year) -- Syntax Error Or Access Violation
> > > > > > AND (Sla_Month_by_loc = 9)
> > > > > > AND (Sla_Pete_Clients_Code_by_loc = 'NJ01')
> > > > > > GROUP BY
> > > > > > Sla_Name_By_Loc) Y
> > > > > > where Y.Re_Comm = @.Parameter2 -- works!
> > > > > > ORDER BY Sla_Name_By_Loc
> > > > > > ---
> > > > > >
> > > > > > Please help!
> > > > > >
> > > > > > Tao
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
Friday, March 23, 2012
Report Model Security
Hello
Is it possible to filter data within a report model (.smdl). We have a
generic report model that we wish to limit, based on user credentials?
Kind regards
Ricky
(SS2005)Ricky,
Yes that's possible. Have a look at the documentation under "Security
Filters".
Also a good summary can be found here:
http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
You'll use the GetUserId() function in the model to tie to your data.
-Krip|||Thanks Krip.
Incidentally, did you manage to get your issues resolved?
"Krip" <anonymous@.anonymous.com> wrote in message
news:C71CBA81-089F-4091-9C6D-21F71FAA7AAB@.microsoft.com...
> Ricky,
> Yes that's possible. Have a look at the documentation under "Security
> Filters".
> Also a good summary can be found here:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
> You'll use the GetUserId() function in the model to tie to your data.
> -Krip
>
Is it possible to filter data within a report model (.smdl). We have a
generic report model that we wish to limit, based on user credentials?
Kind regards
Ricky
(SS2005)Ricky,
Yes that's possible. Have a look at the documentation under "Security
Filters".
Also a good summary can be found here:
http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
You'll use the GetUserId() function in the model to tie to your data.
-Krip|||Thanks Krip.
Incidentally, did you manage to get your issues resolved?
"Krip" <anonymous@.anonymous.com> wrote in message
news:C71CBA81-089F-4091-9C6D-21F71FAA7AAB@.microsoft.com...
> Ricky,
> Yes that's possible. Have a look at the documentation under "Security
> Filters".
> Also a good summary can be found here:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
> You'll use the GetUserId() function in the model to tie to your data.
> -Krip
>
Wednesday, March 21, 2012
Report Model DSV Data Source change.
I have a Data Source View that is based on DS_Dev and all the table/named queries are based on this Data Set. I want to point the DSV to DS_Prod, but when I refresh the DSV it is still trying to look at DS_Dev. I know I must be overlooking something. How can I change all the tables in the DSV to point to the new data source?
I'm assuming I have to replace each table with the corresponding table in the new data source. I hope not.sqlReport Model Data Security
Hello
I have designed a report model, but now have a requirement to limit data
access to certain people. I have tried researching about Role based
security, but nothing seems to make sense. I have to design a suite of
reports 16 in total. However,I have 8 users, all looking at data from
difference business departments. It has been requested that each department
cannot look at another department's data. The thought of cloning these 16
reports, 8 times over, does not seem like a viable option.
Is there an alternative?
Kind regards
Ricky
(SS2005)If you are saying that each report needs to be viewed by each dept and that
the data should be different on the report depending on who is viewing it
then:
On your reports you can have a hidden extra parameter that defaults to the
user id.
Then pass this id into your stored procedures that generate the data.
The stored procedures will do the filtering based on your own internal logic.
"RickyP" wrote:
> Hello
> I have designed a report model, but now have a requirement to limit data
> access to certain people. I have tried researching about Role based
> security, but nothing seems to make sense. I have to design a suite of
> reports 16 in total. However,I have 8 users, all looking at data from
> difference business departments. It has been requested that each department
> cannot look at another department's data. The thought of cloning these 16
> reports, 8 times over, does not seem like a viable option.
> Is there an alternative?
> Kind regards
> Ricky
> (SS2005)
>|||Hi Jimbo
Thanks for your post, but the model cannot not based on an SP, it is created
from a View.
Kind regards
Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> If you are saying that each report needs to be viewed by each dept and
> that
> the data should be different on the report depending on who is viewing it
> then:
> On your reports you can have a hidden extra parameter that defaults to the
> user id.
> Then pass this id into your stored procedures that generate the data.
> The stored procedures will do the filtering based on your own internal
> logic.
>
>
>
> "RickyP" wrote:
>> Hello
>> I have designed a report model, but now have a requirement to limit data
>> access to certain people. I have tried researching about Role based
>> security, but nothing seems to make sense. I have to design a suite of
>> reports 16 in total. However,I have 8 users, all looking at data from
>> difference business departments. It has been requested that each
>> department
>> cannot look at another department's data. The thought of cloning these
>> 16
>> reports, 8 times over, does not seem like a viable option.
>> Is there an alternative?
>> Kind regards
>> Ricky
>> (SS2005)|||Your sp can read from the view :)
"RickyP" wrote:
> Hi Jimbo
> Thanks for your post, but the model cannot not based on an SP, it is created
> from a View.
> Kind regards
> Ricky
> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> > If you are saying that each report needs to be viewed by each dept and
> > that
> > the data should be different on the report depending on who is viewing it
> > then:
> >
> > On your reports you can have a hidden extra parameter that defaults to the
> > user id.
> >
> > Then pass this id into your stored procedures that generate the data.
> >
> > The stored procedures will do the filtering based on your own internal
> > logic.
> >
> >
> >
> >
> >
> >
> >
> > "RickyP" wrote:
> >
> >> Hello
> >>
> >> I have designed a report model, but now have a requirement to limit data
> >> access to certain people. I have tried researching about Role based
> >> security, but nothing seems to make sense. I have to design a suite of
> >> reports 16 in total. However,I have 8 users, all looking at data from
> >> difference business departments. It has been requested that each
> >> department
> >> cannot look at another department's data. The thought of cloning these
> >> 16
> >> reports, 8 times over, does not seem like a viable option.
> >>
> >> Is there an alternative?
> >>
> >> Kind regards
> >>
> >> Ricky
> >> (SS2005)
> >>
>|||Hi Jimbo
Thanks, but I think you must be confused, or maybe I have not stated my
scenario clearly. I have built a Report Model, to be used in Report
Builder, there is no facility to use SP's, maybe I have misunderstood what
you mean?
Kind regards
~Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:2CB0DA49-ED42-4E2F-8326-93322C0F429A@.microsoft.com...
> Your sp can read from the view :)
>
>
> "RickyP" wrote:
>> Hi Jimbo
>> Thanks for your post, but the model cannot not based on an SP, it is
>> created
>> from a View.
>> Kind regards
>> Ricky
>> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
>> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
>> > If you are saying that each report needs to be viewed by each dept and
>> > that
>> > the data should be different on the report depending on who is viewing
>> > it
>> > then:
>> >
>> > On your reports you can have a hidden extra parameter that defaults to
>> > the
>> > user id.
>> >
>> > Then pass this id into your stored procedures that generate the data.
>> >
>> > The stored procedures will do the filtering based on your own internal
>> > logic.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "RickyP" wrote:
>> >
>> >> Hello
>> >>
>> >> I have designed a report model, but now have a requirement to limit
>> >> data
>> >> access to certain people. I have tried researching about Role based
>> >> security, but nothing seems to make sense. I have to design a suite
>> >> of
>> >> reports 16 in total. However,I have 8 users, all looking at data from
>> >> difference business departments. It has been requested that each
>> >> department
>> >> cannot look at another department's data. The thought of cloning
>> >> these
>> >> 16
>> >> reports, 8 times over, does not seem like a viable option.
>> >>
>> >> Is there an alternative?
>> >>
>> >> Kind regards
>> >>
>> >> Ricky
>> >> (SS2005)
>> >>
>>|||Hello!
Im in the same situation as you. What we have done is that we have used the
feature "Default Security Filter" which allows
you to filter data using an entity which you base upon your view containing
DepartmentID/userID. The entity should also include
an attribute which contains the calculated "GETUSERID()".
You then specify a reliation between your entities to be filtered and this
filter-entity and you also set the "Default Security Attribute"
to be this filter-entity.
Thing is that this causes RS to produce even worse-looking SQL and makes it
hard to debug so Im currently trying to
figure out some other way, because it is almost impossible to debug 300
lines of SQL (most of it rs-schmuck).
Anyway - you can try it out, but try and read up on it on the web somewhere
first...
David|||Hi David
Thanks for your post, it seems like there isn't much in the way of security
on the net at the moment, I can't believe we're the only ones trying to
achieve this? I look in to this further - thanks Daivd.
Kind regards
Ricky
"David" <david_sundstrom_sandegard.nospam@.hotmail.com> wrote in message
news:2C9C703C-BA30-4EAA-ABEA-79F0DE457287@.microsoft.com...
> Hello!
> Im in the same situation as you. What we have done is that we have used
> the feature "Default Security Filter" which allows
> you to filter data using an entity which you base upon your view
> containing DepartmentID/userID. The entity should also include
> an attribute which contains the calculated "GETUSERID()".
> You then specify a reliation between your entities to be filtered and this
> filter-entity and you also set the "Default Security Attribute"
> to be this filter-entity.
> Thing is that this causes RS to produce even worse-looking SQL and makes
> it hard to debug so Im currently trying to
> figure out some other way, because it is almost impossible to debug 300
> lines of SQL (most of it rs-schmuck).
> Anyway - you can try it out, but try and read up on it on the web
> somewhere first...
> David
I have designed a report model, but now have a requirement to limit data
access to certain people. I have tried researching about Role based
security, but nothing seems to make sense. I have to design a suite of
reports 16 in total. However,I have 8 users, all looking at data from
difference business departments. It has been requested that each department
cannot look at another department's data. The thought of cloning these 16
reports, 8 times over, does not seem like a viable option.
Is there an alternative?
Kind regards
Ricky
(SS2005)If you are saying that each report needs to be viewed by each dept and that
the data should be different on the report depending on who is viewing it
then:
On your reports you can have a hidden extra parameter that defaults to the
user id.
Then pass this id into your stored procedures that generate the data.
The stored procedures will do the filtering based on your own internal logic.
"RickyP" wrote:
> Hello
> I have designed a report model, but now have a requirement to limit data
> access to certain people. I have tried researching about Role based
> security, but nothing seems to make sense. I have to design a suite of
> reports 16 in total. However,I have 8 users, all looking at data from
> difference business departments. It has been requested that each department
> cannot look at another department's data. The thought of cloning these 16
> reports, 8 times over, does not seem like a viable option.
> Is there an alternative?
> Kind regards
> Ricky
> (SS2005)
>|||Hi Jimbo
Thanks for your post, but the model cannot not based on an SP, it is created
from a View.
Kind regards
Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> If you are saying that each report needs to be viewed by each dept and
> that
> the data should be different on the report depending on who is viewing it
> then:
> On your reports you can have a hidden extra parameter that defaults to the
> user id.
> Then pass this id into your stored procedures that generate the data.
> The stored procedures will do the filtering based on your own internal
> logic.
>
>
>
> "RickyP" wrote:
>> Hello
>> I have designed a report model, but now have a requirement to limit data
>> access to certain people. I have tried researching about Role based
>> security, but nothing seems to make sense. I have to design a suite of
>> reports 16 in total. However,I have 8 users, all looking at data from
>> difference business departments. It has been requested that each
>> department
>> cannot look at another department's data. The thought of cloning these
>> 16
>> reports, 8 times over, does not seem like a viable option.
>> Is there an alternative?
>> Kind regards
>> Ricky
>> (SS2005)|||Your sp can read from the view :)
"RickyP" wrote:
> Hi Jimbo
> Thanks for your post, but the model cannot not based on an SP, it is created
> from a View.
> Kind regards
> Ricky
> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> > If you are saying that each report needs to be viewed by each dept and
> > that
> > the data should be different on the report depending on who is viewing it
> > then:
> >
> > On your reports you can have a hidden extra parameter that defaults to the
> > user id.
> >
> > Then pass this id into your stored procedures that generate the data.
> >
> > The stored procedures will do the filtering based on your own internal
> > logic.
> >
> >
> >
> >
> >
> >
> >
> > "RickyP" wrote:
> >
> >> Hello
> >>
> >> I have designed a report model, but now have a requirement to limit data
> >> access to certain people. I have tried researching about Role based
> >> security, but nothing seems to make sense. I have to design a suite of
> >> reports 16 in total. However,I have 8 users, all looking at data from
> >> difference business departments. It has been requested that each
> >> department
> >> cannot look at another department's data. The thought of cloning these
> >> 16
> >> reports, 8 times over, does not seem like a viable option.
> >>
> >> Is there an alternative?
> >>
> >> Kind regards
> >>
> >> Ricky
> >> (SS2005)
> >>
>|||Hi Jimbo
Thanks, but I think you must be confused, or maybe I have not stated my
scenario clearly. I have built a Report Model, to be used in Report
Builder, there is no facility to use SP's, maybe I have misunderstood what
you mean?
Kind regards
~Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:2CB0DA49-ED42-4E2F-8326-93322C0F429A@.microsoft.com...
> Your sp can read from the view :)
>
>
> "RickyP" wrote:
>> Hi Jimbo
>> Thanks for your post, but the model cannot not based on an SP, it is
>> created
>> from a View.
>> Kind regards
>> Ricky
>> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
>> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
>> > If you are saying that each report needs to be viewed by each dept and
>> > that
>> > the data should be different on the report depending on who is viewing
>> > it
>> > then:
>> >
>> > On your reports you can have a hidden extra parameter that defaults to
>> > the
>> > user id.
>> >
>> > Then pass this id into your stored procedures that generate the data.
>> >
>> > The stored procedures will do the filtering based on your own internal
>> > logic.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "RickyP" wrote:
>> >
>> >> Hello
>> >>
>> >> I have designed a report model, but now have a requirement to limit
>> >> data
>> >> access to certain people. I have tried researching about Role based
>> >> security, but nothing seems to make sense. I have to design a suite
>> >> of
>> >> reports 16 in total. However,I have 8 users, all looking at data from
>> >> difference business departments. It has been requested that each
>> >> department
>> >> cannot look at another department's data. The thought of cloning
>> >> these
>> >> 16
>> >> reports, 8 times over, does not seem like a viable option.
>> >>
>> >> Is there an alternative?
>> >>
>> >> Kind regards
>> >>
>> >> Ricky
>> >> (SS2005)
>> >>
>>|||Hello!
Im in the same situation as you. What we have done is that we have used the
feature "Default Security Filter" which allows
you to filter data using an entity which you base upon your view containing
DepartmentID/userID. The entity should also include
an attribute which contains the calculated "GETUSERID()".
You then specify a reliation between your entities to be filtered and this
filter-entity and you also set the "Default Security Attribute"
to be this filter-entity.
Thing is that this causes RS to produce even worse-looking SQL and makes it
hard to debug so Im currently trying to
figure out some other way, because it is almost impossible to debug 300
lines of SQL (most of it rs-schmuck).
Anyway - you can try it out, but try and read up on it on the web somewhere
first...
David|||Hi David
Thanks for your post, it seems like there isn't much in the way of security
on the net at the moment, I can't believe we're the only ones trying to
achieve this? I look in to this further - thanks Daivd.
Kind regards
Ricky
"David" <david_sundstrom_sandegard.nospam@.hotmail.com> wrote in message
news:2C9C703C-BA30-4EAA-ABEA-79F0DE457287@.microsoft.com...
> Hello!
> Im in the same situation as you. What we have done is that we have used
> the feature "Default Security Filter" which allows
> you to filter data using an entity which you base upon your view
> containing DepartmentID/userID. The entity should also include
> an attribute which contains the calculated "GETUSERID()".
> You then specify a reliation between your entities to be filtered and this
> filter-entity and you also set the "Default Security Attribute"
> to be this filter-entity.
> Thing is that this causes RS to produce even worse-looking SQL and makes
> it hard to debug so Im currently trying to
> figure out some other way, because it is almost impossible to debug 300
> lines of SQL (most of it rs-schmuck).
> Anyway - you can try it out, but try and read up on it on the web
> somewhere first...
> David
Tuesday, March 20, 2012
Report Model - Custom DataSource
Hi!
We have implemented a custom DataProcessingExtension for Reporting Services
2005.
Now we want to implement a Report Model based on this DataProcessingExtension
(for the usage in ReportBuilder).
Can can I accomplish this, because as the documentation says you can only
create a Report Model based on a SQL Server database or Analysis Server database?
Thanks for your help
Klaus Aschenbrenner
www.csharp.at
http://www.sqljunkies.com/weblog/klaus.aschenbrenneHi Klaus,
Were you ever able to make your custom DataProcessingExtension support
Report Models?
I'd be interested in hearing how it went...
Jeremy Wiebe
Klaus Aschenbrenner wrote:
> Hi!
> We have implemented a custom DataProcessingExtension for Reporting Services
> 2005.
> Now we want to implement a Report Model based on this DataProcessingExtension
> (for the usage in ReportBuilder).
> Can can I accomplish this, because as the documentation says you can only
> create a Report Model based on a SQL Server database or Analysis Server database?
> Thanks for your help
> Klaus Aschenbrenner
> www.csharp.at
> http://www.sqljunkies.com/weblog/klaus.aschenbrenner
We have implemented a custom DataProcessingExtension for Reporting Services
2005.
Now we want to implement a Report Model based on this DataProcessingExtension
(for the usage in ReportBuilder).
Can can I accomplish this, because as the documentation says you can only
create a Report Model based on a SQL Server database or Analysis Server database?
Thanks for your help
Klaus Aschenbrenner
www.csharp.at
http://www.sqljunkies.com/weblog/klaus.aschenbrenneHi Klaus,
Were you ever able to make your custom DataProcessingExtension support
Report Models?
I'd be interested in hearing how it went...
Jeremy Wiebe
Klaus Aschenbrenner wrote:
> Hi!
> We have implemented a custom DataProcessingExtension for Reporting Services
> 2005.
> Now we want to implement a Report Model based on this DataProcessingExtension
> (for the usage in ReportBuilder).
> Can can I accomplish this, because as the documentation says you can only
> create a Report Model based on a SQL Server database or Analysis Server database?
> Thanks for your help
> Klaus Aschenbrenner
> www.csharp.at
> http://www.sqljunkies.com/weblog/klaus.aschenbrenner
Report mdoel
Hi,
Is there any way of leveraging the new report model / ad hoc functionality
when reporting from xml data sources? Everything seems to be based around if
your data is actually in an xml format. Is this at all possible?
More specifically would it be possible if the xml data source was in fact a
web service?
I realise this is kind of quirky, it's just that I'm using RS to report off
a web service and now I'm wondering is there any way I coudl allow users to
simply build their own reports.
Any help / pointers would be much appreciated...
Thanks,
PhilXML data sources do not seem to be supported for model generation.
If you go to Report Manager, Data Sources, and look at the properties of a
SQL data source, you'll see a Generate Model button at the bottom. I thought
that if you had an XML data source, you would be able to generate a model
from there as well, however, that button disappears when the data source is
XML type.
"Phil Aldis" wrote:
> Hi,
> Is there any way of leveraging the new report model / ad hoc functionality
> when reporting from xml data sources? Everything seems to be based around if
> your data is actually in an xml format. Is this at all possible?
> More specifically would it be possible if the xml data source was in fact a
> web service?
> I realise this is kind of quirky, it's just that I'm using RS to report off
> a web service and now I'm wondering is there any way I coudl allow users to
> simply build their own reports.
> Any help / pointers would be much appreciated...
> Thanks,
> Phil
Is there any way of leveraging the new report model / ad hoc functionality
when reporting from xml data sources? Everything seems to be based around if
your data is actually in an xml format. Is this at all possible?
More specifically would it be possible if the xml data source was in fact a
web service?
I realise this is kind of quirky, it's just that I'm using RS to report off
a web service and now I'm wondering is there any way I coudl allow users to
simply build their own reports.
Any help / pointers would be much appreciated...
Thanks,
PhilXML data sources do not seem to be supported for model generation.
If you go to Report Manager, Data Sources, and look at the properties of a
SQL data source, you'll see a Generate Model button at the bottom. I thought
that if you had an XML data source, you would be able to generate a model
from there as well, however, that button disappears when the data source is
XML type.
"Phil Aldis" wrote:
> Hi,
> Is there any way of leveraging the new report model / ad hoc functionality
> when reporting from xml data sources? Everything seems to be based around if
> your data is actually in an xml format. Is this at all possible?
> More specifically would it be possible if the xml data source was in fact a
> web service?
> I realise this is kind of quirky, it's just that I'm using RS to report off
> a web service and now I'm wondering is there any way I coudl allow users to
> simply build their own reports.
> Any help / pointers would be much appreciated...
> Thanks,
> Phil
Subscribe to:
Posts (Atom)