Dataset with dynamic columns in tablix/matrix

问题:

I have a dataset that can come with 5 columns, columns 6, 7, 10, 20, or even 100. At least two are static, the remaining are dynamic (am making a scale according to a table exists in the database).

How can I implement this in Reporting Services?

How do I specify the field of tablix that the value is dynamic?

The end result would be this:

|   TITLE | ENUNCIATION | GOOD | VERY GOOD | BAD | VERY BAD |
-------------------------------------------------------------
| title 1 |  question 1 |    5 |         3 |   1 |        0 |
| title 2 |  question 2 |    1 |         0 |   3 |        0 |
| title 3 |  question 3 |    0 |         0 |   1 |        0 |

|   TITLE | ENUNCIATION |   1  |     2     |  3  |     4    |
-------------------------------------------------------------
| title 1 |  question 1 |    5 |         3 |   1 |        0 |
| title 2 |  question 2 |    1 |         0 |   3 |        0 |
| title 3 |  question 3 |    0 |         0 |   1 |        0 |

Note: The first two are static, the remaining dynamics.

EDIT:

I have this table:

|   ID_SCALE  | ID_SCALE_ENTRY   | NAME          |
--------------------------------------------------
|      1       |        1        |    GOOD       |    
|      1       |        2        |    VERY GOOD  |   
|      1       |        3        |    BAD        |   
|      1       |        4        |    VERY BAD   |    
|      2       |        1        |       1       |    
|      2       |        2        |       2       |
|      2       |        3        |       3       |
|      2       |        4        |       4       |
|      2       |        5        |       5       |
|      2       |        6        |       6       |
|      2       |        7        |       7       |    

EDIT + NOTE: I want to show exactly as shown in the first table. The problem is that (Good, Very Good, Bad, Very Bad, 1, 2, 3, 4) fields are created dynamically and it is not possible to specify this in the tablix.

An example: When I want the field value of the dataset, i put this expression Fields!Good.value But now imagine that it is not "Good" but "1" Fields! Good.value no longer exists. Conclusion, is a dataset with dynamic columns.

回答1:

First - You should use Matrix for that.

Second - To accomplish what you want, in the method you return the data you must return at least three data fields.

Ex:

ColDescription - Column description
StaticRowDescription - Row description / static column value
Value - The real value for that column/row combination.

Got it?

Your Matrix should then be like this:

| Any Text               | [ColDescription] |
| [StaticRowDescription] | [Value]          |

EDIT:

Forgot to mention that Any text is my static column in this example. You can add more to match your need.

---------------------------------------------------------------

An example due to your comment.

This is how you would implement in your return method:

Let's say you have one static column Description and the others (n).

public IEnumerable<YourReturnClass> GetData(int param) 
{
    List<YourReturnClass> returnList = new List<YourReturnClass>();
    foreach (var row in allYourRows())
    {
        foreach (var col in row.getColumns())
        {
            returnList.add(new YourReturnClass(){
                StaticRowDescription = row.Description,
                ColDescription = col.Description,
                Value = myValueAccordingToCurrentColumnXRow()
            });
        }
    }

    return returnList;
}

and this as your matrix:

| Description            | [ColDescription] |
| [StaticRowDescription] | [Value]          |

That way if you had one row in allYourRows() with a description "Expenses" and two columns with the names "Gas" and "Electricity" you would have this as your matrix result:

| Description | Gas | Electricity |
-----------------------------------
| Expenses    | 25  | 150         |

Better?

This is just to explain how matrix works, the way you get your data might be different than the one portraited.

The report (RDL) will be the same, the only change is in how you get your data. If you have a table like this: (this would be a table from your database (where you have stored your data))

| RowDescription | DynamicColumnHeader | Value |
------------------------------------------------
| Year 2013      | January             | 500   |
| Year 2013      | February            | 850   |
| Year 2013      | March               | 265   |
| Year 2014      | February            | 965   |

Then your report would look like this:

| Description | January | February | March|
-------------------------------------------
| Year 2013   | 500     | 850      | 265  |
| Year 2014   |         | 965      |      |

Got the idea?

I attached an image of how your report would look like:

NEW EDIT:

|   TITLE | ENUNCIATION    | [NAME]                          |
-------------------------------------------------------------
| [title] |  [enunciation] |  [ID_SCALE] OR [ID_SCALE_ENTRY] |

*or a multiplication, sum, etc...

EDIT BASED ON LAST COMMENT:

|   TITLE | ENUNCIATION    | [NAME]                          |
-------------------------------------------------------------
| [title] |  [enunciation] | [Count(NAME)]                   |

Is that it?

回答2:

What you want to do is know as a Pivot grid, this is made to summarize data, it contains a row area (where Title and Enunciation fields go), a column area (Where the Q_Name field goes) and an area for the data you want to summarize (in your case a count of something i belive).

You can, of course, do this on pure SQL, but it's a nightmare to do and maintain, even if you have a fixed number of columns. if you are more interested on this approach you can take a look at my answer here: How to make a report with count of type of cases in each month in Acess 2010.

Disclaimer: The tool featured in the screenshot is DBxtra, a tool i promote for reporting and business intelligence.