Posts

Tips to Optimize SSRS Performance

IC574390 Tips to Optimize SSRS Performance

A sample report.

Following up on my last post, Best Practices for SQL Server Reporting Services, here is a list of tips to optimize SSRS performance.

  • Sub reports are convenient for reuse purposes, but don’t perform well when there are too many sub report instances during the runtime. To avoid the use of sub reports use tools like LookUp , MultiLookUp , LookUpSet, which will bridge different data sources.
  • Also consider using Drillthrough Reports or Nesting of Data Regions if they are applicable to your project.
    • The initial report should be retrieved using pre-aggregated data from one or two levels of drill-down.
    • The innermost drill-down level of the initial report should have conditional, parameterized drillthrough actions.
    • The drillthrough report will contain the original detail data set, but should be parameterized to only retrieve a subset of data based on the drilldown path (e.g. Time = 2008 Q2, Product = Bikes).
  • If images are required, then they need to be embedded.
  • Dataset Sizes and Row Counts should be carefully dealt with.
    • Perform sorting / filtering / pre-aggregation within the database.
    • Don‘t retrieve any unnecessary data.
    • Use uncorrelated sub queries instead of correlated sub queries. (Uncorrelated sub queries have inner SELECT statement that don’t rely on the outer SELECT statement for information. Uncorrelated sub queries run the inner query once instead of running the query for each row when it is returned by the outer query.)
  • Page Header/Footer mustbe formatted correctly; pay special attention to them.
    • Referencing total page count or complex expressions requires full pagination to show on the first page  so avoid doing this.
  • Post-Sort should be aggregate.
    • Ex: Previous, RunningValue, First, Last – More expensive to calculate than other aggregates therefore avoid them.
  • Interactive Sort have a performance impact, particularly when sorting many group or detail instances.
  • Avoid Blank Pages.
  • Use page breaks to improve performance in large reports.
  • Use data set filters instead of query parameters.
    • Using filters retrieves all data, but only relevant data should be displayed to the user. This may be less efficient on an individual report basis in comparison to filtering at the source, however, by doing so data is retrieved once and stored in a snapshot to serve many different user communities.
    • When using query parameters revisit the data source for every new value of the query parameters. Filters will provide snapshots and full parameterization.

For more information about SSRS performance contact us.

The post Tips to Optimize SSRS Performance appeared first on OptimusBI.

Creating a Dashboard for a Legacy System

dashboard-3 Creating a Dashboard for a Legacy System

An example of a dashboard.

Dashboards are becoming a critical part of presentations due to the increased accountability and transparency they provide. Organizations usually center their pre-engagement discussions on leveraging data by using visual intelligence. Organizations now have to determine how they will leverage real-time data of legacy systems to create dashboard(s).

Most dashboards display real time data, which are most common in operational systems. For example, tasks that are completed in a manufacturing unit may be included in a dashboard that displays real time data.

There are specific ways data interacts; data can be pushed to a temporary cache within a dashboard application or can be pulled directly from the transaction system.

It is best to use data in a batch format for transaction-based dashboards, because it creates a data model that allows for optimal dashboard performance. This type of architecture is also popular in designing dashboards for transactional-based legacy systems, since developers won’t need to make any changes to a transactions based system. This is possible with the independent data source for dashboards.

The other advantage of having a batched architecture is being able to keep the data for an indefinite period of time. This is particularly useful when transaction-based applications need to have old data removed due to limitations in database size.

It is best to capture the real-time data needs of the end users during the dashboard requirements process.

For more details on the dashboards requirements, please feel free to contact us.

The post Creating a Dashboard for a Legacy System appeared first on OptimusBI.

Best Practices Checklist: SQL Server Reporting Services

IC140097 Best Practices Checklist: SQL Server Reporting Services

SQL Server Reporting Services

Microsoft® SQL Server™ Reporting Services is a comprehensive reporting tool. When using Reporting Services a variety of reports can be designed and delivered with features ranging from simple tables to online free-form reports with graphical items and interactive links.

Reporting Services can publish reports in a variety of file formats, including HTML, PDF, Excel, and image formats. These options and choices can be problematic for report authors who make numerous decisions when choosing how to lay out a report.

This post lists best practices to follow when developing a SSRS report.

Backup and Restore

Back up and restore the following elements:

Priority

Elements

Highest
  • Report Server Databases
  • Symmetric Key
  • SharePoint Databases (if applicable)
  • Custom Extensions
Medium
  • Configuration Files
  • RSTempDB
  •  IIS Settings
Low
  • RDLs
  • SSL Certificates

 

Note for Report Server Databases:

  • Use standard backup/restore SQL database techniques
  • Don’t need the RSTempDB content
  • Don’t forget to backup SharePoint databases as well!
  • Ensure RSExec Role is created when restoring DBs
  • Configuration files
    • Recover by COPYING settings into default .config files
    • Use RS Configuration Tool or WMI to recover

 

Security

  • Carefully store credentials in report data sources
  • Use read-only accounts!
  • Review credentials options to ensure report authors are not abusing user’s credentials
  • Use Shared Data Sources
    • Control connection strings & credentials
    • Minimize management overhead
    • Data level security
      • Use Kerberos delegation if you need end to end data security
      • Use User!UserId in reports to identify the user within a report
      • Parameters are not a security mechanism

 

Performance and Optimization

To avoid reports with an overload of underlying data sources, determine which reports are most impactful – optimize these reports – retrieve only required data.

Appropriate execution options for reports need to be used.

  • Reports should run “live”, which is the default setting.
  • Reports that are frequently run should have their cache enabled.
  • Execution Snapshots are often overlooked, so have them run during off peak hours
  • Conduct Performance Testing for reports using database tools like SQL Profiler, sqlcmd scripts, and SQL Scaler, and synthetic test tools like VSTS, Precise and Insight.

 
If you want to learn more about best practices for Reporting Services, contact us.

The post Best Practices Checklist: SQL Server Reporting Services appeared first on OptimusBI.

How to Configure Reporting Services on a Custom URL

Recently when working on Business Intelligence projects, we came across various configuration issues with Reporting Services.

This post will provide step-by-step instructions on the configurations we made. This post is a setup guide and doesn’t necessarily consider performance or security.

The environment settings:

  • a SQL Server 2005 Edition
  • a mixed mode security
  • its service status running under a domain login account

 
Initially Reporting Services were not installed, but the installation steps will be outlined later on in the post.

The changes made to the IIS configurations are shown below:

Reporting Services requires an “Allowed” setting only for ASP.NET v1.1.

config-1-1024x670 How to Configure Reporting Services on a Custom URL

Install and configuration steps:

Step1. Create a login account on the SQL Server, which Reporting Services will use to login. The account created for this example is named “ReportingLogin”.

Note: The default database for this login has to be set as master.

config-2-1024x919 How to Configure Reporting Services on a Custom URL

The security settings for this installation:

Untitled-1024x658 How to Configure Reporting Services on a Custom URL

Step 2: Install Reporting Services on the web server. Only ‘Reporting Services’ should be selected for installation on the web server. Nothing in the advance tab should be changed.

config-4-1024x806 How to Configure Reporting Services on a Custom URL

config-5-1024x670 How to Configure Reporting Services on a Custom URL

After clicking next Reporting Services will be installed.

Step 3: Create a DSN entry for any website that needs to be configured with Reporting Services. If an entry is titled ‘Reporting’, the website will also be named ‘Reporting’.

An entry can be named anything except the server name (Exception: If there is a use of server name, then reporting services is to be installed in under the default website).

Step 4: Create a folder with the name of the web site that Reporting Services will run under, which uses any suitable path that can be referenced later.

Step 5: Use Internet Information Services (IIS) Manager to create a new web site.

Config-6-1024x610 How to Configure Reporting Services on a Custom URL

Step 5.1: Right click on Web Sites and select new.

Config-7-1024x564 How to Configure Reporting Services on a Custom URL

Step 5.2: Enter “Reporting” or the name of the desired name for the website in Description and click next.

config-8-1024x796 How to Configure Reporting Services on a Custom URL

Step 5.3: Enter “Reporting” for the host header and leave the default as “All Unassigned”

config-9-1024x796 How to Configure Reporting Services on a Custom URL

Step 5.4: Browse to the path to locate the directory for the website. Also, turn Anonymous access off.

config-10-1024x796 How to Configure Reporting Services on a Custom URL

Step 5.5: Set the permissions, but these can be changed later if needed.

config-11-1024x796 How to Configure Reporting Services on a Custom URL

The website has been created (as seen below).

config-12-1024x625 How to Configure Reporting Services on a Custom URL

Here are the properties pages for the new web site.

config-13-1024x994 How to Configure Reporting Services on a Custom URL

Now we will look at how to configure the reporting services for this website.

Step 1: From the Start menu run the Reporting Services Configuration Manager.

The Machine Name it is asking for is the web server not the database server. It should default to the correct values so just click Connect.

config-14-1024x451 How to Configure Reporting Services on a Custom URL

On the initial screen make sure the Service Status is “Running”.

config-16--1024x702 How to Configure Reporting Services on a Custom URL

Step 2: Go to the tab Report Server Virtual Directory, click the “New” button to create a new virtual directory for report server.

config-17-1024x826 How to Configure Reporting Services on a Custom URL

Change the Website to the website created. In this case it is “Reporting”.

config-18-1024x290 How to Configure Reporting Services on a Custom URL

Here is the successful result.

config-19-1024x826 How to Configure Reporting Services on a Custom URL

Step 3: Go to the tab Report Manager Virtual Directory, click the “New” button to create a new virtual directory for report manager.

config-20-1024x826 How to Configure Reporting Services on a Custom URL

Change the Website field to Reporting.

config-21-1024x290 How to Configure Reporting Services on a Custom URL

Here is the successful result.

config-22-1024x727 How to Configure Reporting Services on a Custom URL

Note: Now, launch IIS Manager and see that two new virtual directories have been created under the website, in this case “Reporting”.

Configuring-Reporting-Services-23-1024x738 How to Configure Reporting Services on a Custom URL

Step 4: Go to the next tab, Windows Service Identity and click Apply. Don’t change anything in it.

Configuring-Reporting-Services-24-1024x711 How to Configure Reporting Services on a Custom URL

Step 5: The next tab is Web Service Identity.

Leave “ASP.NET Service Account” as the default setting. However, a new application pool for the virtual directories needs to be created. Use the default application pool as well. Click “New” to create the new pool.

Configuring-Reporting-Services-25-1024x706 How to Configure Reporting Services on a Custom URL

Step 5.1: Fill out the new window as follows:

Configuring-Reporting-Services-26-1024x691 How to Configure Reporting Services on a Custom URL

Step 5.2: When creating the new application pool for the Report Manager, select the newly created application pool from the drop down.

Click on the Apply button.

Configuring-Reporting-Services-27-1024x767 How to Configure Reporting Services on a Custom URL

Below is a screen shot of the changes that are automatically done in IIS Manager after creating the application pool.

Configuring-Reporting-Services-28-1024x715 How to Configure Reporting Services on a Custom URL

Step 6: Next comes the Database Setup tab configuration. Select the Server Name from the drop-down and click connect.

Configuring-Reporting-Services-29-1024x715 How to Configure Reporting Services on a Custom URL

Step 6.1: Click on the “New” button to create a new database and fill in the details to match the screenshot.

Configuring-Reporting-Services-30-1024x870 How to Configure Reporting Services on a Custom URL

Step 6.1: Then click OK; the main screen updates to this:

Configuring-Reporting-Services-31-1024x827 How to Configure Reporting Services on a Custom URL

Step 6.2: Fill out the details as shown below and click Apply.

Configuring-Reporting-Services-32-1024x827 How to Configure Reporting Services on a Custom URL

Step 6.3: The program will ask for some information that has previously been entered.

Configuring-Reporting-Services-33-1024x611 How to Configure Reporting Services on a Custom URL

The final success screen will look like this.

Configuring-Reports-35-1024x826 How to Configure Reporting Services on a Custom URL

Step 7: Next, access the Encryption Key tab and create a back up Encryption Key. This is extremely important!

Configuring-Reporting-Services-36-1024x711 How to Configure Reporting Services on a Custom URL

Step 8:  Next select the email configuration tab. Email configuration is only required if a subscription set for the reports is needed.

This can be set-up or changed later.

Fill in the email address that will be used to send mail and the IP address for the SMTP server.

Make sure that emails can be sent from this SMTP server. Test this using telnet.

Configuring-Reporting-Services-37-1024x610 How to Configure Reporting Services on a Custom URL

Step 9: Next open the Initialization tab. This option is always disabled (greyed out). However, it’ll show the success check mark.

At this point if the Report Manager web site is accessed the following error page will be displayed.

Configuring-Reporting-Services-38-1024x505 How to Configure Reporting Services on a Custom URL

Listed below are some fixes:

Fix 1. Go to the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer\

Use any editor like notepad to open the rsreportserver.config file

Do a text search for the web server name. In this case its sqltest5. There should only be one occurrence of this text in the UrlRoot Xml tag under the Services tag.

Configuring-Reporting-Services-39-1024x650 How to Configure Reporting Services on a Custom URL

Replace the web server name with the web site name. Save the changes and close Notepad.

Configuring-Reporting-Services-40-1024x547 How to Configure Reporting Services on a Custom URL

Fix 2: Go to the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager\

Open the RSWebApplication.config file in an editor.

Here it is before any changes:

Configuring-Reporting-Services-41-1024x679 How to Configure Reporting Services on a Custom URL

Edit lines 3 and 4 and confirm line 5 is set to FullTrust:

Line #3 <ReportServerUrl>http://Reporting/ReportServer</ReportServerUrl>

Line #4 <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Line #5 <ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>

Save the changes and close Notepad.

Configuring-Reporting-Services-42-1024x526 How to Configure Reporting Services on a Custom URL

Reboot the web server to have these changes take effect.

The websites can now be opened.

The URL http://Reporting/Reports will redirect to the Admin portal where reports can be setup and configured.

Configuring-Reporting-Services-43-1024x670 How to Configure Reporting Services on a Custom URL

Here is a screenshot of the actual user’s reporting portal.

Configuring-Reporting-Services-44-1024x656 How to Configure Reporting Services on a Custom URL

If you’re interested in learning more about Reporting Services comment below or contact us.

The post How to Configure Reporting Services on a Custom URL appeared first on OptimusBI.

5 Steps to Capturing Dashboard Requirements

data-management 5 Steps to Capturing Dashboard RequirementsThe executives of organizations are moving away from large, static views of data. They are now demanding more detailed information presented in intuitive and visual presentations, such as dashboards.

Dashboards provide a quick view of situations. They give a quick and graphical representation of organizational data, which allows for quick decisions. Dashboards help by reducing “Time-To-Decision” and “Time-To-Information” for senior management. However, the key challenge is successfully migrating data from legacy static reports to dashboards, and having interactive methods of accessing data.

Organizations should follow these five key steps to identify dashboard requirements:

  1. Begin by Identifying and interviewing the users, like CEOs, CFOs, and CTOs, and departments, like marketing, sales, and HR, that require dashboards.
  2. Then capture Key Performance Indicators (KPIs), such as top items in sales revenue and marketing contributors to revenue, that are required to be visually captured in dashboards.
  3. Next, identify data sources like as CRM database, financial application database, which facilitate the calculation and presentation of KPIs.
  4. Then streamline the different styles of presentation such as bar graphs and line graphs that will be used for data visualization.
  5. In the steps outlined above make sure to capture future needs

 
If you want to learn more about dashboarding get in touch with OptimusBI!

The post 5 Steps to Capturing Dashboard Requirements appeared first on OptimusBI.

Benefits of Working with a Microsoft BI Gold Partner

MS-Gold-BI Benefits of Working with a Microsoft BI Gold PartnerRecently we were certified as a Microsoft Business Intelligence Gold Partner in Vancouver, British Columbia. This new partnership provides many benefits for our clients, which include:

  • Access to Microsoft software and in-depth product information that helps to guides clients to the right tool and strategy for their Business Intelligence initiative.
  • Various training materials, which help facilitate workshops on Microsoft’s platform and tools with the client.
  • Virtual labs and independent study resources that can be used as reference points when discussing a client’s technical problem.
  • For any client-related technical issues, Microsoft offers a specific number of hours of Technical Enablement and advisory services from Microsoft consultants.

Additionally, clients will find it cost effective to engage with a Microsoft Partner that provides umbrella services (i.e. .Net development, BI, etc.), instead of hiring an individual for each requirement.

Microsoft’s definition of a Gold Partner:

“Gold competencies demonstrate your best-in-class expertise within Microsoft’s marketplace. Earning a gold competency is evidence of the deepest, most consistent commitment to a specific, in-demand, business solution area, along with the distinction of being among only 1 percent of Microsoft partners worldwide that have attained this outstanding degree of proficiency.”

