Archive for category: Blog

Back to my When (null) is not Null post, there is a undocumented breaking change in SQL Server 2005 SP2 which you should be aware of. In response to the customer demand, SP2 treats aggregate rows as detail rows.  As a result, if the report doesn’t use the Aggregate() function, the report will show all rows. For those using the Aggregate function, there will be no change.

If you target the SSRS Report Builder as an UDM client you may find the following two workarounds (aka hacks) useful. Both hacks require manually updating the SMDL definition. Alas, so much about model auto-generation L.

Multi-grain Relationships Don’t Appear

If a dimension joins a measure group at a higher grain the corresponding Report Builder role is excluded from the model. Steps to repro:

1. Open the Adventure Works SSAS project in the BIDS cube designer and switch to the Dimension Usage tab.
2. Note that the Date dimension joins the Sales Targets measure group at the Calendar Quarter level (not the Date level). That’s because the FactSalesQuota (the fact table Sales Targets is based on) stores the sales quotas at the quarter level in AW.
3. Generate a report model from a data source pointing to the Adventure Works SSAS database
4. Open the Report Builder and connect to the Adventure Works cube.
5. From the Sales Targets folder, drag the Sales Amount Quota on the report canvas.
6. Note that the Entities pane excludes the Date dimension.

Hack: Change the SMDL definition manually to include the dropped relationship. To do so, compare the SMDL definitions side-by-side before and after the grain was changed using a file comparison tool such as SourceSafe or ExamDiff. For example, the first missing role entry should look like the one below:

<RoleID>udm:Role_MeasureGroupDimension_Entity_MeasureGroup_RPM_Profitability_Measures_Entity_Dimension_Date_Entity_Dimension_RPM_Date</RoleID>

</RolePathItem>

</Path>

<AttributeID>udm:Attribute_Hierarchy_Date.Date</AttributeID>

</AttributeReference>

<AttributeReference>

<Path>

<RolePathItem>

In my case, I had to restore three entries caused by raising the grain of a dimension relationship. Once the entries have been restored manually and the model was deployed, the reports worked as expected.

Text-based Calculated Members Are No-Show

An UDM text-based calculated member comes back as empty in a Report Builder report. That’s because the Report Builder defaults the data type of the member to Float. Steps to repro:

1. In the Adventure Works sample cube add the following calculated member:
   
   

   Create Member CurrentCube.[Measures].[Hello]
   As “Hello”,
   VISIBLE = 1;
   

2. Assign the member to a measure group so it will show up in the Report Builder.
3. Create a table report and uses the Hello member. The column comes back empty.

Hack: Overwrite the data type of the member in the auto-generated SMDL:

<Attribute ID=”udm:Attribute_Measure_Hello”>

<Name>Hello</Name>

<DataType>String</DataType>

<Nullable>true</Nullable>

…

</Attribute>

Deploy the model and observe that the text-based calculated member now does show.

The ASP.NET ReportViewer control has a HyperlinkTarget property which may give false expectations. You may believe that this property applies to all hyperlinks, including links to drillthrough reports. For example, you may attempt to set the HyperlinkTarget to _blank to open a drillthrough report in a new window. However, you will be disappointed to find that drillthrough reports ignore the HyperlinkTarget property completely. That’s because they require a page postback.

As it stands, the HyperlinkTarget property applies only to “regular” (Jump to URL) hyperlinks. Propagating HyperlinkTarget to drillthrough links is on the wish list for a next release. Meanwhile, if you need to open a drillthrough report in a new window, consider using a Jump To URL link that points to an ASP.NET page hosting the ReportViewer control (or reuse the same one that displays the master report). The ASP.NET page would parse the link and load the drillthrough report in the ReportViewer control. If you set the HyperlinkTarget property to _blank, the drillthrough report will open in a new window because it now launched from a regular link.