none
C# Visual Studio 2010 ReportViewer

    Question

  • Hello,

    This is the scenario I am trying to do: (In VS 2010 local reportviewer)

    I have a table that has filter based on year (say 2012)..I am retrieving all data across all years from the DB and applying this year filter in reportviewer to display data specific to the year(say 2014). My report is embedded in a WinForm.

    Table has around 15 columns however one column has to display prior year's data (i.e., 2013). Since the table filter is set to 2014, I am unable to find a way to retrieve 2013's data to display.

    How do I achieve this? Please help!

    Thank you

    Tuesday, July 17, 2012 2:37 PM

Answers

  • Like lets say we have a BirthDayParty Table

    With the columns :(Primary key is UniquePersonIdentifier + Year)

    UniquePersonIdentifier

    Year

    PartyDate

    NumberOfPeopleAtParty

    If I want all of this year info + the number of people at last year party, I can do the following query

    Select bidp1.UniquePersonIdentifier, bidp1.Year, bidp1.PartyDate, bidp2.NumberOfPeopleAtParty

    from BirthDayParty bidp1

    join BirthDayParty bidp2

    on bidp2.UniquePersonIdentifier = bidp1.UniquePersonIdentifier

    and bidp2.Year = 2011

    Where bidp1.UniquePersonIdentifier = 'BobThePartyAnimal1234'

    and bipd1.Year = 2012

    Implying there are rows for BobThePartyAnimal1234 for the years 2011 and 2012.

    • Proposed as answer by Edward Zhu Thursday, July 19, 2012 1:38 AM
    • Marked as answer by Edward Zhu Tuesday, July 24, 2012 2:28 AM
    Wednesday, July 18, 2012 6:16 PM

