Formula to pull value in SSRS based on 2 criteria

SSAS and SSRS
1

I am fairly new to SSRS, and I am attempting to create a formula which will allow me to pull a balance based on both an account number (GLAccountNumber) and a date (DateDate). The GlAccount number is in one query within the report, and the DateDate is a separate query which are parameters for the column headers. These dates are also contained in the main query and named posting date, but I need these posting dates to match the user defined dates which are selected with the parameters.

Essentially I'm looking to create a formula somewhere along the lines of:
=sum(iif(Fields!GLAccountNumber.Value ="1500000",AND(fields!postingdate.value =(ReportItems!Textbox604.value),CDbl(Fields!MTDAVG.Value),CDbl(0.0)))

The textbox in this case is just the column header which is based off of the parameter query. I know the "And" portion of my query is where things start to take a turn for the worse, but I wanted to illustrate what I'm trying to accomplish. Any guidance on how to structure this formula would be much appreciated.

2

=sum(iif(Fields!GLAccountNumber.Value = "1500000" And Fields!postingdate.value = ReportItems!Textbox604.value, CDbl(Fields!MTDAVG.Value), CDbl(0.0))

This reads as:

If GLAccountNumber = 1500000 And PostingDate = Textbox604 Then MTDAVG Else 0

Should work...

1 Like
3

Thank you for the assistance. I'm now receiving the error: There is a syntax error in the Value expression for the textrun ‘Textbox605.Paragraphs[0].TextRuns[0]’: ‘)’ expected.

4

You need an extra bracket.

=SUM
(
	iif
	(
		Fields!GLAccountNumber.Value = "1500000" And Fields!postingdate.value = ReportItems!Textbox604.value, 
		CDbl(Fields!MTDAVG.Value),
		CDbl(0.0)
	)
)
1 Like
5

This worked, thank you!