Parameter Problems ! ! !

Hi,
I have gone through all the suggestions on this site, but non of them have
solved my problem. I am new to reporting services, and I am facing a problem
and I dont' know whether the url based reporting is going to solve this for
me (i really hope it can).
I have a datagrid with link button which basically passes the parameters to
a report like id, startDate and and an endDate, so basically the report shows
data for that id between the startdate and the endDate. Now the parameters
are passed fine and it shows up on the text boxes in the report, however the
problem is that though I want to use the id as a parameter from the url, I do
not want to give the user a text box to change that value i.e. basically i
want it hidden and at the same time I want it to be able to use the value i
pass in the url... can this be done.. I would really appreciate if someone
could direct me on the right path...I have tried hiding prompts throught the
reporting manager, and using just a "space" in the reporting string, but
there's still text box showing up which I don't want to show... sooo plzzz
some one help me out. Thank you.. in advanceYes. It can be done.
Here is an example Url.
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
Replace the values that in brackets that match your requirements.
<ReportDirectory> - use if you've created a folder where your report
resides.
<ReportName> - the name of the report that you want to render.
<Parameter> - the parameter name that you are going to pass a value to the
report.
<ParameterValue> - this is the actual parameter value being passed.
If you have additional parameters you can add them to the end of the Url
using this syntax:
&<ParameterName2>=<ParameterValue2>
http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
I hope this helps. I use it all the time in my c# applications.
Yosh
"Safder" <Safder@.discussions.microsoft.com> wrote in message
news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a
> problem
> and I dont' know whether the url based reporting is going to solve this
> for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters
> to
> a report like id, startDate and and an endDate, so basically the report
> shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however
> the
> problem is that though I want to use the id as a parameter from the url, I
> do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value
> i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught
> the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo
> plzzz
> some one help me out. Thank you.. in advance|||Hi,
Thanks for the suggestion, unfortunately this did not work, is there any
pre-reqs to doing this .. i mean do i have to change any properties of the
report before doing this.? if not.. i tried using this same url you have
suggested, but all the text boxes still appear. Also i'de like to specify
that i have 3 parameters out of which i want to show only two. The three
parameters once again are id, startDate and endDate. So I'de like to show the
start and end dates and would like to give the user the option to change
those parameters, however i do not want to give the user the option to change
the id, and hence i want the "id" parameter to be hidden. Please let me know
if i can do this. Thank you.
"Yosh" wrote:
> Yes. It can be done.
> Here is an example Url.
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>
> Replace the values that in brackets that match your requirements.
> <ReportDirectory> - use if you've created a folder where your report
> resides.
> <ReportName> - the name of the report that you want to render.
> <Parameter> - the parameter name that you are going to pass a value to the
> report.
> <ParameterValue> - this is the actual parameter value being passed.
> If you have additional parameters you can add them to the end of the Url
> using this syntax:
> &<ParameterName2>=<ParameterValue2>
> http://localhost/ReportServer?/<ReportDirectory>/<ReportName>&rc:Toolbar=true&rc:parameters=false&rs:Command=Render&<ParameterName>=<ParameterValue>&<ParameterName2>=<ParameterValue2>
> I hope this helps. I use it all the time in my c# applications.
> Yosh
>
> "Safder" <Safder@.discussions.microsoft.com> wrote in message
> news:44BA0D0A-53E3-4031-B8A8-43E0BC0BAFA3@.microsoft.com...
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a
> > problem
> > and I dont' know whether the url based reporting is going to solve this
> > for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters
> > to
> > a report like id, startDate and and an endDate, so basically the report
> > shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however
> > the
> > problem is that though I want to use the id as a parameter from the url, I
> > do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value
> > i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught
> > the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo
> > plzzz
> > some one help me out. Thank you.. in advance
>
>|||In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
parameter a label and it should be hidden from the user.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Safder" wrote:
> Hi,
> I have gone through all the suggestions on this site, but non of them have
> solved my problem. I am new to reporting services, and I am facing a problem
> and I dont' know whether the url based reporting is going to solve this for
> me (i really hope it can).
> I have a datagrid with link button which basically passes the parameters to
> a report like id, startDate and and an endDate, so basically the report shows
> data for that id between the startdate and the endDate. Now the parameters
> are passed fine and it shows up on the text boxes in the report, however the
> problem is that though I want to use the id as a parameter from the url, I do
> not want to give the user a text box to change that value i.e. basically i
> want it hidden and at the same time I want it to be able to use the value i
> pass in the url... can this be done.. I would really appreciate if someone
> could direct me on the right path...I have tried hiding prompts throught the
> reporting manager, and using just a "space" in the reporting string, but
> there's still text box showing up which I don't want to show... sooo plzzz
> some one help me out. Thank you.. in advance|||If I'm understanding you, you want to have an ID parameter filled in
programmatically but not available to the user to change. I have the same
requirement. All I do is set the parameter to hidden under Report : Report
Parameters and check the Hidden checkbox.|||That is for RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bob Fisher" <bob.fisher@.mben.com> wrote in message
news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> If I'm understanding you, you want to have an ID parameter filled in
> programmatically but not available to the user to change. I have the same
> requirement. All I do is set the parameter to hidden under Report :
> Report Parameters and check the Hidden checkbox.
>|||Hi,
Thank you for the suggestion, I tried what you told me to do in SQL Server
2000 i.e. from "Report manager" i checked the "Prompt user" but i didnt give
any "prompt string" , but what happens in that case is only a text box
without a label shows up,and that does not help I am lookin for something
which does not show a label or a text box. Please let me konw if it's
possible. Bob fisher actually summed up pretty well about what i want,
however his suggestion was for 2005, i am lookin for something which would
work for 2000. Thank you
"Wayne Snyder" wrote:
> In SQL 2005 simply check the hidden property... In SQL 2000 do NOT give the
> parameter a label and it should be hidden from the user.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Safder" wrote:
> > Hi,
> > I have gone through all the suggestions on this site, but non of them have
> > solved my problem. I am new to reporting services, and I am facing a problem
> > and I dont' know whether the url based reporting is going to solve this for
> > me (i really hope it can).
> > I have a datagrid with link button which basically passes the parameters to
> > a report like id, startDate and and an endDate, so basically the report shows
> > data for that id between the startdate and the endDate. Now the parameters
> > are passed fine and it shows up on the text boxes in the report, however the
> > problem is that though I want to use the id as a parameter from the url, I do
> > not want to give the user a text box to change that value i.e. basically i
> > want it hidden and at the same time I want it to be able to use the value i
> > pass in the url... can this be done.. I would really appreciate if someone
> > could direct me on the right path...I have tried hiding prompts throught the
> > reporting manager, and using just a "space" in the reporting string, but
> > there's still text box showing up which I don't want to show... sooo plzzz
> > some one help me out. Thank you.. in advance|||how would you do it in Reporting services 2000
"Bruce L-C [MVP]" wrote:
> That is for RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > If I'm understanding you, you want to have an ID parameter filled in
> > programmatically but not available to the user to change. I have the same
> > requirement. All I do is set the parameter to hidden under Report :
> > Report Parameters and check the Hidden checkbox.
> >
> >
>
>|||Hi Safder
if you want your paramaeter to be hidden from user. After deployment to
Portal, you will have to go to report manager and go to you report properties
and go to parameters section and uncheck the prompt user check box
"Safder" wrote:
> how would you do it in Reporting services 2000
> "Bruce L-C [MVP]" wrote:
> > That is for RS 2005.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bob Fisher" <bob.fisher@.mben.com> wrote in message
> > news:uKj7TXM2FHA.1032@.TK2MSFTNGP12.phx.gbl...
> > > If I'm understanding you, you want to have an ID parameter filled in
> > > programmatically but not available to the user to change. I have the same
> > > requirement. All I do is set the parameter to hidden under Report :
> > > Report Parameters and check the Hidden checkbox.
> > >
> > >
> >
> >
> >sql

