Passing OLAP-parameters between reports

I have to reports, ReportA and ReportB. Both uses datasets that are based on
OLAP cubes. Both reports has parameters and works fine.
My challenge:
When clicking in a field in ReportA, I want to navigate to ReportB, sending
the appropriate parameters from ReportA to ReportB. The first parameter I
send is the value of a fiield "Location", for instance "Seattle". However,
when I send this to reportB (which takes, amongst others, a location
parameter), the value "Seattle" is sent, and not the value which is
understood by ReportB's parameter, that is "[Dim Location].[Location].1".
To accomplsh my task I think I somehow need to print the value "[Dim
Location].[Location].1" in a hidden field in ReportA and send this fields
value to ReportB. But how do get both Seattle and [Dim
Location].[Location].1" as member values of the same OLAP dimension?
Hope I made my self clear enough here. It is a bit difficult to explain, and
don't hesitate to ask me if there is something you didn't understand.
Thanks in advance for your helpYou can return the OLAP dimension member's Unique name in your query, like
this:
with member [Measures].[MyName] as '[Gender].currentmember.name'
member [Measures].[MyLevelOrdinal] as '[Gender].currentmember.Level.Ordinal'
member [Measures].[MyUniqueName] as '[Gender].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]'
member [Measures].[MyMeasure5] as '[Measures].[Store Cost]'
member [Measures].[MyMeasure6] as '[Measures].[Store Sales]'
select {[Measures].[MyName], [Measures].[MyLevelOrdinal],
[Measures].[MyUniqueName], [Measures].[MyMeasure4], [Measures].[MyMeasure5],
[Measures].[MyMeasure6]} on columns,
{filter([Gender].members, [Measures].[Unit Sales] > 0) } on rows
from [Sales]
(try with Foodmart 2000)
In your case, this should return something like
MyName = Seattle
MyLevelOrdinal = Seattle's level ordinal
MyUniquName = [Dim Location].[Location].1
LIke: member [Measures].[MyUniqueName] as '[Dim
Location].currentmember.UniqueName'
Kaisa M. Lindahl
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:35966F0A-9112-487A-A8EC-BF6E3F2E39C3@.microsoft.com...
>I have to reports, ReportA and ReportB. Both uses datasets that are based
>on
> OLAP cubes. Both reports has parameters and works fine.
> My challenge:
> When clicking in a field in ReportA, I want to navigate to ReportB,
> sending
> the appropriate parameters from ReportA to ReportB. The first parameter I
> send is the value of a fiield "Location", for instance "Seattle". However,
> when I send this to reportB (which takes, amongst others, a location
> parameter), the value "Seattle" is sent, and not the value which is
> understood by ReportB's parameter, that is "[Dim Location].[Location].1".
>
> To accomplsh my task I think I somehow need to print the value "[Dim
> Location].[Location].1" in a hidden field in ReportA and send this fields
> value to ReportB. But how do get both Seattle and [Dim
> Location].[Location].1" as member values of the same OLAP dimension?
> Hope I made my self clear enough here. It is a bit difficult to explain,
> and
> don't hesitate to ask me if there is something you didn't understand.
> Thanks in advance for your help

Passing in a Parameter by URL but Not displaying in tool bar

Currently we're using an application and the Report Viewer to "view"
our reports. One report that we need to generate is solely based on
the user's SalesRepCode. So what we want is that each user select a
report and then pass there SalesRepCode which we get from the
application, through the query string to Reporting Services to
generate that user's specific report. We don't want to give the user
the ability to use another user's SalesRepCode so we don't specify a
prompt for this parameter in the report parameters dialog boxes. This
is how I'm passing the SalesRepCode value to reporting services (I
know that the query string can be modified so the security on this is
weak. If you have an alternative solution I would love to hear it).
Is there anyway to pass in a parameter and not have it displayed on
the toolbar?
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
This is the error I'm receiving
The report parameter 'SalesRepCode' is read-only and cannot be
modified. (rsReadOnlyReportParameter) Get Online HelpIn order to pass a parameter via the query string but hide it in the toolbar
you will need to be running Reporting Services SP1. This is a new feature
in SP1.
If you have SP1 then edit the parameter properties of the report via the
Report Manager and check "Prompt User" and clear out the value of "Prompt
String" for your SalesRepCode parameter.
--
erik perez
www.solien.com
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> Currently we're using an application and the Report Viewer to "view"
> our reports. One report that we need to generate is solely based on
> the user's SalesRepCode. So what we want is that each user select a
> report and then pass there SalesRepCode which we get from the
> application, through the query string to Reporting Services to
> generate that user's specific report. We don't want to give the user
> the ability to use another user's SalesRepCode so we don't specify a
> prompt for this parameter in the report parameters dialog boxes. This
> is how I'm passing the SalesRepCode value to reporting services (I
> know that the query string can be modified so the security on this is
> weak. If you have an alternative solution I would love to hear it).
> Is there anyway to pass in a parameter and not have it displayed on
> the toolbar?
>
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> This is the error I'm receiving
> The report parameter 'SalesRepCode' is read-only and cannot be
> modified. (rsReadOnlyReportParameter) Get Online Help|||Hey Erik
I installed the SP1 and tried what you suggested. It still doesn't
work. When I go into the reports manager and select my report and
then select parameters from the left side the list of parameters
appears. For my parameter has default is checked but there is no
value in the default text box. Prompt user is unchecked and prompt
string is SalesRepCode:
I still get the same error stating that the parameter is readonly.
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> In order to pass a parameter via the query string but hide it in the toolbar
> you will need to be running Reporting Services SP1. This is a new feature
> in SP1.
> If you have SP1 then edit the parameter properties of the report via the
> Report Manager and check "Prompt User" and clear out the value of "Prompt
> String" for your SalesRepCode parameter.
> --
> erik perez
> www.solien.com
> "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > Currently we're using an application and the Report Viewer to "view"
> > our reports. One report that we need to generate is solely based on
> > the user's SalesRepCode. So what we want is that each user select a
> > report and then pass there SalesRepCode which we get from the
> > application, through the query string to Reporting Services to
> > generate that user's specific report. We don't want to give the user
> > the ability to use another user's SalesRepCode so we don't specify a
> > prompt for this parameter in the report parameters dialog boxes. This
> > is how I'm passing the SalesRepCode value to reporting services (I
> > know that the query string can be modified so the security on this is
> > weak. If you have an alternative solution I would love to hear it).
> > Is there anyway to pass in a parameter and not have it displayed on
> > the toolbar?
> >
> >
> http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> >
> > This is the error I'm receiving
> >
> > The report parameter 'SalesRepCode' is read-only and cannot be
> > modified. (rsReadOnlyReportParameter) Get Online Help|||Ok. Now:
1) Check the "Prompt User" checkbox so it is active
2) Remove the "SalesRepCode" value inside the "Prompt String" text field so
that the field is empty
3) Apply and try feeding the code via the query string.
--
erik perez
www.solien.com
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409020907.14bd20de@.posting.google.com...
> Hey Erik
> I installed the SP1 and tried what you suggested. It still doesn't
> work. When I go into the reports manager and select my report and
> then select parameters from the left side the list of parameters
> appears. For my parameter has default is checked but there is no
> value in the default text box. Prompt user is unchecked and prompt
> string is SalesRepCode:
> I still get the same error stating that the parameter is readonly.
> "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > In order to pass a parameter via the query string but hide it in the
toolbar
> > you will need to be running Reporting Services SP1. This is a new
feature
> > in SP1.
> > If you have SP1 then edit the parameter properties of the report via the
> > Report Manager and check "Prompt User" and clear out the value of
"Prompt
> > String" for your SalesRepCode parameter.
> >
> > --
> > erik perez
> > www.solien.com
> >
> > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > Currently we're using an application and the Report Viewer to "view"
> > > our reports. One report that we need to generate is solely based on
> > > the user's SalesRepCode. So what we want is that each user select a
> > > report and then pass there SalesRepCode which we get from the
> > > application, through the query string to Reporting Services to
> > > generate that user's specific report. We don't want to give the user
> > > the ability to use another user's SalesRepCode so we don't specify a
> > > prompt for this parameter in the report parameters dialog boxes. This
> > > is how I'm passing the SalesRepCode value to reporting services (I
> > > know that the query string can be modified so the security on this is
> > > weak. If you have an alternative solution I would love to hear it).
> > > Is there anyway to pass in a parameter and not have it displayed on
> > > the toolbar?
> > >
> > >
> >
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > >
> > > This is the error I'm receiving
> > >
> > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > modified. (rsReadOnlyReportParameter) Get Online Help|||I'm such an idiot, I should pay more attention to what I write. I
didn't finish my paragraph.
Erik
I did what you said the first time. I removed the text in the "Prompt
String" text field, but when I pressed the "Apply" button I received a
warning for every other text box saying "A value is required." Because
all of my default text boxes were blank. Now I have
calculations/expressions for those default values in my reports so I
didnt' know exactly what to do so just for a test I placed default
values there and tried to submit the value via query string.
Unfortunatly I still receive a read-only error when I submit it. I
have installed the SP, I changed the parameters in the report manager.
Could it be another setting or maybe its machine specific?
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> Ok. Now:
> 1) Check the "Prompt User" checkbox so it is active
> 2) Remove the "SalesRepCode" value inside the "Prompt String" text field so
> that the field is empty
> 3) Apply and try feeding the code via the query string.
> --
> erik perez
> www.solien.com
> "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > Hey Erik
> >
> > I installed the SP1 and tried what you suggested. It still doesn't
> > work. When I go into the reports manager and select my report and
> > then select parameters from the left side the list of parameters
> > appears. For my parameter has default is checked but there is no
> > value in the default text box. Prompt user is unchecked and prompt
> > string is SalesRepCode:
> >
> > I still get the same error stating that the parameter is readonly.
> >
> > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > In order to pass a parameter via the query string but hide it in the
> toolbar
> > > you will need to be running Reporting Services SP1. This is a new
> feature
> > > in SP1.
> > > If you have SP1 then edit the parameter properties of the report via the
> > > Report Manager and check "Prompt User" and clear out the value of
> "Prompt
> > > String" for your SalesRepCode parameter.
> > >
> > > --
> > > erik perez
> > > www.solien.com
> > >
> > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > Currently we're using an application and the Report Viewer to "view"
> > > > our reports. One report that we need to generate is solely based on
> > > > the user's SalesRepCode. So what we want is that each user select a
> > > > report and then pass there SalesRepCode which we get from the
> > > > application, through the query string to Reporting Services to
> > > > generate that user's specific report. We don't want to give the user
> > > > the ability to use another user's SalesRepCode so we don't specify a
> > > > prompt for this parameter in the report parameters dialog boxes. This
> > > > is how I'm passing the SalesRepCode value to reporting services (I
> > > > know that the query string can be modified so the security on this is
> > > > weak. If you have an alternative solution I would love to hear it).
> > > > Is there anyway to pass in a parameter and not have it displayed on
> > > > the toolbar?
> > > >
> > > >
> > >
> http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > >
> > > > This is the error I'm receiving
> > > >
> > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > modified. (rsReadOnlyReportParameter) Get Online Help|||Did you try unchecking the "Has Default" checkboxes for those fields you are
feeding defaults to in the report?
--
erik perez
www.solien.com
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409030527.271c9ada@.posting.google.com...
> I'm such an idiot, I should pay more attention to what I write. I
> didn't finish my paragraph.
> Erik
> I did what you said the first time. I removed the text in the "Prompt
> String" text field, but when I pressed the "Apply" button I received a
> warning for every other text box saying "A value is required." Because
> all of my default text boxes were blank. Now I have
> calculations/expressions for those default values in my reports so I
> didnt' know exactly what to do so just for a test I placed default
> values there and tried to submit the value via query string.
> Unfortunatly I still receive a read-only error when I submit it. I
> have installed the SP, I changed the parameters in the report manager.
> Could it be another setting or maybe its machine specific?
>
> "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> > Ok. Now:
> > 1) Check the "Prompt User" checkbox so it is active
> > 2) Remove the "SalesRepCode" value inside the "Prompt String" text field
so
> > that the field is empty
> > 3) Apply and try feeding the code via the query string.
> >
> > --
> > erik perez
> > www.solien.com
> >
> > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > > Hey Erik
> > >
> > > I installed the SP1 and tried what you suggested. It still doesn't
> > > work. When I go into the reports manager and select my report and
> > > then select parameters from the left side the list of parameters
> > > appears. For my parameter has default is checked but there is no
> > > value in the default text box. Prompt user is unchecked and prompt
> > > string is SalesRepCode:
> > >
> > > I still get the same error stating that the parameter is readonly.
> > >
> > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> > news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > > In order to pass a parameter via the query string but hide it in the
> > toolbar
> > > > you will need to be running Reporting Services SP1. This is a new
> > feature
> > > > in SP1.
> > > > If you have SP1 then edit the parameter properties of the report via
the
> > > > Report Manager and check "Prompt User" and clear out the value of
> > "Prompt
> > > > String" for your SalesRepCode parameter.
> > > >
> > > > --
> > > > erik perez
> > > > www.solien.com
> > > >
> > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > > Currently we're using an application and the Report Viewer to
"view"
> > > > > our reports. One report that we need to generate is solely based
on
> > > > > the user's SalesRepCode. So what we want is that each user select
a
> > > > > report and then pass there SalesRepCode which we get from the
> > > > > application, through the query string to Reporting Services to
> > > > > generate that user's specific report. We don't want to give the
user
> > > > > the ability to use another user's SalesRepCode so we don't specify
a
> > > > > prompt for this parameter in the report parameters dialog boxes.
This
> > > > > is how I'm passing the SalesRepCode value to reporting services (I
> > > > > know that the query string can be modified so the security on this
is
> > > > > weak. If you have an alternative solution I would love to hear
it).
> > > > > Is there anyway to pass in a parameter and not have it displayed
on
> > > > > the toolbar?
> > > > >
> > > > >
> > > >
> >
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > > >
> > > > > This is the error I'm receiving
> > > > >
> > > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > > modified. (rsReadOnlyReportParameter) Get Online Help|||GREAT THAT WORKED!!!! THANKS
BUT now is there any way to pass a parameter without using the query
string. The SalesRepCode I'm trying to pass is sensitive information
and I don't want it in the query string. If you've got any
suggestions I'd love to hear them.
Phoenix
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message news:<##LUp9bkEHA.3872@.TK2MSFTNGP11.phx.gbl>...
> Did you try unchecking the "Has Default" checkboxes for those fields you are
> feeding defaults to in the report?
> --
> erik perez
> www.solien.com
> "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> news:1538bf33.0409030527.271c9ada@.posting.google.com...
> > I'm such an idiot, I should pay more attention to what I write. I
> > didn't finish my paragraph.
> >
> > Erik
> >
> > I did what you said the first time. I removed the text in the "Prompt
> > String" text field, but when I pressed the "Apply" button I received a
> > warning for every other text box saying "A value is required." Because
> > all of my default text boxes were blank. Now I have
> > calculations/expressions for those default values in my reports so I
> > didnt' know exactly what to do so just for a test I placed default
> > values there and tried to submit the value via query string.
> > Unfortunatly I still receive a read-only error when I submit it. I
> > have installed the SP, I changed the parameters in the report manager.
> > Could it be another setting or maybe its machine specific?
> >
> >
> > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> > > Ok. Now:
> > > 1) Check the "Prompt User" checkbox so it is active
> > > 2) Remove the "SalesRepCode" value inside the "Prompt String" text field
> so
> > > that the field is empty
> > > 3) Apply and try feeding the code via the query string.
> > >
> > > --
> > > erik perez
> > > www.solien.com
> > >
> > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > > > Hey Erik
> > > >
> > > > I installed the SP1 and tried what you suggested. It still doesn't
> > > > work. When I go into the reports manager and select my report and
> > > > then select parameters from the left side the list of parameters
> > > > appears. For my parameter has default is checked but there is no
> > > > value in the default text box. Prompt user is unchecked and prompt
> > > > string is SalesRepCode:
> > > >
> > > > I still get the same error stating that the parameter is readonly.
> > > >
> > > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > > > In order to pass a parameter via the query string but hide it in the
> toolbar
> > > > > you will need to be running Reporting Services SP1. This is a new
> feature
> > > > > in SP1.
> > > > > If you have SP1 then edit the parameter properties of the report via
> the
> > > > > Report Manager and check "Prompt User" and clear out the value of
> "Prompt
> > > > > String" for your SalesRepCode parameter.
> > > > >
> > > > > --
> > > > > erik perez
> > > > > www.solien.com
> > > > >
> > > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > > > Currently we're using an application and the Report Viewer to
> "view"
> > > > > > our reports. One report that we need to generate is solely based
> on
> > > > > > the user's SalesRepCode. So what we want is that each user select
> a
> > > > > > report and then pass there SalesRepCode which we get from the
> > > > > > application, through the query string to Reporting Services to
> > > > > > generate that user's specific report. We don't want to give the
> user
> > > > > > the ability to use another user's SalesRepCode so we don't specify
> a
> > > > > > prompt for this parameter in the report parameters dialog boxes.
> This
> > > > > > is how I'm passing the SalesRepCode value to reporting services (I
> > > > > > know that the query string can be modified so the security on this
> is
> > > > > > weak. If you have an alternative solution I would love to hear
> it).
> > > > > > Is there anyway to pass in a parameter and not have it displayed
> on
> > > > > > the toolbar?
> > > > > >
> > > > > >
> > > > >
> > >
> http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > > > >
> > > > > > This is the error I'm receiving
> > > > > >
> > > > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > > > modified. (rsReadOnlyReportParameter) Get Online Help|||If the salesrepcode can be tied to the logged in user you can use the global
variable User!userid and create a query to get the salesrepcode from a
table.
Bruce L-C
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409031308.1185e4ca@.posting.google.com...
> GREAT THAT WORKED!!!! THANKS
> BUT now is there any way to pass a parameter without using the query
> string. The SalesRepCode I'm trying to pass is sensitive information
> and I don't want it in the query string. If you've got any
> suggestions I'd love to hear them.
> Phoenix
>
> "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:<##LUp9bkEHA.3872@.TK2MSFTNGP11.phx.gbl>...
> > Did you try unchecking the "Has Default" checkboxes for those fields you
are
> > feeding defaults to in the report?
> >
> > --
> > erik perez
> > www.solien.com
> >
> > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > news:1538bf33.0409030527.271c9ada@.posting.google.com...
> > > I'm such an idiot, I should pay more attention to what I write. I
> > > didn't finish my paragraph.
> > >
> > > Erik
> > >
> > > I did what you said the first time. I removed the text in the "Prompt
> > > String" text field, but when I pressed the "Apply" button I received a
> > > warning for every other text box saying "A value is required." Because
> > > all of my default text boxes were blank. Now I have
> > > calculations/expressions for those default values in my reports so I
> > > didnt' know exactly what to do so just for a test I placed default
> > > values there and tried to submit the value via query string.
> > > Unfortunatly I still receive a read-only error when I submit it. I
> > > have installed the SP, I changed the parameters in the report manager.
> > > Could it be another setting or maybe its machine specific?
> > >
> > >
> > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> > news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> > > > Ok. Now:
> > > > 1) Check the "Prompt User" checkbox so it is active
> > > > 2) Remove the "SalesRepCode" value inside the "Prompt String" text
field
> > so
> > > > that the field is empty
> > > > 3) Apply and try feeding the code via the query string.
> > > >
> > > > --
> > > > erik perez
> > > > www.solien.com
> > > >
> > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > > > > Hey Erik
> > > > >
> > > > > I installed the SP1 and tried what you suggested. It still
doesn't
> > > > > work. When I go into the reports manager and select my report and
> > > > > then select parameters from the left side the list of parameters
> > > > > appears. For my parameter has default is checked but there is no
> > > > > value in the default text box. Prompt user is unchecked and
prompt
> > > > > string is SalesRepCode:
> > > > >
> > > > > I still get the same error stating that the parameter is readonly.
> > > > >
> > > > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> > news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > > > > In order to pass a parameter via the query string but hide it in
the
> > toolbar
> > > > > > you will need to be running Reporting Services SP1. This is a
new
> > feature
> > > > > > in SP1.
> > > > > > If you have SP1 then edit the parameter properties of the report
via
> > the
> > > > > > Report Manager and check "Prompt User" and clear out the value
of
> > "Prompt
> > > > > > String" for your SalesRepCode parameter.
> > > > > >
> > > > > > --
> > > > > > erik perez
> > > > > > www.solien.com
> > > > > >
> > > > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > > > > Currently we're using an application and the Report Viewer to
> > "view"
> > > > > > > our reports. One report that we need to generate is solely
based
> > on
> > > > > > > the user's SalesRepCode. So what we want is that each user se
lect
> > a
> > > > > > > report and then pass there SalesRepCode which we get from the
> > > > > > > application, through the query string to Reporting Services to
> > > > > > > generate that user's specific report. We don't want to give
the
> > user
> > > > > > > the ability to use another user's SalesRepCode so we don't
specify
> > a
> > > > > > > prompt for this parameter in the report parameters dialog
boxes.
> > This
> > > > > > > is how I'm passing the SalesRepCode value to reporting
services (I
> > > > > > > know that the query string can be modified so the security on
this
> > is
> > > > > > > weak. If you have an alternative solution I would love to
hear
> > it).
> > > > > > > Is there anyway to pass in a parameter and not have it
displayed
> > on
> > > > > > > the toolbar?
> > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > > > > >
> > > > > > > This is the error I'm receiving
> > > > > > >
> > > > > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > > > > modified. (rsReadOnlyReportParameter) Get Online Help

