Comments (0)

By David Petersen, December 13, 2019 12:19 pm

The other day, I was asked to make an Excel spreadsheet that was populated by a SQL stored procedure parameterized. This spreadsheet is supposed to run each month to analyze the previous month’s sales and commissions. I wrote a stored procedure that automatically pulled the previous month’s data. Now, they wanted to specify the Start Date and End Date.

I searched all over the internet on how to do this, and while I am not sure that my solution is the most efficient, I decided to write this up to help anyone who might have this problem, or if there is a better way to do it.

The report is called Monthly Financial Analysis and pulls data from an SQL database stored procedure I wrote called usp_GetEndOfMonthSalesMaster. This stored procedure will accept a Start Date and an End Date, but if not supplied, automatically selects the previous month’s data.

In the Excel spreadsheet, I created on Query that called the stored procedure. To get the parameters, I created a second sheet that only contains the parameters. I created a named Table that encompassed the parameters.

PowerQuery uses the Power Query M formula language to create the query in the Advanced Editor

In the my Query, I used the Advanced Editor. I started by selecting the Parameters table as a Source and then got to the data via the Table.TransformColumns command. Once I had the Extracted data, I set variables dtStartDate and dtEndDate via the Date.ToText function, as illustrated below so I could include them in the SQL Query.

Calling the stored procedure takes place in the Sql.Database connection command. After specifying the server and database name. In the options of the method, you can provide the Query text. This is where you can use the date variables.

This took me a while to figure out. I didn’t even know there was a PowerQuery M formula language to use so I had to try to understand how that worked. If anyone has a better way to do this, please provide comments so we can all learn from your expertise.

Comments (0)

By David Petersen, November 8, 2016 9:32 pm

Last year, I decided that I wanted to learn different ways to create web applications.  The two major candidates were Angular 1.x and ReactJS.  I went through some courses on Pluralsight and other training sites for both.  After “playing” around with both, I can honestly say that I prefer ReactJS over Angular.  Angular 2.0 looks promising, but I think some of the same reasons I choose React over Angular still exist:

1. Angular supplements HTML with its own attributes and puts javascript in HTML.  React is straight Javascript.
2. You have to learn a whole framework with Angular.  With React, I can choose my own ecosystem.
3. One thing I read, is that because React is just javascript, it teaches you to be a better JavaScript programmer.
4. React is focused on doing one thing and being the best at it.  It makes UI’s fast!  The virtual DOM just makes sense to me.
5. Data flow in React is one way.  It flows down from the top and state is passed back up.  That makes sense to me.

I’ve been developing React apps on NodeJS but I’m a SharePoint Developer.  I kept wondering if I could write Single Page Apps (SPA) for SharePoint.

Basically, my requirements were:

1. Write a ReactJS app that would compile down to a static JavaScript file that I could embed in a Content Editor Web Part.
2. Use SharePoint’s REST interfaces to access the SharePoint objects.
3. Since this would be hosted on SharePoint, I didn’t need to worry about cross-domain authentication.
4. Use React Router to control app routing.
5. Create a workflow that would allow me to easily develop and deploy these apps to SharePoint.

With the recent guidance from Microsoft about the SharePoint Framework, I thought this was the perfect time to attempt to write a React app on SharePoint.

After a lot of research, I found a couple of things.

There is a project called ReactJS.NET that makes using React with .NET easier.  It focuses on the .NET MVC pattern.  I looked at that, created some demos but it didn’t fit my requirements.  I didn’t want to have to deploy compiled C# code to a SharePoint server.

I decided to start rolling my own solution.  I chose

