Post reply

Alias name for column data in the output

  • May 20, 2009 at 9:53 am

    #205063

    hi guys,

    i have two tables, i need an output in my report to be like i shown in the output below, can anyone help me

    table1:policyaudit

    -----------------------------------------------------------------------------------------

    NetworkID Username Officeid submittedform submitdate

    ------------------------------------------------------------------------------------------

    a1 abcFL/abc/officepolicy20.aspx2008-10-20 15:49:54.180

    b1 defDE/abc/officepolicy20.aspx2008-10-21 12:52:43.577

    c1 ghiDE/abc/officepolicy20.aspx2008-10-21 12:57:23.677

    d1 jkl NV/def/deptpolicy20.aspx 2008-10-21 14:40:05.503

    ------------------------------------------------------------------------------------------

    table2: policyalias

    ---------------------------------------------------

    Submittedform Alias

    --------------------------------------------------

    /abc/officepolicy20.aspxSecurity Policy

    /def/deptpolicy20.aspx Department Policy

    ---------------------------------------------------

    Question: how to select a statement in which i can display the columns from table 1(networkid, username,officeid,submitdate) and submittedform as alias in table2

    like the output should look like this:

    output:

    -----------------------------------------------------------------------------------------

    NetworkID Username Officeid submittedform submitdate

    ------------------------------------------------------------------------------------------

    a1 abcFLSecurity Policy2008-10-20 15:49:54.180

    b1 defDESecurity Policy2008-10-21 12:52:43.577

    c1 ghiDESecurity Policy2008-10-21 12:57:23.677

    d1 jkl NVDepartment Policy2008-10-21 14:40:05.503

    ------------------------------------------------------------------------------------------

  • May 20, 2009 at 10:08 am

    #997746

    Give this a try:

    select

    paudit.networkid,

    paudit.username,

    paudit.officeid,

    palias.Alias as submittedform,

    paudit.submitdate

    from

    dbo.policyaudit paudit

    inner join dbo.policyalias palias

    on (paudit.submittedform = palias.submittedform);

  • May 20, 2009 at 10:23 am

    #997754

    Thanks Lynn ..you are genious, it work the moment i just clicked..

    thanks a lot

  • May 20, 2009 at 10:29 am

    #997756

    You are welcome.

  • May 20, 2009 at 10:39 am

    #997762

    Lynn:

    I was needing this output for a report, i am getting the output i wanted in both the report and in management studio,

    but the issue is in my report i have two parameters, one is policyname and the second one is officeid

    if i select one kind of policyname like ex: security policy,

    i should get the details of that policy only but instead i am getting details of other policies also, how to sort this out in ssrs,

  • May 20, 2009 at 10:58 am

    #997776

    This could be done one of two ways...

    First, you could add the parameters to your where statement of your query to only bring back the details you want. This would probably be best if your have a large dataset but only need a few rows from it.

    Read more about it here. http://msdn.microsoft.com/en-us/library/ms156288.aspx

    Second, You could create a filter on your table object and filter it by your two columns.

    http://msdn.microsoft.com/en-us/library/ms157307.aspx

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply