CRM 2011 SSRS: CRM 2011 Online Reports with FetchXML

CRM 2011 has the ability to create custom reports in SSRS using FetchXML.

Before you start, you need to have BIDS installed. BIDS is available as part of SQL Server Install, and is not available free.
Secondly, install Microsoft Dynamics CRM 2011 Report Authoring Extension.


This is how to create reports in SSRS using FetchXML (CRM 2011).

Here are the steps:


1. Use advanced find to create a query that will be used by the report. Choose all the colums that you need in the report. Then select "Download FetchXML" button on the advanced find ribbon. Save the xml file.

 

2. Go to Settings/ Customizations/ Developer Resources and note down the Organization unique name

 

 

 3. Launch BIDS and create a new Report Server Project.

4. In Solution Explorer, right click on reports and select "Add New Report". Give a name for the data source, and select "Microsoft Dynamics CRM Fetch" as the Type. The connection string is in this format:
CRM online url;org_name;home realm url


Home Realm URL is the Identity Provider used by your organization and is needed when your organization uses Federation for identity management. For our setup, I am giving the server url and org name only.

Do not make it a shared data source!

5. Under credentials, provide your live id and password to log into crm online.

6. Create a new data set for the data source created. Select Query Type as "Text". You can now import the FetchXML that was saved in step 1 into the Query area. Choose the Query Designer, and execute the query to make sure you return results that are the same as from the advanced find.

If you want to parameterize the search, replace the value="3" (for last 3 months) with value="@months". Notice I have done the same for total cost.

7. Create the report. From the figure, you will see that I have grouped the report first by Account Name, and I have grouped it further by total revenue for each account and for the grand total.

8. Save the rdl. In crm Online, if you want the report to be part of a solution, go to settings/solution, open/ create a new solution, go to components/ reports and add the new report from file. Choose to display it in the reports section.

9. Run the report from Workplace/ Reports. For this report, I have selected all orders that have shipped in the last 4 months, and have revenue of more than 10,000$.

 

Obtaining a license key from the Microsoft Dynamics CRM database

If all of a sudden it happened that you lost your LicenseKey for MS CRM , it can peep in the database MS SQL.

It should do the following query to your database MS CRM :

  SELECT * from License
  SELECT LicenseKey from License

select * from sys.databases

"Contains one row per database in the instance of Microsoft SQL Server."

select * from sys.databases; 

If the caller of sys.databases is not the owner of the database and the database is not master
or tempdb, the minimum permissions required to see the corresponding row are
ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission,
or CREATE DATABASE permission in the master database. If a database is not ONLINE
or AUTO_CLOSE is set to ON, the values of some columns may be NULL. If a database is
OFFLINE, the corresponding row is not visible to low-privileged users.
To see the corresponding row if the database is OFFLINE, a user must have at least the
ALTER ANY DATABASE server-level permission or the CREATE DATABASE permission
in the master database.

name
Name of database, unique within an instance of SQL Server.

database_id
ID of the database, unique within an instance of SQL Server.

source_database_id
Non-NULL = ID of the source database of this database snapshot.

owner_sid
SID (Security-Identifier) of the external owner of the database, as registered to the server.

create_date
Date the database was created or renamed. For tempdb, this value changes every time the server restarts.

compatibility_level
Integer corresponding to the version of SQL Server for which behavior is compatible:

collation_name
Collation for the database. Acts as the default collation in the database.

user_access
User-access setting:

user_access_desc
Description of user-access setting:

is_trustworthy_on
1 = Database has been marked trustworthy.
0 = Database has not been marked trustworthy.

is_db_chaining_on
1 = Cross-database ownership chaining is ON.
0 = Cross-database ownership chaining is OFF.

is_parameterization_forced
1 = Parameterization is FORCED.
0 = Parameterization is SIMPLE.

is_master_key_encrypted_by_server
1 = Database has an encrypted master key.
0 = Database does not have an encrypted master key.

is_read_only
1 = Database is READ_ONLY.
0 = Database is READ_WRITE.

is_auto_close_on
1 = AUTO_CLOSE is ON.
0 = AUTO_CLOSE is OFF.

is_auto_shrink_on
1 = AUTO_SHRINK is ON.
0 = AUTO_SHRINK is OFF.

state
Database state:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE

state_desc
Description of the database state:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE

is_in_standby
Database is read-only for restore log.

is_cleanly_shutdown
1 = Database shutdown cleanly; no recovery required on startup.
0 = Database did not shutdown cleanly; recovery is required on startup.

snapshot_isolation_state
State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.
1 = Snapshot isolation state ON. Snapshot isolation is allowed.
2 = Snapshot isolation state is in transition to OFF state. All transactions have their modifications versioned. Cannot start new transactions using snapshot isolation. The database remains in the transition to OFF state until all transactions that were active when ALTER DATABASE was run can be completed.
3 = Snapshot isolation state is in transition to ON state. New transactions have their modifications versioned. Transactions cannot use snapshot isolation until the snapshot isolation state becomes 1 (ON). The database remains in the transition to ON state until all update transactions that were active when ALTER DATABASE was run can be completed.

