Here is a tsql technique where your dataset returns individual sales and a comment that tests whether a sale exists in the prior two months.

You'll need to adapt to your...

• This reply was modified 1 year, 10 months ago by  Chrissy321.

Can you share the query used in the dataset of the report?

Check SSRS caching-https://learn.microsoft.com/en-us/sql/reporting-services/report-server/set-report-processing-properties?redirectedfrom=MSDN&view=sql-server-ver16

This should get you started.

SELECT
C2.Name AS Data_Source_Name,
C.Name AS Dependent_Item_Name,
C.Path AS Dependent_Item_Path
FROM ReportServer.dbo.DataSource AS DS
INNER JOIN ReportServer.dbo.Catalog AS C
ON DS.ItemID = C.ItemID AND DS.Link IN (SELECT ItemID FROM...

Thanks Martin, That certainly looks viable. Since I am more of a TSQL rather than a SSIS proponent I probably will go in this direction.

--https://www.mssqltips.com/sqlservertip/2790/dynamic-xmla-using-tsql-for-sql-server-analysis-services/

DECLARE @myXMLA NVARCHAR(MAX)

SET...

As a test you may want to add 'N/A' to FullName and see what happens.

In the sql query you can uses COALESCE. COALESCE(FullName, 'N/A')

Are you grouping by FullName in the...

Yes a unique index can be created on Entity, Attribute, and StartDate and yes this is a Slowly Changing Dimension format. Refined code and data below.

At this point my performance...

Thanks Jeff

There is not a UNIQUE column in the table to identify individual rows with. Its an EAV slowly changing dimension.

DROP TABLE IF EXISTS #Test

CREATE TABLE #Test

(

Entity VARCHAR(20),

Attribute VARCHAR(800),

AttributeType VARCHAR(10),--This...

Thank you both for replying. I apologize for my delayed response, I though I would get an email when follow-ups were posted.

I am trying to validate data during an ETL...

Following up.

My questions where really about the use of the SUBSTRING parameters start and length but after your hint and a little further study the use has become clear to...

I am moving ahead with technical review/testing of code based on what you provided so yes. Thanks. When I have a bit more...

Conversion failed when converting date and/or time from character string.

The modified script I posted updates the data structure and will generate the error when run.

Thanks Jeff this looks fantastic! I truly appreciate the time you took to put this together.

I am having some issues converting to my actual data structure. Apologies, I need to...

Hello Again,

Still working on this issue. My previous requirements to have a view and no CTEs no longer apply.

I am close to a working solution but I'd like to clean...

Adding PARTITION BY ID to the SELECT INTO works with data I provided. In my real data I am getting the first value rather than the previous. Larger issue is...