Parameter Passing and Oracle Issues

I am relatively new to reporting services so I've been figuring this out as I go, but basically I am in the process of converting BI reports from another reporting service to the MS reporting services. The reports themselves have been recreated without a hitch but the parameters gave me issues. Frankly I needed more control over the parameters then RS allowed in order to match the features in the previous BI solution.

To solve this I decided to build a vb .net or asp .net front end and get the parameters, do whatever needs to be done, then pass them to a report viewer. Great, except a lot of my parameters are "from query" and the whole point of using these new reports is to avoid having oracle installed on the client computers. The only way I know of now to get these "from query" parameters is to run the query client side, meaning oracle needs to be installed.

Basically I am looking for either a way to obtain the "from query" parameter options from a report and pull them out for use in a vb.net or asp.net application, or another idea of how to get around this problem. Basically I want to be able to handle the parameters completely within the app, then just generate the report.

Hi Jeremicus,

A few thoughts:

Can you explain what you were trying to do with the parameters in the report that was causing you problems? We might be able to come up with a work around without having to go to an external source.

If you are creating an asp.net page, you should be able to query oracle and display the results without installing anything on the clients. Whatever odbc / oracle drivers you're using will be installed on the web server themselves.

You're not going to want to run a report to use data in an application. You should run the query directly in your application. Then if you want to display a report, you can pass the data using parameters.

HTH,

Jessica

|||Well my main problems with the parameters were things to do with multi-valued parameters. Basically in the previous reporting setup they could easily choose to leave input boxes blank, or put in a value, or put in multiple ones (either typing them in as a comma delimited string or selecting from a list).

Since the multi value parameters in RS wont allow you to not select something, this removes the ability to leave them blank, and the workarounds to avoid this aren't overly user friendly in the setting that these reports would be used, so basically I wanted to handle all that on my own and give more flexibility to the users and just do all the parameter formatting or other less pretty stuff 'behind the scenes'.

I might just go with the asp page instead of the application, I was kind of hoping for the app over the asp page but it'll have to do if I can't figure a way around this.
sql

Parameter not working

I am pretty new to reporting services and hope some of you experts can help.

I created a report from AS cube that has a column "Description" with 30 distinct values. The report has a multi-value parameter "Description" with the following details:

Data Type : String
Value Field: Description
Label Field : Description

The filter that i have on the table is =(Fields!Description.Value) = Cstr(Parameters!Description.Value(0))

When i preview the report i only see the top 1 item from the values that i select from the parameter list.

What am i doing wrong?

Any help is appreciated.

Thanks

Rookie,

Try

=(Fields!Description.Value) in Join(Parameters!Description.Value,", ")

because you have a multi field value this would be a better approach.

Ham

|||

Thanks for replying.

I just tried that. Now when i select more than one item in the parameter list, nothing displays. It works only if i select one value from the parameter list.

Any ideas?

|||

Rookie,

I was able to return results with setting my filters to

Expression =Cstr(Fields!Income1.Value)

Operator = In

Value = Join(Parameters!Report_Parameter_0.Value,"', '")

My parameter values were 1,2, 3, 4,

my Income was 1

when I selected 1 parameter, I recieve the expected results.

Ham

|||

Yeah it works for me only when i select one value, if i select two values in the parameter, nothing displays.

Have no clue whats happening.

|||

Rookie,

I played around with this for a bit, I was able to return multiple value with this expression in my filter.

Split(Join(Parameters!Inc.Value,","),",")

Ham

|||

Rookie,