OptimusBI has extensive experience, ranging from reporting services to data management, helps clients set themselves apart from their competition. To provide world-class service, we adhere to a proven methodology, provide a superior client experience, and become a true business partner on every project. We bring a broad depth of industry, business, and technology experience to projects. Our team has a focus on customer satisfaction, productivity, and quality of work. OptimusBI views our Microsoft Gold partnership as an integral part of our offerings.

Building SSRS Reports Using Multiple Data Sources

SQL Server Reporting Services (SSRS) allow users to create reports using data from a variety of sources, including relational databases, multidimensional databases, web services, XML, and other document types. Using SSRS, we can present data from multiple data sources in a single report.

Methods to achieve this include:

  • Using sub reports
  • Using Openrowset queries.
  • Using Report Builder functions like Look Up, Look Up set, and Multi look up.

In this post we will look at Look Up and Multi Look Up functions.

Consider a typical scenario in which we have two different database instances:

  • MS SQL Server Instance – This instance holds the records for customer sales.
  • Oracle Server Instance   – This instance holds the records for customer information.

The SQL server would have customer table with records like:

Dbo.CUSTOMER_TRANS

customer_id

trans_date

amount

1233

2012-03-01 00:00:00.000

100

2708

2012-04-05 00:00:00.000

150

3421

2012-07-22 00:00:00.000

120

8765

2012-08-27 00:00:00.000

109

The Oracle server would have Customer tables containing customer information like:

CUSTOMER_INFO

cust_id

Name

Email

1233

John

john@xyz.com

2708

David

david@xyz.com

3421

Michael

Michael@xyz.com

8765

Jack

jack@xyz.com

In order to fill data from these two sources into a single report, we would follow these steps:

  1. Create and select data sources (that are already present on your report server):

Untitled-1 Building SSRS Reports Using Multiple Data Sources

Untitled1 Building SSRS Reports Using Multiple Data Sources

  1. Create a dataset using Sql server data source (db_Customer_SqlServer):

Untitled-4 Building SSRS Reports Using Multiple Data Sources

And similarly create a dataset using Oracle Data source:

Untitled-3 Building SSRS Reports Using Multiple Data Sources

  1. Add a table to the report and select the SQL server dataset:

Untitled-2 Building SSRS Reports Using Multiple Data Sources

In the report add any columns that you want to display:

Untitled Building SSRS Reports Using Multiple Data Sources

You’ll see that names and emails are not populated from the SQL server dataset. To fill out names and emails we have to refer Oracle server dataset.

Right click the name cell and go to values. This will open an expression builder screen.

Write an expression along the lines of this:
=Lookup (Fields!customer_id.Value, Fields!cust_id.Value, Fields!Name.Value, ds_Customer_Oracle)
Basically the syntax is:
=Lookup (source expression, destination expression, result expression, dataset)
We can perform the same actions for the email value, therefore when we run the report we will get the desired outputs.

The Look Up instead returns a single value only; in this case it will return a name corresponding to a customer ID that it pulls from the SQL dataset. Similarly, Lookupset return an array of values for a particular condition.

If you’re interested in learning more comment below or contact us at info@optimusinfo.com

Multilevel Cascading with Select All

The long-standing issue with cascading parameters in SSRS is that when changing the selection of the “parent” parameter, the default selection of the dependent parameter is not always automatically changed.

After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading.

To address this issue, you need to write a query in your dataset in a manner so that the dependent parameter changes its value every time you change its parent parameter.

I used Northwind database (datasource) for testing.

The steps are as follows:

1. Open Report Builder.
2. Create data source.
3. Create datasets.
4. Create parameters.
5. Assign parameter values to datasets.

Here is the screenshot of what this process will look like:
Multilevel-Cascading-with-Select-All-1 Multilevel Cascading with Select All

 

Data Source

1. Right click on the Data Source folder in the left window.
2. Click on Add Data Source.
3. The following screen will appear:
Multilevel-Cascading-with-Select-All-2 Multilevel Cascading with Select All

 

Datasets

Dataset 1: Country

SELECT DISTINCT Country

FROM Northwind.dbo.Customers

ORDER BY Country ASC

 

Dataset 2: City

