Conditinal Format in SSRS

If you want 0 to come up instead of 0.0% for your percentage field

=iif(Sum(Fields!Sales.Value) <> "0",Format("0.0%"),Nothing)

Excel report does not show number format even after implementing number format

We noticed that the data which is in number was sometimes not coming up as number inspite of applying the number format for that textbox. To resolve this issue we used CDBL(FieldName).

SSRS excel output merges some cells

Client came back to us that the excel output cells were getting merged and this was because of multiple textboxes aligned with the Tablix. To overcome this issue, we changed the size of the Tablix from inch and cm to pt. We also maintained the distance between each text box as 0 point. 

Filter in MDX

Filter in MDX query

NaN error was encountered in SSRS MDX reports. Usage of Single.IsNaN(Fieldname) did not fix the issue. To remove the NaN error we used the iif statement for the denominator filed to replace 0 with 1

IIF([Meaures].[Plant Share] = "0" , 1, [Meaures].[Plant Share])

We also used  

1.       Not IsEmpty( [Measures].[COG_OQP_INT_m11] ) 

2.       Having  [Measures].[DC Out of Stock Ind] >0

3.       Non Empty {

[Location].[Location].[COG_OQP_USR_SOUTH LL (MONTAGUE)]}

SSRS sorting in report level

Sub report

 master child relationship –

SSRS reports would have 2 tablix and the sorting would be at report level. I,e if Tablix 1 generated 3,4 as data and yablix 2 generated 1,2 as data SSRS report would show 1,2,3,4 as output. On analyzing this functionality was achieved by using the Maser child relationship I,e Subreport concept in SSRS. The main report will have a list of Sl No that has to be in order and the subreport will generate result for every single Sl No. However this reduced the speed of the generating the report. Client was okay with the speed and hence nothing much could be done.

Hyperlink in SSRS for MDX report

Hyperlink

 This project required us to use hyperlinks for Excel and PDF icons to open reports in Excel and PDF respectively. Oracle reports worked as expected but MDX report did not work properly. This is because the data which was passed in the hyperlink was included in brackets[]. We had to replace the [ with the %5B symbol to overcome this issue

="&Globals!ReportServerUrl&/Pages/Report.aspx?%2fScheduled+Reports%2fFoods%2fDaily+Summary+Dashboard%2fPhase+2%2fLongLife%2fAvailability%2fLong+Life+Department+Detail(ProductClass+Group)&pGroup=" & Replace(Replace(Replace(Fields!GroupLabel.Value,"[","%5B"),"]","%5D"),"&","%26") &"&pProductClass=" & Replace(Replace(Replace(Parameters!pProductClass.Value,"[","%5B"),"]","%5D"),"&","%26") &"&rs:Format=EXCEL"

Number format in SSRS is not number format in excel

Number format in SSRS is not number format in excel

Solution
Use - =CDBL(FieldName.Value) in your expression

How to include space in SSRS expression

How to include space in SSRS expression
Solution - Use =chr(160) & FieldName.Value in your expression

Default value in switch statement in ssr

Hi Folks,
Follow the below code to get a default  value for Switch statement used in SSRS
=Switch(Fields!PO_TYPE.Value = "Warehouse",1,Fields!PO_TYPE.Value = "Local",1,Fields!PO_TYPE.Value = "Z",3,1=1,4)

Handle NaN error in SSRS or MDX

Hi Folks!
Today I learnt how to handle NaN error in SSRS. Use the below code for solution


=iif (Single.IsNaN(Sum(Fields!Sales.Value)),"",Sum(Fields!Sales.Value))

Thanks to my friend Anuj for suggesting me this code