snapshot_isolation_state_desc
Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
OFF
ON
IN_TRANSITION_TO_ON
IN_TRANSITION_TO_OFF



is_read_committed_snapshot_on
1 = Read-committed-snapshot option is ON. Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.
0 = Read-committed-snapshot option is OFF (default). Read operations under the read-committed isolation level use share locks.

recovery_model
Recovery model selected:
1 = FULL
2 = BULK_LOGGED
3 = SIMPLE

recovery_model_desc
Description of recovery model selected:
FULL
BULK_LOGGED
SIMPLE

page_verify_option
Setting of PAGE_VERIFY option:
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM

page_verify_option_desc
Description of PAGE_VERIFY option setting:
NONE.TORN_PAGE_DETECTION
CHECKSUM

is_date_correlation_on
1 = DATE_CORRELATION_OPTIMIZATION is ON.
0 = DATE_CORRELATION_OPTIMIZATION is OFF.

is_auto_create_stats_on
1 = AUTO_CREATE_STATISTICS is ON.
0 = AUTO_CREATE_STATISTICS is OFF.

is_auto_update_stats_on
1 = AUTO_UPDATE_STATISTICS is ON.
0 = AUTO_UPDATE_STATISTICS is OFF.

is_auto_update_stats_async_on
1 = AUTO_UPDATE_STATISTICS_ASYNC is ON.
0 = AUTO_UPDATE_STATISTICS_ASYNC is OFF.

is_ansi_null_default_on
1 = ANSI_NULL_DEFAULT is ON.
0 = ANSI_NULL_DEFAULT is OFF.

is_ansi_nulls_on
1 = ANSI_NULLS is ON.
0 = ANSI_NULLS is OFF.

is_ansi_padding_on
1 = ANSI_PADDING is ON.
0 = ANSI_PADDING is OFF.

is_ansi_warnings_on
1 = ANSI_WARNINGS is ON.
0 = ANSI_WARNINGS is OFF.

is_arithabort_on
1 = ARITHABORT is ON.
0 = ARITHABORT is OFF.

is_concat_null_yields_null_on
1 = CONCAT_NULL_YIELDS_NULL is ON.
0 = CONCAT_NULL_YIELDS_NULL is OFF.

is_numeric_roundabort_on
1 = NUMERIC_ROUNDABORT is ON.
0 = NUMERIC_ROUNDABORT is OFF.

is_quoted_identifier_on
1 = QUOTED_IDENTIFIER is ON.
0 = QUOTED_IDENTIFIER is OFF.

is_recursive_triggers_on
1 = RECURSIVE_TRIGGERS is ON.
0 = RECURSIVE_TRIGGERS is OFF.

is_cursor_close_on_commit_on
1 = CURSOR_CLOSE_ON_COMMIT is ON.
0 = CURSOR_CLOSE_ON_COMMIT is OFF.

is_local_cursor_default
1 = CURSOR_DEFAULT is local.
0 = CURSOR_DEFAULT is global.

is_fulltext_enabled
1 = Full-text is enabled for the database.
0 = Full-text is disabled for the database.

is_supplemental_logging_enabled
1 = SUPPLEMENTAL_LOGGING is ON.
0 = SUPPLEMENTAL_LOGGING is OFF.

is_published
1 = Database is a publication database in a transactional or snapshot replication topology.
0 = Is not a publication database.

is_subscribed
1 = Database is a subscription database in a replication topology.
0 = Is not a subscription database.

is_merge_published
1 = Database is a publication database in a merge replication topology.
0 = Is not a publication database in a merge replication topology.

is_distributor
1 = Database is the distribution database for a replication topology.
0 = Is not the distribution database for a replication topology.

is_sync_with_backup
1 = Database is marked for replication synchronization with backup.
0 = Is not marked for replication synchronization with backup.

service_broker_guid
Identifier of the service broker for this database. Used as the broker_instance of the target in the routing table.

is_broker_enabled
1 = The broker in this database is currently sending and receiving messages.
0 = All sent messages will stay on the transmission queue and received messages will not be put on queues in this database.

By default, restored or attached databases have the broker disabled.

log_reuse_wait
Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
6 = Replication
7 = database snapshot Creation
8 = Log Scan
9 = Other (transient)

If the reason is LOG_BACKUP, it may take two backups to actually free the space.

log_reuse_wait_desc
Description of reuse of transaction log space is currently waiting on one of the following:
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

select * from sys.backup_devices

Contains a row for each backup-device registered by using sp_addumpdevice  or created in SQL Server Management Studio.

select * from sys.backup_devices;


Column names

name:  Name of the backup device. Is unique in the set.
type: Type of backup device:
type_desc: Description of backup device type:
physical_name: Physical file name or path of the backup device.