Monday, November 6, 2017

Get the list of AdHoc queries from SSRS reports on Report Manager

Dear All,

Sometimes it is difficult to see the Adhoc queries in reports which are deployed on Report Manager. If there is any performance issues with sql query in SSRS report, you have to download the report from Report Manager and Open the RDL file in Visual Studio. You have to do this manually for each report, if you have any query issues in more than one report. But, there is a way where we can write a query to retrieve all Adhoc queries in all SSRS reports which are currently deployed and LIVE !!!

Please find the below queries as per SSRS Versions available:

-- SSRS 2016
-- RUN THE QUERY ON REPORT SERVER DATABASE
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' AS REP),
     ListAdhocSSRS2016Version
     AS (
     SELECT c.Path AS Path_Name,
            c.Name AS Report_Name,
            DataSetXML.value('@Name', 'varchar(MAX)') AS DataSource_Name,
            CASE DataSetXML.value('REP:Query[1]/REP:CommandType[1]', 'varchar(MAX)')
                WHEN 'StoredProcedure'
                THEN 'Stored Procedure'
                ELSE 'Adhoc'
            END AS CommandType,
            DataSetXML.value('REP:Query[1]/REP:CommandText[1]', 'varchar(MAX)') AS CommandText
     FROM
     (
         SELECT ItemID,
                CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportXML
         FROM dbo.Catalog
         WHERE Type = 2
     ) AS ReportXML
     CROSS APPLY ReportXML.nodes('//REP:DataSet') AS DataSetXML(DataSetXML)
     INNER JOIN dbo.Catalog AS c ON ReportXML.ItemID = c.ItemID
     WHERE DataSetXML.value('REP:Query[1]/REP:CommandType[1]', 'varchar(MAX)') IS NULL)
    
 SELECT *
     FROM ListAdhocSSRS2016Version;


Monday, October 23, 2017

Get FirstDay and LastDay of Current year - SQL Server

To get first day of current year and get last day of year and some other formats:



SELECT
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS LastDayOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0) AS FirstOfNextYear,
   DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS LastTimeOfYear,
   CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM],-- Current Month
   REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY] --Current Month Name




Wednesday, September 13, 2017

Living In Denmark - Good Resources

Hi Everyone, 

Good evening !!!

It must be hard for anyone who is moving to Denmark and start a new life. Here, you can find good links for understanding Denmark :)

http://www.expatindenmark.com/LivingInDenmark/Pages/default.aspx

http://www.dejligedayswelcome.com/free-resources.html

http://ihcph.kk.dk/

https://www.facebook.com/groups/expatsincopenhagen/

https://www.nyidanmark.dk/en-US/

http://careercph.blogspot.dk/

http://workandlife.dk/gettingstarted/

https://dejligedays.com/

https://www.thelocal.dk/

http://cphpost.dk/

https://www.rejseplanen.dk/

http://www.scandinaviastandard.com/30-useful-websites-for-surviving-and-thriving-in-denmark/

https://www.facebook.com/WAU-Copenhagen-630088340355657/?ref=ts

http://www.saeson-web.dk/frugt-groent-i-saeson/

https://www.hittegods.dk/index.php/en/

http://foderengros.dk/

https://dk.trustpilot.com/

https://minetilbud.dk/

http://skat.dk/skat.aspx?oid=3099

http://skat.dk/

https://www.just-eat.dk/

https://www.yelp.com/københavn

Hope this post helps!!!

Have a nice stay in Denmark :)



Monday, May 1, 2017

SSRS - Change the Width of Bars in charts

Hi All,

I am trying to develop a chart with monthly interval. When ever there is less data for only 3 or 4 months, bars are displaying with more size as per default width size. Looks like below:


I want to make sure, width should be always same when ever there is more months data or less month data. We can do modify width of bars with below property:

1. Select chart bars --> F4 properties
2. Go to "CustomAttributes" --> Check PointWidth option
3. Increase or decrease the value. As I want to decrease the size, I changed it from 0,8 to 0,4 value



Below is the image, chart looks after modifications. This is a simple property to remember ;-)


Happy designing with SSRS :)



Thursday, April 27, 2017

SSAS MDX - UNION 3 MDX query results as a single MDX query result

Hi All,

I was trying to write 3 MDX queries and UNION of all 3 results with different filters or conditions. I couldn't find a solution as I am not expert in writing MDX. But I learned something which is interesting , it is possible to write it in MDX by using "+ Union" operator.
Below is the link for more details

https://docs.microsoft.com/en-us/sql/mdx/union-mdx-operator-reference

I asked for a help in MSDN Analysis Forum and got answer. Complete post link for your reference.

MDX query below is an example, how we can write 3 MDX queries in to a single result set.

