none
Creating a date limited report using Report Builder 3.0 with a Sharepoint List as datasource RRS feed

  • Question

  • Hi

    I'm hoping someone can help with this - I'm probably just doing something very silly (I'm new to SharePoint/Reporting/Infopath/XML etc. Be gentle with me).

    So... the background is:

    I've set up a sharepoint site which has an Infopath form on it, which users enter data into and it all gets saved in a sharepoint list.

    What I'm trying to do is create a report that displays the data from that sharepoint list in a useful manner. A key part of making this useful is that I need to be able to filter the data by using date parameters. The report needs to give users the ability to select a parameter (branch), then use a date picker to select a to and from date, so that when they hot run, the report brings back the results from that region/branch that were created between the dates selected. For the purposes of this report though, I'm only using the 'Branch' parameter to begin with.

    What I've done so far:

    • I've created a report in Report Builder 3.0 that has the sharepoint list as its data source. I don't want the information from every column in the list, so I've set up a dataset that has all of the fields that are of interest to me.
    • I've also set up another dataset for for 'Branch' and added it as a parameter (I was informed that this needed to be in a separate dataset, so that it could populate the dropdown on the report before the actual report on the main dataset was actually run). When I run the report at this point it works fine - I select a branch from the list, and I can see the sharepoint list entries generated by users from that branch.
    • Being derived from a SharePoint List, obviously one of the columns I have is 'Created' which is the date/time formatted column I want to use for my parameter. I've created two parameters (which I think I've set up correctly to use this 'Created' field as their source) - one called 'Datefrom' and one called 'Dateto', and this is where I'm getting lost.

    Essentially what I think I need is a between statement. Or a way of saying Ggreater than' Datefrom and Less than 'Dateto' would obviously do the same job. I know how to write these in SQL but not in XML. I've been flailing about trying to get this working, but all I seem to be able to generate is a succession of annoying error messages :) These include error messages about only being able to use a datasource once, trying to use a field as a parameter, trying to use a parameter as a parameter (!?) and whole host of others. I've tried mucking about with the parameter settings, the dataset settings, trying to apply filters to one of them, then both of them. It's fair to say, I've been making a total pig's ear of it.

    Is what I'm trying to do actually possible? Does anyone have advice on how to achieve my goal?

    Any help greatly appreciated, and sorry for the noobness. I'm living proof that a having a little bit of technical knowledge is a dangerous thing. If any more info is required, let me know.

    Friday, August 31, 2012 1:45 PM

Answers

  • Hello

    The language used with SharePoint List Connection Type is called Collaborative Application Markup Language (CAML).
         SharePoint List Connection Type (SSRS)
         http://msdn.microsoft.com/en-us/library/ee633650.aspx#Query
    To implement a filter between to dates you could use some combination of "greater than or equal to" (GEQ), "less than or equal to" (LEQ), "greater than" (GT), "less than" (LT) for the two datetimes.
    You can find the reference here:
         Gt Element (Query)
         http://msdn.microsoft.com/en-us/library/ms458990.aspx
         Lt Element (Query)
         http://msdn.microsoft.com/en-us/library/ms479398.aspx
         Geq Element (Query)
         http://msdn.microsoft.com/en-us/library/ms416296.aspx
         Leq Element (Query)
         http://msdn.microsoft.com/en-us/library/ms431787.aspx

    For example the WHERE tag could look something like this:
                ….
                <Where>
                  <And>
                    <Geq>
                      <FieldRef Name="Datefrom" />
                      <Value Type="DateTime">
                        <Parameter Name="Datefrom" />
                      </Value>
                    </Geq>
                    <Lt>
                      <FieldRef Name="Dateto" />
                      <Value Type="DateTime">
                        <Parameter Name="Dateto" />
                      </Value>
                    </Lt>
                  </And>
                </Where>
                ….


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thanks,
    Orsi
    Microsoft Online Community Support

    Thursday, October 11, 2012 12:49 PM