Reporting Business Requirements Document – SSRS BRD

There comes a time in every developers career where you will receive change request after change request on what in the beginning seems like an “easy” report.  This can increase development time and create unnecessary tension and frustration between the end business user and the developer.  How about those times where you encounter scope creep?  Annoying right?

I have been put into a position to create business requirement documentation or a BRD for the reporting groups to help streamline the issues that we were facing in the above paragraph.

In general, I think the most important features are:

1. Report mock-up
This has been a LIFE SAVER!  Seriously….This should be the easiest piece for the business user slapping together the document.  Go into excel, create what you want the report to look like colors, layout, etc.  Paste it into the requirements document.  It alleviates any confusion for the developer on something as simple as the column headers or color schema.

2. Calculations
Believe it or not this has been a tough one to get the business user to understand.  Especially where I am now, they use different calculations for the same ‘meaning’ depending on the client.  This can become confusing and flat out make a data warehouse impossible to obtain…But on the flip side, having these all up front can help the developer determine if the SQL engine will be best utilized in the dataset or within the RDL itself.

3. Roles – Developers, Testing, Approvals
Seems like a no brainer, but you would not believe how many times I have had to go back to the requestor and ask who will be validating and testing their material.

I highly encourage you to take a look at the document I have attached.  It is the form I created and fine tune it to meet your organizations needs.

Reporting Business Requirements Document

Posted in SQL Server Reporting Services, SSRS | 1 Comment

Aggregate a Sum/Running Total in SQL

Although most of my time is spent in Reporting Services, I often run into the question of “Can I have a running total and then run calculations off that running total?”  You can see how it might quickly escalate into please turn this one little bit of data into a complicated statistical analysis without using Analysis Services and other lofty dreams….YES almighty client I can do that for you (or at least says the salesman)!  :)

I have built a piece of code that you may mold into whatever your requirements may be.  Let me set up a scenario for you.  Let’s say the requirement is that you need 7 days of data and regardless if the day has data you still want to see a 0 or the running total.

The first piece is to create a table variable that inputs the first column of data that you need to be “static”.  This can be every day between a date range selected, a set of integers, but in this case it will be integers 0-6 to represent day difference between the date in your aggregations and today, so if the date in the aggregation is the same as today you will see 0.  This while loop basically inserts the values you set the @integer to be between, in this case 0-6.

So this is what your outcome will look like if you run SELECT * FROM @Projected after the query:

Timeframe

0

1

2

3

4

5

6

The following will insert the timeframe and sum values for the calculations you are looking to find.

Output:

Timeframe

Success

Contacts

0

1715

2342

1

271

466

2

102

200

3

77

141

4

46

105

5

0

0

6

34

79

Next we will add in our running total of Contacts by using a join on the result set.

Output:

Timeframe

Success

Contacts

Running Total Contacts

0

1715

2342

2342

1

271

466

2808

2

102

200

3008

3

77

141

3149

4

46

105

3254

5

0

0

3254

6

34

79

3333

I hope this gives you a general understanding of how to aggregate a running total using SQL alone.

Here is the link to the full code to see it as a whole:

https://gist.github.com/5668020

Posted in SQL Server 2008, SQL Server 2008 R2 | Leave a comment

Report Server ExecutionLog SSRS

The report server execution log contains information about reports that are run.  The execution log can be very useful when trying to troubleshoot issues on the report server (such as ClockSkew and Blocked Process).  As a database administrator you can use this information and relay back to the report developers how they may improve performance of specific reports that are hindering the server’s performance.

There are three standard views for the execution log:

dbo.ExecutionLog – Available since 2000
dbo.ExecutionLog2 – Introduced in 2008
dbo.ExecutionLog3 – Introduced in 2008R2

I thought it might be of most use to break this blog up into 3 sections and dive into each of the columns within each view.  So let’s start with dbo.ExecutionLog!

dbo.ExecutionLog

Example Query:
SELECT *
FROM dbo.ExecutionLog E (NOLOCK)
WHERE
     TimeStart >= '9/18/2012'

Column Name

Description

InstanceName

Name of server that handled the request.

ReportID

Report ID can be joined to Catalog.ItemID to find the Report Name.

UserName

User that executed the report.

RequestType

Who requested the report:

System (subscriptions) also can be thought as any windows service process – 0

User, also can be thought as any web service process or interactive request– 1

Format

Rendered format, will be RPL when web service process.

Parameters

The parameters used on the report execution.