Try this is in your filter expression Split(Join(Parameters!Inc.Value,","),",")

Ham

|||

Thanks a lot hammer2. This made it work.

Thanks

parameter length limitation

Hi,
I am using sql reporting services 2000, i am trying to pass a parameter with
the length of 3000 character and since the URL limitation is 2040 i used the
post method as follow
<FORM name=Form1 method="post" target="_self" >
<input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
<input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
<input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
<input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
<input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
<input name="PIN" type=hidden value="" ID=""/>
<input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
ID="Text2"/>
<input name="RegionId" type=hidden value="<%=request.form("regions") %>"
ID="Text3"/>
<input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
ID="Text4"/>
<input name="CommStatus" type=hidden value="" ID="Text5"/>
<input name="WIN" type=hidden value="" ID="Text6"/>
<input name="WorkOrder" type=hidden value="" ID="Text7"/>
<input name="County" type=hidden value="" ID="Text8"/>
<input name="StateRoute" type=hidden value="" ID="Text9"/>
<input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
%> " ID="Text10"/>
<input name="SearchParameter" type=hidden value="" ID="Text11"/>
<input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
<script>
document.Form1.action= "<% response.write request.form("formaction") %>"; //
form action which will be the url to the reporting server
document.Form1.submit();
</script>
even after i used this method i still have parameter length limitation. is
there any way to go around this problem?
Thanks
</form>Robert,
What kind of parameter are you passing that it has to be 3,000 chars?!
Are you passing SQL into a report through a parameter?
-Josh
Robert wrote:
> Hi,
> I am using sql reporting services 2000, i am trying to pass a parameter with
> the length of 3000 character and since the URL limitation is 2040 i used the
> post method as follow
>
> <FORM name=Form1 method="post" target="_self" >
>
> <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
>
> <input name="PIN" type=hidden value="" ID=""/>
> <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> ID="Text2"/>
> <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> ID="Text3"/>
> <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> ID="Text4"/>
> <input name="CommStatus" type=hidden value="" ID="Text5"/>
> <input name="WIN" type=hidden value="" ID="Text6"/>
> <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> <input name="County" type=hidden value="" ID="Text8"/>
> <input name="StateRoute" type=hidden value="" ID="Text9"/>
> <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> %> " ID="Text10"/>
> <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> <script>
> document.Form1.action= "<% response.write request.form("formaction") %>"; //
> form action which will be the url to the reporting server
> document.Form1.submit();
> </script>
> even after i used this method i still have parameter length limitation. is
> there any way to go around this problem?
> Thanks
> </form>|||Josh
user can choose different documents and then i send the documentid(s) to
report server and stored procedure on the back is going to do some processing
on it and then return it.
so some times may this documentid have length of 3000 or more because these
documentids are commas seprated.
do you have any suggestion that may help me?
Thanks
"Josh" wrote:
> Robert,
> What kind of parameter are you passing that it has to be 3,000 chars?!
> Are you passing SQL into a report through a parameter?
> -Josh
>
> Robert wrote:
> > Hi,
> > I am using sql reporting services 2000, i am trying to pass a parameter with
> > the length of 3000 character and since the URL limitation is 2040 i used the
> > post method as follow
> >
> >
> > <FORM name=Form1 method="post" target="_self" >
> >
> >
> > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> >
> >
> > <input name="PIN" type=hidden value="" ID=""/>
> > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > ID="Text2"/>
> > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > ID="Text3"/>
> > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > ID="Text4"/>
> > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > <input name="WIN" type=hidden value="" ID="Text6"/>
> > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > <input name="County" type=hidden value="" ID="Text8"/>
> > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > %> " ID="Text10"/>
> > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> >
> > <script>
> > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > form action which will be the url to the reporting server
> >
> > document.Form1.submit();
> > </script>
> >
> > even after i used this method i still have parameter length limitation. is
> > there any way to go around this problem?
> >
> > Thanks
> > </form>
>|||OK... this suggestion will only work with SSRS 2005, so I will also
recommend an upgrade.
One of the enhancements offered in SSRS 2005 is multi-valued
parameters. With the introduction of this option, they also added a
JOIN function that will convert a parameter array into a character
delimited string. Sooo...
You create a docID parm (probably string would be best) that is
multi-value. Then, whatever you are using to call the report and pass
that huge string can build the URL like this:
http://serverName/ReportServer/Pages/ReportViewer.aspx?/Folder1/Folder2/ReportName&docID=abc1&docID=abc2&docID=abc3
Create another parameter called docIDstring and mark it as hidden or
internal. (If you want to be able to pass the string already formatted
as CSV, use hidden so that you can pass it in the URL. If you are ONLY
going to use the approach that I am suggesting, mark it as internal.)
Set the default value of the internal/hidden parm to:
=JOIN(Parameters!docID.Value,","). Multi-value parameters are treated
as an array, and this JOIN function will convert the array to CSV.
If you used this expression with the URL that is listed above,
docIDstring would = "abc1,abc2,abc3".
Sorry it only works with SSRS 2005, but you probably need to upgrade
sometime anyway.
Hope this helps!
-Josh
Robert wrote:
> Josh
> user can choose different documents and then i send the documentid(s) to
> report server and stored procedure on the back is going to do some processing
> on it and then return it.
> so some times may this documentid have length of 3000 or more because these
> documentids are commas seprated.
> do you have any suggestion that may help me?
> Thanks
> "Josh" wrote:
> >
> > Robert,
> >
> > What kind of parameter are you passing that it has to be 3,000 chars?!
> > Are you passing SQL into a report through a parameter?
> >
> > -Josh
> >
> >
> > Robert wrote:
> > > Hi,
> > > I am using sql reporting services 2000, i am trying to pass a parameter with
> > > the length of 3000 character and since the URL limitation is 2040 i used the
> > > post method as follow
> > >
> > >
> > > <FORM name=Form1 method="post" target="_self" >
> > >
> > >
> > > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> > >
> > >
> > > <input name="PIN" type=hidden value="" ID=""/>
> > > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > > ID="Text2"/>
> > > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > > ID="Text3"/>
> > > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > > ID="Text4"/>
> > > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > > <input name="WIN" type=hidden value="" ID="Text6"/>
> > > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > > <input name="County" type=hidden value="" ID="Text8"/>
> > > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > > %> " ID="Text10"/>
> > > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> > >
> > > <script>
> > > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > > form action which will be the url to the reporting server
> > >
> > > document.Form1.submit();
> > > </script>
> > >
> > > even after i used this method i still have parameter length limitation. is
> > > there any way to go around this problem?
> > >
> > > Thanks
> > > </form>
> >
> >|||the problem with this approach is that the url can only be 2040 character and
mine for sure will be more.
Thanks
"Josh" wrote:
> OK... this suggestion will only work with SSRS 2005, so I will also
> recommend an upgrade.
> One of the enhancements offered in SSRS 2005 is multi-valued
> parameters. With the introduction of this option, they also added a
> JOIN function that will convert a parameter array into a character
> delimited string. Sooo...
> You create a docID parm (probably string would be best) that is
> multi-value. Then, whatever you are using to call the report and pass
> that huge string can build the URL like this:
> http://serverName/ReportServer/Pages/ReportViewer.aspx?/Folder1/Folder2/ReportName&docID=abc1&docID=abc2&docID=abc3
> Create another parameter called docIDstring and mark it as hidden or
> internal. (If you want to be able to pass the string already formatted
> as CSV, use hidden so that you can pass it in the URL. If you are ONLY
> going to use the approach that I am suggesting, mark it as internal.)
> Set the default value of the internal/hidden parm to:
> =JOIN(Parameters!docID.Value,","). Multi-value parameters are treated
> as an array, and this JOIN function will convert the array to CSV.
> If you used this expression with the URL that is listed above,
> docIDstring would = "abc1,abc2,abc3".
> Sorry it only works with SSRS 2005, but you probably need to upgrade
> sometime anyway.
> Hope this helps!
> -Josh
>
> Robert wrote:
> > Josh
> >
> > user can choose different documents and then i send the documentid(s) to
> > report server and stored procedure on the back is going to do some processing
> > on it and then return it.
> >
> > so some times may this documentid have length of 3000 or more because these
> > documentids are commas seprated.
> > do you have any suggestion that may help me?
> > Thanks
> >
> > "Josh" wrote:
> >
> > >
> > > Robert,
> > >
> > > What kind of parameter are you passing that it has to be 3,000 chars?!
> > > Are you passing SQL into a report through a parameter?
> > >
> > > -Josh
> > >
> > >
> > > Robert wrote:
> > > > Hi,
> > > > I am using sql reporting services 2000, i am trying to pass a parameter with
> > > > the length of 3000 character and since the URL limitation is 2040 i used the
> > > > post method as follow
> > > >
> > > >
> > > > <FORM name=Form1 method="post" target="_self" >
> > > >
> > > >
> > > > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > > > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > > > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > > > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > > > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> > > >
> > > >
> > > > <input name="PIN" type=hidden value="" ID=""/>
> > > > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > > > ID="Text2"/>
> > > > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > > > ID="Text3"/>
> > > > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > > > ID="Text4"/>
> > > > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > > > <input name="WIN" type=hidden value="" ID="Text6"/>
> > > > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > > > <input name="County" type=hidden value="" ID="Text8"/>
> > > > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > > > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > > > %> " ID="Text10"/>
> > > > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > > > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> > > >
> > > > <script>
> > > > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > > > form action which will be the url to the reporting server
> > > >
> > > > document.Form1.submit();
> > > > </script>
> > > >
> > > > even after i used this method i still have parameter length limitation. is
> > > > there any way to go around this problem?
> > > >
> > > > Thanks
> > > > </form>
> > >
> > >
>|||I see.
How many chars is each document ID?
-Josh
Robert wrote:
> the problem with this approach is that the url can only be 2040 character and
> mine for sure will be more.
> Thanks
>
> "Josh" wrote:
> >
> > OK... this suggestion will only work with SSRS 2005, so I will also
> > recommend an upgrade.
> >
> > One of the enhancements offered in SSRS 2005 is multi-valued
> > parameters. With the introduction of this option, they also added a
> > JOIN function that will convert a parameter array into a character
> > delimited string. Sooo...
> >
> > You create a docID parm (probably string would be best) that is
> > multi-value. Then, whatever you are using to call the report and pass
> > that huge string can build the URL like this:
> >
> > http://serverName/ReportServer/Pages/ReportViewer.aspx?/Folder1/Folder2/ReportName&docID=abc1&docID=abc2&docID=abc3
> >
> > Create another parameter called docIDstring and mark it as hidden or
> > internal. (If you want to be able to pass the string already formatted
> > as CSV, use hidden so that you can pass it in the URL. If you are ONLY
> > going to use the approach that I am suggesting, mark it as internal.)
> >
> > Set the default value of the internal/hidden parm to:
> > =JOIN(Parameters!docID.Value,","). Multi-value parameters are treated
> > as an array, and this JOIN function will convert the array to CSV.
> >
> > If you used this expression with the URL that is listed above,
> > docIDstring would = "abc1,abc2,abc3".
> >
> > Sorry it only works with SSRS 2005, but you probably need to upgrade
> > sometime anyway.
> >
> > Hope this helps!
> >
> > -Josh
> >
> >
> >
> > Robert wrote:
> > > Josh
> > >
> > > user can choose different documents and then i send the documentid(s) to
> > > report server and stored procedure on the back is going to do some processing
> > > on it and then return it.
> > >
> > > so some times may this documentid have length of 3000 or more because these
> > > documentids are commas seprated.
> > > do you have any suggestion that may help me?
> > > Thanks
> > >
> > > "Josh" wrote:
> > >
> > > >
> > > > Robert,
> > > >
> > > > What kind of parameter are you passing that it has to be 3,000 chars?!
> > > > Are you passing SQL into a report through a parameter?
> > > >
> > > > -Josh
> > > >
> > > >
> > > > Robert wrote:
> > > > > Hi,
> > > > > I am using sql reporting services 2000, i am trying to pass a parameter with
> > > > > the length of 3000 character and since the URL limitation is 2040 i used the
> > > > > post method as follow
> > > > >
> > > > >
> > > > > <FORM name=Form1 method="post" target="_self" >
> > > > >
> > > > >
> > > > > <input name="rc:LinkTarget" value="_self" type="hidden" ID="Hidden1">
> > > > > <input name="rs:Format" value="HTML4.0" type="hidden" ID="Hidden2">
> > > > > <input name="rc:Toolbar" value="true" type="hidden" ID="Hidden3"/>
> > > > > <input name="rs:ClearSession" value="true" type="hidden" ID="Hidden4"/>
> > > > > <input name="rc:Parameters" value="Collapsed" type="hidden" ID="Hidden5"/>
> > > > >
> > > > >
> > > > > <input name="PIN" type=hidden value="" ID=""/>
> > > > > <input name="EnvIds" type=hidden value="<%=request.form("envids") %>"
> > > > > ID="Text2"/>
> > > > > <input name="RegionId" type=hidden value="<%=request.form("regions") %>"
> > > > > ID="Text3"/>
> > > > > <input name="PhaseIds" type=hidden value="<%=request.form("phaseids") %>"
> > > > > ID="Text4"/>
> > > > > <input name="CommStatus" type=hidden value="" ID="Text5"/>
> > > > > <input name="WIN" type=hidden value="" ID="Text6"/>
> > > > > <input name="WorkOrder" type=hidden value="" ID="Text7"/>
> > > > > <input name="County" type=hidden value="" ID="Text8"/>
> > > > > <input name="StateRoute" type=hidden value="" ID="Text9"/>
> > > > > <input name="CommitmentIds" type=hidden value=" <%=request.form("CommIds")
> > > > > %> " ID="Text10"/>
> > > > > <input name="SearchParameter" type=hidden value="" ID="Text11"/>
> > > > > <input name="CommitmentDescription" type=hidden value="" ID="Text12"/>
> > > > >
> > > > > <script>
> > > > > document.Form1.action= "<% response.write request.form("formaction") %>"; //
> > > > > form action which will be the url to the reporting server
> > > > >
> > > > > document.Form1.submit();
> > > > > </script>
> > > > >
> > > > > even after i used this method i still have parameter length limitation. is
> > > > > there any way to go around this problem?
> > > > >
> > > > > Thanks
> > > > > </form>
> > > >
> > > >
> >
> >sql

Parameter in WHERE clause using Functions

I'm trying to use a report parameter in my where clause to compare against
the month portion of a field. Reporting Services (version 2000) does not seem
to like this. Here's the WHERE clause I'm trying to get to work (of course
it's all on one line):
WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
(YEAR(close_time) = @.DSYear1)
When I try to execute the query, I get an error that states:
Invalid object name "dbo.RAW_DW.MONTH"
Invalid object name "dbo.RAW_DW.YEAR"
It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
trying to use the MONTH and YEAR functions to extract that data from the
datetime field (close_time) and compare it to the report parameter. It may
just be a formatting issue on my part.
Does anyone know what's wrong with the clause?
Thanks in advance.
BrendaHi Brenda,
I dont think that the error you are getting is related to the WHERE clause
in this case. Do you have RAW_DW.Month and ...YEAR in your overall query?
What are the data types of the two parameters, @.DSMonth1 and @.DSYear1?
Without seeing the full query, it is hard to troubleshoot the error, if
indeed these are valid objects in your database.
Rodney Landrum
"BLKeller" <BLKeller@.discussions.microsoft.com> wrote in message
news:69A3593E-8832-46BB-ADD1-A1079829C95E@.microsoft.com...
> I'm trying to use a report parameter in my where clause to compare against
> the month portion of a field. Reporting Services (version 2000) does not
> seem
> to like this. Here's the WHERE clause I'm trying to get to work (of course
> it's all on one line):
> WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
> (YEAR(close_time) = @.DSYear1)
> When I try to execute the query, I get an error that states:
> Invalid object name "dbo.RAW_DW.MONTH"
> Invalid object name "dbo.RAW_DW.YEAR"
> It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
> trying to use the MONTH and YEAR functions to extract that data from the
> datetime field (close_time) and compare it to the report parameter. It may
> just be a formatting issue on my part.
> Does anyone know what's wrong with the clause?
> Thanks in advance.
> Brenda

Parameter dropdown width

How do I adjust the reporting parameter dropdown width? the dropdown is
populated from a dataset and the width of the values exceed the width of the
drop down
thanks
--
Message posted via http://www.sqlmonster.comI am also looking for the similar solution. Please respond ,if it is possible
to adjust the drop down parameter list width. Would it be possible after SP2.
I hope somebody from Reporting Services team would help us here.
Regards
Vijay
"Angie via SQLMonster.com" wrote:
> How do I adjust the reporting parameter dropdown width? the dropdown is
> populated from a dataset and the width of the values exceed the width of the
> drop down
> thanks
>
> --
> Message posted via http://www.sqlmonster.com
>|||There is no way to do this that I know of. That sort of fine tuning of the
Report Manager UI is not possible. I don't know about RS 2005 but I for RS
2000 SP2 this is not possible. You would need to create your own web page
and then integrate via either URL or webservices.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vijay Tripathi" <VijayTripathi@.discussions.microsoft.com> wrote in message
news:49AAE0A4-A32E-41CC-A821-F81BBEDDD501@.microsoft.com...
>I am also looking for the similar solution. Please respond ,if it is
>possible
> to adjust the drop down parameter list width. Would it be possible after
> SP2.
> I hope somebody from Reporting Services team would help us here.
> Regards
> Vijay
> "Angie via SQLMonster.com" wrote:
>> How do I adjust the reporting parameter dropdown width? the dropdown is
>> populated from a dataset and the width of the values exceed the width of
>> the
>> drop down
>> thanks
>>
>> --
>> Message posted via http://www.sqlmonster.com

Parameter doesn''t get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@.startWeek and @.endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@.startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!
Nevermind, I got it solved. Seem that I had to press the refresh button in the dataset for the parameters to work, which wasn't mentioned anywhere that I looked. Unlogical, but now it works at least :-/
|||same thing happened to me. I actually thought that the code was messed up (code behind). So deleted my report and recreated another one. But then i refreshed my dataset over and over and now its workin!! THANKS!!!

Parameter doesn't get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@.startWeek and @.endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@.startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!
Nevermind, I got it solved. Seem that I had to press the refresh button in the dataset for the parameters to work, which wasn't mentioned anywhere that I looked. Unlogical, but now it works at least :-/
|||same thing happened to me. I actually thought that the code was messed up (code behind). So deleted my report and recreated another one. But then i refreshed my dataset over and over and now its workin!! THANKS!!!

Parameter calendar control : week start date

With Reporting Services (SQL Server 2005), if you include a parameter with a
data type of DateTime, you get a calendar control for the parameter on your
report.
How do you change the first day of the week from Sunday to Monday?
Thanks,
CraigIs your question related to calender control, because you have asked
seperately,
in fact you cant change the calender control. Ofcourse in sql server yes you
can by giving
SET DATEFIRST 1 (Monday)
SET DATEFIRST 7 (Sunday - Default)
Amarnath
"CraigHB" wrote:
> With Reporting Services (SQL Server 2005), if you include a parameter with a
> data type of DateTime, you get a calendar control for the parameter on your
> report.
> How do you change the first day of the week from Sunday to Monday?
> Thanks,
> Craig

parameter boxes are empty

Hi,

I am have a reporting server which connects to a analysis server which connects to a sql server and a db2 server. I am using windows integrated security all the time except for the DB2 connection. That is a fixed username/password.

Reporting server (RS), analysis server (AS) and sql server are all on the same machine.

I created a local group and added the correct users to it.
I gave that group browse rights on the reports so that works correctly.

I created a role in AS and added that group to that role. Gave all read rights to that role.

I created a login for that group on the SQL server and gave that group db_datareader role.

When I connect as an administrator every reports runs fine.

When the test user connects they can browse the reports but the drop down boxes for the parameters are empty.

What goes wrong ?

With regards,

Constantijn Enders

Hi Constantjin

Im having the same problem...

I'm guessing when you say: <<Give all read rights to that role>>
you mean that you are setting the read rights in the cell data tab
when editing your role's security....

If I am correct, try the following:

Uncheck the Combo boxes on the cell data tab
(The one for 'Enable Read Permissions' and 'Enable Read Contingent Permissions')
and see the effect.
User will now be able to see everything.

It seemes that any attempt to set permissions on the cell data tab causes this
problem.

G

|||

Hi,

Unchecking did solve the problem.

I also removed them from SQL server and it works.

So basically what I had to do to give report users rights was
- create a role in AS
- put users/groups in that role
- give that role read rights on the cubes

and that's it.

Thanks for your quick help

Constantijn Enders

Parameter based Report Schedule problem Problem

Here is my Case
+++++++++++++++++++++++++++++
I have a common report object residing on Reporting Service. The report
shows Monthly Activity for a Department. This report is based on a Stored
Procedure and has Department ID as a parameter.
Through an application different departments either can run this report or
create a schedule so that at the end of each month, any department can get
the report related to only its data.
How can I do that?
+++++++++++++++++++++++++
You input highly appreciatble...Hi Sue,
This is more an issue for the developers of the applications calling the
report as they will be passing the DepartmentID.
How can the app know which department it is run from? Is there a setting in
Active Directory for the user? Is there a database table somewhere with who
lives in which department? (very messy to maintain).
I'd be querying AD if possible.
"Sue" wrote:
> Here is my Case
> +++++++++++++++++++++++++++++
> I have a common report object residing on Reporting Service. The report
> shows Monthly Activity for a Department. This report is based on a Stored
> Procedure and has Department ID as a parameter.
> Through an application different departments either can run this report or
> create a schedule so that at the end of each month, any department can get
> the report related to only its data.
> How can I do that?
> +++++++++++++++++++++++++
> You input highly appreciatble...
>|||Hi Mary:
Thank you for your response.
I have been using RS API calls to generate the Report from my Web Application.
While generating a report the system already know the Dapartment ID based on
the person who logged in.
Let me give you more information regarding this problem.
1. Report object already exists in the common folder located in the
Reporting Server.
2. End-User can supply Department ID is supplied throgh the program. How can
I perform generate ad-hoc report?
3. How can the end-user schedule this report?
Your input highly appreciatable.
Thanks
Sue
"Mary Bray [SQL Server MVP]" wrote:
> Hi Sue,
> This is more an issue for the developers of the applications calling the
> report as they will be passing the DepartmentID.
> How can the app know which department it is run from? Is there a setting in
> Active Directory for the user? Is there a database table somewhere with who
> lives in which department? (very messy to maintain).
> I'd be querying AD if possible.
> "Sue" wrote:
> > Here is my Case
> > +++++++++++++++++++++++++++++
> > I have a common report object residing on Reporting Service. The report
> > shows Monthly Activity for a Department. This report is based on a Stored
> > Procedure and has Department ID as a parameter.
> >
> > Through an application different departments either can run this report or
> > create a schedule so that at the end of each month, any department can get
> > the report related to only its data.
> >
> > How can I do that?
> > +++++++++++++++++++++++++
> > You input highly appreciatble...
> >
> >sql

Parameter area in RS2000

Hi,

Is there any way to have some kind of control over the controls in the reporting services htmlviewer parameter area.

What i really wan't to do is set a minimum width to the combobox's in that area, or a fixed width,it would be good too.

Thanks.

To keep it short and simple, no.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Unlike Rs2005 there is no css file for the htmlviewer in rs2000? Or anything that resembles that?

Thank's

|||Oh sure yes there is a css file, I was not aware that you can influence the display behaviour of the controls by these files.

http://www.sqlserver2005.de|||

At least RS2005 we can. I think that in RS2000 things are a bit diferent. I can′t seem to find the file htmlviewer.css (this is the file we can tweak in RS2005 to provide some control over the display behaviour), on anything that resembles.

Thanks.

parameter area extension

How can I extend Reporting services UI to include into input
parameter's area TreeView control like to calendar picker ?
Thank you in advance,
L.Lubich,
MCSD, Web programmerneed to write your own UI, since SSRS doesn't provide facitilites for
changing the UI of the report.
Amarnath
"Lubich" wrote:
> How can I extend Reporting services UI to include into input
> parameter's area TreeView control like to calendar picker ?
> Thank you in advance,
> L.Lubich,
> MCSD, Web programmer
>

Parameter All Level

Hello,

I'm using SQL Server Reporting Services with an Analysis Services Cube. My current report has a query based parameter. The query returns country codes and the ALL-Level of my country dimension.

Is there a way to remove the ALL-Level of the result set of the query? How can I realize this?

Thanks in advance.

Christian

Hi,

I suggest to change the query. I think, your generated query looks like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Country].[Country].ALLMEMBERS ON ROWS FROM [MyCube]

Change it to:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , Descendants([Country].[Country], [Country].[yourLevel1name]) ON ROWS FROM [MyCube]

For further help, look at the Descendants Function in MDX.

Hans

Parametarize Reports

I am new to reporting service but i see all the tutorial that just show how to create static reports. But I want something like I put some combo box and when i select particaul option it should show such reports

milindsaraswala

Firstly if you can get your hands on the Reporting Services Book Online, you will find some very useful information.

There are a number of ways to add a parameter the easiest way for me is to add some criteria to your query, for eg,

Change it from

SELECT * FROM table1

to

SELECT * FROM table1 WHERE name = @.Parameter1

When you look at the report parameters option in your layout tab you will now see Parameter1 as a parameter for the report, in this view you can set things like prompt, where nulls/blanks are allowed. It is here that you can select the values for the paramter be based on a query, which when you go to your preview tab will display the parameter now as a combo box.

HTH

Paramaters

Hi, we run an ASP.NET portal that has fairly extensive reporting needs (it's
a government portal...) We're thinking of switching to reporting services
from a custom xml/xsl solution. My questions are:
1. interfacing with RS from our existing .net web app. We have screens set
up to let the user do things like select paramaters and stuff. Can this be
integrated? Currently for things that require multiple selections (for
instance, selecting a set of cities to report on), we build a string of city
ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which uses
the string to select info on each city (using IN(1,3,45,60,61)). Is this
possible in RS, and can we use our existing frontend? If we use our existing
frontend, can we still have access to the toolbar, for things like exporting
to different formats?
2. I know dynamic fields aren't possible. But can fields be left out /
included based on the presence of data to fill it?
3. Is it possible to use XML as a datasource to a report?
If anyone has any insight into these, I'd really appreciate it. Thanks in
advance
Peter LRS has two ways to integrate an existing app. You can either use web
services or you can use URL integration. To be able to have the toolbar you
need to use the URL integration. What you want to do with #1 is very easy to
do using URL integration. For question number 2, whether something is
visible can be set with an expression. The expression can decide not to show
it based on whatever you choose. #3 is possible but a little more difficult.
It is possible to create your own data extension. I suggest first seeing if
you really need to do this or if you can just use the stored procedures
and/or queries instead.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:CF96A1DF-84CA-4136-BCF4-9AF3BBC345CF@.microsoft.com...
> Hi, we run an ASP.NET portal that has fairly extensive reporting needs
> (it's
> a government portal...) We're thinking of switching to reporting services
> from a custom xml/xsl solution. My questions are:
> 1. interfacing with RS from our existing .net web app. We have screens set
> up to let the user do things like select paramaters and stuff. Can this be
> integrated? Currently for things that require multiple selections (for
> instance, selecting a set of cities to report on), we build a string of
> city
> ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which
> uses
> the string to select info on each city (using IN(1,3,45,60,61)). Is this
> possible in RS, and can we use our existing frontend? If we use our
> existing
> frontend, can we still have access to the toolbar, for things like
> exporting
> to different formats?
> 2. I know dynamic fields aren't possible. But can fields be left out /
> included based on the presence of data to fill it?
> 3. Is it possible to use XML as a datasource to a report?
>
> If anyone has any insight into these, I'd really appreciate it. Thanks in
> advance
> Peter L

ParallelPeriod: Same Day Last Month in long months

I am using the following MDX query to return same day last month values:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month],1),[Measures].[Active Account Indicator])'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

