question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked db042190-2983 answered

what is the best practice for issuing an error from ssrs?

hi we are currently on 2014 enterprise and will be on 2019 enterprise in the coming months.

on at least 24 of our reports, there is a set of parameters called minimum balance and maximum balance. I can get into a long explanation about negatives meaning the customer owes us and positives meaning we owe him, but the bottom line for this question is that by definition zero is invalid for a min or max balance.

i suspect that is difficult if not impossible to control from ssrs. who knows. maybe there is a way to introduce an ssrs expression based function before the proc is run?

so we are thinking about either throwing an exception in the proc or returning one liners to our tablix's with the appropriate error. exceptions sound like the more appropriate way to go but then we wonder about the whole "enabling remote errors" thing being a prerequisite to the user understanding what he sees once we throw the exception.

can someone guide us on best practice? we dont enable remote errors right now and i dont think we will be doing that.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @db042190-2983,

Do you mean that when the parameter of minimum balance and maximum balance reach a certain value , the report would like to generate a message saying something like "Error Message" ?

If yes, I think custom code will be meet your requirement.

Here is a same sample you may take a reference: http://experiencingmsbi.blogspot.com/2011/09/ssrs-calendar-and-date-restriction.html

If I misunderstand your need, please incorrect me.

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

thx zoehui. yes, if either the min param value entered is zero or the max is zero we want to generate an eye catching error and block generation of a dataset. i'm going to read your article now.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

that is definitely an option but i was hoping to invoke such a function before the proc is run and stop the report rendering cold or throw an exception from the proc without affecting existing or adding new textboxes.

is there a way for a raised error message to show "as is"? ie in the same area and in that familiar small font that general but ambiguous errors like insufficient permissions or dataset errors show? see below what i'm talking about where i removed a proc...

147759-sampleexceptionwhenprocremoved.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

i added the code you see below to the proc and get the same ambiguous error shown in the last post...

 if (@pMinBalance=0 or @pMaxBalance =0) raiserror (N'Zero is invalid as a balance range boundary',10,1)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @db042190-2983,

Thanks for your detailed response, check below to see if it could bring you some ideas.

In my case, when the bookid<3, it will thrown 'paramter.value is invalid as a balance range boundary'

In the report add a textbox with expression =IIF(Count(Fields!BookID.Value, "DataSet1")=0,Parameters!bookid.Value+" is invalid as a balance range boundary", nothing)

In the dataset I use below code, you may change it with your data.

 if @bookid>'3'
 SELECT [BookID]
       ,[Book Title]
       ,[Book Price]
   FROM [AdventureWorksDW2012].[dbo].[books]
 where bookid in(@bookid)
 else
 select null

And then check my report as shown.

148121-untitled1.png

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



untitled1.png (6.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

i tried a few things and have to conclude enabling remote errors is the best choice given how inexpensive a solution it is if you introduce a raise error in your proc. but see caveat in last paragraph,

before coming to that conclusion, i tried introducing the vb expression based func you see below and then finding available real estate for a new textbox. And returning no rows (new predicate) in the proc in this situation. This is what zoehui has been recommending. My biggest problem with this is finding the available real estate for a new text box and not upsetting the delicate balance we often have with merge cells when saving to excel.

the only surprise i've seen so far with enabling remote errors and raising your own error (even sev 18) in the proc is that if you don't also introduce the predicate in your proc's select to basically return nothing (like zoehui recommended), your proc actually runs thru the data anyway even though no resultset shows on your report. i dont think this was true before i enabled remote errors and raised sev 18 but i could be wrong. i think before, the raise error halted execution of the proc immediately.

 Function CheckBalanceParameters(p1 as Double, p2 as Double)
 Dim msg as String
 msg = ""
    
 If (p1 =0 or p2=0) Then                                         
 msg="INVALID BALANCE RANGE"
 End If
    
 Return msg
 End Function


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.