Basic Parameter

This is a beginning type of question - I am trying to set up a parameter that
will contain 5 numeric digits. As the user starts typing i.e. a 3 I would
like to see 30000 appear, then if they type a 1 then 31000 will appear, next
if they type a 2 then I would see 31200 then a 5 they would see 31250 and
lastly if they typed a 1 then 31251 would appear in the list and when they
hit enter that would be the number of the work order that appears.
Thank you.On Nov 8, 3:49 pm, NormaD <Nor...@.discussions.microsoft.com> wrote:
> This is a beginning type of question - I am trying to set up a parameter that
> will contain 5 numeric digits. As the user starts typing i.e. a 3 I would
> like to see 30000 appear, then if they type a 1 then 31000 will appear, next
> if they type a 2 then I would see 31200 then a 5 they would see 31250 and
> lastly if they typed a 1 then 31251 would appear in the list and when they
> hit enter that would be the number of the work order that appears.
> Thank you.
Unfortunately, this type of functionality does not exist in SSRS/
Reporting Services (aside from standard auto-complete in your browser,
which isn't quite the same thing). The best way to create this
functionality is to create a custom ASP.NET application that does this
via callback/postback. Sorry that I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Bar Visibility

I am trying to toggle the visibility of a simple bar by the value of a
parameter named DETAIL.
This is the expression used =IIF(Parameters!DETAIL.Value=true,false,true)
When I view the report in .NET IDE it displays fine, when deployed to the
target server I recieve the following error
Reporting Services Error
----
--
a.. Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
a.. Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
a.. Object reference not set to an instance of an object.
----
--
Microsoft Reporting Services
Any help would be greatly appreciated.
-SteveNot sure, but probably this is happening because you are getting a type
mismatch. Try this:
=(Parameters!DETAIL.Value.ToString()="false")
Note that you don't really need to use the IIf statement here, because your
expression evaluates to a Boolean anyway...
"Steven Bennett" <Steven.Bennett@.newsgroup.nospam> wrote in message
news:eYwdp0R7EHA.1204@.TK2MSFTNGP10.phx.gbl...
>I am trying to toggle the visibility of a simple bar by the value of a
> parameter named DETAIL.
> This is the expression used =IIF(Parameters!DETAIL.Value=true,false,true)
> When I view the report in .NET IDE it displays fine, when deployed to the
> target server I recieve the following error
> Reporting Services Error
> ----
> --
> a.. Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> a.. Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> a.. Object reference not set to an instance of an object.
> ----
> --
> Microsoft Reporting Services
>
> Any help would be greatly appreciated.
> -Steve
>|||Hello Sophie,
The ability to toggle visibility works with the text box but not with a
line. Both preview correctly in Visual Studio and both deploy with no errors
and no warnings. When I access the report it throws a rendering error (see
original post).
Please try the following to re-create the error.
Create a blank report.
Add a line to the report.
Create a boolean parameter named DETAIL with a default value of False and a
prompt.
Add the statement "=(Parameters!DETAIL.Value=false)" to the Visibility
property of the line.
Preview the report in Visual Studio.
Toggle the DETAIL parameter to True and then click View Report.
Toggle the DETAIL parameter to False and then click View Report.
Deploy the report to the ReportServer
Access the ReportServer using IE
(http://ReportServerName/ReportServer?/ReportServerFolder/ReportName&rs:comm
and=render)
This is the point where I recieve the exception. Please let me know if you
were succesfull in recreating the error.
-Steve
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:C65hYCX7EHA.2600@.cpmsftngxa10.phx.gbl...
> Hello Steve,
> I recommend you perform the following steps to narrow down the issue:
> 1. Create a new sample report file which includes a text box.
> 2. Set the Visibility property of the text box to the following
expression:
> =IIF(Parameters!DETAIL.Value=true,false,true)
> Note: you need create a report parameter called DETAIL, please refer to
the
> attachment i upload to set the properties of DETAIL.
>
> 3. Try to preview the report.
> If it can be previewed successfully, deploy the new created report. If you
> can deploy the report successfully, it indicates that the problem is not
> related to the expression.
> If you are unable to deploy the new created report successfully, send me
> the Reporting Services Log Files for research. If Reporting Services is
> installed in the 'Program Files' folder, you can find the logfiles in the
> following folder:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles
> Note: please compress the log files before you send it to me.
> If anything is unclear, get in touch. I look forward to hearing from you.
>
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.|||Hello Steve,
Thanks for your reply.
I have reproduced this issue on my side and received the exact same error.
I believe you have found a new bug in Reporting Service.
I have forwarded this issue to the appropriate folks to catch their
immediate attention. They will investigate this issue and determine if it
is a bug at the urgent level.
Thank you for all your feedback on RS. I believe that all customers'
feedback will enhance our products.
Have a nice day!
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Baffled trying to display a parameter label

I have two parameters: year and month. The Month parameter includes All, 1, 2, ect..

I tried to display the parameter slected with the following expression:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(Parameters!DateShippedMonth.Label)) & ", " & Parameters!DateShippedYear.Label

