Before we start using these strings in our report, we need to set up the other query strings that pull the actual data from the OLAP cube. If we use the example from the first solution above in a new dataset, our report data will contain something like the following:
|
Measures_Sell |
Measures_Sell |
Measures_Sell |
|
|
Product1 |
192 |
190 |
112 |
|
Product2 |
10 |
12 |
9 |
If we created a matrix within our report and started to drag the dataset fields to our matrix columns, everything would be fine — until next week. Then the value in the first column would be from last week. The report designer would have to go into Visual Studio, rerun the OLAP dataset query, then drag and drop the dataset fields again to the matrix data section.
Inspecting the matrix properties, in the first column, we would see that the value is derived from this code:
=Fields(Measures_Sell_ToTime_2006_2006W05_2006_02_02_00_00_00).Value
Can we simply modify this code somehow to pass in a string? Yes. We can escape out of the code as before with the =”<code>” method, but with a small modification. Since Reporting Services doesn’t like =”Fields(blahblah)”, we’ll use =Fields(“blahblah”).value so it doesn’t complain.
We’ll take our dynamic date string dataset (named DataSet1 in this example) and pass it into our matrix data section code.
=Fields(“Measures_Sell_To_Time_” + First(Fields!C1.Value, “DataSet1”) + “”).Value
Since datasets execute before the report definition evaluates report items, we’re getting the list of dates as strings and passing them immediately to the report headers, which then evaluate the newly made string. We can also use the date string anywhere else we want, most likely in the column header names of the report.
The code =First(Fields!C1.Value, “DataSet1”) can be put in the header textboxes of the matrix column to display the string for our customers.
To review, this solution simply gets a list of the last thirteen dates, converts them to strings, pivots the result set and substitutes these strings into report items.
This solution can definitely be improved upon. No exception handling is done by our stored procedure, a custom pivot function could be used in place of the messy self-join and our derived table could include better-formatted date strings.
Conclusion
With a bit of effort, Reporting Services reports can become self sufficient to the point where they only need to be authored once. Introducing logic into your datasets can provide the functionality you need to make self sufficient, intelligent reports.
]]>