About Me

My Photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Sunday, November 09, 2014

Powershell Profile Setup and Resize script on startup

1)Setup your profile for powershell: 

if (!(test-path $profile )) 
{new-item -type file -path $profile -force} 

2)Open up the profile:
Notepad $profile
 
3)Cut and paste resize into notepad and save: 
 
##
## Author   : Roman Kuzmin
## Synopsis : Resize console window/buffer using arrow keys
##

function Size($w, $h)
{
    New-Object System.Management.Automation.Host.Size($w, $h)
}

function resize()
{
Write-Host '[Arrows] resize  [Esc] exit ...'
$ErrorActionPreference = 'SilentlyContinue'
for($ui = $Host.UI.RawUI;;) {
    $b = $ui.BufferSize
    $w = $ui.WindowSize
    switch($ui.ReadKey(6).VirtualKeyCode) {
        37 {
            $w = Size ($w.width - 1) $w.height
            $ui.WindowSize = $w
            $ui.BufferSize = Size $w.width $b.height
            break
        }
        39 {
            $w = Size ($w.width + 1) $w.height
            $ui.BufferSize = Size $w.width $b.height
            $ui.WindowSize = $w
            break
        }
        38 {
            $ui.WindowSize = Size $w.width ($w.height - 1)
            break
        }
        40 {
            $w = Size $w.width ($w.height + 1)
            if ($w.height -gt $b.height) {
                $ui.BufferSize = Size $b.width $w.height
            }
            $ui.WindowSize = $w
            break
        }
        27 {
            return
        }
    }
  }
}
 
 
4) Change policy for the profile, exit powershell and open a new version "Run as Administrator":
Set-ExecutionPolicy bypass -Scope CurrentUser
 
 
5) Close powershell and open a new version, type resize.

Thursday, October 23, 2014

SSRS: Setting a value dynamically for an unknown count of distinct values -- AND -- having a legend outside of a chart


One of the tricks that I do, is removing the legend out of charts into a table. To do this, I need to explicitly set the colors and/or markers for each category. In this example, to do this dynamically, I use this basic statement with a list of colors -- where the number of possible results back maybe be up to 4 distinct values.

=Choose(RunningValue(Fields!Unknown.Value,CountDistinct,Nothing),
"#0000e7",
"#626297",
"CornflowerBlue",
"#2f52a6")

So, in this example I put a sparkline in the table with no category or series group -- forcing it to only display one color marker. The logic that I used in the chart's marker and fill color are then also used in the sparkline, giving a legend outside of the chart. 



If need be, you can just create a chart with the name and color/marker sparkline and move your legend anywhere you want.

Change Username for ClaimsIdentity in C#


I needed a way to change the authentication's username in a website. This basically applies the change, and then signs the user out, change session info and cookie info, and then back in  -- so that username change is displayed right away.


[HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> ChangeUserName(ManageUsernameViewModel model)
        {
            bool hasPassword = HasPassword();

            if (hasPassword)
            {
                if (ModelState.IsValid)
                {

                    //Get UserID
                    var userID = User.Identity.GetUserId();
                    var currentUserName = User.Identity.GetUserName();
                    var newUserName = model.NewUserName;

                    //Check if new username already exisits
                    if (UserManager.FindByName(model.NewUserName) != null)
                    {
                        ModelState.AddModelError(string.Empty, "That username already exist.");
                        return RedirectToAction("Manage", new { Message = ManageMessageId.DuplicateUserName });
                    }

                    var user = UserManager.Find(model.OldUserName, model.Password);
                    if (user != null)
                    {
                        //Change username
                        if (DataAccess.userDataAccess.updateUserName(model.NewUserName, userID))
                        {

                            AuthenticationManager.SignOut();
                            await SignInAsync(user, false);

                            var identity = new ClaimsIdentity(User.Identity);
                            identity.RemoveClaim(identity.FindFirst(identity.NameClaimType));
                            identity.AddClaim(new Claim(identity.NameClaimType, model.NewUserName));
                            AuthenticationManager.AuthenticationResponseGrant = new AuthenticationResponseGrant(new ClaimsPrincipal(identity), new AuthenticationProperties { IsPersistent = false });                            

                            return RedirectToAction("Manage", new { message = ManageMessageId.ChangeUserNameSuccess });
                        }
                        else
                        {
                            AddErrors(new IdentityResult("Failed updating your username."));
                        }
                    }
                    else
                    {
                        ModelState.AddModelError("", "Invalid username and password.");
                    }
                }
            }

            // If we got this far, something failed, redisplay form
            return View();
        }

Tuesday, October 21, 2014

Optimizing SSRS Rendering and Performance

I often run into the situation of SSRS taking too long to render or a long delay before processing starts. Here are 3 links provided by Microsoft that might help in solving those inefficiencies.

Troubleshooting Reports: Report Performance

http://msdn.microsoft.com/en-us/library/bb522806.aspx  


Exporting Reports

 http://msdn.microsoft.com/en-us/library/ms157153.aspx

 

Understanding Rendering Behaviors

http://msdn.microsoft.com/en-us/library/bb677573.aspx

 

 





 

Saturday, October 11, 2014

Somewhat Dynamic Pivot in C#

I started working on a dynamic pivot to be used in Kendo UI, where the user could select the datapoints to be shown in the graph. Didn't quite finish, but here is some of the code, didn't want to waste/lose it; since, the concept was interesting.


Code:
public class StageDataPoints
    {
        public List<StageDataPoint> dataSeries { get; set; }

        public List<ExpandoObject> pivot()
        {
            var datapoints = dataSeries.Select(n => n.DataPoint).Distinct().ToList();

            var sdpGroup = from d in dataSeries
                           group d by new { d.DateTimeGrainValue, d.Stage, d.SubStage }
                               into grp
                               select new
                               {
                                   Key = grp.Key,
                                   Dict = grp.ToDictionary(n => n.DataPoint, n => n.DecimalValueAvg)
                               };

            List<ExpandoObject> defactoObjs = new List<ExpandoObject>();

            foreach (var row in sdpGroup)
            {
                dynamic r = new ExpandoObject();
                r.DateTime = row.Key.DateTimeGrainValue.ToString("M/d/yy h:mm");
                r.Stage = row.Key.Stage;
                r.SubStage = row.Key.SubStage;

                double value = double.NaN;
                string tempDP = "";

                foreach (var dp in datapoints)
                {
                    tempDP = dp;

                    foreach (var idp in row.Dict)
                    {
                        if (idp.Key == dp)
                        {
                            value = idp.Value;
                            break;
                        }
                    }
                    //Console.Write(dp + " " + value.ToString());
                    ((IDictionary<string, object>)r).Add(dp, value);
                }
                defactoObjs.Add(r);
            }
            return defactoObjs;
        }

        public List<StageDataPointMinify> rpt()
        {
            List<StageDataPointMinify> ret = new List<StageDataPointMinify>();
            foreach (var x in dataSeries)
            {
                ret.Add(new StageDataPointMinify()
                {
                    //Convert datetime to javascript verion
                    DateTimeGrainValueStr = x.DateTimeGrainValue.ToString("M/d/yy H:mm"),
                    DateTimeGrainValue = x.DateTimeGrainValue,
                    DataPoint = x.DataPoint,
                    DecimalValueAvg = x.DecimalValueAvg
                });
            }
            return ret;
        }
    }

    public class StageDataPoint
    {
        //public DateTime CalendarGrainValue { get; set; }
        //public string ClockGrainValue { get; set; }
        public DateTime DateTimeGrainValue { get; set; }
        public string Stage { get; set; }
        public string SubStage { get; set; }
        public string DataPoint { get; set; }
        //public Guid CJobGuid { get; set; }
        public string JobName { get; set; }
        public double DecimalValueAvg { get; set; }
        //public double DecimalValueMin { get; set; }
        //public double DecimalValueMax { get; set; }
    }
 

Tuesday, March 18, 2014

Pull what reports were ran and by whom.

A very basic query to look at the ReportServer database to pull what reports there are, who has ran it, and how many times.

Code Snippet
  1. SELECT
  2. c.NAME,
  3. el.UserName,
  4. el.LATEST_RUN_DATE,
  5. el.NUM_TIMES_RAN,
  6. c.[path]
  7. FROM
  8. DBO.[CATALOG] AS c
  9. LEFT JOIN
  10. (
  11.     SELECT
  12.     EL.REPORTID
  13.     ,EL.UserName
  14.     ,COUNT(EL.TIMESTART) NUM_TIMES_RAN
  15.     ,MAX(EL.TIMESTART) AS LATEST_RUN_DATE
  16.     FROM
  17.     dbo.EXECUTIONLOG AS el
  18.     GROUP BY
  19.     el.REPORTID, EL.UserName
  20. )el on el.ReportID = c.ItemID
  21. WHERE
  22. C.[TYPE] = 2
  23. ORDER BY c.NAME, el.UserName

Tuesday, February 04, 2014

SSRS Report Width Limit Error

Well, you can filed this error in the "probably won't ever happen to me" section, but this happen to me today. In SSRS there is a Width limit of 455 inches for the report section.

The error:

The value of the Width property for the report section 'ReportSection0' is "476.32297in", which is out of range. It must be between 0in and 455in.



This report, that I'm creating is very unique, in that the user can select from a list of views, and then select fields from those views. The report lists out all the fields, but only displays the selected fields. It's an interesting concept of allowing the user to access data via SSRS, mainly since the department isn't allowed to have cubes, data marts, etc... this ends up being the best solution, for now. 

Tuesday, January 28, 2014

Query to help create RDL

Usually, I would use the SSRS wizard to quickly create my table with all my fields. In this case I have to add more fields onto an existing report that has my custom formatting of each column (eg. showing/hiding columns).

So for a quick solution, I developed this ad hoc query that I can then use to pull the fields I would need to add from a view into the report. This ends up being a lot of copy & paste actions into the xml (view code) of the rdl.

 
CREATE PROCEDURE [hrr].[usp_RDL_ColumnField_XML]

(

       @view varchar(50)

)

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

 

       --DECLARE @view varchar(50)

       --SET @view = 'V_EVALUATIONS'

      

--COPY & PASTE INTO DATASET TO ADD MORE FIELDS

SELECT

' + c.NAME  + '">

       ' + c.NAME  + '

       ' +

       CASE t.NAME

       WHEN 'varchar' THEN 'System.String'

       WHEN 'int' THEN 'System.Int32'

       ELSE 'true'

       END +'

' as 'COPY & PASTE INTO DATASET TO ADD MORE FIELDS'
FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

---COPY & PASTE INTO COLUMN SECTION  --

SELECT

'

       1.5in

' as 'COPY & PASTE INTO COLUMN SECTION  -- '
FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

 

---COPY & PASTE INTO FIRST TABLIX CELLS HEADER --

SELECT

'

      

              + c.NAME + '">

                     true

                     true

                    

                          

                                 

                                        

                                                ' + REPLACE(c.NAME,'_',' ') + '

                                               

                                        

                                 

                                 

                          

                    

            Textbox' + c.NAME + '

           

                          

                           SteelBlue

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' as 'COPY & PASTE INTO FIRST TABLIX CELLS HEADER -- '
FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

---COPY & PASTE INTO FIRST TABLIX CELLS Rows --

SELECT

'

      

              +c.NAME+'">

                     true

                     true

                    

                          

                                 

                                        

                                                =Fields!'+ c.NAME + '.Value

                                               

                                        

                                 

                                 

                          

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' AS 'COPY & PASTE INTO FIRST TABLIX CELLS Rows -- '
FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

 

--- COPY & PASTE INTO Tablix Column Hierarchy section --

SELECT

'

      

              =IIF(INSTR(JOIN(Parameters!fields.Value,","),"' + b.NAME + '.' + c.NAME+'") > 0,FALSE,TRUE)

      

' AS 'COPY & PASTE INTO Tablix Column Hierarchy section -- '
FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

END

 

 

GO