TimeStart

Start time of report.

TimeEnd

End time of report.

TimeDataRetrieval

Number of milliseconds to retrieve the data.

TimeProcessing

Number of milliseconds to process the report.

TimeRendering

Number of milliseconds to render the data or generate the output.

Source

Source of the report execution.

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (i.e., Report Builder)
6: Session (i.e., another request within existing session)
7: RDCE (i.e., Report Definition Customization Extension) 

Status

rsSuccess – Report completed successfully 
rsProcessingAborted
– Report failed
Error code – Report failed

If more than one error occurs, only the first is logged.

ByteCount

Size of the rendered report in Bytes.

RowCount

Number of rows the query or queries return. 

 

dbo.ExecutionLog2

The difference between ExecutionLog2 and ExecutionLog is not much.  They view added a few new fields and renamed a few of the older ones.

Example Query:
SELECT *
FROM dbo.ExecutionLog E (NOLOCK)
WHERE
     TimeStart >= '9/18/2012'

Column Name

Description

InstanceName

Name of server that handled the request.

ReportPath

The path to the report.

UserName

User that executed the report.

ExecutionID

ID associated with the request.

RequestType

Subscription or Interactive

Format

Rendered format, will be RPL when web service process.  If null, the request is an interactive event such as a drill through or interactive sort.

Parameters

The parameters used on the report execution.

ReportAction

Render, Sort, BookMarkNavigation, DocumentNavigation, GetDocumentMap, Findstring

TimeStart

Start time of report.

TimeEnd

End time of report.

TimeDataRetrieval

Number of milliseconds to retrieve the data.  Includes time opening connection to
the data source and time spent reading rows from the data extension.

TimeProcessing

Number of milliseconds to process the report. Includes report and tablix processing.

TimeRendering

Number of milliseconds to render the data or generate the output.

Source

Source of the report execution.

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (i.e., Report Builder)
6: Session (i.e., another request within existing session)
7: RDCE (i.e., Report Definition Customization Extension) 

Status

rsSuccess – Report completed successfully
rsProcessingAborted – Report failed
Error code – Report failed

If more than one error occurs, only the first is logged.

ByteCount

Size of the rendered report in Bytes.

RowCount

Number of rows the query or queries return.

AdditionalInfo

XML data about the execution.

dbo.ExecutionLog3

The difference between ExecutionLog3 and ExecutionLog2 is not much.  The ExecutionLog3 added additional diagnostic information inside the final column AdditionalInfo.  The AdditionalInfo column contains a 1:Many relationship.  Overall the fields structure is identical to ExecultionLog2.

Example Query:
SELECT *
FROM dbo.ExecutionLog E (NOLOCK)
WHERE
     TimeStart >= '9/18/2012'

Column Name

Description

InstanceName

Name of server that handled the request.

ReportPath

The path to the report.

UserName

User that executed the report.

ExecutionID

ID associated with the request.

RequestType

Subscription or Interactive

Format

Rendered format, will be RPL when web service process.  If null, the request is an interactive event such as a drill through or interactive sort.

Parameters

The parameters used on the report execution.

ReportAction

Render, Sort, BookMarkNavigation, DocumentNavigation, GetDocumentMap, Findstring

TimeStart

Start time of report.

TimeEnd

End time of report.

TimeDataRetrieval

Number of milliseconds to retrieve the data.  Includes time opening connection to
the data source and time spent reading rows from the data extension.

TimeProcessing

Number of milliseconds to process the report. Includes report and tablix processing.

TimeRendering

Number of milliseconds to render the data or generate the output.

Source

Source of the report execution.

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (i.e., Report Builder)
6: Session (i.e., another request within existing session)
7: RDCE (i.e., Report Definition Customization Extension) 

Status

rsSuccess – Report completed successfully
rsProcessingAborted – Report failed
Error code – Report failed

If more than one error occurs, only the first is logged.

ByteCount

Size of the rendered report in Bytes.

RowCount

Number of rows the query or queries return.

AdditionalInfo

XML data about the execution.

More on the AdditionalInfo field
Typical Output:

<AdditionalInfo>
<ProcessingEngine>2</ProcessingEngine>
<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
<EstimatedMemoryUsageKB>
<Pagination>44</Pagination>
<Processing>17</Processing>
</EstimatedMemoryUsageKB>
<DataExtension><SQL>1</SQL></DataExtension>
</AdditionalInfo>