All replies

  • Any suggestions please?
    Tuesday, July 17, 2012 3:31 PM
  • Can someone please provide suggestions please..any pointers would help
    Tuesday, July 17, 2012 8:56 PM
  • Well i guess that if you filter your data to only show the data from 2012, you cannot see the data from an other year and it's by design I think. Also, querying your whole database instead of just the rows you need will bring you some performance problems if your database gets big.

    I am really not sure to understand what you are trying to achieve. Could you clarify and maybe show your tablix/query?

    Tuesday, July 17, 2012 9:24 PM
  • Thanks for replying.

    First step is there's a form which takes year as input and should load the report details for that year.

    I use a LINQ query to retrieve data for all years and apply filter in reportviewer..I am not sure how to pass the year value entered by user in Winform to this LINQ query. Here's the LINQ query structure

                                                                       

       var fApps =
                    from f in BasicDataAccess.Data.FieldsList
                    join fA in BasicDataAccess.Data.FAppsList on f.ID equals fA.fieldID
                    ....several other joins (6 joins in total)

                    select new
                    {
                     //select a bunch of columns


                    };

                var nApps =
                    from f in BasicDataAccess.Data.FieldsList
                    join nA in BasicDataAccess.Data.NAppsList on f.ID equals nA.fieldID
                 
                    select new
                    {

                        //select a bunch of columns
                    };

                var result= fApps.Union(nApps).ToList()
                .toNonAnonymousList(typeof(<>));

                return (List<>)result;

    This query returns several hundred rows from which I am trying to filter the year entered and year entered -1 for a particular field.

    Since tablix has year set as filter, I am unable to retrieve data for year - 1 which is the issue. If you could help me figure out a better way to do this, that would be very helpful too.

    Thank you.



    • Edited by aqua267 Tuesday, July 17, 2012 9:47 PM
    Tuesday, July 17, 2012 9:45 PM
  • Hi There
    Thanks for your posting. I think you might be able to achieve this by using Boolean type condition. Please right click on your tablix go to properties and then go to filter and apply the Boolean filter as shown in the following figure

    Please have a look the screenshot for your help
    If you have any questions please ask
    Many thanks
    Syed Qazafi Anjum

    Wednesday, July 18, 2012 1:58 AM
    Moderator
  • If you only want your data for 2 years, change your linq to add a where clause!

    Taken from http://msdn.microsoft.com/en-us/library/bb397927.aspx

    var queryLondonCustomers = from cust in customers where cust.City == "London" || cust.City == "Paris"

    select cust;


    Wednesday, July 18, 2012 2:19 PM
  • Thank you for the detailed explanation. Your solution brought me a step further. Now the tablix has access to data from both years (say 2013 and 2014). However my next step is to filter data in each textbox(column) belonging to the table.

    For instance, table has columns such as a,b,c,d,e

    Values in a,b,d,e should display from year 2014 however column c should have value displayed from 2013 year (since it displays the information for previous year) . Can I do an and condition 

    i.e., populate values in the table only if the following condition is true...

    IIF(Fields!Year.Value = 2014, then populate the row, else dont populate a value)

    This gives me blank rows if value is 2013 however it shudnt create a row at all instead of creating a blank one.

    Is this even possible? 

    Wednesday, July 18, 2012 2:31 PM
  • In fact, your query should return the correct data and your Tablix should only display it.

    In your joins, on the table where you get your field that needs to come from the year 2013, add a join condition!

    (Implying the date comes from 2 different tables)

     var fApps =
                    from f in BasicDataAccess.Data.FieldsList
                    join fA in BasicDataAccess.Data.FAppsList on f.ID equals fA.fieldID and f.ColumnThatContainsYear = 2013

                   .....

                   where TheSecondTableWithTheYear.ColumnThatContainsYear = 2014

    If TheSecondTableThatContainsTheYear is the same table then the f table in the exemple, you need to join on a second instance of the said table.

    like

    from DataTable1 dt

    join TableThatContainsDateField ttcdf1 on dt.id = ttcdf1.Id and ttcdf1.ColumnThatContainsYear = 2014

    join whateverelseTables on ...

    join TableThatContainsDateField ttcdf2 on TableThatContainsFieldFromOtherYear.id = ttcdf2.Id and ttcdf2.ColumnThatContainsYear = 2013

    So when you will select your fields, you select your field from the other year in the table TableThatContainsFieldFromOtherYear.

    I know its not a super clean explanation, but I hope it will help you!



    • Edited by Alex Jean Wednesday, July 18, 2012 3:13 PM typo errors
    Wednesday, July 18, 2012 2:46 PM
  • Thanks Alex. I modified LINQ to add the filter. Thanks for pointing me in the right direction

    This is how my output should look like:

    Table

    ColA (data from 2014) name (data from 2013) name (data from 2014) colB (from 2014) and all other column values from 2014

    So the query that you pasted abve is modified to include both 2013 and 2014 year..

    var fApps =

                    from f in BasicDataAccess.Data.FieldsList
                    join fA in BasicDataAccess.Data.FAppsList on f.ID equals fA.fieldID and f.ColumnThatContainsYear in ( 2013, 2014)

                   .....

                   where TheSecondTableWithTheYear.ColumnThatContainsYear = 2014

    This gives me two sets of values one for 2013 and other for 2014 which I believe I have to filter in the report viewer table..Is there a better way of writing the SQL query so that I get both prior years value and current years value in the same row.. all data for 2014 + one column 'name' for 2013 is what I require..

    Thanks again for your help.

    Wednesday, July 18, 2012 4:15 PM
  • That was exactly what I was trying to explain you in fact hehe If you look at the part after the 'like'. You must make a join with a second instance of your table.

    But tell me your data structure and I'll write you a little SQL query.

    Like table names, column names, where the date filter must be done and the join fields.

    Wednesday, July 18, 2012 5:47 PM
  • Like lets say we have a BirthDayParty Table

    With the columns :(Primary key is UniquePersonIdentifier + Year)

    UniquePersonIdentifier

    Year

    PartyDate

    NumberOfPeopleAtParty

    If I want all of this year info + the number of people at last year party, I can do the following query

    Select bidp1.UniquePersonIdentifier, bidp1.Year, bidp1.PartyDate, bidp2.NumberOfPeopleAtParty

    from BirthDayParty bidp1

    join BirthDayParty bidp2

    on bidp2.UniquePersonIdentifier = bidp1.UniquePersonIdentifier

    and bidp2.Year = 2011

    Where bidp1.UniquePersonIdentifier = 'BobThePartyAnimal1234'

    and bipd1.Year = 2012

    Implying there are rows for BobThePartyAnimal1234 for the years 2011 and 2012.

    • Proposed as answer by Edward Zhu Thursday, July 19, 2012 1:38 AM
    • Marked as answer by Edward Zhu Tuesday, July 24, 2012 2:28 AM
    Wednesday, July 18, 2012 6:16 PM
  • Thanks very much. I got it working in SQL. Now trying to port to LINQ query. Thanks!
    Wednesday, July 18, 2012 8:03 PM