This works for most days. However, for the last day of the month this cannot work when the current month has more days than the previous month. For example, on March 29, 30 and 31, the query will return NULL for [Same Day Last Month], since February 29, 30, 31 is an invalid date.

I am sure others have run into the same problem. What is the best way to solve this?

I'm grateful for any insights.

Dan

This could perhaps be more efficiently handled in scoped MDX script; but a recursive approach like this should work (except in the first month):

WITH

MEMBER [Measures].[Same Day Last Month] AS

iif(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]) is null,

([Reporting Date].[Year - Quarter - Month - Date].PrevMember,

[Measures].[Same Day Last Month]),

(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]),

[Measures].[Active Account Indicator]))

|||

Thank you, Deepak. I appreciate your response.

I believe I would have to use nested recursive statements for March 31. One month ago would indicate February 31, previous day would be February 30, and so forth until a valid date is reached.

However, this lead me to another solution which worked and follows our business rules: The last day of any month always contains values. Therefore, I was able to solve my problem using the following approach, employing the IsLeaf(), and the ClosingPeriod() function:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'IIf(

--valtest

IsLeaf(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

)

,

--valtrue

(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

),

[Measures].[Active Account Indicator]

)

,

--valfalse

(

ClosingPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Date],

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

[Reporting Date].[Year - Quarter - Month - Date].CurrentMember.Parent

)

),

