Passing null parameters to subreports

This is similar to Carlos' question on 7/7/05 that nobody responded to.
Please respond.
(http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
a Parent report, I setup a hyperlink to jump to another report that accepts a
number of parameters (some of the parameters are defined as nullable in the
parent and the calling report). However when I try to preview the report and
click on the hyperlink cell passing parameters that are null in addition to
parameters that are populated, the subreport seems to hang and wait for
input. I tried so many variations of this and it just does't work. It only
works if all the parameters are populated, but that is not realistic.
Can someone please suggest a solution ASAP? This looks like a bug to me.
This is not rocket science here.
Thanks.
--
-RB
:)I had this problem too....
The solution is to manually change the .rdl file with the <Omit> element
under the
<Parameter> element...
Please check
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
for more details...
"capricorn" wrote:
> This is similar to Carlos' question on 7/7/05 that nobody responded to.
> Please respond.
> (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> a Parent report, I setup a hyperlink to jump to another report that accepts a
> number of parameters (some of the parameters are defined as nullable in the
> parent and the calling report). However when I try to preview the report and
> click on the hyperlink cell passing parameters that are null in addition to
> parameters that are populated, the subreport seems to hang and wait for
> input. I tried so many variations of this and it just does't work. It only
> works if all the parameters are populated, but that is not realistic.
> Can someone please suggest a solution ASAP? This looks like a bug to me.
> This is not rocket science here.
> Thanks.
> --
> -RB
> :)|||Hi Trisha.
Thank you for responding. It worked like a charm. What a relief.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||I also noticed that after I changed the .RDL file and go back into report
designer and save the report again for some other change, all my omit
statements are wiped out. Is there any way to stop this from happening?
Thanks.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||No, there is not. RS designer rewrites the RDL.
Something to try which I would like to hear if you are successfull is to map
the subreport parameter to an expression and put this in for the expression:
= Nothing
I think that might work for you but I am not sure since I have never needed
to do this myself. Let me know if it works. It would be easier than messing
with the RDL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"capricorn" <capricorn@.discussions.microsoft.com> wrote in message
news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable in
>> > the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)|||=Nothing will work for subreport and drillthrough report parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O9bbI%23HtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to
> map the subreport parameter to an expression and put this in for the
> expression:
> = Nothing
> I think that might work for you but I am not sure since I have never
> needed to do this myself. Let me know if it works. It would be easier than
> messing with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>>I also noticed that after I changed the .RDL file and go back into report
>> designer and save the report again for some other change, all my omit
>> statements are wiped out. Is there any way to stop this from happening?
>> Thanks.
>> --
>> -RB
>> :)
>>
>> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded
>> > to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable
>> > in the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait
>> > for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)
>|||Save the rdl file and then go to the designer...I've had no problems after
doing it this way....
"capricorn" wrote:
> I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
> > I had this problem too....
> > The solution is to manually change the .rdl file with the <Omit> element
> > under the
> > <Parameter> element...
> >
> > Please check
> > http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> > for more details...
> >
> >
> >
> > "capricorn" wrote:
> >
> > > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > > Please respond.
> > > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> > >
> > > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > > number of parameters (some of the parameters are defined as nullable in the
> > > parent and the calling report). However when I try to preview the report and
> > > click on the hyperlink cell passing parameters that are null in addition to
> > > parameters that are populated, the subreport seems to hang and wait for
> > > input. I tried so many variations of this and it just does't work. It only
> > > works if all the parameters are populated, but that is not realistic.
> > >
> > > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > > This is not rocket science here.
> > >
> > > Thanks.
> > > --
> > > -RB
> > > :)|||I tried mapping the drillthrough parameters to an expression that contains:
=Nothing
It did not work. Since one of the parameters is an integer datatype, I got
the following error message:
"The value provided for the report parameter ... is not valid for its type."
--
-RB
:)
"Bruce L-C [MVP]" wrote:
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to map
> the subreport parameter to an expression and put this in for the expression:
> = Nothing
> I think that might work for you but I am not sure since I have never needed
> to do this myself. Let me know if it works. It would be easier than messing
> with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
> >I also noticed that after I changed the .RDL file and go back into report
> > designer and save the report again for some other change, all my omit
> > statements are wiped out. Is there any way to stop this from happening?
> > Thanks.
> > --
> > -RB
> > :)
> >
> >
> > "Trisha" wrote:
> >
> >> I had this problem too....
> >> The solution is to manually change the .rdl file with the <Omit> element
> >> under the
> >> <Parameter> element...
> >>
> >> Please check
> >> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> >> for more details...
> >>
> >>
> >>
> >> "capricorn" wrote:
> >>
> >> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> >> > Please respond.
> >> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >> >
> >> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
> >> > cell of
> >> > a Parent report, I setup a hyperlink to jump to another report that
> >> > accepts a
> >> > number of parameters (some of the parameters are defined as nullable in
> >> > the
> >> > parent and the calling report). However when I try to preview the
> >> > report and
> >> > click on the hyperlink cell passing parameters that are null in
> >> > addition to
> >> > parameters that are populated, the subreport seems to hang and wait for
> >> > input. I tried so many variations of this and it just does't work. It
> >> > only
> >> > works if all the parameters are populated, but that is not realistic.
> >> >
> >> > Can someone please suggest a solution ASAP? This looks like a bug to
> >> > me.
> >> > This is not rocket science here.
> >> >
> >> > Thanks.
> >> > --
> >> > -RB
> >> > :)
>
>

Passing a variable to a Linked Query (OPENROWSET for Excel Syntax)

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)

but I have not gotten all the ''''' + @.variable syntax right.

Here is my raw openrowset with what I am aiming at.

Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game? Many thank-yous, in advance.

Kind Regards,

Claudia.

You can make use of the QUOTENAME function to help you out here.

I couldn't get the MSDASQL Excel driver to work on my desktop, but below is an example that uses the same principles but with the Jet Excel driver. Simply modify the values of the provider, connection string, filename and query variables as appropriate.

Chris

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.Provider NVARCHAR(100)

DECLARE @.FIL NVARCHAR(256)

DECLARE @.ConnectionString NVARCHAR(1000)

DECLARE @.Query NVARCHAR(1000)

SET @.Provider = N'Microsoft.Jet.OLEDB.4.0'

SET @.FIL = N'C:\Company Folders\Documentation\INVENTORY.xls'

SET @.ConnectionString = N'Excel 8.0;DATABASE=' + @.FIL

SET @.Query = N'SELECT * FROM [Inventory$]'

SET @.SQL = N'SELECT *

FROM OPENROWSET(' + QUOTENAME(@.Provider, N'''') + N', '

+ QUOTENAME(@.ConnectionString, N'''') + N', '

+ QUOTENAME(@.Query, N'''') + N')'

EXEC sp_executesql @.SQL