Overcoming Software Limitations in Multidimensional Reports

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
_To_Time_2006
_2006W06_20
06_02_09_00
_00_00

Measures_Sell
_To_Time_2006
_2006W05_20
06_02_02_00
_00_00

Measures_Sell
_To_Time_2006
_2006W04_20
06_01_29_00
_00_00

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.

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |