What's The (Share)Point?

Tag: query

  • Adding Parameters To Excel SQL Query

    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.

    Parameters Table

    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.

    PowerQuery M formula language 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.

  • Why does making a Lookup site column an indexed field break my CAML Query?

    I created a custom timer job definition that processes email-enabled list items. Since these list items were created from email, I extract certain email header information from the email and save that information as meta-data.

    One of those site columns is called EmailSource. This is a Lookup field using the Title field of the EmailSource list as the value. My timer job queries the main list to find all list items where the EmailSource IsNULL and then I extract information from the email headers, and then find the appropriate EmailSource from the EmailSource list and write it to the EmailSource field in the main list.

    It has been working pretty slick for the last month or so. Recently, I thought that I would try to improve the response of the main list views by setting the EmailSource site column as an indexed column. Since most of the views are created filtering on the EmailSource field, I figured that this would be an ideal candidate for indexing. As soon as I set the EmailSource site column as an indexed column, my query for IsNull started returning 0 (zero) items, even when I could see that there were list items that didn’t have an EmailSource value.

    My query is:

    <Query>
      <Where>
        <IsNull>
          <FieldRef Name='EmailSource' />
        </IsNull>
      </Where>
    </Query>

    Now – normally, when the EmailSource field is NOT set as an indexed column, this query would return all list items where EmailSource didn’t have a value. As soon as I set EmailSource as an indexed column, the query would always return zero items, even though I knew it should return some list items.

    As soon as I removed that field from the indexed columns list, the query began to work correctly again.

    So – I ask you. Why would setting a field as an indexed column break a query?!!!!