0.00000000000 as Text Error when exporting to Excel from SSRS.

Just a quick tip/fix for this problem. When exporting a report to excel, sometimes you'll end up getting a long text version of the number zero instead of the actual number -- causing issue and/or time correcting the issue for users.

The fix is simple, in this case, I just cast the numbers as decimal in my query forcing it to treat it as such.  (ex:  CAST(PCT AS DECIMAL) AS PCT  )

Running specialized CSV in SSRS with different device settings.

So this will be useful if you want to change the default settings for a single report, and not all reports, that are exported to CSV. An example, making semi-colon or tab delimited values.

A good read on bypassing the "CSV Device Information Settings" used on the reporting server.

http://blogs.infosupport.com/modify-reporting-services-export-to-csv-behavior/

Here are a list of the settings that can be changed in the url:
https://msdn.microsoft.com/en-us/library/ms155365%28v=sql.105%29.aspx


So instead of using the standard user-friendly interface, you will be using the file system interface, found in /ReportServer/Pages/ReportViewer.aspx?

and adding on your commands, just like you would do with parameters, to override the settings:

 &rs:Command=Render&rs:Format=CSV&rc:ExcelMode=true&rc:Qualifier=%22&rc:NoHeader=false&rc:FieldDelimiter=, 

Note:
This however, still leaves the problem with the qualifier. I was told that a csv file being sent to a third-party had to have quotation marks around each value (e.g. "First Name"). This doesn't seem possible in SSRS. The qualifier will only put quotes around a field if there was already a quote in the value (e.g. William "Big Bill" Andrus => "William "Big Bill" Andrus") which would also give incorrect usage of quotes if you hard code them (e.g. "First Name" => ""First Name""). And, there seems to be no way to force the qualifier to display. 

Mocking a user

After lots of searching and testing, I finally ended up with piece of code to mock a user for unit testing.

            this.Principal = new Mock<IPrincipal>();

            List<Claim> claims = new List<Claim>{
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", username), 
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier", userid)
            };

            var fakeIdentity = new GenericIdentity(username);
            //fakeIdentity.RemoveClaim(new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", ""));
            fakeIdentity.AddClaims(claims);

            var fakePrincipal = new GenericPrincipal(fakeIdentity, roles);
            Principal.Setup(n => n.Identity).Returns(fakeIdentity);

            Http.Setup(x => x.User).Returns(fakePrincipal);
            Http.Setup(x => x.User.Identity.Name).Returns(username);
            Http.Setup(x => x.User.Identity).Returns(fakeIdentity);

Find a field in SQL Server database

Sometimes you need to find a field and don't know what table or view it might be coming from. A very useful script to pull out:

SELECT b.name, c.name
FROM sys.schemas a              
JOIN sys.all_objects b ON b.schema_id = a.schema_id 
JOIN sys.columns c ON c.object_id = b.object_id 
WHERE c.NAME LIKE '%Find Field%'

Search for fields on ReportServer

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

/* * Get a list of reports in a specific path/folder */ ;WITH ItemContentBinaries AS ( SELECT ItemID ,Name ,[Type] ,CASE [Type] WHEN 2 THEN 'Report' WHEN 5 THEN 'Data Source' WHEN 7 THEN 'Report Part' WHEN 8 THEN 'Shared Dataset' ELSE 'Other' END AS TypeDescription ,CONVERT(varbinary(max),Content) AS Content FROM ReportServer.dbo.[Catalog] with (nolock) WHERE [PATH] LIKE '/Department Level/%' ) ---ADD WORDS, USED IN FIELDS, TO SEARCH FOR ,WordSearchCTE AS ( SELECT 'SSN' AS Word UNION ALL SELECT 'AGE' UNION ALL SELECT 'DOB' UNION ALL SELECT 'EPMP' UNION ALL SELECT 'PAY' UNION ALL SELECT 'COMP' ) --This CTE strips off the BOM if it exists... ,ItemContentNoBOM AS ( SELECT ItemID ,Name ,[Type] ,TypeDescription ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content))) ELSE Content END AS Content FROM ItemContentBinaries ) --The old outer query is now a CTE to get the content in its xml form only... ,ItemContentXML AS ( SELECT ItemID,Name,[Type],TypeDescription ,CONVERT(xml,Content) AS ContentXML FROM ItemContentNoBOM ) --now use the XML data type to extract the queries, and their command types and text.... --and only select from those that contain the search terms SELECT * FROM ( SELECT ItemID,Name,[Type],TypeDescription,ContentXML ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText FROM ItemContentXML --Get all the Query elements (The "*:" ignores any xml namespaces) CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query) )x JOIN ( select '%'+Word+'%' as pattern from WordSearchCTE ) w on CommandText like w.pattern

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.

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();
        }

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

 

 

 

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; }
    }