MDX-1:
SELECT  
NON EMPTY { [Measures].[Actual], [Measures].[Forecast], [Measures].[Goal], [Measures].[Required] } ON COLUMNS, 
NON EMPTY { ([Date].[Year].[Year].ALLMEMBERS * [Date].[Month Year].[Month Year].ALLMEMBERS * [Date].[YYYYMM].[YYYYMM].ALLMEMBERS *  [Date].[Date].[Date].ALLMEMBERS * [KPI Type].[KPI Code].[KPI Code].ALLMEMBERS ) * [KPI Type].[KPI Name].[KPI Name].ALLMEMBERS } 
ON ROWS FROM 
( SELECT ( StrToMember("[Date].[Year].[Year].[" + Format(now(), "yyyy") + "]") ) ON COLUMNS 
FROM ( SELECT ( { [KPI Type].[KPI Code].&[1] } )  ON COLUMNS 
FROM [XXXXXXXXXX])) 


MDX-2:
SELECT 
NON EMPTY { [Measures].[Actual], [Measures].[Forecast], [Measures].[Goal], [Measures].[Required] } ON COLUMNS, 
NON EMPTY { ([Date].[Year].[Year].ALLMEMBERS * [Date].[Month Year].[Month Year].ALLMEMBERS * [Date].[YYYYMM].[YYYYMM].ALLMEMBERS *  [Date].[Date].[Date].ALLMEMBERS * [KPI Type].[KPI Code].[KPI Code].ALLMEMBERS ) * [KPI Type].[KPI Name].[KPI Name].ALLMEMBERS } 
ON ROWS FROM 
( SELECT ( StrToMember("[Date].[YYYYMM].[YYYYMM].[" + Format(now(), "yyyyMM") + "]") ) ON COLUMNS 
FROM ( SELECT ( { [KPI Type].[KPI Code].&[2] } ) 
ON COLUMNS FROM [XXXXXXXXXX])) 


MDX-3:
SELECT  
NON EMPTY { [Measures].[Actual], [Measures].[Forecast], [Measures].[Goal], [Measures].[Required] } ON COLUMNS, 
NON EMPTY { ([Date].[Year].[Year].ALLMEMBERS * [Date].[Month Year].[Month Year].ALLMEMBERS * [Date].[YYYYMM].[YYYYMM].ALLMEMBERS *  [Date].[Date].[Date].ALLMEMBERS * [KPI Type].[KPI Code].[KPI Code].ALLMEMBERS ) * [KPI Type].[KPI Name].[KPI Name].ALLMEMBERS } 
ON ROWS FROM 
( SELECT ( StrToMember("[Date].[Year].[Year].[" + Format(now(), "yyyy") + "]") ) ON COLUMNS 
FROM ( SELECT ( { [KPI Type].[KPI Code].&[3] } )  ON COLUMNS 
FROM [XXXXXXXXXX])) 

Solution for the above 3 MDX queries in to a single query.

SELECT 
 NON EMPTY { [Measures].[Actual], [Measures].[Forecast], [Measures].[Goal], [Measures].[Required] } ON COLUMNS,
 NON EMPTY

 {StrToMember("[Date].[Year].[Year].[" + Format(now(), "yyyy") + "]")
  * [Date].[Month Year].[Month Year].ALLMEMBERS
  * [Date].[YYYYMM].[YYYYMM].ALLMEMBERS
  *  [Date].[Date].[Date].ALLMEMBERS
  * [KPI Type].[KPI Code].&[1] 
  * [KPI Type].[KPI Name].[KPI Name].ALLMEMBERS }

  +
{[Date].[Year].[Year].AllMembers
 * [Date].[Month Year].[Month Year].ALLMEMBERS
 * StrToMember("[Date].[YYYYMM].[YYYYMM].[" + Format(now(), "yyyyMM") + "]")
 *({StrToMember("[Date].[Date].[Date].[" + Format(now()-15, "yyyy-MM-dd") + "]") :
 StrToMember("[Date].[Date].[Date].[" + Format(now(), "yyyy-MM-dd") + "]")  })
 * [KPI Type].[KPI Code].&[2] 
 * [KPI Type].[KPI Name].[KPI Name].ALLMEMBERS }

  +
  {StrToMember("[Date].[Year].[Year].[" + Format(now(), "yyyy") + "]")
  * [Date].[Month Year].[Month Year].ALLMEMBERS
  * [Date].[YYYYMM].[YYYYMM].ALLMEMBERS
  *  [Date].[Date].[Date].ALLMEMBERS
  * [KPI Type].[KPI Code].&[3] 
  * [KPI Type].[KPI Name].[KPI Name].ALLMEMBERS }

 ON ROWS
 FROM [XXXXXXXXXX]