I want it to either display the label (All) or the name of the month. I get an error when All is the parameter selected but not when any month is selected. If I removed the MonthName() function, I don't get an error but I also don't get the month name.

Any ideas?

What error are you getting?

The MonthName takes an integer as MonthNumber. If you are using the parameter label, then I would assume that you are trying to pass a string to the MonthName function.

If you are indeed displaying a number 1,2, 3 etc in the month parameter, then try a CInt function around the label.

BobP

|||I tried both with and without CInt() in the MonthName function. If the parameter selected is a month I don't get an error, which is strange as I SHOULD get an error when I'm NOT using the CInt(). I only get an incorrect input string was not correct format when I add the MonthName function. If I run MonthName alone I get the error on the "All" parameter which is why I tried to pull out the "All" parameter with the IIf|||

It looks like it is validating the MonthName function on it's own, outside the context of the IIF.

So I did this to get it to work:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(iif(Parameters!DateShippedMonth.Label="All",1,Parameters!DateShippedMonth.Label))) & ", " & Parameters!DateShippedYear.Label

BobP

|||

Thanks. I'll try that. I already wrote some custom code to resolve the problem but this looks easier. Although I think I have to use custom code to translate a fiscal period of, say 4, which is really July of the previous year.

Thanks again!

|||

If you are reporting off of a Data Mart/Data Warehouse, your dimDate dimension should take care of that.

Other wise you can use the TSQL function DateName:

Quarter = DateName(q,@.CurDate)

BobP

|||I'm doing mdx queries from a cube and my dimDate month is in integers. If I change it to month names they don't sort correctly.

Baffled trying to display a parameter label

I have two parameters: year and month. The Month parameter includes All, 1, 2, ect..

I tried to display the parameter slected with the following expression:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(Parameters!DateShippedMonth.Label)) & ", " & Parameters!DateShippedYear.Label

I want it to either display the label (All) or the name of the month. I get an error when All is the parameter selected but not when any month is selected. If I removed the MonthName() function, I don't get an error but I also don't get the month name.

Any ideas?

What error are you getting?

The MonthName takes an integer as MonthNumber. If you are using the parameter label, then I would assume that you are trying to pass a string to the MonthName function.

If you are indeed displaying a number 1,2, 3 etc in the month parameter, then try a CInt function around the label.

BobP

|||I tried both with and without CInt() in the MonthName function. If the parameter selected is a month I don't get an error, which is strange as I SHOULD get an error when I'm NOT using the CInt(). I only get an incorrect input string was not correct format when I add the MonthName function. If I run MonthName alone I get the error on the "All" parameter which is why I tried to pull out the "All" parameter with the IIf|||

It looks like it is validating the MonthName function on it's own, outside the context of the IIF.

So I did this to get it to work:

=IIf(Parameters!DateShippedMonth.Label="All",Parameters!DateShippedMonth.Label,

MonthName(iif(Parameters!DateShippedMonth.Label="All",1,Parameters!DateShippedMonth.Label))) & ", " & Parameters!DateShippedYear.Label

BobP

|||

Thanks. I'll try that. I already wrote some custom code to resolve the problem but this looks easier. Although I think I have to use custom code to translate a fiscal period of, say 4, which is really July of the previous year.

Thanks again!

|||

If you are reporting off of a Data Mart/Data Warehouse, your dimDate dimension should take care of that.

Other wise you can use the TSQL function DateName:

Quarter = DateName(q,@.CurDate)

BobP

|||I'm doing mdx queries from a cube and my dimDate month is in integers. If I change it to month names they don't sort correctly.sql

baffled .. no data returned to web browser

Hi.
I have a parameter driven report that has been built and deployed
succesfully with no errors.
After execution of the report I am just left with the format of my
report in my web browser i.e. no data is displayed.
However ...when I run the exact same SP from query analyzer I receive
the correct result set.
In the executionlog table the executioon of the report is marked as
successfull and that a certain amount of bytes have been returned to
the client...
The amount of pages are also displayed indicating to me that it knows
it has received data'
Anyone have any ideas'
LCan you export the report from the web browser? And if so, how does it
look there?

Bad startup parameter on clustered instance

