none
Get a table to automatically Increment

    Question

  • First of all forgive for my non-technical background i am fairly new to SSRS and everything i have learnt so far is from forums.

    I have a created a SQL query which takes information with regards to various parts of a journey, orders them by date and then assigns a sequence number, like below:

    From the table you can see that some columns are not relevant to the part of the journey taking place.

    So my SSRS issue is...

    I'd like the sequence to remain as it is however have 5 seperate tables, only showing their relevant columns.

    My current work around is having 5 seperate tables, I then filter to sequence no. =1 , 2, 3 , 4, 5 etc. I then use the column visibity function to say that if the column value is N/A or Null then hide the column.

    The problem with this work around is that there can be up to 10+ legs to a journey, so my question is...

    Is there a way to have one table which can then look at the sequence number and create 5 different tables each with their own relevant columns?

    Tuesday, November 26, 2013 9:12 AM

Answers

  • Hi GMACH-10,

    If I understand correctly, it seems that you want to divide one table into several individual tables based on the sequence in Reporting Services. And the separated tables should only show their relevant columns without NULL and N/A column. After testing it in my local environment, we can drag the table into a list to separate the table, and then use lookup function to control the column visibility. For more details, please see:

    1. Drag a list to the design surface, drag the table into the list.
    2. Right-click the list to open Tablix Properties dialog box, select the current dataset in the drop-down list.
    3. Click the list, Right-click the (Details) group to open the Group Properties dialog box, add a group grouped by the [Sequence].
    4. Using the expression below to control the Column Visibility of Arrival_Date column:
      =iif(isnothing(lookup(Fields!Sequence.Value,Fields!Sequence.Value,(Fields! Arrival_Date.Value),"DataSet1")),false,true)
    5. Using the similar expression (just change the Arrival_Date field into other fields) to control other columns.

    If you have any more questions, please feel free to ask.

    Regards, 
    Katherine Xiong 


    Katherine Xiong
    TechNet Community Support

    Wednesday, November 27, 2013 2:28 PM

All replies

  • >>>to say that if the column value is N/A or Null then hide the column.

    I think you cannot hide the entire column as sequence 2 could be NULL but for sequence 4 there is a value over there.

    I am not sure that understood your question. Is that possible to post sample data + desired result?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 26, 2013 10:26 AM
  • Make your question more clear.

    From my understanding based on one table entry you want to create 5 different table as per the squence number?

    Tuesday, November 26, 2013 10:37 AM
  • Sorry for not being so clear its a very tricky one to describe.

    So the ultimate aim is to have 5 indivual tables. These 5 tables will be produced from one table in the SSRS desgin.

    So within the designer i have a single table:

    which when executed returns 5 tables in order based on the sequence:

    As it stands i have 10 tables within the designer. Each table filters to a different sequence number, i.e. the top table is sequence 1, the bottom is sequence 10. This seems a real waste or resources though.

    The ultimate aim is to create a chronological order of the journey elements which can then be used to create an itinerary.



    • Edited by GMACH-10 Tuesday, November 26, 2013 12:31 PM
    Tuesday, November 26, 2013 12:26 PM
  • Hi GMACH-10,

    If I understand correctly, it seems that you want to divide one table into several individual tables based on the sequence in Reporting Services. And the separated tables should only show their relevant columns without NULL and N/A column. After testing it in my local environment, we can drag the table into a list to separate the table, and then use lookup function to control the column visibility. For more details, please see:

    1. Drag a list to the design surface, drag the table into the list.
    2. Right-click the list to open Tablix Properties dialog box, select the current dataset in the drop-down list.
    3. Click the list, Right-click the (Details) group to open the Group Properties dialog box, add a group grouped by the [Sequence].
    4. Using the expression below to control the Column Visibility of Arrival_Date column:
      =iif(isnothing(lookup(Fields!Sequence.Value,Fields!Sequence.Value,(Fields! Arrival_Date.Value),"DataSet1")),false,true)
    5. Using the similar expression (just change the Arrival_Date field into other fields) to control other columns.

    If you have any more questions, please feel free to ask.

    Regards, 
    Katherine Xiong 


    Katherine Xiong
    TechNet Community Support

    Wednesday, November 27, 2013 2:28 PM
  • I would personally take a different approach. The separate tables means you have to interject different header rows in the midst of the data making it difficult to follow. I would stick with the single to table and massage the data and/or header labels.

    For instance, instead of calling it Depart_Date, call it Starting. That aligns to departure date/time for a flight and a check-in date/time for hotel. It applies to the hired car and even the taxi. Change Arrival date to Ending. For the value of the ending column, use a formula that checks for null and substitutes the starting field value:

    IIf(IsNothing(Fields!Arrival.Value), Fields!Depart.Value, Fields!Arrival.Value)

    The assumption here is that the Taxi ride will start and end on the same date. You can also change Depart From and Destination to Start Location and End Location. I would also resequence those so the immediately follow the appropriate date/time field (Starting or Ending). I notice that your Accomodation row doesn't have data for either of these which is puzzling since a hotel has to be somewhere. Your multi-table view confirms this. So, again, use formulas to massage the data. Check the type of entry. If accomodation, display Hotel add value for both Start Location and End Location (hotels don't usually move :)). If flight, use Dept From for Start Location and Destination for End Location. Something like this:

    =Switch(Fields!Type.Value = "Accomodation", Fields!HotelAdd.Value, Fields!Type.Value = "Flight", Fields!DeptFrom.Value, Fields!Type.Value = "Taxi", Fields!DeptFrom.Value, Fields!Type.Value = "Hire Car", Fields!Pickup_DropOff.Value)

    I would take the other fields and use expression to combine them into a Comments column. For flight rows, comment would be Terminal, Airline Flight and Booking ref concationated with ", " separators (I would probably drop Airline since the same code appears to be in the flight no info making it redundant). You would use another switch statement to concatenate different fields depending on type. I won't reproduce the whole statement here but follow the example for Start Location above. It would start something like:

    =Switch(Fields!Type.Value = "Flight", "Depart Terminal - "+Fields!Terminal.Value+", Flight # - "+Fields!Flight_no.Value+", Ref # - "+Fields,Booking_Ref.Value, Fields!Type.Value = "Accomodation"...

    All of this is what I would do. You may wish to structure it differently but I think that the multiple tables with multiple headers creates confusion. Massage your data and the labels instead.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Wednesday, November 27, 2013 3:31 PM