Passing Formula as a parameter to SQL Query in AddCommand

Hi,

I'am new to Crystal reports,

I created a formula based on that i have to fetch column value from database

for that i wrote a very simple query in SQLCOMMAND

as SELECT <TABLE.CPLUMN> FROM <TABLE>

WHERE <TABLE.OTHERCOLUMN>='@.CRYSTAL REPORT FORMULA'

when closing the commandbox i'am getting error "OLE DB ERROR NO DATABSE RECORDS FOUND"

if i pass a some hardcoded vale(data whic exists in database) then the query is not throwing any error..

Thanks
MikeI don't think you can do that. You can create and use parameters in you query but not formulas. Your query looks really simple, so I have some doubts if it was that necessary to create it in your case.

Why don't you use something like that:

if {TABLE.OTHERCOLUMN}>=here print what you have in your '@.CRYSTAL REPORT FORMULA' then {TABLE.OTHERCOLUMN}

This formula does the same.

passing date parameters between reports changing from dd/mm/yyyy to mm/dd/yyyy

I have a report which is based on a start and end date that are passed
as parameters at run time. This works fine. However, in the report is
a link to another report, which needs to run based on the same date
parameters. However, the following happens...
Report 1 runs fine with date parameters and report is generated.
When report 2 is selected the same dates are passed, but the date
format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
report 2 will either run with the wrong dates, or it will crash and not
run at all if swapping month and day gives an invalid date.
I have my report languages set to default so it should pick up my
regional setting ok. But when passing these dates between reports the
settings seem to be lost somehow.
Can anyone help'Check the data settings on the machine, and the report server configuration.
That may be the culprit.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<gearoid_healy@.yahoo.com> wrote in message
news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>|||Oops. Not "data settings" but "date settings", as in Control Panel.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:er8cQd82EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Check the data settings on the machine, and the report server
> configuration. That may be the culprit.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> <gearoid_healy@.yahoo.com> wrote in message
> news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>>I have a report which is based on a start and end date that are passed
>> as parameters at run time. This works fine. However, in the report is
>> a link to another report, which needs to run based on the same date
>> parameters. However, the following happens...
>> Report 1 runs fine with date parameters and report is generated.
>> When report 2 is selected the same dates are passed, but the date
>> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
>> report 2 will either run with the wrong dates, or it will crash and not
>> run at all if swapping month and day gives an invalid date.
>> I have my report languages set to default so it should pick up my
>> regional setting ok. But when passing these dates between reports the
>> settings seem to be lost somehow.
>> Can anyone help'
>|||The date settings on my pc are correct. How do I check the report
server configuration? I've taken a look at RSReportServer.config and
the other .config files but can't see anything there that look like
regional settings, apart from
<Render>
<Extension Name=...>
<Configuration>
<OWCConfiguration>
<OWCDownloadLocation
language="en">http://office.microsoft.com/downloads/2002/owc10.aspx</OWCDownloadLocation>
but I don't think this has anything to do with it? Like I said, it
renders fine the first time, but when the dates are passed as part of
the url in a query string to the second report the date format changes,
so it must be some kind of report server configuration setting alright.
I just don't know where to go to check or fix this...|||ok, finally found a solution to this. Apparently it's a known issue
that was addressed in SP1 -
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
...which states "The rs:ParameterLanguage URL access parameter
alleviates a problem in which culture-sensitive report parameters, such
as dates, times, currency, and numbers, are interpreted using the
browser language"
Basically you have to add
...&rs:ParameterLanguage=<regionalSetting>
where regional setting is en-ie or en-us or whatever...|||Great, thanks for posting your solution back to the newsgroup.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102422016.683757.198040@.z14g2000cwz.googlegroups.com...
> ok, finally found a solution to this. Apparently it's a known issue
> that was addressed in SP1 -
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
> ...which states "The rs:ParameterLanguage URL access parameter
> alleviates a problem in which culture-sensitive report parameters, such
> as dates, times, currency, and numbers, are interpreted using the
> browser language"
> Basically you have to add
> ...&rs:ParameterLanguage=<regionalSetting>
> where regional setting is en-ie or en-us or whatever...
>|||I posested my solution to this before I actually tested it. This still
doesn't solve the problem I was having...
I call all my reports through a custom .net application, and display
them in my own application, building up my own URLs to display them.
However, when I link from one report (by clicking on an an image) it
calls the second report. But this is done within the report manager
and as such I don't get the option to append my own parameters to the
query string - namely the &rs:ParameterLanguage=en-ie part that I need.
Can anyone tell me how I might overcome this? I tried adding it as a
parameter in the Image Properties - Navigation - Hyperlink action -
Parameters but got the following error
...Parameter names must be CLS-compliant identifiers.
I'd appreciate a response from someone from Microsoft on this as it's a
known issue. How do I get around this bug in Reporting Services'|||It sounds like you are using Jump to Report. Use Jump to URL. You can put an
expression in Jump to URL that can be anything you want so you should have
no problem appending this onto it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102509563.514657.75340@.z14g2000cwz.googlegroups.com...
> I posested my solution to this before I actually tested it. This still
> doesn't solve the problem I was having...
> I call all my reports through a custom .net application, and display
> them in my own application, building up my own URLs to display them.
> However, when I link from one report (by clicking on an an image) it
> calls the second report. But this is done within the report manager
> and as such I don't get the option to append my own parameters to the
> query string - namely the &rs:ParameterLanguage=en-ie part that I need.
> Can anyone tell me how I might overcome this? I tried adding it as a
> parameter in the Image Properties - Navigation - Hyperlink action -
> Parameters but got the following error
> ...Parameter names must be CLS-compliant identifiers.
> I'd appreciate a response from someone from Microsoft on this as it's a
> known issue. How do I get around this bug in Reporting Services'
>|||Thanks Bruce!
Exactly what I was looking for. One slight problem... As I said, I'm
running these reports from a .net app. Is there anyway of reading the
URl of the web server from my web.config for my .net app'|||Use the global variable for this. Here is an example of a working jump to
url expression of mine:
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
First(Fields!manifstdocno.Value, "LoadID") &"&WasteIDNum=" &
First(Fields!wasteidnum.Value, "LoadID")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102529644.706986.86770@.z14g2000cwz.googlegroups.com...
> Thanks Bruce!
> Exactly what I was looking for. One slight problem... As I said, I'm
> running these reports from a .net app. Is there anyway of reading the
> URl of the web server from my web.config for my .net app'
>|||brilliant - thanks a million for that Bruce!! Problem sorted.|||I have something to share with everyone for this problem.
If you are using Jump to Report method then to solve this problem simply
format the date you are passing to linked report as "dd-MMM-yyyy" in
paraemeters window.
This worked for me.
Cheers,
Harry
"gearoid_healy@.yahoo.com" wrote:
> I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>

Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Passing an array of strings to a Stored Procedure

