date output format

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - How to create a SAPConnection in .Net Code with .NET Data Provider Correct! That really helped me out! Thanks. Tag: date output format
  - 2
    - What advanced features can be built with code in SQL Server 2005 Reporting Services? Hi, Andrew, Thanks a lot for your help. Very useful. Basically the functionalities I am looking for are : can we highlight any exceptions once some figures are out of normal values Can we drill down the reports to new page with different layout and columns from the page drill down happening Also, can we display series in different chart types (like I have 2 series, and I would like to display them in different chart formats within the same chart report), and also, can we have more than one scale displaying two different metrics in the same chart report (like if we display two different metrics, then they have very different value ranges, so it is better if we can have two different scales for these two metrics in the same chart report. Thank you very much. With best regards, Yours sincerely, Tag: date output format
  - 3
    - Accessing a real table from inside a TV UDF Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method. Tag: date output format
  - 4
    - Adding a article causes ALL articles to be refreshed I have tried using sp_article. The problem is that it gives me the error: Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot. And in order to avoid the error, if I use the Option of @force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything. BTW, What does IIRC mean Thanks, Amir Tag: date output format
  - 5
    - Making a report smaller...? You can put your table into multiple columns on the page by: From the main menu, select report|report properties select Layout tab Change the columns to 3 type 0in for spacing and .5in for the margins check it out in preview these instuctions are from the book SSRS2005 by Brian Larson, get your own copy from Amazon Tag: date output format
  - 6
    - Connect to SQL Server from a PDA You are mixing SQL security and integrated security - remove "Integrated Security=SSPI" from your connection string. Tag: date output format
  - 7
    - Installing Express toolkit Hi I am running XP Pro SP2. Tag: date output format
  - 8
    - SQL using 2003 AD Groups We have began to use Windows AUthentication for our SQL 2000 Server. When we llok atthe groups in AD we do not see any Domain Local groups Is there a reason for that Can I just add the group without browsing I assume DLG will work with SQL for security. Thanks Brent Tag: date output format
  - 9
    - How to find out PublicKeyBlob After you run the command, I think if you RightClick -> SelectAll, then hit the Enter key, it should copy the entire command prompt contents to the clipboard for pasting. Tag: date output format
  - 10
    - SQL Server Windows Auth for ETL called by CreateProcessAsUser Hi, I was already able to solve the double-hop issue for this. Though, I have problems. First, the ETL (or dtexec) does not run when the impersonated user is not part of the locla Administrators group and having the local privilege "Replace a Process Level Token". Having those rights assigned, the ETL runs but again fails because it can not connect properly. Based on my logs, the impersonated user is the "operator" or caller of the dtexec but when it tries to connect to SQL server, it does not use the impersonated identity but it uses "NT AUTHORITY/Anonymous Logon". Because of this the ETL call fails. I've researched some, can "cloaking" (COM) help here Thanks! Any help is appreciated! Tag: date output format
  - 11
    - deAttach database thanks , but do u know how can i stop the sql server service Tag: date output format
  - 12
    - Cannot add a new folder under a Reporting Server Project Hello, I was just wondering why is there no option to add a new folder under a reporting server project. I have a reports project and under it i would like to add subfolders like Daily and Weekly just to make it more organized. I understand that during deployment, we can specify to which folder we want the report(s) to be deployed, but what is the reason behind not having an option to add a new folder Thanks, maui Tag: date output format
  - 13
    - Generic calculated measure You can also create is as a calculated member in a seperate dimension called ValueType or something. I can't give you tested precise answer because i'm installing SP2 at hte moment, but for your example it would look something like create calculated member CurrentCube.ValueType.Ytd as sum(ytd(); It will display the Ytd of whatever measure is in context. If it should only apply to certain measures you could scope on those. Regards GJ Tag: date output format
  - 14
    - SSIS Randomly Empties Out Column Data While Using Flat File Source Seemingly it is the Object Model. I load the package using Apartment Threaded. LoadPackage([FilePath], null). The null sets it to using Apartment Threaded where as the other method uses Neutral Threaded. I am calling it through a webservice which shouldnt cause a problem, which means that all the code used to provide properties and set various variables inside the package is on the server. I want to see if I get any responses here on this issue. Opening a ticket with Microsoft is definitely my next bet. Tag: date output format
  - 15
    - Validating the parameter text box Hi, I have one date parameter in my report. The user will be able to manually enter or use the calendar control. In the case of entering manually, is there any way to validate the input string How can we prevent users from entering alphabets Also, is there any way to reduce the length of the parameter textbox. Can you please help Thanks, Sonu. Tag: date output format
  - 16
    - aggregation question Do you mean 'product' as in factorial Tag: date output format
- Next
  - 1
    - how to change the actual data filename i'm hoping someone can help me out with this problem. i have a database called Tracking.mdf and its associated logfile Tracking_log.ldf i need to build another database based on the above with more tables and keep the data separate hence the requirement to make a copy of the tracking data file i've tried changing its name but it will not attach. i can change its database name to whatever i want but somehow can't find out how i can say change its actual filename to Tracker.mdf and Tracker_log.ldf would someone be able to help me out. thanks. Tag: date output format
  - 2
    - MSResultSetGenerator does not work any more Just as a thought - have you tried running VS2005 as administrator and see if you have the same issue. Be warned VS2005 under Vista is still not officially supported as they haven't released the update yet - the beta fixes some, but definitely not all the Vista related issues. Tag: date output format
  - 3
    - Best Practice for Counts/Distinct Count Measures for Filtered Data Darren, I had a feeling that these 2 alternatives would not produce the same results when I was typing up the question. Perhaps my example scenario wasn't a good one, because the limited testing with my applicable situation yielded the same results. In any event, I will just go ahead and go forward with the separate measure group with the new Named Query approach. Thanks! Tag: date output format
  - 4
    - another dynamic assembly loading problem (XmlSerializer) In order to get Visual Studio to actually build a serialization assembly, you have to both 1. Set the "Generate Serialization Assembly" to ON, and 2. Add, by hand, the following SGEN task to your project file: < Target Name = " GenerateSerializationAssembliesForAllTypes " DependsOnTargets = " AssignTargetPaths;Compile;ResolveKeySource " Inputs = " $(MSBuildAllProjects);@(IntermediateAssembly) " Outputs = " $(OutputPath)$(_SGenDllName) " > < SGen BuildAssemblyName = " $(TargetFileName) " BuildAssemblyPath = " $(OutputPath) " References = " @(ReferencePath) " ShouldGenerateSerializer = " true " UseProxyTypes = " false " KeyContainer = " $(KeyContainerName) " KeyFile = " My_Key_File.snk " DelaySign = " $(DelaySign) " ToolPath = " $(SGenToolPath) " > < Output TaskParameter = " SerializationAssembly " ItemName = " SerializationAssembly " /> </ SGen > </ Target > There is, however, a major problem with this approach: The SGEN task will only execute AFTER all post-build events have fired. Thus, if you have a post-build event that copies the serialization assembly to, say, a deployment folder, it will copy the old one because the copy occurs before the SGEN task is executed. This problem is best resolved by running SGEN from a post-build event like so: sgen /a (ProjectDir)$(OutDir)$(ProjectName).dll /compiler:/keyfile (ProjectDir)My_Key_File.snk /f But this is where your problems really begin. The only way I have been able to get SQL CLR to actually load the serialization assembly is to apply the XmlSerializationAssembly attributes to my serializable classes AND load the serialization assemblies into the GAC and SQL CLR. I have described this in detail in a previous post and it is the ONLY way I am able to invoke XML serialization from within a SQL CLR procedure. Developing SQL CLR components is substantially more difficult than it needs to be due to issues just like this - and documentation is almost impossible to find for all but the most trivial use cases. I was very excited about the integration of SQL Server and .Net. After having worked a bit with this technology though, I am much less enthusiastic.... Tag: date output format
  - 5
    - How do you add multiple data sources to a report? The only way I've found to combine data from different data sources into a single dataset is to use techniques like OPENROWSET or use linked servers at the SQL Server level. RS cannot combine data from 2 data sources into one dataset and cannot internally join 2 separately retrieved datasets. Tag: date output format
  - 6
    - How to capture error upon connect error? I would like to continue processing when a connect error occurs. Set oServer = CreateObject("SQLDmo.SqlServer") oServer.LoginSecure = True oServer.Connect oName Would like to continue if the connect fails rather than have the script fail. How do I do that Thanks! Tag: date output format
  - 7
    - Server Upgrade We currently have a stand alone production server SQL 2005 STD + SP2 and plan to upgrade to clustered SQL 2005 Ent + SP2. What's the proper way to upgrade I want the existing server instance use Ent engine. Tag: date output format
  - 8
    - How to reflect base table changes in cursor Below is my complete code. It select the range of records from the base table and insert into a different table and then deletes those records from the base table but when i debuged the cursor i am still getting the value of time_encoede which should be deleted. declare @pgname nvarchar(20) declare @videorelease nvarchar(40) declare @dateaired nvarchar(15) declare @dateencoded nvarchar(15) declare @timeencoded nvarchar(15) declare @network nvarchar(12) declare @count int declare localshowcursor cursor for SELECT video_release, programname, network, date_encoded, time_encoded, COUNT(*) AS Number FROM SigmaStageTemp s Where program_type = бо1бп Group by video_release, programname, network, date_encoded, time_encoded Having count(*) >= 10 Order by video_release, programname, network, date_encoded, time_encoded OPEN localshowcursor FETCH NEXT FROM localshowcursor INTO @videorelease, @pgname, @network, @dateencoded, @timeencoded, @count WHILE @@FETCH_STATUS = 0 BEGIN Insert into sigmtemp(Video_release, country, market_rank, Designated_mrkt_area, station, network, date_aired, day_of_week, day_part, half_hour_aired, programname, program_type, start_time, end_time, length_aired, date_encoded, time_encoded, rel_type, VR_CODE1, VR_CODE2, VR_CODE3, VR_CODE4, DMA_CODE, sid_code, station_code) (Select top(1) Video_release, country, market_rank, Designated_mrkt_area, station, network, date_aired, day_of_week, day_part, half_hour_aired, programname, program_type, start_time, end_time, length_aired, date_encoded, time_encoded, rel_type, VR_CODE1, VR_CODE2, VR_CODE3, VR_CODE4, DMA_CODE, sid_code, station_code from sigmastagetemp s1 where s1.video_release = @videorelease and s1.programname = @pgname and s1.network = @network and s1.date_encoded = @dateencoded and convert(int,s1.time_encoded) between convert(int,@timeencoded) -2 and convert(int,@timeencoded) +2 Order by video_release, programname, network, date_encoded, time_encoded ) Delete from sigmastagetemp where video_release = @videorelease and programname = @pgname and network = @network and date_encoded = @dateencoded and convert(int,time_encoded) between convert(int,@timeencoded) -2 and convert(int,@timeencoded) +2 FETCH NEXT FROM localshowcursor INTO @videorelease, @pgname, @network, @dateencoded, @timeencoded, @count END CLOSE localshowcursor DEALLOCATE localshowcursor Tag: date output format
  - 9
    - Memory allocation error while processing cubes I am using Analysis Services 2005 Enterprise Edition with (to my knowledge) the latest service pack, version 9.00.2047.00. Tag: date output format
  - 10
    - Performing insert / update queries using Pocket PC / SQL CE The SDF file's held on the storage card as opposed to the program files with the rest of the application. The same happens for any update/insert queries I apply to the database using Query Analyser, they're simply not there when I restart the program... Tag: date output format
  - 11
    - Page Break stops working after adding condition in Hide property I don't think so that both will not work simultaneously...Any other solution..idea.. from any one is appriciated... -Thanks, Digs Tag: date output format
  - 12
    - REPLACE Arguments If you are going to reuse it, a more compact form is something like: substring (' Yes', 1+3*(UserLevelId/5), 3) as in the example: declare @test table ( UserLevelId integer ) insert into @test values (1) insert into @test values (2) insert into @test values (3) insert into @test values (4) insert into @test values (5) select UserLevelId, substring (' Yes', 1+3*(UserLevelId/5), 3) as Indicator from @test -- UserLevelId Indicator -- ----------- --------- -- 1 -- 2 -- 3 -- 4 -- 5 Yes Tag: date output format
  - 13
    - How to schedule to run Integration Packages using SQL Agent/Job? Hi Garynkill23, This could be due to the ProtectionLevel setting for the individual packages - that's my guess. By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense When the job you created executes, it runs under the SQL Agent Service logon credentials. My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that). One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them. There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials. Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch. Hope this helps, Andy Tag: date output format
  - 14
    - Question on joins May be this.. Code Snippet Create Table #tab1 ( [id] int ); Insert Into #tab1 Values ( '1' ); Insert Into #tab1 Values ( '3' ); Insert Into #tab1 Values ( '4' ); Create Table #tab2 ( [id] int ); Insert Into #tab2 Values ( '1' ); Insert Into #tab2 Values ( '3' ); Create Table #tab3 ( [id] int ); Insert Into #tab3 Values ( '4' ); Insert Into #tab3 Values ( '7' ); Insert Into #tab3 Values ( '8' ); Insert Into #tab3 Values ( '9' ); Create Table #tab4 ( [id] int ); Insert Into #tab4 Values ( '1' ); Insert Into #tab4 Values ( '5' ); Insert Into #tab4 Values ( '9' ); Code Snippet Select Id , Case When DuplicateAt & 1 = 1 Then 'Y' Else '' End [With tab1] , Case When DuplicateAt & 2 = 2 Then 'Y' Else '' End [With tab2] , Case When DuplicateAt & 4 = 4 Then 'Y' Else '' End [With tab3] , Case When DuplicateAt & 8 = 8 Then 'Y' Else '' End [With tab4] From ( Select Id , Sum ( TabId ) DuplicateAt from ( Select Id , 1 TabId from #tab1 union all Select Id , 2 from #tab2 union all Select Id , 4 from #tab3 union all Select Id , 8 from #tab4 ) as data group By Id having Count ( Id ) > 1 ) as data Tag: date output format
  - 15
    - microsoft visual studio cannot shut down because a modal dialog is active I'm also having this annoying nag :( Didn't find any pattern but i think it my have something to do with source safe when it works with reporting services... Tag: date output format
  - 16
    - how to cgange cell-borderstyle at runtime Hi, how can i change the cell-borderstyle during runtime Tag: date output format

VSempoux

Hi,

I have the following problem.

I have made a parametered report where i ask a begin date (@datefrom) and an end date (@dateto).

when i enter my dates via the little calendar is see the format dd/mm/yyyy

When i do a preview of report i have a textbox with the following expression :

="Total work time from " & Parameters!DateFrom.Value & " to " & Parameters!DateTo.Value

but the output of the date format is mm/dd/yyyy.

What can i do to have the output format dd/mm/yyyy

Vincent

Top

Re: date output format

Markus Schechner

Hi Vincent,

Try to use

="Total work time from " & CDATE(Parameters!DateFrom.Value).ToString("dd/MM/yyyy") & " to " & CDATE(Parameters!DateTo.Value).ToString("dd/MM/yyyy")

Top

Re: date output format

VSempoux

Hi,

Thanks for the quick response.

This works great.

Greetings

Vincent

Top