why the field does not appear and sorting


Attached is my SQL Reporting Services 2008 rdl file.  In the first field, I have an if, then statement.  But, the data for that field is not appearing.  The purpose of the statement is to display a number corresponding to the second field (the "Product" field).  How can I get that first field to appear?

Also, I want to srt by that first field.  How do I do that?

Thanks!  Any help is appreciated!

Who is Participating?
Nico BontenbalConnect With a Mentor Commented:
I had to make a few changes to the design of your report to get it working without having your database so it is a bit different than you report. But this video demonstrates how I at first have the problem you describe, and then solve it by deleting an recreating the column. Finally I add the sort option.

<<I'm becoming frequently appalled>>
I'm afraid I can't help you with that. But when you have a few year experience with 2005 and only a short time with 2008 it might need a little "getting used to". A person with a few years experience in 2008 who starts with 2005 would also have problems with it.
You could also try Report Builder 3.0 to design your reports. Maybe you like this report designer better than the Visual Studio one. You can download it here:
Nico BontenbalCommented:
Because your first column is on a different grouping level. It is shown only once for all the products (ITMCLSCD). You can solve this following these steps:
1. Delete the first column of your report
2. Right click the column heading of the product column and choose Insert Column,Inside Group - Left
3. Copy the formula you had in your original report to the text box in this column. (oh, step 0 was copy the original formula :-)  )

You could use the Switch function instead of the iif function to save you a lot of )))) at the end. Then your expression looks like this:
Fields!ITMCLSCD.Value = "IV-STD Y", "1",
Fields!ITMCLSCD.Value = "IV-STD NAC", "2",
Fields!ITMCLSCD.Value = "IV-FILTER", "3",
Fields!ITMCLSCD.Value = "IV-NAC & F", "4"

Open in new window

For the sorting you can use the expression to use on your text box with the iif/switch as the sorting for the group. In the Row Groups section at the bottom of your screen right click the (ItemClass) row and choose 'Group properties'. Select the Sorting section on the left. At the right you'll see ITMCLSCD as the sort by. Click the expression button behind it and paste the iif/switch expression into this field.

Also consider using the database to get the number for the ITMCLSCD. If this number was in the IV00101 table you wouldn't need this expression in your report. Now when a row is added to the database you need to change the design of your report as well. But since it looks like you are using MS Dynamics you might not want to do it this way.
apitechAuthor Commented:
Hi Nicobo:

Thanks, for looking into this stuff for me again.

I'm afriad that I'm still not able to get this to work.  There are at least two textboxes in that first column.  When I preview, the column is still blank.  Any ideas as ot what I'm doing wrong?

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

apitechAuthor Commented:
By the way, I'm using the iif function.  Is that why I still cannot get the results of this stupid thing to show up in the column?

I'm becoming frequently appalled at the amount of changes in SQL 2008 Reporting Services vs. SQL 2005.  It's seems like SQL 2005 was so much easier.  I'm having to re-learn the very basics of creating a SQL report.  Microsoft can be so unfair sometimes.
apitechAuthor Commented:
Hi Nicobo:

Thanks.  But, I'm still not able to get it to work.  Data for that first column still does not show.

Attached is a screenshot, though I do not know if it will help.

The only thing that I can think of that I'm doing differently is that I'm using a start date and end date parameter before previewing.  But, I can't believe that that's causing the trouble.

Nico BontenbalCommented:
Change the last line of your expression to:
iif(Fields!ITMCLSCD.Value = "PHSS", "13", "0")))))))))))))))))))
Now the expression returns 0 if no match is found.
If you now have a 0 on every line in the first column you know you have the grouping right, but there is something wrong with your expression. The text is case sensitive so if your ITMCLSCD is in fact "Phss" it wil not match. You can solve this by using:
=iif(UCase(Fields!ITMCLSCD.Value) = "IV-STD Y", "1",
and repeat the UCase function on every line of your expression of course.
If this doesn't help there might be other characters in the ITMCLSCD field that don't show up on the report. Like spaces at the end of the field.
apitechAuthor Commented:

You're a genius!

Thank you!  I got it working!

Nico BontenbalCommented:
I don't know how you solved it but I think it is best to use:
=iif(UCase(Trim(Fields!ITMCLSCD.Value)) = "IV-STD Y", "1",
This way it will work regardless of the casing and leading or trailing spaces in the field.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.