Reports: Dynamic SQL reports cannot be viewed

  • Article

After data and log files have been imported into the Data Warehouse and you have run the Report preparation Data Transformation Services (DTS) task, you run a dynamic SQL Server report and the pivot control returns the following error message:

"The pivot table list "Pivot Table" could not connect to the data source "". For information about the data source, consult the creator of the file. (0x80040e4d)."

If you are viewing a dynamic report that is accessing a SQL Server database to which you have not been granted read or write permissions, then you will not be able to view the report. This issue does not affect dynamic online analytical processing (OLAP) reports.

The following reports are affected:

  • Ad Reach and Frequency per Campaign

  • Ad Reach and Frequency per Campaign Item

  • Ad Reach and Frequency per Day

  • Campaign Item Summary

  • Distinct Users and Visits by Week

  • Distinct Users by Day

  • Registered Users by Date Registered

  • User Days to Register

  • User Registration Rate

  • Visits by Browser, Version, and OS

Solution

You need to have read and write privileges on the database that is being accessed by the reports. You may want to define a user group in Microsoft Windows 2000 with read/write privileges to the SQL Server database. Then you can add users who require these privileges.

To define a user group with read/write privileges

  1. Create a new user group in Windows 2000. Add any users you want to include in this group.

  2. Click Start, point to Programs, point to Microsoft SQL Server and then click Enterprise Manager.

  3. Expand the console root node to Microsoft SQL Servers/<server name>/Security/Logins.

  4. Right click on Logins and select New Login.

  5. In the SQL Server Login Properties - New Login dialog box, on the General tab, in the Name box, type the new group login name or click Ellipsis to browse for the new group name.

  6. Select SQL Server Authentication and enter a password for the group.

  7. Click Add, and then click OK.

  8. On the Database Access tab, select the Permit box option for the database for which you are granting permissions, for example, Retail_dw.

    The new group login name appears in the User column.

  9. In the Database roles box, check the Permit boxes for db_datareader and db_datawriter, and then click OK.

You can now add yourself and any users to this group for access to dynamic SQL Server reports.


All rights reserved.