parameter properties in report manager - "Has Default"

ok I've designed a report template that uses cascading paramters based
on several sp's. The user selects a value for the first parameter and
that value is passed to the sp that populates the second parameter
list. The value selected for the second param is used to populate the
third param list etc...
I want the first parameter to be required and the other 3 to be
optional - the report should still run if they are not selected. RS
BOL told me to go into the Report manager, select "Properties"
then"Parameters" and then select the "Has Default" check box for the
three optional paramters. This in turn enables the "Null" checkbox, so
I checked that box so null will be the default value passed if these
parameters are not selected.
By all accounts this should allow the report to run with only the first
parameter selected, but when run from Report Manager it still keeps
prompting the user to enter values for the 3 other optional parameters.
Any ideas? help?Never mind! found a workaround...

Parameter prompt based on expression

Hi,

Is it possible to construct the prompt (i.e. the text that is displayed next to the parameter text box when the report runs) for a parameter from an expression

Actually i have written some custom code and i want my parameter prompts to be populated based on the returned values from that custom code. I don't want these do be fixed texts.

Thanks,

Simranjeev

Can you use SWITCH option?

Igor.

|||

But the Switch option is also used inside an expression, and i don't know how to create the parameter labels from expressions.

On my report, I have a parameter called "StartDate". I've given it the label "Start Date:". Now i want that when the locale of the user is English, this label should be displayed as "Start Date:" and when the locale of the user changes to some other language, lets say German, then the label should be displayed as the translated value for "Start Date:" in German.

I've already done this for the textboxes on my reports, by calling custom code which reads from resource files depending on user locale. I wanted to know if this is possible for parameter labels as well, as i couldn't find any option to do so.

parameter problem with report

I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name

The data source for this report is an oracle db

SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND

(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")

GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,

sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||

Can you mark the helpful response as an answer?

Parameter Either Or

Hi

Background

I have a report that can be either date based (start and end dates, shows multiple jobs) or job number based (shows one job). Parameters all setup and it's working, it uses one SP. I have Allow Null selected for each of the three possible entry boxes. SP says IF JobNumber IS NULL Then Do this ELSE Do that

Problem

It looks messy. I have a label on the start date parameter "EITHER Please select a start date:" And a label on the job number parameter "OR Please select a job number:"

The user can then tick/untick NULL and enter either a date or a job number. Any way of making this work a little better? An initial parameter of report type > Job Number Or Date. Then just show the appropriate parameter for example. To do this I'd need to (I think) dynamically hide a parameter, can this be done? Any other ideas?

Cheers

Just have one parameter (string)

You can always use a code block to check formatting and provide msgboxs.

Then just convert the string to the required datatype|||

You can have three parameters. Use first one to decide whether you would want to run the report with parameter 1 or Parameter 2.

So, the out of three user will pick 2 parameters. and in the back end you will use the first one to decide which one to use and which one to discard.....

|||

Thanks for the replies. Adolf - I have 3 parameters so 1 free entry text box isn't really going to work for me.

TechQuest - I have no problem making it work in the back end, I want the front end to display either parameters start & end dates or parameter job number. If I have the initial parameter with options of Date Or Job Number. They select Date, how do I make just the Date parameters appear?

I may not understand exactly what you mean but you say the user will pick 2 out of the 3 parameters and the back end will sort it, but that's what I have now

|||

Unfortunately there is no way for you to achieve a much better user experience. RS does not allow you to show/hide parameters dynamically at run time.

What I suggest is maybe setting the value of the unused parameter something meaningfull to show that it should not be used. A lot depends on your current setup of the parameters i.e is job id a dropdown or a text box? Is date from and to date picker controls or textboxes?

So, based on the previous suggestions, I would say have the following:

Parameter 1 = Filter Type {Job ID, Date Range}

Parameter 2 = Job ID, I suggest you make this a dropdown of valid job ID's. When parameter 1 = Date Range populate this dropdown with just 1 value that says "N/A - Please select a date range".

Parameters 3 & 4 = You have a couple options here, if you make them date pickers then you can't set their values to to infomative text. You could if you make them text boxes but then you wouldn't get the nice functionality of a date picker.

Either way it's not going to be as nice as coding your own UI.

|||

Thanks for that Adam. At least I can stop searching for an 'nice' answer now as I know it's not possible. I'll go down the route you suggest.

Cheers

sql

Parameter Either Or

Hi

Background

I have a report that can be either date based (start and end dates, shows multiple jobs) or job number based (shows one job). Parameters all setup and it's working, it uses one SP. I have Allow Null selected for each of the three possible entry boxes. SP says IF JobNumber IS NULL Then Do this ELSE Do that

Problem

It looks messy. I have a label on the start date parameter "EITHER Please select a start date:" And a label on the job number parameter "OR Please select a job number:"

The user can then tick/untick NULL and enter either a date or a job number. Any way of making this work a little better? An initial parameter of report type > Job Number Or Date. Then just show the appropriate parameter for example. To do this I'd need to (I think) dynamically hide a parameter, can this be done? Any other ideas?

Cheers

Just have one parameter (string)

You can always use a code block to check formatting and provide msgboxs.

Then just convert the string to the required datatype|||

You can have three parameters. Use first one to decide whether you would want to run the report with parameter 1 or Parameter 2.

So, the out of three user will pick 2 parameters. and in the back end you will use the first one to decide which one to use and which one to discard.....

|||

Thanks for the replies. Adolf - I have 3 parameters so 1 free entry text box isn't really going to work for me.

TechQuest - I have no problem making it work in the back end, I want the front end to display either parameters start & end dates or parameter job number. If I have the initial parameter with options of Date Or Job Number. They select Date, how do I make just the Date parameters appear?

I may not understand exactly what you mean but you say the user will pick 2 out of the 3 parameters and the back end will sort it, but that's what I have now

|||

Unfortunately there is no way for you to achieve a much better user experience. RS does not allow you to show/hide parameters dynamically at run time.

What I suggest is maybe setting the value of the unused parameter something meaningfull to show that it should not be used. A lot depends on your current setup of the parameters i.e is job id a dropdown or a text box? Is date from and to date picker controls or textboxes?

So, based on the previous suggestions, I would say have the following:

Parameter 1 = Filter Type {Job ID, Date Range}

Parameter 2 = Job ID, I suggest you make this a dropdown of valid job ID's. When parameter 1 = Date Range populate this dropdown with just 1 value that says "N/A - Please select a date range".

Parameters 3 & 4 = You have a couple options here, if you make them date pickers then you can't set their values to to infomative text. You could if you make them text boxes but then you wouldn't get the nice functionality of a date picker.

Either way it's not going to be as nice as coding your own UI.

|||

Thanks for that Adam. At least I can stop searching for an 'nice' answer now as I know it's not possible. I'll go down the route you suggest.

Cheers

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 Available Values

If I have a paramter that has query based available values, how do I get it to use a connection string specified in another paramter to do the query?

The scenario is that I have one set of reports that pull from a number of databases that have identical schemas and each serve a different customer. i need to be able to somehow populate the valid values with the right data from the appropriate database at runtime.

Nevermind, I found it.

I just had to populate an array of ParameterValues with just the connection string and then do a call to GetReportParameters, that made sure that the other parameters had the data that they needed.

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

Parameter & Execution plans.

Hi all,

I have a table TableA with few million rows. When I query TableA , the execution plans changes based on the input parameter as shown below . Why this happens ? How to resolve this ? Any inputs would be appreciated.

SELECT * FROM TableA WHERE Column1 = 1 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 2 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 3 => SELECT -> Parallelism (3%) -> Clustered Index Scan (97%)

SELECT * FROM TableA WHERE Column1 = 4 => SELECT -> Nested Loops -> Index Seek (50%) -> Clustered Index Seek (50%)

(takes a very long time to retrieve the records)

Thanks in advance,

DBLearner.

Hi DBLearner,

SQL Server recompiles your SQL each time becuase you are running Adhoc SQL. You can either create a stored procedure or use Forced Parameterization database option (sql 2005 ONLY).

The reason its using different access methods could be down to what STATISTICS are stored for those values. You might want to update stats and try it again.

Jag

|||

what all are the indexes created on this table/column. ALso tell us the approx occurence of this column value (if its not PK).

Madhu

ParallelPeriod Calculated Member - Don't want to tie to year

I currently have Calculated Member in a cube that shows ParallelPeriod using a hierachy based off the year. So you always get prior year amounts, even while drilling into the hierarchy. Here is this Calculated Member (nothing special). This takes the amount you are on and subtracts the previous years amount.

Code Snippet

[Measures].[Amount] - ([Measures].[Amount], ParallelPeriod([Date].[Year - Quarter - Month].[Year], 1, [Date].[Year - Quarter - Month].CurrentMember))

What I am looking to do is always show the prior period based on where you are in the hierarchy (and do it with one calculated member). So I want to take amount and subtract it by the previous period based on where you are in the hierachy (Q2 2007 - Q1 2007, and not Q2 2007 - Q2 2006). This would be an example.

Hierarchy is Year - Quarter - Month. When looking at Years, you see previous year....when you drill into Quarter, you see the previous Quarter of the same year (so you see Q2, 2007 compared to Q1, 2007 and not Q2, 2006). And so on.

Hope that makes sense. Any help is greatly appreciated.

Would PrevSibling be applicable here.

Steve

|||Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)
|||

Jeffrey Wang wrote:

Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)

This seems to work. I knew it was something straightforward. Thank you very much for your help!

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
>

