____ JK - BI Blog ____

How to update column values in table based on values from two tables: Some time we need to update values in a table based on the join from other table e.g. for a customer calculate and update data values for Order values in from Item Price and Order table. below is an idea for doing this... Create Main table to be updated: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimCustOrderValue] ( [CustID] [int] NULL, [CustomerName] [nchar] ( 20 ) NULL, [OrderValue] [numeric] ( 18 , 2 ) NULL ) ON [PRIMARY] GO --Create other required tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimItemOrder] ( [OrderId] [int] NULL, [ItemId] [int] NULL, [CustID] [int] NULL [OrderQuantity] [int] NULL, ) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimItemPrice] ( [ItemID] [int] NULL, [ItemName] [nc...

How to Rename a SSRS Report Project Solution: Overview: As a developer many times we come across scenarios where we have created a Report Project solution with a specific name e.g. "Reports_Dev". after some time say when the development is complete, we might think to rename the same Project solution to "Reports_Production" Let’s see how to do it... Solution: 1.         Create a copy of the SSRS Project Solution 2.         Now find the following items: Solution Folder Name:  Reports_Development Files in this Folder:                 Reports_Development.sln  (this a .sln file)                 Reports_Development.suo  (this is a hidden .suo file)            ...

SQL Server Reporting Services 2012 has added new rendering options for Excel and Word to support the office 2007 -2010: Now it Supports for new file formats: ·           Excel renders for  Excel 2007-2010 (*.xlsx) o      For more info check:  http://msdn.microsoft.com/en-us/library/dd255234(v=sql.110).aspx ·           Word render for  Word 2007-2010 (*.docx) o      For more info check:  http://msdn.microsoft.com/en-us/library/dd283105(v=sql.110).aspx Below are new features with this new file format support: 1.         Feature equivalence with existing word and excel renders 2.         ZIP-compressed files – now easy to export and share the files 3.         Larger worksheets in Excel ·        ...

SQL Server 2012 is launched, with great new features like columnstorage index, sql azure reporting, power view, tabular model etc. check it on- http://www.sqlserverlaunch.com

Report Model: 1. Field Alignment:  Apply the Alignment properly for all the fields as per the Requirement i.e. Code we can set the Center align, Text (Address, Names, and Description) we can set the Left align, Currency, financial data we can align Right. 2. Fields Default Sorting: Define the fields default sorting column to sort the data. 3. Format for the values: Define a proper format for the fields values i.e. for Number use N0, for Percentage use P0 etc. 4. Use Proper Naming conventions for the fields in the Report Model. it should be user friendly Names which represent the proper link to the information in the field. 5. Remove unnecessary aggregations on the numeric fields which are of no use. i.e. for a code in int we can remove the avg. of code which we know that is will not give any significant result. 6. Only include the necessary tables in the Model. To remove to complexity of the model use the Named Query to fetch the data for a particu...

Page Setup: This Property have a very high importance when we need to print the report or export the report to the hard page break formats like PDF, word etc. For every Report as a best practice we should do proper page set so that the report items are properly displayed while printing or exporting the PDF or word etc. Let’s see an example how to do it: 1. Enable the Ruler in the design 2. Open the Report Properties 3. By default the page size is width-8.5in Height: 11in, now you have to set the page size as following: Take the width of the design using ruler and add the margin into that and set this value as the width of the page. E.g. width in the design is showing as 15in and margin is set at 1in left and right  so total width of the page is 17in (15in+1in+1in). Set this amount in the page width and when you run the report it will display the objects properly on the PDF or Print etc. View of the report before setting the page setup in the report propert...

Shared Data Source: As a best Practice we should use a Share data source in all our Reports as far as possible. benifits of doing this are: 1. We can Move all our Reports from one environment to another with less efforts. 2. No Need to concern about handling so many Data sources for the Reports. 3. Shared Data source is easy to handle in the Reports, Just change the Database or server Name and all your Reports will pointing to the new Data. 4. Coding Standards also will take few efforts for the Data Source. 5. Less Confusion about the Source Data when using the Shared Data Source i.e from which data base my reports are fetching data? etc. Happy Learning!

Which Report Link we can share to the End-Users: Once we have done with the coding and testing for the Report, it is ready for the deployment to the Report server and will be available to share the link with the End-users. We can share a Report manager report link with the end-user or we can share a Report viewer link. Let’s see how these two links works: Report Manager Link e.g http://%3c%3cservername./Reports/Pages/Report.aspx?ItemPath=%2f<FolderName>%2f<ReportName > Report viewer link e.g http://%3c%3cservername%3e%3e/ReportServer/Pages/ReportViewer.aspx?%2f<FolderName>%2f<ReportName>&rs:Command=Render After analyzing these two snapshot for the same report open from the Report Manager and Report Viewer following are the observations: 1.  in the Report manager Report link we are able to see the links like Home, folders etc. but in the Report Viewer Report link these links are not visible on the report...

I have created a report subscription for a Report. after that I was thinking how does it works automaticaly and sends the file to the define format. I found that when we create a report subscription then it creates a Job in the SQL Server Agent with the details which we have set in the report scheduling for the subscription. So whenever the perticular schedule criteria meets, this job gets executed which triggers the report to run and delivers the report out put in the defined format and to the define location or users. so in the job agent also we can change the schedule for the Report subscription if required. Happy Learning!