• ReactJS
• NodeJS
• Babel (to transpile JSX)
• npm (to load the node packages and run scripts
• Webpack to bundle them into static files

I was trying to put this all together when someone recommended that I check out the team at Facebook Incubator’s create-react-app bootstrapper.  After looking through the demos. Installing it myself and creating some quick (albeit small) apps and getting them to run on SharePoint, I think I have found my solution.

A couple of challenges.

1.  How do I develop on NodeJS without connecting to the SharePoint REST interfaces?
2. How do I deploy the created app to SharePoint.

The answer to the first question was to create a “dummy” REST interface for development.  I created the lists that I needed in SharePoint and then used Postman to call the REST interfaces and grab the data that was returned.  I copied that data object into my React App as the data source and then created an API module that would use the local data instead of calling SharePoint.  Since we can request a JSON object from SharePoint using the Accept header, it was easy to recreate the API necessary using array methods.  Once the app was developed using my dummy API, I substituted my real API that actually called the SharePoint REST endpoints.

The second question posed more of a problem.  I planned to put my app on a web part page by referencing it in a content editor web part.  For years I have been storing custom code in the Site Assets library.  You could also create a CDN to host your app code.  Either way, your development workflow needs to be able to copy the static files from your production build, to SharePoint.  My original solution was to upload them via the web interface.  This worked but it was too cumbersome.  My next solution was to map a drive to Site Assets folder in SharePoint.  This works some of the time, but this also is cumbersome.  I recently read about an npm module called spsave.  I’m going to try to work it into my workflow.  This should allow me to automatically deploy my files to SharePoint when I run the build command.

I put together a presentation about my work to get React to work in SharePoint.  It’s still a work in progress.  I delivered the presentation at SharePoint Saturday – Kansas City on October 1 and then again in Minneapolis for SharePoint Saturday – Twin Cities.  My slides are linked below but the best thing to do would be to look at the code of the Contacts app I created for the presentation.  It’s a simple app but demostrates the use of a dummy API to develop with.  The github link to the code is below also.

I plan on continuing to improve my demo and adding to it.  I think I’m going to add some tests and I still need to add a delete function.  If you are interested, go ahead and star my repository.  If you find an error in the code, or a better way of doing something – I’d love to hear about it!  Remember, I’m still learning so be kind!

ReactJs SPA’s on SharePoint

https://github.com/dipetersen/ReactOnSharePoint

spstc2016-reactjs-on-sharepoint

Comments (0)

By David Petersen, October 7, 2016 3:46 pm

I just uploaded the slides to the two presentations I did at SPS-KC on October 1, 2016.  You can find them on the SPSKC site under Sessions, or you can get the links on my Presentation Notes page.

Comments (0)

By David Petersen, January 1, 2016 9:48 pm

If you notice the previous posts, it’s been since 2014 since I have been doing any writing on this blog. I have been a little busy. I will write more in detail abou what I have been up to in a future post, but until then, know that I have not stopped doing SharePoint Consulting, or doing some cool things in SharePoint and Office365 with other web technologies.

In future posts, look for topics like:

1.  One page apps using AngularJS and SharePoint REST.
2. One page apps using ReactJS and SharePoint REST.
3. Easy ways to display list data using jQuery.
4. Interesting SharePoint layouts using Bootstrap!

Also, I am still the leader of the Omaha SharePoint User Group. I am in need of speakers for our monthly meetings. If you want to present at our meeting, contact me and we’ll get you on the schedule.

One more thing. Our 4th Annual SharePoint Saturday will be held on April 9, 2016. More information on that will be coming but it goes without saying that we are looking for speakers for that conference also.

It’s good to be back. I missed you.

Comments (0)

By David Petersen, January 27, 2014 4:05 pm

I recently made the decision to go to the SharePoint Conference in LasVegas in March.  My company won’t pay for it, so I will be funding this trip on my own.  With my responsibilities as a leader of the Omaha SharePoint User Group, and an organizer of SharePoint Saturday – Omaha, I feel it is extremely important to be there.  So,  I made my reservations and I’m on my way.  I have already covered my flight and lodging.  I think I can get transportation to the strip, and from there I can walk or take the rail.  I’ll eat fast food for my meals.

Thanks to the fine folks at KWizCom, I now have admission into the Exhibitors Hall.  Everything I need! Check out KWizCom’s website to see all of the components they have to use on your SharePoint site.

Dave

Comments (0)

By David Petersen, January 27, 2014 3:35 pm

Washington University

I had a wonderful time speaking at SPS St. Louis on January 11, 2014. I spoke on the same topics as I did in Phoenix. I did battle the demo gods, as my code, which I have used over and over again, did not work in St. Louis. There were others having problems with their demos, so I’m going to blame it on the WiFi and not my code. I was able to show them a working copy that I had on my SharePoint site online.

The 6 1/2 hour drive wasn’t that bad either. So, where to next? I had to skip SPSUtah event that I was scheduled to speak at. I had a prior commitment at my daughters school that I forgot about. The next time that I can speak will be in May at SPS Chicago Suburbs. I haven’t been selected to speak yet, but I am sending in my abstracts.

Comments (0)

By David Petersen, October 31, 2013 3:54 pm

I’m very excited to be speaking at SharePoint Saturday – Phoenix on November 23, 2013.  This is the furthest WEST I have presented and I am excited.  I love Phoenix and I have visited and worked here a few times over my career.  I think HDR even has an office or two here.  For this conference, I will be presenting BI: From the desktop and beyond… and my presentation on an Org Chart Mashup using jQuery, SharePoint, web services and the Google Charting API.  This should be fun and I’m looking forward to it!

Comments (1)

By David Petersen, October 22, 2013 2:03 pm

I was just notified that I will be presenting at SharePoint Saturday – Kansas City on November 9, 2013.  I believe I have presented at every SPSKC that they have put on.  Having lived in KC and with relatives still there, it is my second home.  I wish I had a job that allowed me to work down there periodically.  Anyway, I don’t know exactly what presentations I will be doing down there but as soon as I find out, I will update this post.  If you have any questions, or want to make a caravan down to KC to attend this conference, let me know.  I think it would be cool to have a large turnout from Omaha.

Hope to see you there!

Comments (2)

By David Petersen, October 10, 2013 9:01 am

SharePoint Saturday-Twin Cities has to be my favorite conference to speak at.  The group that runs it is always professional and they really demonstrate how to run a SharePoint Saturday.  I used them as a resource when I started planning SharePoint Saturday – Omaha.  The lessons I learned from attending SPSTC and from their leaders helped make SPSOmaha a success.

So, what will I be speaking about this time?  My presentation is, “Microsoft BI: From the desktop and beyond…”.  I’m really excited about this presentation because I am really excited about the Microsoft BI platform.  When I first saw a demo of PowerPivot, PowerQuery, PowerMap and PowerView, I saw the possibilities that this could mean for BI at my company.  No longer would my users have to wait 6 months to get a BI report.  The people that knew the data could be the same ones analysing the data.  My users could create rich dashboards and reports on their own, limited only by their imaginations.

But, do you know what the best thing that turned me on about this platform?  We already owned it – we just didn’t know it!  In fact, I’m sure there were users already using PowerPivot on their desktops without even realizing that they could extend their expertise to the team or enterprise.  The more I thought about it, I realized that anyone who had an Enterprise license of SharePoint had access to the same great technology, and maybe didn’t even know about it.  So that’s what prompted me to start creating this presentation.  I read a lot.  I attended many BI presentations, both in person and online.  Then, I got access to the PowerBI preview.  There it was – a website that I could test and demo what I was so excited about.

If you are going to SPSTC and want to learn more about the Microsoft BI platform, come to my session at 3pm.

Comments (0)

By David Petersen, April 3, 2013 4:52 pm

I love my Drobo 2nd Gen so much that I just ordered a new 5N. Now I find out that the folks at Geekbeat.TV are giving away 4 Drobo Mini’s! How do you win? Go to http://onemansblog.com/drobomini and follow the instructions to enter.  #DroboLove