Paging in MDX

Hi

I am new to mdx.i want to know can we implement paging in mdx query? is there any function in mdx so that i can return resultset based on count.i am using topcount function to return 10 rows.but i want to return result for next top 10 and so on.. in same query. like we do in paging.

Thanks in advance

There are probably ways of doing this, one that comes to mind would be to use a combination of topcount and tail

eg. page1... TopCount( <set>, <expression>, 10)

page2... TopCount( <set>, <expression>, 20).tail(10)

Pagination Issue when Rending to PDF

I am having a pagination issue with a report when I render to PDF. The
report is a patient form that is based on a single record that is returned
from a stored procedure. Most of the records are output fine, but some
records are not paginating properly and leave 3/4 page of blank space. The
field that follows on the next page is short and should definitely fit on
that page. The report renders fine in HTML, but the requirement is a PDF
format.
The body of the report is contained in a table. Each field of the report is
contained in its own row. If the field is blank, the entire row is hidden so
that only populated data is output in the report.
In troubleshooting this, I have removed the logic that hides the rows, but
the pagination is still not correct.
I have also made sure that the data is clean and doesn't have whitespace.
Any suggestions as to how to fix this problem?
TIAOn Dec 18, 8:47 pm, JC <J...@.discussions.microsoft.com> wrote:
> I am having a pagination issue with a report when I render to PDF. The
> report is a patient form that is based on a single record that is returned
> from a stored procedure. Most of the records are output fine, but some
> records are not paginating properly and leave 3/4 page of blank space. The
> field that follows on the next page is short and should definitely fit on
> that page. The report renders fine in HTML, but the requirement is a PDF
> format.
> The body of the report is contained in a table. Each field of the report is
> contained in its own row. If the field is blank, the entire row is hidden so
> that only populated data is output in the report.
> In troubleshooting this, I have removed the logic that hides the rows, but
> the pagination is still not correct.
> I have also made sure that the data is clean and doesn't have whitespace.
> Any suggestions as to how to fix this problem?
> TIA
You will want to check the width of the report. Normally, if it is
wider than 6.5" in design view, it will wrap once exported to PDF.
Also, if you are using a table control, you will want to put it into a
rectangle control, this should shrink the extra space. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the quick reply. The report is slightly wider than 6.5" in design
mode to incorporate the header (which renders fine). The fields before and
after the blank space are very short (like 1 word and a label).
I did try putting the table in a table control, but that didn't do anything.|||On Dec 18, 9:43 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks for the quick reply. The report is slightly wider than 6.5" in design
> mode to incorporate the header (which renders fine). The fields before and
> after the blank space are very short (like 1 word and a label).
> I did try putting the table in a table control, but that didn't do anything.
You're welcome. Did you mean you put it into a table control or a
rectangle control?
Enrique Martinez
Sr. Software Consultant|||Sorry. I meant that I put the table control inside the rectangle control. I
just removed all code that controls the visibility property for each row, and
the pagination problem went away.
The problem is that the customer only wants rows of the table to appear that
have data. Here's the code on the visiblity property of the tablerow:
=iif(Fields!FirstSignature.Value = string.empty, True, False)
It does work as it is supposed to, but it seems that the space is still
being allocated for the row.
BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
the same results.|||On Dec 18, 11:05 pm, JC <J...@.discussions.microsoft.com> wrote:
> Sorry. I meant that I put the table control inside the rectangle control. I
> just removed all code that controls the visibility property for each row, and
> the pagination problem went away.
> The problem is that the customer only wants rows of the table to appear that
> have data. Here's the code on the visiblity property of the tablerow:
> =iif(Fields!FirstSignature.Value = string.empty, True, False)
> It does work as it is supposed to, but it seems that the space is still
> being allocated for the row.
> BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
> the same results.
You might try =IIF(Fields!FirstSignature.Value = Nothing, True, False)
or =IIF(IsNothing(Fields!FirstSignature.Value), True, False)
Either should do what you want. Make sure you set the property for
the row and not the individual cells/textboxes.|||Thanks, Toolman.
I will try that too. Is using the table + row format to hide empty fields
the best practice to accomplish this? I was also going to try to set the
height to 0 if it was going to be hidden. It just seems that SSRS is taking
the empty rows into consideration when making the pagination decision.|||On Dec 19, 2:32 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks, Toolman.
> I will try that too. Is using the table + row format to hide empty fields
> the best practice to accomplish this? I was also going to try to set the
> height to 0 if it was going to be hidden. It just seems that SSRS is taking
> the empty rows into consideration when making the pagination decision.
I believe that it is. If for no other reason than the convenience of
not having to enter the expression for each textbox/cell. Also, my
experience has been that using a table rather than a collection of
boxes just works better in general. I know that trying to hide the
entire row by hiding all the cells doesn't seem to close up the white
space.