tag:blogger.com,1999:blog-2379842053067321961.post4993292309320304435..comments2024-03-28T13:24:50.042-07:00Comments on Welcome To TechBrothersIT: SSRS Tutorial 90- How to Create SSRS Report When Number of Columns Can change Anytime in Source ObjectAamir Shahzadhttp://www.blogger.com/profile/16777994869678463807noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-2379842053067321961.post-65477493815140596592022-03-10T07:34:03.258-08:002022-03-10T07:34:03.258-08:00DECLARE @SQLString NVARCHAR(MAX);
SET @SQLString =...DECLARE @SQLString NVARCHAR(MAX);<br />SET @SQLString = N'SELECT r.desc_short as Exceptions, <br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date10, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date10, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date10,101) + '],<br />count(case when e.exception_status_key = 1 then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date0,101) + '], <br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date1, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date1, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date1,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date2, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date2, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date2,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date3, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date3, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date3,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date4, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date4, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date4,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date5, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date5, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date5,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date6, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date6, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date6,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date7, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date7, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date7,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date8, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date8, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date8,101) + '],<br />count(case when cast(e.created_dttm as date) < ''' + Convert(Varchar(10), @Date9, 101) +''' and (closed_dt is null or cast(closed_dt as date) >=''' + Convert(Varchar(10), @Date9, 101) +''' <br />) then e.exception_type_key end) as [' + CONVERT(VARCHAR(10),@Date9,101) + ']<br />from r_exception_type r<br />left join its_exception e on e.exception_type_key = r.exception_type_key<br />where active_flag =1 <br />group by desc_short';<br />Roopahttps://www.blogger.com/profile/15378318532552161497noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-55059398401062213472022-03-10T07:33:39.347-08:002022-03-10T07:33:39.347-08:00
DECLARE @date DATE=null
set @Date = coalesce( @D...<br />DECLARE @date DATE=null<br /> set @Date = coalesce( @Date , getdate() ) <br />DECLARE @Date0 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, 0, default, default, default)<br />DECLARE @Date1 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -1, default, default, default)<br />DECLARE @Date2 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -2, default, default, default)<br />DECLARE @Date3 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -3, default, default, default)<br />DECLARE @Date4 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -4, default, default, default)<br />DECLARE @Date5 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -5, default, default, default)<br />DECLARE @Date6 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -6, default, default, default)<br />DECLARE @Date7 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -7, default, default, default)<br />DECLARE @Date8 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -8, default, default, default)<br />DECLARE @Date9 DATE =[its].[custom_fnGetNextWorkdayByCalendar](@Date, -9, default, default, default)<br />DECLARE @Date10 DATE =[its].[custom_fnGetNextWorkdayByCalendar](DATEADD(yy,-1,@Date), 0, default, default, default)<br />Roopahttps://www.blogger.com/profile/15378318532552161497noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-61714659427819652342022-03-10T07:30:19.188-08:002022-03-10T07:30:19.188-08:00Hi I got a problem with dynamic query that the col...Hi I got a problem with dynamic query that the columns are dynamic I dont know how to give the values to the ssrs table.<br />My stored procedure is about the count of exceptions from past 10 days from the given date and the same date last year.<br />here is my code which gives the current data what I required but the problem is how can i convert that into report.<br />Exceptions 3/10/2021 3/10/2022 3/9/2022 3/8/2022 3/7/2022 3/4/2022 3/3/2022 3/2/2022 3/1/2022 2/28/2022 2/25/2022<br />053 - INVALID PERIOD END DATE 0 11 11 11 11 11 11 11 11 11 11<br />ENTITY TYPE MISMATCH 0 0 0 0 0 0 0 0 0 0 0<br />INVALID CRITICAL DETAIL RECORD 0 0 0 0 0 0 0 0 0 0 0<br />UNKNOWN DOCUMENT TRANSACTION TYPE CODE 0 0 0 0 0 0 0 0 0 0 0<br />REFUND REQUESTED ON AMENDED RETURN 0 0 0 0 0 0 0 0 0 0 0<br /><br /><br />Roopahttps://www.blogger.com/profile/15378318532552161497noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-50220842261968262062020-04-09T11:56:51.208-07:002020-04-09T11:56:51.208-07:00I created a dynamically scripted query that I then...I created a dynamically scripted query that I then put into a temp table and used the above to display it in SSRS. It worked great, except I don't seem to have any control over the sorting of the columns (they're alphabical instead of the order they're in the temp table) or the rows. Is there a way to control this?Anonymoushttps://www.blogger.com/profile/11417658975749955137noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-27589535184001459212019-08-14T02:14:05.290-07:002019-08-14T02:14:05.290-07:00i didn't get detaset valuei didn't get detaset value Anonymoushttps://www.blogger.com/profile/10385646230934433516noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-29636089417119671612019-04-09T08:11:31.661-07:002019-04-09T08:11:31.661-07:00Thanks It does work. However my data do have Dates...Thanks It does work. However my data do have Dates and cannot format those values as dates on the SSRS report. <br /><br />So the end client cannot use filter on dates fields which is vital for my report. any advise on formatting?Anonymoushttps://www.blogger.com/profile/02137507667335704740noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-50674399045255859232019-02-13T07:55:33.784-08:002019-02-13T07:55:33.784-08:00Hello brother. Your video is really helpful. Thank...Hello brother. Your video is really helpful. Thanks for the knowledge you are sharing with us. <br />I have a query brother if you can help me out here. <br />I have a web project. ASP.NET Webforms.<br />I have a table called employees. Now i want to add a functionality where a user select employee from dropdownlist and then that employee record should loads from database. and then the user select columnof their need and print only that columns. e.g if a user select employee name 'AFFAQ' then the whole record of affaq loads from data which has 20 columns i-e name, fatherName,CNIC,Contact,Email,address and so on..but the user only want to select the name,contact and cnic and want to print it on rdlc report. i hope you understand my question. please help me brother Anonymoushttps://www.blogger.com/profile/17663517391942721350noreply@blogger.com