Designing Techniques in SSRS

INDEX : 

1. Links 

2.  Alternative color in SSRS 2012

3. Color Expression  for SSRS reports in ax 2012.

4.  Display Message when Report is Empty.
5. Change Format of Number Text box, using Code
6. To get New line in SSRS text box
7. visible only in first page of report
8. Change the format of text box Get comma separator for real value instead of '.'
9. Get address in one line :

Replace(First(Fields!CompanyAddress.Value, "SalesConfirmHeaderDS"), Chr(10), ", ") 

===========================================================

http://www.youtube.com/watch?v=nBYn1DU3VMQ - >  Important link to set no.of rows per report.

Dynamics AX 2009 SSRS Tutorials

=======================================================================

 In reporting I add DateDialog this is on current date then the year must reflect to the filter field label of a dialog,qbr.Label(strfmt('FieldLabel',year(dateVal));

Alternative color in SSRS 2012

Write in the expression in SSRS
=iif(RowNumber(Nothing) Mod 2, "White", "WhiteSmoke")

or use

=IIF(RunningValue(Fields!BuildingId.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0,"WhiteSmoke","White")

//Here BuildingId is the group by value to have alternative color for every group.
========================================================================
Custom Format for Date Expression  :

Get  date format as per company format.. ( ex : get Date format as per Company Trans date)

= Microsoft.Dynamics.Framework.Reports.BuiltInMethods.GetExtendedDataTypeFormat("Transdate", Parameters!AX_RenderingCulture.Value)

Colour expression for ssrs reports in ax 2012

For changing colour

Go to design –select fild-rc-properties-colour-expression –write expr

iif(field.value>0,”black”,”red”)

iif(field.value=”fail”,”black”,”red”)

iif(field.value>0, ”black” , ”#0f0f” )

for field back ground colour changing

Go to design –select fild-rc-properties-back ground colour-expression –write expr

iif(field.value>0, ”black” , ”#0f0f” )

for alternative colour rows as white and grey set the table template property as” table style alternative row template”
====================================================================

Display message when empty report "No data found in SSRS"

We can set the property of Dataset ... - RowCountMessage ... 

Instead of Below code... 

In the property window os Visual studio (SSRS) check 'NoRowsMessage' property for tablix. You can set this property to show a custom message when no row is returned.

NORowsMessage = "No data available for current filter selection" //user defined msg that is to be displayed

You can also specify the specific font style like size, color, format etc.

Add a text box with expression =IIF(Count(<SomeId Field>,"DataSet1")=0,"No Data Returned", nothing)

 Or

set the visibility of this textbox as =IIF(Count(<SomeId Field>,"DataSet1")=0,False,True)

In the table header (header fields), use expression for each of the column headers to Set the visibility to false so that the end user won’t be able to see the table header when there is no data.

=====================================================================

https://community.dynamics.com/ax/f/33/t/114872.aspx

AX 2012 formating currency in SSRS

We need two different currency format in our SSRSreport depending of the language of the report :

In English the format should be  99,999.99$ and in French it should be 99 999,99$ (with a comma separating the decimal).

Now,  in my  SSRS report, in the text box properties I have put

the following formula in the custom format of the number :  = iif(Fields!Language.Value = "en-ca", "##,##0.00", "## ##0,00")

It works well in English but in French the format doesn’t work, the comma and the decimals are not showing.

So does anyone knows what is the correct format to replace the dot by a comma as a decimal separator.

=====================================================

 6. New Line in SSRS Text Box

Add New Line in SSRS Text box

Expression used in SSRS reports

=Fields!AddressLine1 + VBCRLF + Fields!AddressLine2 + VBCRLF + Fields!AddressLine3 

e.g:
 Address1
 Address2
 Address3

Note: If VBCRLF is used first or last part the expression, it has no impact on result. If you want a line in the first or last, then use one more extra VBCRLF.

7.Visibility only in first page and hidden in remain pages
 Hidden =IIf(Globals!PageNumber = Globals!TotalPages, False, True) 

==================================================

Example Description	Example
Yesterday	=DateSerial(Year(Parameters!TodaysDate.Value),Month(Parameters!TodaysDate.Value),Day(Parameters!TodaysDate.Value)-1)
Two Days Ago	=DateSerial(Year(Parameters!TodaysDate.Value),Month(Parameters!TodaysDate.Value),Day(Parameters!TodaysDate.Value)-2)
One Month Ago	=DateSerial(Year(Parameters!TodaysDate.Value),Month(Parameters!TodaysDate.Value)-1,Day(Parameters!TodaysDate.Value))
Two Months Ago	=DateSerial(Year(Parameters!TodaysDate.Value),Month(Parameters!TodaysDate.Value)-2,Day(Parameters!TodaysDate.Value))
One Year Ago	=DateSerial(Year(Parameters!TodaysDate.Value)-1,Month(Parameters!TodaysDate.Value),Day(Parameters!TodaysDate.Value))
Two Years Ago	=DateSerial(Year(Parameters!TodaysDate.Value)-2,Month(Parameters!TodaysDate.Value),Day(Parameters!TodaysDate.Value))

==================================================

8. Change format for real value . get Comma separator instead of '.': 

=Replace(Format(Sum(Fields!NetAmount.Value, "AgentFeeBonusHeaderFooterTmp"), "#.00"),".",",")

===============================================