Well, Imanaged to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:

USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROC [dbo].[gv_sp_UpdatePOs]

(

@.IDListvarchar(500),

@.ReorderLevelint,

@.ProductNamenvarchar(30)

)

AS

BEGIN

SETNOCOUNTON

EXEC('Update dbo.Products

SET ReorderLevel = ('+ @.ReorderLevel+') ,ProductName = ('''+ @.ProductName+''')

WHERE ProductID IN ('+ @.IDList+')')

END

-------

THis works fine inside Sql Server 2005 Query analyser.

But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.

Any help would be appreciated

And can someone convert it to support XML instead of list of strings. thanks.

Hello my friend,

It would be better to do the following. First, run the following SQL: -

CREATE FUNCTION dbo.StringArrayIntoTable
(
@.String VARCHAR(8000),
@.Separator VARCHAR(1)
)
RETURNS @.tblStrings TABLE(Item VARCHAR(8000))

AS

BEGIN

DECLARE @.pos INT,
@.SubStr VARCHAR(800)

SET @.pos = CHARINDEX(@.Separator, @.String)

WHILE @.pos > 0
BEGIN
SET @.SubStr = SUBSTRING(@.String, 0, @.pos)

INSERT INTO @.tblStrings (Item) VALUES (@.SubStr)

SET @.String = SUBSTRING(@.String, LEN(@.SubStr) + 2, LEN(@.String) - LEN(@.SubStr) + 1)
SET @.pos = CHARINDEX(@.Separator, @.String)
END

INSERT INTO @.tblStrings (Item) VALUES (@.String)
RETURN

END

Test this function via the following: -

SELECT Item FROM dbo.StringArrayIntoTable('red,blue,yellow', ',')
SELECT Item FROM dbo.StringArrayIntoTable('USA|Germany|Russia|UK', '|')

Now change your procedure to the following: -

ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@.IDList varchar(500),
@.ReorderLevel int,
@.ProductName nvarchar(30)
)
AS