SELECT l11.City1+’_’+ CAST(row_number() over( order by  l11.city1 desc) AS VARCHAR(50) )as city11

,l11.City

FROM (

SELECT l1.City+’_’+ CAST(row_number() over( order by  l1.City asc) AS VARCHAR(50) )as city1

,l1.City

FROM (

SELECT DISTINCT City

FROM Northwind.dbo.Customers

WHERE Country IN (@Country)

)l1

)l11

ORDER BY l11.City ASC

 

Dataset 3: Company Name

SELECT l11.CompanyName1+’_’+ CAST(row_number() over( order by  l11.CompanyName1 desc) AS VARCHAR(50) )as CompanyName11

,l11.CompanyName

FROM (

SELECT l1.CompanyName+’_’+ CAST(row_number() over( order by  l1.CompanyName asc) AS VARCHAR(50) )as CompanyName1

,l1.CompanyName

FROM (

SELECT DISTINCT CompanyName

FROM Northwind.dbo.Customers

WHERE City IN(@City)

)l1

)l11

ORDER BY l11.CompanyName ASC

 

Dataset 4: Contact Title

SELECT l11.ContactTitle1+’_’+ CAST(row_number() over( order by  l11.ContactTitle desc) AS VARCHAR(50) )as ContactTitle11

,l11.ContactTitle

FROM (

SELECT l1.ContactTitle+’_’+ CAST(row_number() over( order by  l1.ContactTitle asc) AS VARCHAR(50) )as ContactTitle1

,l1.ContactTitle

FROM (

SELECT DISTINCT ContactTitle

FROM Northwind.dbo.Customers

WHERE CompanyName IN(@CompanyName)

)l1

)l11

 

ORDER BY l11.ContactTitle ASC

 

Dataset 5: Contact Name

SELECT l11.ContactName1+’_’+ CAST(row_number() over( order by  l11.ContactName desc) AS VARCHAR(50) )as ContactName11

,l11.ContactName

FROM (

SELECT l1.ContactName+’_’+ CAST(row_number() over( order by  l1.ContactName asc) AS VARCHAR(50) )as ContactName1

,l1.ContactName

FROM (

SELECT DISTINCT ContactName

FROM Northwind.dbo.Customers

WHERE ContactTitle IN(@ContactTitle)

)l1

)l11

ORDER BY l11.ContactName ASC

 

Parameters

Parameter 1: Country

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-3 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-4 Multilevel Cascading with Select All

 

Parameter 2: City

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-5 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-6 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-7 Multilevel Cascading with Select All

 

Parameter 3: CompanyName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-8 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-9 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-10 Multilevel Cascading with Select All

 

Parameter 4: ContactTitle

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-11 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-12 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-13 Multilevel Cascading with Select All

 

Parameter 5: ContactName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-14 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

 

Now assign parameter values to datasets

Steps:
1. Double click dataset “CompanyName”.
2. The following screen will appear:
Multilevel-Cascading-with-Select-All-16 Multilevel Cascading with Select All

3. Click Okay for both windows.
4. Now double click dataset “ContactTitle”.
5. The following screen will appear:
Multilevel-Cascading-with-Select-All-17 Multilevel Cascading with Select All

6. Click Okay for both windows.
7. Now double click dataset “ContactName”.
8. The following screen appear:
Multilevel-Cascading-with-Select-All-18 Multilevel Cascading with Select All

9. Click Okay for both windows.
10. Now run the report by pressing F5.

If you are interested in learning more, please feel free to ask in the comments below or email us at info@optimusinfo.com

Best Practices to Begin a BI Project

reporting-services-300x192 Best Practices to Begin a BI Project

At the last event I attended, I participated in a discussion about business intelligence (BI) projects going over budget. It was mentioned that companies sometimes struggle to complete projects within a well-defined budget and time frame. In particular, the chances of associated support and maintenance costs operational BI systems was highlighted. Many people have found BI deployment time frames consistently exceeding their deadlines by one to six months. Organizations now fear making investments that will improve their business intelligence competency, mainly due to several associated hidden costs. Consequently, executives are seeking quick ROI results from on their BI projects, which has resulted mainly in requiring BI deliverables to be supplied in weeks rather than months.