[Measures].[Active Account Indicator]

)

) --End IIf

'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

Paragrahs in textboxes

Hi,

Can anyone advise me how to force paragraph breaks in a textbox in Reporting Services 2000? I've tried Shift + Enter and in layout view it forces a paragraph break, but as soon as the report is rendered, the breaks disappear.

Thanks.

Hello Matt,

Try creating an expression of your paragraph, then when you want to add a break, add a Chr(10).

In this example, the first and second sentences will be on different lines.

="This is my first sentence." + chr(10) + "This is my second sentence."

Hope this helps.

Jarret

|||

Hi Jarret,

Perfect! Thank you very much.

Matt

|||

Yup I found the chr(10) very helpful.

I did notice however that when viewing a report, in the report viewer before printing, that the ch(10) has no effect. All of the text runs together as one long string of text.

When I print the report all is fine with the chr(10) doing its job of providing a line break. It also looks ok in the design tool when building the report. It is just that the report viewer does not respect this .

Does anyone have a solution where by the report viewed in the report viewer does the same thing with the text as the the printed version.

Regards

Matteo

|||

Looks like the suggestion from this post works: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=734028&SiteID=1

I've used "Environment.Newline" and it seems to work for the report viewer.

Paragrahs in textboxes

Hi,

Can anyone advise me how to force paragraph breaks in a textbox in Reporting Services 2000? I've tried Shift + Enter and in layout view it forces a paragraph break, but as soon as the report is rendered, the breaks disappear.