Hello,
I inadvertantly mis-edited the SQL startup parameters on a clustered
instance and now the instance won't start. I'm wondering if there's a quick
fix to correct the problem. (short of uninstall/reinstall <g>)
I've researched this and am aware of the problems changing the local
registry not being persistent due to the cluster-shared reg overwriting the
local version. What I guess I'd like to do is force an overwrite of the
cluster version of the reg setting from the local node. Is there a way to do
this?
Thanks,
Randy Rabin
Well, you shouldn't directly edit the registry entries of a cluster-aware
app like a clustered SQL Server in the first place. That's totally
unsupported. From what you said, you are probably stuck in a vicious cycle.
No matter how hard you try to edit the startup parameter back to its correct
value, your entered value will be overwritted by the bad value, causing the
SQL Server resource to keep failing the startup.
This is because the cluster service checkpoints its changes and basically
replicates the registry changes, and your entered value is overwritten by
the checkpoint saved by the cluster service.
But there is a way to get out of the vicious cycle.
1. Remote console to the node that currently owns the offline SQL Server
group.
2. Open a command prompt on the node, and start up the SQL Server from the
command line with the correct parameters, something like the following:
cmd>sqlservr -sMSSQLSERVER -c -dE:\MSSQL\Data\master.mdl -eE:\MSSQL\Log\errorlog
-le:\MSSQL\Data\master.ldf
** (If this doesn't work, see Note below)
3. Once SQL Server is running, start up Enterprise Manager, and change your
startup parameter(s) there to their correct value. This is critical because
the change made through Enterprise Manager will be saved by the cluster
service in a checkpoint, and will be replicated to the other nodes.
4 . Go to Cluster Administrator, bring up SQL Server resource online.
5. Check the registry entries for the startup parameters to make sure tht
they are still correct, and they should be correct at this point. This step
is not necessarily. But it's just a sanity check.
6. Move the SQL Server resource group to a different node. It should come up
online on that node.
7. Check the registry entries for the startup parameters on the node to make
sure they are correct, and they should be because the startup parameter
value should have been replicated over by the cluster service from the first
node you worked on.
8. Repeat Step 6 and 7 on all the remaining nodes.
**Note that I'm typing this from my recollection. For Step 2, it may also
work if you first edit the local registry to correct the startup parameters,
and then start the SQL Server service on the command line using net start.
If Step 2 as described above doesn't work, give this a try. I know for sure
that this alternative would work for SQL Server 2005 when it's stuck in the
same situation.
***DON'T EVER DIRECTLY EDIT A CLUSTERED SQL SERVER REGISTRY ENTRIES
AGAIN****
Linchi
"Randy Rabin" <randyr@.channeladvisor.com> wrote in message
news:%23mlrK1N0FHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I inadvertantly mis-edited the SQL startup parameters on a clustered
> instance and now the instance won't start. I'm wondering if there's a
> quick
> fix to correct the problem. (short of uninstall/reinstall <g>)
> I've researched this and am aware of the problems changing the local
> registry not being persistent due to the cluster-shared reg overwriting
> the
> local version. What I guess I'd like to do is force an overwrite of the
> cluster version of the reg setting from the local node. Is there a way to
> do
> this?
> Thanks,
> Randy Rabin
>
|||Hi Linchi,
Thank you for the response, but I don't appreciate the soapbox. In fact I
*had* used Enterprise Manager to edit the startup parameter in the first
place. EntMgr wrote the incorrect parameter to the cluster-shared copy of
the registry, which then of course got replicated down to the nodes. The
"fix" I had tried was to re-edit the local node's registry (and yes, I never
do this and agree that normally nobody else should either <g>) and obviously
found out quickly that it wasn't going to work.
I did get the SQL instance running again after a 2-hour call to PSS and a
utility that allowed me to reset the value in the cluster-shared copy of the
registry. Thanks for your solution below, perhaps it will help others.
Regards,
Randy
"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:ubi5OCU0FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Well, you shouldn't directly edit the registry entries of a cluster-aware
> app like a clustered SQL Server in the first place. That's totally
> unsupported. From what you said, you are probably stuck in a vicious
cycle.
> No matter how hard you try to edit the startup parameter back to its
correct
> value, your entered value will be overwritted by the bad value, causing
the
> SQL Server resource to keep failing the startup.
> This is because the cluster service checkpoints its changes and basically
> replicates the registry changes, and your entered value is overwritten by
> the checkpoint saved by the cluster service.
> But there is a way to get out of the vicious cycle.
> 1. Remote console to the node that currently owns the offline SQL Server
> group.
> 2. Open a command prompt on the node, and start up the SQL Server from the
> command line with the correct parameters, something like the following:
>
cmd>sqlservr -sMSSQLSERVER -c -dE:\MSSQL\Data\master.mdl -eE:\MSSQL\Log\erro
rlog
> -le:\MSSQL\Data\master.ldf
> ** (If this doesn't work, see Note below)
> 3. Once SQL Server is running, start up Enterprise Manager, and change
your
> startup parameter(s) there to their correct value. This is critical
because
> the change made through Enterprise Manager will be saved by the cluster
> service in a checkpoint, and will be replicated to the other nodes.
> 4 . Go to Cluster Administrator, bring up SQL Server resource online.
> 5. Check the registry entries for the startup parameters to make sure tht
> they are still correct, and they should be correct at this point. This
step
> is not necessarily. But it's just a sanity check.
> 6. Move the SQL Server resource group to a different node. It should come
up
> online on that node.
> 7. Check the registry entries for the startup parameters on the node to
make
> sure they are correct, and they should be because the startup parameter
> value should have been replicated over by the cluster service from the
first
> node you worked on.
> 8. Repeat Step 6 and 7 on all the remaining nodes.
> **Note that I'm typing this from my recollection. For Step 2, it may also
> work if you first edit the local registry to correct the startup
parameters,
> and then start the SQL Server service on the command line using net start.
> If Step 2 as described above doesn't work, give this a try. I know for
sure
> that this alternative would work for SQL Server 2005 when it's stuck in
the[vbcol=seagreen]
> same situation.
> ***DON'T EVER DIRECTLY EDIT A CLUSTERED SQL SERVER REGISTRY ENTRIES
> AGAIN****
> Linchi
>
> "Randy Rabin" <randyr@.channeladvisor.com> wrote in message
> news:%23mlrK1N0FHA.2212@.TK2MSFTNGP15.phx.gbl...
to
>