sweetbuttercup
asked on
SSRS report arraylist?
I have a report I'm convertting from Crystal to SSRS. In Crystal I used an array at the group level to hold the values then print them in the required format. How do I declare an array in SSRS and populate it then call the values.
Attached is a quick explample of what the report should do.
report.xls
Attached is a quick explample of what the report should do.
report.xls
ASKER
Thank you, mlmcc. I see that array is not the option but I still need to accomplish the report layout using a different approach. I think I have to add a dataset to define the columns but cannot figure it out
I don't use ssrs so I really can't add anything more unless I just search for example. Since it is a weekend, many experts aren't as active.
mlmcc
mlmcc
What you're looking for is the Matrix control.
I'll use the field names as shown in your attachment in the explanation below. I'll assume that your report already has a dataset defined, and I'll also assume that you're using SSRS 2008 (the interface of 2005 differs quite a bit).
So first step: drag a Matrix onto the report design. Now put test_date into the Rows "cell" (just drag & drop the field from the Dataset pane). With the matrix selected so that you've got the Excel-like grey "buttons" to the left and top, right-click the grey area on top of the test_date column and select Add Group > Parent Group. In the popup that appears, select pat_id.
Your matrix should now have three columns in total. Next, drag test_type into the Columns cell, and result into Data.
That's it, when rendering the preview you should see a similar layout as shown in your attachment.
I'll use the field names as shown in your attachment in the explanation below. I'll assume that your report already has a dataset defined, and I'll also assume that you're using SSRS 2008 (the interface of 2005 differs quite a bit).
So first step: drag a Matrix onto the report design. Now put test_date into the Rows "cell" (just drag & drop the field from the Dataset pane). With the matrix selected so that you've got the Excel-like grey "buttons" to the left and top, right-click the grey area on top of the test_date column and select Add Group > Parent Group. In the popup that appears, select pat_id.
Your matrix should now have three columns in total. Next, drag test_type into the Columns cell, and result into Data.
That's it, when rendering the preview you should see a similar layout as shown in your attachment.
ASKER
Thank you Valentino. I'm using Visual Studio 2008.
I already have the matrix functioning and the layout is just the one you described. The report I'm creating is more like a standard form where the columns need to be present even if the test_xx doesn't have any data. Regardless the data the columns are standard and always the same order same position. I have a list of specific test_xx and those are the only ones that must be displayed. I'm using a ref cursor as the dataset and the ref cursor cannot be edited to as it's shared across other reports. So there are test_xx that I don't need to display. In the case of my Crystal Report I have declared an array of 6 elements then I use an If condition to populate the exact test_xx to the assigned position.
I already have the matrix functioning and the layout is just the one you described. The report I'm creating is more like a standard form where the columns need to be present even if the test_xx doesn't have any data. Regardless the data the columns are standard and always the same order same position. I have a list of specific test_xx and those are the only ones that must be displayed. I'm using a ref cursor as the dataset and the ref cursor cannot be edited to as it's shared across other reports. So there are test_xx that I don't need to display. In the case of my Crystal Report I have declared an array of 6 elements then I use an If condition to populate the exact test_xx to the assigned position.
In that case you'll have to turn the rows into columns in your data source, then use regular table in SSRS.
Use PIVOT function or CASE WHEN structure in your data source SQL.
Use PIVOT function or CASE WHEN structure in your data source SQL.
I see, so what you're now looking for is a method to eliminate those test cases you're not interested in without changing the dataset query, right?
You have a couple of options to achieve that. You can either use the Filter on the Dataset or on the Tablix. Both work in the same way. One of the possibilities in getting that to work is:
Hopefully this helps? Let me know if something wasn't clear!
You have a couple of options to achieve that. You can either use the Filter on the Dataset or on the Tablix. Both work in the same way. One of the possibilities in getting that to work is:
set up an additional dataset that returns a list of test cases that you want to display (optional, avoids hardcoding in next step
create a hidden report parameter which uses the dataset from previous step as Default Values
set up a filter on either dataset (the one that returns your actual data) or tablix, with Expression = test_type, Operator = IN, Value = @YourHiddenParam
Hopefully this helps? Let me know if something wasn't clear!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ah, "ref cursor", missed that part from the comment....
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/d1ba3ba8-fbef-4413-a1fb-715ce154da42