Search code examples
sqlsql-serverssrs-2008

SSRS report builder 2.0 store STATIC DATA to use to query results

Does any one know if there is a way to import a spreadsheet into report builder 2.0 and then use my data set to make calculations against.

This might seem like a novice question as my limited experience of report builder does not help.

The reason i want to do this is so that i don't have to have my main data-set run the query on working out averages of hundreds of thousands of records as it take ages to run. by having the benchmark average data static i would want to run my query and do the calculations in report builder which will make it a 100 times faster.

Thank you for your time in advance

Solution

  • You may be able to overcome this by adding Calculated Fields to your dataset (DS). I am assuming that your static data can be related to your dataset by using at least one existing field. Using the Switch function, you can populate your calculated fields. Switch “evaluates a list of expressions and returns an Object value corresponding to the first expression in the list that is True.”

    You can use the function like this:

    =Switch(Fields!DsField1.Value = 2, “Your Value1”, Fields!DsField1.Value = 5, “Your Value2”, Fields!DsField1.Value = 10, “Your Value3”, ….)

    If you have any condition that needs to be checked, you can add it before the Switch statement like this:

    =IIF(Fields!DsField20.Value <>1000, Switch(Fields!DsField1.Value = 2, “Your Value1”, Fields!DsField1.Value = 5, “Your Value2”, Fields!DsField1.Value = 10, “Your Value3”, ….), Nothing)

    You can have your values in an Excel sheet to make the creation of the formula easier. Simply create your formula in the first row, copy the row down to extend your formula, and cover all your values. Then from Excel simply copy and paste the column of data into your calculated field(s).

    Here’s an example of My Excel formula. This is the best I could do as I could not paste the sample here. You can copy and paste these and replace them your own values.

    In Cell-A2 2
    In Cell-B2 YourValue1
    In Cell-C2 YourOtherValue1
    In Cell-D2 YourOtherOtherValue1
    In Cell-E2 YourOtherOtherOtherValue1
    In Cell-F2 ="Fields!DsField1.Value ="&A2&","&""""&B2&""""&","
    In Cell-G2 ="Fields!DsField1.Value ="&$A2&","&""""&C2&""""&","
    In Cell-H2 ="Fields!DsField1.Value ="&$A2&","&""""&D2&""""&","
    In Cell-I2 ="Fields!DsField1.Value ="&$A2&","&""""&E2&""""&","**

    Sorry if there is anything I have missed; I did this in a rush.