<ProcessingEngine>2</ProcessingEngine>
This piece of the XML identifies the version of processing engine that was used for executing the report.
1 = Yukon Engine
2 = On-Demand Processing Engine

<ScalabilityTime>
<Pagination>0</Pagination>
<Processing>0</Processing>
</ScalabilityTime>
In this particular instance, the value 0 means that no time was spent doing any scalable operations.  In other words you have low memory usage (w00t!).  If the value is > 0, then that is the number of milliseconds spent doing the particular task.

<EstimatedMemoryUsageKB>
<Pagination>44</Pagination>
<Processing>17</Processing>
</EstimatedMemoryUsageKB>

This number indicates the memory usage for the data size dependent operations such as grouping, filtering, sorting, any aggregations, page layout and render operations.  Anything under 10000 may be acceptable, but you might want to look into any report that seems to be over 5000.

<DataExtension><SQL>1</SQL></DataExtension>
This identifies the data extensions and data sources that are utilized for the report.  The number in the middle tells you how many times the specific type of data source is queried.

Posted in SQL Server 2008, SQL Server 2008 R2, SSRS | Leave a comment

Execute SSRS Report Subscriptions Manually

Execute SSRS Report Subscriptions Manually.  The following code will allow the report developer to manually execute a report subscription if the subscription has failed (or is needed to be run ad-hoc).

1. Open SQL Server Management Studio
2. Connect to the Report Server.
3. Choose ReportServer for the database.
4. Execute the following Query to determine the SQLAgent Job Name (Job ID).

/*Execute SSRS Subscription Manually*/
/*Connect to Database ReportServer*/
SELECT
     S.ScheduleID AS SQLAgent_Job_Name
     ,SUB.Description AS Sub_Desc
     ,SUB.DeliveryExtension AS Sub_Del_Extension
     ,C.Name AS ReportName
     ,C.Path AS ReportPath
FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE
     C.Name LIKE '' --Enter Report Name to find Job_Name

5. Connect to MSDB Database on the Report Server.
6. Insert the SQLAgent_Job_Name in the following and execute.

/*Connect to Database MSDB on the Reporting Server*/
/*Enter SQLAgent_Job_Name to execute the subscription based on Job ID*/
USE msdb
EXEC sp_start_job @job_name = –Enter SQLAgent_Job_Name

Posted in Uncategorized | 1 Comment

Alternating Row Background Color for SSRS Matrix with 1 Row Group

So in SQL Server 2005 BIDS, they came out with this awesome new feature called a Matrix!  Yea yea, a little slow to the game, but none the less, the matrix is a fun feature and very very useful for dynamic information.  The problem comes with dynamic information that you are not able to alternate the row color very easily, because the matrix has to be built on the fly and the data and order can change each time the report is run.  I was getting so aggravated searching google and just being sent to only a few sites/blogs that had a solution, but they were sooooo hard to follow!  So here is a step-by-step help on how to alternate row coloring on a matrix that only has 1 row group.

1. Create a new report (Set up your data source(s), data set(s), and parameter(s).)

2. Drag a Matrix over from the Toolbox bar.

3. Drag fields onto your matrix.

4. Right-click on Row Group, “Deal Type” (innermost, in this case the only one).  Select Add Group > Child Group….

5. In the Group By field set value =1  and click OK.

6. Highlight the header of the first two columns.  Right-click and click Merge Cells.

7. Right-click on the data box for Group 1 and select Text Box Properties.

8. On the General tab, insert the following equation for Value: =iif(RunningValue(Fields!DEALTYPE.Value,CountDistinct,Nothing) Mod 2, “LightBlue”, “White”)

9. On the Font tab, set Size to 0in.

10. On the Fill tab, set Fill color to =Value.

11. On the Border tab remove the left border and then click OK.

12. On the Properties tab, under Font set Color to =Value.

13. Right click the data cell of the first column and select Text Box Properties.

14. On the Border tab remove the right border and then click OK.

15. On the Properties tab, under Fill, set Background Color as: =iif(RunningValue(Fields!DEALTYPE.Value,CountDistinct,Nothing) Mod 2, “LightBlue”, “White”)

16. Highlight the data cell of the last column and on the Properties tab, under Fill, set Background Color as:
=ReportItems!Group1.Value

17. Drag the right border of the middle column (the group column) to the left to be barely visible.

Voila!  Final Result.  Note that I masked the font color so you can’t see the numbers, but they are there :).