BEGIN

SET NOCOUNT ON

UPDATE dbo.Products SET
ReorderLevel = @.ReorderLevel,
ProductName = @.ProductName

WHERE ProductID IN
(
SELECT Item FROM dbo.StringArrayIntoTable(@.IDList, ',')
)

END

Kind regards

Scotty

|||

USE [Northwind]GO/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROC [dbo].[gv_sp_UpdatePOs](@.IDListvarchar(500),@.ReorderLevelint,@.ProductNamenvarchar(30) )ASBEGINSET NOCOUNT ON EXEC('Update dbo.ProductsSET ReorderLevel = (' +CAST( @.ReorderLevelas varchar(20) ) +') ,ProductName = (''' + @.ProductName +''')WHERE ProductID IN (' + @.IDList +')')END

Hello,
Try this.|||

Scotty, nice trick thanks.

Hasan, thanks. It is working now with casting.

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

pass hash (#) table with different structure to stored procedure

Dear Techies,

I making one stored procedure, which does some operation based on an
interface hash (#) table -- name #mydata.

This stored has two section of code (seperated by parameter value 0
and 1)
But hash table #mydata (same name) number/name of columns changes as
per call 0 or 1.

e.g.
when call for 0, --> Pass 2 columns as company_cd and section_cd in
interface hash (#) table -- name #mydata.
when call for 1, --> Pass 3 columns as Section_cd, line_cd and
subline_cd in interface hash (#) table -- name #mydata.

As a result, none of the case (0 or 1) is running properly, It gives
problem.

When I execute procedure for 0 by passing #mydata with two columns
--> it gives problem in 1 section code

And When I execute procedure for 1 by passing #mydata with three
columns --> it gives problem in 0 section code

Please suggest !!! If anybody have faced the same problem or have any
idea about this case.
(I think passing hash table with 3 column as col1,col2,col3 can serve
the purpose, but this may cause rework in my case, so looking for
alternate solution)

Thanks in Advance,
T.S.NegiIt sounds like creating the temp table with all three columns is
probably the best solution - see here for more details and other
options:

http://www.sommarskog.se/share_data.html

Simon

Pass a parameter to SSIS from .NET App

I'd like to pass a parameter value from a ASP.NET application to an SSIS package. My SSIS package pulls data from SQL and loads it a flat file based on the parameter value.

Is this possible?

Very much possible.

Check out the example @. http://www.codeproject.com/useritems/CallSSISFromCSharp.asp

More details on "Loading and Running a Package Programmatically " @. http://msdn2.microsoft.com/en-us/library/ms136090.aspx

Thanks,
Loonysan

|||Sweet! Thanks :)

Pass a Parameter into a textbox on a report?

I have a report that is unsing a multi-value parameter selection list. Based on the parameter I select, I would like to display the selection in a textbox directly on the report. Would someone know what statement I place into the textbox to capture the parameter:

Some details: 'read_perflvl' is the data field in the original database. 'perflvl' is the name of my report parameter.

I dropped the 'read_perflvl' field into the text box on my report and it defaulted to

"=First(Fields!read_perflvl.Value)" , and this actually worked fine if I selected only one parameter. But if I selected two or more from the multi-valued parameter list, it only shows the first one (clearly due to the FIRST function it is referencing) in the textbox.

Is there a different prefix to the statement that I can use so that it will display all of the parameter values I select?

Thanks for any suggestions.

Dear esloat,

try the following expression in the text box.

-right click on the text box and select expression from the drop down menu.

-then write the following expression:

=parameters!perflvl.value

I think this should display the value of the selected parameter in the text box.

Sincerely,

Amde

|||

Amde, Wow, great minds think alike. Just as I received your message I had pasted that exact expression into the textbox - I lifted it from the code block that was refering to the parameter in the report.

Unfortunately when I run the report I get a non-discript '#ERROR' message returned. The rest of the report runs fine, but the value of the textbox displays as an error.

Might this be due to a conflict between a string and numeric value? The originating database field is a string. Does the parameter expression store the result as a numeric value?

Anyway, I'll keep trying and post if I resolve the error message. Thanks.

|||hi , u havnt tld me frm where to pass frm asp.net to reports . so u can do as report parameter id=new report parameter()
id.name="ur id name";
id.values.add(textbox1.text);
reportviewer.serverreport(id)

..in reports ,, use same field(=fileds!database.value)

deploy report,, and use in asp.net as
reportviewer.reportpath="report path" as "/ report project1/reports"