Based on my experience working on BI projects, the following five key points have helped my team and I deploy several enterprise-grade projects on time and budget.

  1. Realistic Estimation of Project Duration: Many vendors claim their solutions are customized and able to be deployed in a matter of days or weeks. However, this is only possible if there is a well defined process for data cleaning and integration already in place prior to the vendor’s involvement. Ideally, these processes are automated, otherwise deployments projects can take several weeks, months or even longer. Deadlines need to be identified and discussed during the planning phase.
  2. Availability and Involvement of End Users: End users play a key role in identifying and streamlining data needs and identifying what insights data should provide. Therefore companies should create a BI stakeholder committee that includes individuals from management, IT and end users from various departments.
  3. Proper End-User Training: End user training plays a vital role in the acceptance and adoption of any BI system. Creating a formal training process an environment where new users can lean about the BI system is crucial. However, training programs do add cost and time to a project, therefore estimations should be considered well in advance.
  4. BI Delivery: Delivery is a key component of any BI application – standalone, integrated, and delivered across the organization. The requirements should be listed with the help of end-users so any hidden costs can be prevented. The budget and resources associated with infrastructure and used in the delivery phase should be identified during the pre-planning phase to avoid any unexpected errors during deployment.
  5. Reoccurring Costs: BI requirements constantly evolve for every organization, therefore a solution’s adaptability to the changes (and its relevant maintenance costs) should be considered when budgeting for an initiative.

OptimusBI provides business intelligence and reporting services services in Canada. We specialize in developing cross-platform and cross-application solutions. If you would like to learn more about BI services and experience, please feel free to contact us. We will be happy to sit down and discuss your needs.

If you are interested in learning more, please feel free to contact me at: rupmeet.singh@optimusinfo.com.

Why Do You Need BI Reports in Addition to ERP’s Canned Reports?

business_intelligence_reports-150x150 Why Do You Need BI Reports in Addition to ERP's Canned Reports?

Customized Reports in SQL 2012

In a recent meeting, we had a discussion around the difference between Business Intelligence (BI) reports and canned reports that come with Enterprise Resource Planning (ERP) systems such as Microsoft Dynamics. Similar discussions are now a regular part of technology strategy meetings of many organizations.

This post answers the following question: why are external BI reports still required in addition to reports generated by the ERP solution?

blog-post-26-1 Why Do You Need BI Reports in Addition to ERP's Canned Reports?

Fig 1. Typical ERP Report

Real Canadian Super Store is a Canadian hypermarket chain. Their ERP would capture countless transactions on a daily basis throughout the country. If a Sales Director wants to see a sales report for April 2012, then the ERP system will generate a report similar to Fig. 1.

A typical comparison report would have multiple dimensions such as, Items, Region, and Timeframe. In this example, the ERP’s canned report does not provide enough information on region-specific sales. Additionally, the sales comparison to previous months is missing.

It is often the case that a canned report meets 90% of an organization’s needs, but that last 10% can provide a great deal of value. With our clients, we have often seen that the last 10% of value is added by bringing in data from other systems.

In the example described here, data from a 3rd party system could be brought in to compare publicly available weather information to product sales. Then the Sales Director will see trends that associate with heat-waves and cold-snaps.

blog-post-26-2 Why Do You Need BI Reports in Addition to ERP's Canned Reports?

Fig 2: Detail BI Report

Pivot table reports are available in the tools, such as Microsoft SQL Server 2005 and above. However, due to computing complexity, increased processor time and other time consuming factors, such tools are often cumbersome to use. A similar report developed in a BI system will only take a few seconds to show results. Here is a case study on how we built additional reports in SSRS for an ERP system.

Due to the limitations of ERP systems, senior management uses spreadsheets for analysis and presentation. There are some challenges on sharing these spreadsheets with other members in the organization such as:

  1. There is no single version because a new sheet has to be created each time.
  2. It is difficult and time consuming to manually prepare the sheet using data from multiple applications/systems.
  3. There is a lack of standardization as different sheets are prepared by different team members.

These challenges are overcome by using a BI solution. Additionally, BI solutions using services, such as Microsoft SSRS, also provide custom report development functionalities, dashboards, data visualization, and drilldown capabilities.

BI vendors like Optimus provide report development as a managed service to the organizations with need for reports and dashboards on an ERP system. If you would like to learn more about our report development services, please feel free to contact us. We will be happy to sit down and discuss your needs.