Thanks.

Hello Matt,

Try creating an expression of your paragraph, then when you want to add a break, add a Chr(10).

In this example, the first and second sentences will be on different lines.

="This is my first sentence." + chr(10) + "This is my second sentence."

Hope this helps.

Jarret

|||

Hi Jarret,

Perfect! Thank you very much.

Matt

|||

Yup I found the chr(10) very helpful.

I did notice however that when viewing a report, in the report viewer before printing, that the ch(10) has no effect. All of the text runs together as one long string of text.

When I print the report all is fine with the chr(10) doing its job of providing a line break. It also looks ok in the design tool when building the report. It is just that the report viewer does not respect this .

Does anyone have a solution where by the report viewed in the report viewer does the same thing with the text as the the printed version.

Regards

Matteo

|||

Looks like the suggestion from this post works: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=734028&SiteID=1

I've used "Environment.Newline" and it seems to work for the report viewer.

Paper Based Reports Support

Hi,
I am a newbie to SQL Reporting Services. I am trying to evaluate whether to
use SQL reporting services as opposed to crystal reports. I want to create
paper based reports as well as web based reports. Is it fair to say that
SQL Reporting Services focuses more on web based reports than on paper based
reports? I know that it can export reports in an Excel and PDF format but
that seems to be the full extent of true paper based reporting. Am I
missing something? Will there be more support for paper based reports in
the future?
Thanks,
VaughnDepends on what you mean by paper based reports. RS is a format independent
reporting solution. The report is specified via the RDL (if you look at the
RDL it is an xml file). The report definition says how the data should be
formated. How it is formated (rendered) depends on what you ask RS to do.
The default is HTML but that is just the default and really it is just one
of the rendering formats. When you see the export all that is happening is
RS is being asked to run the report but using a different rendering format.
PDF is the format that is used for printing today. With SP2 there will be a
print button on the toolbar which will provide client side printing so you
do not have to go through exporting. My guess (only a guess, no inside
knowledge) is that we will see SP2 sometime first quarter next year.
One thing that I do consider paper based that they do not yet handle well is
the whole issue of labels. For instance being able to easily design a report
for particular labels. In MS Access they have a wizard for this. The wizard
really doesn't do anything that couldn't be done in the GUI designer but it
makes it very easy to do so. It would be nice to have something like this in
RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vaughn" <vhaybittle@.mweb.co.za> wrote in message
news:%23Ce6mq51EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am a newbie to SQL Reporting Services. I am trying to evaluate whether
to
> use SQL reporting services as opposed to crystal reports. I want to
create
> paper based reports as well as web based reports. Is it fair to say that
> SQL Reporting Services focuses more on web based reports than on paper
based
> reports? I know that it can export reports in an Excel and PDF format but
> that seems to be the full extent of true paper based reporting. Am I
> missing something? Will there be more support for paper based reports in
> the future?
> Thanks,
> Vaughn
>