Posted in BIDS, SQL Server 2008, SQL Server 2008 R2, SQL Server Reporting Services, SSRS | 7 Comments

Dynamic Grouping in SSRS Parameters Pt.2

Overview:

This blog I discuss how to do dynamic grouping using SQL Server Reporting Services (SSRS).  This part will focus on setting up the parameters, datasets, and data source for the report.

Step-by-Step

  1. Open Microsoft SQL Server Report Bulider.
  2. Create New Blank Report.
  3. In the Report Data pane, click New > Data Source….
  4. Create a connection to your Database where the stored procedure was run.
  5. Click OK.
  6. In the Report Data pane, click New > Dataset….
  7. On the Query tab, type Group1 as the Name.  Select Use a dataset embedded in my report. Select the Data source that was created in Step 4.  Select Query type  as Stored Procedure and then select TrackingElements_Group1 from the drop down list.
  8. Click OK.
  9. Repeat Steps 6-8 for Group2 and Group3 respectively.
  10. In the Report Data pane, click New > Parameter…
  11. On the General tab, create a Name and Prompt.  The Data type will be Text and select Allow blank value (“”) and Allow null value.
  12. On the Available Values tab, select Get Values from Query.  For Dataset select Group1, Value field select Groupby and for Label field select GroupBy.
  13. On the Default Values tab select Specify values.  Click Add.  Click the fx (function) icon and use the value =”".
  14. Click OK.
  15. Repeat steps 10-14 for Groups 2 and 3.
Posted in Uncategorized | Leave a comment

Dynamic Grouping in SSRS Parameters Pt.1

Overview:

This week I discuss how to do dynamic grouping using SQL Server Reporting Services (SSRS).  This is not an out-of-the-box feature and needs a bit of manipulation.  I had a recent client ask to build a set of reports that they could choose up to ten parameters dynamically that would determine the grouping on the report.  They wanted the ability to choose up to ten parameters, each parameter a single select, and the drop-down to update so that if an item was chosen you would not see it in the next drop-down.  They also wanted to see subtotals for each grouping.

For simplicity sake, I have broken this blog into two sections and I will only focus on three parameters.  The first section will cover the stored procedure that will need to be put on the server.  The second section will cover how to implement the dynamic group by and the subtotal group features into Business Intelligence Design Studio (BIDS).

 

Step-by-Step

 

  1. Open SQL Server Management Studio
  2. Click New Query.
  3. Insert a database table and add/remove the amount of elements needed using the following code. 
  4. Press F5 to run the stored procedure.
  5. Your SQL Server should now look like the following.

 

Posted in BIDS, Parameter, SQL Server 2008, SQL Server 2008 R2, SQL Server Reporting Services, SSRS | Leave a comment

Create a Solution in Business Intelligence Development Studio

Overview

In Business Intelligence Development Studio (BIDS), a project holds one type of object.  An object can include an SSRS report, SSIS package, etc.  I will use projects to break down my departments or functions within the company.  So for example, our company runs reports off of its customer relationship management (CRM) system.  So, I have a “CRM” project, that contains all my SSRS reports that link to the CRM system.

A solution will contain one or more projects.  The projects can be of different natures.  For example, you can have a SSRS and SSIS project within one solution.  I use this daily when I work on client projects as it keeps SSAS cube building much easier to work with.

In this blog, I will walk you through creating a new solution and adding a SSRS, SSAS, and SSIS project into that new solution.

Step-by-Step

Follow these steps to create a blank solution in BIDS:

1.  Click File > New > New Project.
2.  Click + next to “Other Project Types”.  Then click Visual Studio Solutions.
3.  Choose Blank Solution.  Type a name on the Name field and then click OK.

Follow these steps to add a new Report Project to a solution in BIDS:

1.  Click File > Add > New Project.
2.  Click Report Server Project.
3.  Type a name in the Name field and location in the Location field.
4.  Click OK.

Follow these steps to add a new Analysis Services Project to a solution in BIDS:

1.  Click File > Add > New Project.
2.  Click Analysis Services Project.
3.  Type a name in the Name field and location in the Location field.
4.  Click OK.

Follow these steps to add a new Integration Services Project to a solution in BIDS:

1.  Click File > Add > New Project.
2.  Click Integration Services Project.
3.  Type a name in the Name field and location in the Location field.
4.  Click OK.

In the end, your Solution Explorer will look like the following image:


Posted in BIDS, Uncategorized | 1 Comment