SCENARIO: I need to generate a Sales Report for a Country and be able to drill down to the individual States. How can I achieve this in SSRS? SOLUTION: Yes, you will be able to create a Report with drill down features using SSRS. In this tutorial, let us look at the steps involved to create an SSRS Drill down Report. STEP 1: Click here, and follow Steps 1, 2, 3, 4...

Posted on Apr 11 2014 - 2:07pm by admin
#0

FORMAT is a built-in function introduced in SQL Server 2012 and SQL Azure, which returns a value with a format that is provided. SYNTAX: FORMAT([Value to be formatted], [Format required]) EXAMPLE: Let us consider a phone number 1234567890. We need to convert it into a format like (123)-456-7890. Prior to the introduction of Format, to do this, we need to write...

Posted on Apr 7 2014 - 11:26am by admin
#2

One of our visitors here at SQL SAGA, wrote me via the contact page if I can publish an article about using Maps in SSRS. Here is a detailed article on how to create a report using Maps in SSRS. SCENARIO: I have a query that pulls all the Sales related to each state per year. Can I represent this information in a Map? SOLUTION: Yes, apart from the regular grids and...

SCENARIO: One of our readers wrote me : “I have a table with a bunch of columns along with an Identity(Auto Incrementing Number) Column. Now that there is some wrong data, I had to delete those records but the identity value doesn’t get reset. It starts from where it was left. Is there something I can do to reset that value?”. SOLUTION: Yes, you...

SCENARIO: Can I use an SSIS Package as a data source for my report I am building through SSRS? I don’t see SSIS as a data source under the list of Data sources, How do I use SSIS as a data source? SOLUTION: Yes, you can use an SSIS package as a data source for your report built through SSRS. I believe, a very beginner level person working on SSIS / SSRS might...

SCENARIO: I have a source table that looks similar to my Destination table. My Source table will have any new records or updates that I should move to my Destination table. I don’t want to use any cursors or Truncating the table and reload is not an option because my Source will have only any New or Updated data. Can I do that using T-SQL? SOLUTION: Absolutely...

SCENARIO: I have a spreadsheet with PIVOTED data. My table structure will allow the unpivoted version of the data. Is there a way in SSIS I can unpivot the data while I load it. SOLUTION: The answer is Yes, you can unpivot the data and load it to a destination you need to. When I read this question from one of our readers, I thought I would create a post about that...

SCENARIO: I have a Source Shared folder where the Business people actually drop the files and I need to copy / move those files to the Server using SSIS. Is this achievable? SOLUTION: Yes, we can either Copy / Move / Delete or perform many other tasks on files present in a location using SSIS. We use the File System Task to do the job for us. In this Solution I am...

SCENARIO: My Application passes two integer parameters @Year and @Month. Will I be able to convert them to the beginning of the month in the YYYY-MM-DD format? SOLUTION: Yes, By using conversion techniques we can achieve this. Please read the rest of the post to understand this better. First let us declare 2 Scalar Variable’s for this. DECLARE @Year INT =...

Posted on Feb 12 2014 - 4:54pm by admin
#0

Date dimension is one of the most important dimensions in any OLAP cube created through SSAS or any BI solution created. Every calculation, be it Sum or the Count, we make through the help of measures is some how or the other tied to date. Date dimension is responsible for Year to Date Calculations or any such hierarchies defined. We populate this dimension once so...