[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

why the field does not appear and sorting

Posted on 2011-05-01
8
Medium Priority
?
437 Views
Last Modified: 2012-05-11
Hello:

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!

Apitech
Summary-Sales-By-Product.txt
0
Comment
Question by:apitech
  • 4
  • 4
8 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35500577
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:
=Switch(
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.
0
 
LVL 1

Author Comment

by:apitech
ID: 35504329
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?

Apitech
0
 
LVL 1

Author Comment

by:apitech
ID: 35504447
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.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35504949
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:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d3173a87-7c0d-40cc-a408-3d1a43ae4e33
Nicobo-449674.flv
0
 
LVL 1

Author Comment

by:apitech
ID: 35505030
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.

Apitech
why.docx
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35505133
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.
0
 
LVL 1

Author Comment

by:apitech
ID: 35505211
THANK YOU, AGAIN, NICOBO!!!  FOR REASONS WHICH I DO NOT KNOW WHY, THE DATABASE FIELD HAS SPACES AT THE END!

You're a genius!

Thank you!  I got it working!

Apitech
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35505322
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question